Recommendations for modernizing the Microsoft SQL Server platform
Mon, 03 Aug 2020 16:08:49 -0000
|Read Time: 0 minutes
This blog follows Introduction to SQL Server Data Estate Modernization, the second in a series discussing what’s entailed in modernizing the Microsoft SQL server platform and recommendations for executing an effective migration.
SQL Server—Love at First Sight Despite Modernization Challenges
SQL Server is my favorite Microsoft product and one of the most prolific databases of our time. I fell in love with SQL Server version 6.5, back in the day when scripting was king! Scripting is making a huge comeback, which is awesome! SQL Server, in fact, now exists in pretty much every environment—sometimes in locations IT organizations don’t even know about.
It can be a daunting task to even kick off a SQL modernization undertaking. Especially if you have End of Life SQL Server and/or Windows Server, running on aging hardware. My clients voice these concerns:
- The risk is too high to migrate. (I say, isn’t there a greater risk in doing nothing?)
- Our SQL Server environment is running on aging hardware—Dev/Test/Stage/Prod do not have the same performance characteristics, making it hard to regression test and performance test.
- How can I make the case for modernization? My team doesn’t have the cycles to address a full modernization workstream.
I will address these concerns, first, by providing a bit of history in terms of virtualization and SQL Server, and second, how to overcome the challenges to modernization, an undertaking in my opinion that should be executed sooner rather than later.
When virtualization first started to appear in data centers, one of its biggest value propositions was to increase server utilization, especially for SQL Server. Hypervisors increased server utilization by allowing multiple enterprise applications to share the same physical server hardware. While the improvement of utilization brought by virtualization is impressive, the amount of unutilized or underutilized resources trapped on each server starts to add up quickly. In a virtual server farm, the data center could have the equivalent of one idle server for every one to three servers deployed.
Fully Leveraging the Benefits of Integrated Copy Data Management (iCDM)
Many of these idle servers, in several instances, were related to Dev/Test/Stage. The QoS (Quality of Service) can also be a concern with these instances.
SQL Server leverages Always On Groups as a native way to create replicas that can then be used for multiple use cases. The most typical deployment of AAG replicas are for high availability failover databases and for offloading heavy read operations, such as reporting, analytics and backup operations.
iCDM allows for additional use cases like the ones listed below with the benefits of inline data services:
- Test/Dev for continued application feature development, testing, CI/CD pipelines.
- Maintenance to present an environment to perform resource intensive database maintenance tasks, such as DBCC and CHECKDB operations.
- Operational Management to test and execute upgrades, performance tuning, and pre-production simulation
- Reporting to serve as the data source for any business intelligence system or reporting.
One of the key benefits of iCDM technology is the ability to provide cost efficient lifecycle management environment. iCDM provides efficient copy data management at the storage layer to consolidate both primary data and its associated copies on the same scale-out, all-flash array for unprecedented agility and efficiency. When combined with specific Dell EMC products, bullet-proof, consistent IOPS and latency, linear scale-out all-flash performance, and the ability to add more performance and capacity as needed with no application downtime, iCDM delivers incredible potential to consolidate both production and non-production applications without impacting production SLAs.
While emerging technologies, such as artificial intelligence, IoT and software-defined storage and networking, offer competitive benefits, their workloads can be difficult to predict and pose new challenges for IT departments.
Traditional architectures (hardware and software) are not designed for modern business and service delivery goals. Which is yet another solid use case for a SQL modernization effort.
As I mentioned in my previous post, not all data stores will be migrating to the cloud. Ever. The true answer will always be a Hybrid Data Estate. Full stop. However, we need to modernize for a variety of compelling reasons.
5 Paths to SQL Modernization
Here’s how you can simplify making the case for modernization and what’s included in each option.
Do nothing (not really a path at all!):
- Risky roll of the dice, especially when coupled with aging infrastructure.
- Run the risk of a security exploit, regulatory requirement (think GDPR) or a non-compliance mandate.
Purchase Extended Support from Microsoft:
- Supports SQL 2008 and R2 or Windows Server 2008 and R2 only.
- Substantial cost using a core model pricing.
- Costs tens of thousands of dollars per year…and that is for just ONE SQL instance! Ouch. How many are in your environment?
- Available for 3 additional years.
- True up yearly—meaning you cannot run unsupported for 6 months then purchase an additional 6 months. Yearly purchase only. More – ouch.
- Paid annually, only for the servers you need.
- Tech support is only available if Extended Support is purchased (oh…and that tech support is also a separate cost).
Transform with Azure/Azure Stack:
- Migrate IaaS application and databases to Azure/ Dell EMC Azure Stack virtual machines (Azure, on-premises…Way Cool!!!).
- Receive an additional 3 years of extended security updates for SQL Server and Windows Server (versions 2008 and R2) at no additional charge.
- In both cases, there is a new consumption cost, however, security updates are covered.
- When Azure Stack (on-premises Azure) is the SQL IaaS target, there are many cases where the appliance cost plus the consumption cost, is still substantially cheaper than #2, Extended Support, listed above.
- Begin the journey to operate in Cloud Operating Model fashion. Start with the Azure Stack SQL Resource Provider and easily, within a day, be providing your internal subscribers a full SQL PaaS (Platform as a Service).
- If you are currently configured with a Highly Available – Failover Cluster Instance with SQL 2008, this will be condensed into a single node. The Operating System protection you had with Always On FCI is not available with Azure Stack. However, your environment will now be running within a Hyper-Converged Infrastructure. Which does offer node failure (fault domain) protection, not operating system protection, or downtime protection from an Operating System patch. There are trade-offs. Best to weigh the options for your business use case and recovery procedures.
Move and Modernize (the Best Option!):
- Migrate IaaS instances to modern all Flash Dell EMC infrastructure.
- Migrate application workloads on a new Server Operating System – Windows Server 2016 or 2019.
- Migrate SQL Server databases to SQL Server 2017 and quickly upgrade to SQL 2019 when Generally Available. Currently SQL 2019 is not yet GA. Best guess, before end of year 2019.
- Enable your IT teams with more efficient and effective operational support processes, while reducing licensing costs, solution complexity and time to delivery for new SQL Server services.
- Reduce operating and licensing costs by consolidating SQL Server workloads. With the Microsoft SQL Server per-core licensing model in SQL Server 2012 and above, moving workloads to a virtual/cloud environment can often present significant licensing savings. In addition, through the Dell Technologies advisory services; we typically discover within the enterprise SQL server landscape, that there are many SQL Server instances which are underutilized, which presents an opportunity to reduce the CPU core counts or move SQL workloads to shared infrastructure to maximize hardware resource utilization and reduce licensing costs.
Rehost 2008 VMware Workloads:
- Run your VMware workloads natively on Azure
- Migrate VMware IaaS applications and databases to Azure VMware Solution by CloudSimple or Azure VMware solution by Virtustream
- Get 3 years of extended security updates for SQL Server and Windows Server 2008 / R2 at no additional charge
- vSphere network full compatibility
Remember, your Windows Server 2008 and 2008 R2 servers will also be EOL January 14, 2020.
Avoid Risks and Disasters Related to EOL Systems (in Other Words, CYA)
Can your company afford the risk of both an EOL Operating System and an EOL Database Engine? Pretty scary stuff. It really makes sense to look at both. Do your business leaders know this risk? If not, you need to be vocal and explain the risk. Fully. They need to know, understand, and sign off on the risk as something they personally want to absorb when an exploit hits. Somewhat of a CYA for IT, Data professionals and line of business owners. If you need help here, my team can help engage your leadership teams with you!
In my opinion, the larger problem, between SQL EOL and Windows Server EOL, is the latter. Running an unsupported operating system that supports an unsupported SQL Server workload is a serious recipe for disaster. Failover Cluster Instances (Always On FCI) was the normal way to provide Operating System High Availability with SQL Server 2008 and lower, which compounds the issue of multiple unsupported environment levels. Highly Available FCI environments are now left unprotected.
Summary
Some migrations will be simple, others much more complex, especially with mission critical databases. If you have yet to kick off this modernization effort, I recommend starting today. The EOL clock is ticking. Get your key stakeholders involved. Show them the data points from your environment. Send them the link to this blog!
If you continue to struggle or don’t want to go it alone, Dell Technologies Consulting Services, the only holistic SQL Server workload provider, can help your team every step of the way. Take a moment to connect with your Dell Technologies Service Expert today and begin moving forward to a modern platform.
Other Blogs in This Series:
Best Practices to Accelerate SQL Server Modernization (Part II)
Related Blog Posts
Introduction to SQL Server data estate modernization
Mon, 03 Aug 2020 16:08:26 -0000
|Read Time: 0 minutes
This blog is the first in a series discussing what’s entailed in modernizing the Microsoft SQL server platform.
I hear the adage “if it ain’t broke don’t fix it” a lot during my conversations with clients about SQL Server, and many times it’s from the Database Administrators. Many DBA’s are reluctant to change—maybe they think their jobs will go away. It’s my opinion that their roles are not going anywhere, but they do need to expand their knowledge to support coming changes. Their role will involve innovation at a different level, merging application technology (think CI/CD pipelines) integrated into the mix. The DBA will also need to trust that their hardware partner has fully tested and vetted a solution for best possible SQL performance and can provide a Future Proof architecture to grow and change as the needs of the business grow and change.
The New Normal Is Hybrid Cloud
When “public clouds” first became mainstream, the knee-jerk reaction was everything must go to the cloud! If this had happened, then the DBA’s job would have gone away by default. This could not be farther from the truth and, of course, it’s not what happened. With regards to SQL Server, the new normal is hybrid.
Now some years later, will some data stores have a place in public cloud?
Absolutely, they will.
However, it’s become apparent that many of these data stores are better suited to existing on-premises. Also, there are current trends where data is being re-patriated from the public cloud back to the on-premises environments due to cost, management and data gravity (keeping the data close to the business need and/or specific regulatory compliance needs).
The SQL Server data estate can be a vast, and in some cases, a hodgepodge of “Rube Goldberg” design processes. In the past, I was the lead architect of many of these designs, I am sad to admit. (I continue to ask forgiveness from the IT gods.) Today, IT departments manage one base set of database architecture technology for operational databases, another potential hardware partner caching layer, and yet another architecture for data analytics and emerging AI.
Oh wait…one more point…all this data needs to be at the fingertips of a mobile device and edge computing. Managing and executing on all these requirements, in a real-time fashion, can result in a highly complex and specialized platform.
Data Estate Modernization
The new normal everyone wants is to keep it as simple as possible. Remember those “Rube Goldberg” designs referenced above? They’re no longer applicable. Simple, portable and seamless execution is key. As data volumes increase, DBA’s partnering with hardware vendors need to simplify as security, compliance and data integrity remain a fixed concern. However, there is a new normal for data estate management; one where large volumes of data can be referenced in place, with push down compute or seamlessly snapped and copied in a highly efficient manner to other managed environments. The evolution of SQL Server 2019 will also be a part of the data estate orchestration solution.
Are you an early adopter of SQL 2019?
Get Modern: A Unified Approach of Data Estate Management
A SQL Server Get Modern architecture from Dell EMC can consolidate your data estate, defined with our high value core pillars that align perfectly for SQL Server.
The pillars will work with any size environment, from the small and agile to the very large and complex SQL database landscape. There IS a valid solution for all environments. All the pillars work in concert to complement each other across all feature sets and integration points.
- Accelerate – To not only accelerate and Future-Proof the environment but completely modernize your SQL infrastructure. A revamped perspective on storage, leveraging RAM and other memory technologies, to maximum effect.
- Protect – Protect your database with industry leading backups, replication, resiliency and self-service Deduplicated copies.
- Reuse – Reuse snapshots. Operational recovery. Dev/Test repurposing. CI/CD pipelines.
Aligning along these pillars will bring efficiency and consistency to a unified approach of data estate management. The combination of a strong, consistent, high-performance architecture supporting the database platform will make your IT team the modernization execution masters.
What Are Some of the Compelling Reasons to Modernize Your SQL Server Data Estate?
Here are some of the pain point challenges I hear frequently in my travels chatting with clients. I will talk through these topics in future blog posts.
1. Our SQL Server environment is running on aging hardware:
- Dev/Test/Stage/Prod do not have the same performance characteristics making it hard to regression test and performance test.
2. We have modernization challenges:
- How can I make the case for modernization?
- My team does not have the cycles to address a full modernization workstream.
3. The Hybrid data estate is the answer… how to we get there?
4. We are at EOL (End of Life) for SQL Server 2008 / 2008R2 and Windows Server but are stuck due to:
- ISV (Independent Software Vendor) “lock in” requirement to a specific SQL Server engine version.
- Migration plan to modernize SQL cannot be staffed and executed through to completion.
5. We need to consolidate SQL Server sprawl and standardize on a SQL Server version:
- Build for the future, where disruptive SQL version upgrades become a thing of the distant past. Think…containerized SQL Server. OH yeah!
- CI/CD success – the database is a key piece of the puzzle for success.
- Copies of databases for App Dev / Test / Reporting copies are consuming valuable space on disk.
- Backups take too long and consume too much space.
6. I want to embrace SQL Server on Linux.
7. Let’s talk modern SQL Server application tuning and performance.
8. Where do you see the DBA role in the next few years?
Summary
I hope you will come along this SQL Server journey with me as I discuss each of these customer challenges in this blog series:
Best Practices to Accelerate SQL Server Modernization (Part I)
Best Practices to Accelerate SQL Server Modernization (Part II)
And, if you’re ready to consider a certified, award-winning Microsoft partner who understands your Microsoft SQL Server endeavors for modernization, Dell EMC’s holistic approach can help you minimize risk and business disruption. To find out more, contact your Dell EMC representative.
The new DBA role—Time to get your aaS in order
Mon, 03 Aug 2020 16:07:49 -0000
|Read Time: 0 minutes
Yes, a “catchy” little title for a blog post but aaS is a seriously cool and fun topic and the new DBA role calls for a skill set that’s incredibly career-enhancing. DBA teams, in many cases, will be leading the data-centric revolution! The way data is stored, orchestrated, virtualized, visualized, secured and ultimately, democratized.
Let’s dive right in to see how aaS and the new Hybrid DBA role are shaking up the industry and what they’re all about!
The Future-ready Hybrid DBA
I chatted about the evolution of the DBA in a previous blog within this series called Introduction to SQL Server Data Estate Modernization. I can remember, only a few short years ago, many of my peers were vocalizing the slow demise of the DBA. I never once agreed with their opinion. Judging from the recent data-centric revolution, the DBA is not going anywhere.
But the million-dollar question is how will the DBA seamlessly manage all the different attributes of data mentioned above? By aligning the role with the skill that will be required to exceed in exceptional fashion.
Getting Acquainted with the aaS Shortlist
To begin getting assimilated with what aaS has in store, the DBA will need to get his/her aaS in order and become familiar with the various services. I am not a huge acronym guy (in meetings I often find that folks don’t even know the words behind their own acronyms), so let’s spell out a shortlist of aaS acronyms, so there’s no guesswork at their meaning.
And this is by no means is a be-all-end-all list – technology is advancing at hyper-speed – as Heraclitus of Ephesus philosophized, “Change is the only constant in life.”
Why all this aaS, you ask?
Because teams must align with some, if not all of these services, to enable massive scalability, multitenancy, independence and rapid time to value. These services make up the layers in the cake which comprise the true solution.
Let’s take SQL Server 2019, for instance, a new and incomparable version that’s officially generally available and whose awesome goodness will be the topic of many of my blogs in 2020!
SQL 2019 Editions and Feature Sets
I started with SQL Server 6.5, before a slick GUI existed. Now, with SQL 2019, we have the ability to manage a stateful app, SQL Server, in a container, within a cluster, and as a platform.
That is “awesome sauce” spread all around!
And running SQL Server 2019 on Linux? We’re back to scripting again. I love it! Scripting is testable, repeatable, sharable, and can be checked into source control. Which, quite simply, enables collaboration, which in turn makes a better product. You’ll need to be VCaaS-enabled to store all these awesome scripts.
Is your entire production database schema in source control? If it isn’t, script it out and put it there. A backup of a database is not the same as scripting version control. DBA’s often tell me their production code resides in the database, which is backed up. It’s best practice, however, to begin your proven, repeatable pattern by scripting out the DDL and checking into and deploying from a VCaaS. Aligning your database DDL and test data sets with a CI/CD application development pipeline, should be addressed ASAP. By the way, try this with ADS (Azure Data Studio) and the dacpac extension. Many of the aaSes can be managed with ADS. And, with the #SQLFamily third-party community – the ADS extension tooling will only continue to grow.
DBA as a DevOps Engineer?
Reading this blog as a DBA, you may be thinking I don’t need to know about all these aaS details. Simply put, yes, you do! In many respects, the DBA is now the new DevOps engineer, utilizing all the services listed above. This hybrid role is becoming more of a full stack developer – providing support for multiple scripting languages, an “IT Polyglot.”
Moreover, as a DBA, you are already executing on all of these attributes, and you may not even know it! It turns out that DBAs have always been a part of DevOps. Think about it, you align with Dev — like writing SQL, tuning performance, doing Object Analysis and reporting — and you already do Ops — like, configuring servers and VMS, running backups and restores, and tuning the OS, network and storage. So, you are uniquely positioned to now offer all this as a service.
Congratulations! Your organization is looking to you to lead the charge as the data-centric view of “all things” has arrived!
The Hybrid DBA Role and His or Her Requisite “aaSes”
Let’s look deeper into the chain of services.
As a hybrid DBA, you want to provide DBaaS. What will you need to provide, and understand from a tooling perspective? Well, you are going to need to understand IaaS. Infrastructure as code, to define and automate your core database target workload servers. You will be grouping these services into networking namespaces, there is a bit of NaaS alignment. Even more services if you have data sources residing in a public or on-premises cloud. Maybe you have a FaaS-enabled in a public cloud, that is writing to an unstructured data store. A FaaS, remember, can very much be the replacement for older ETL processes.
Next for agility, let’s containerize these things using CaaS. What will you need to manage and orchestrate those containers? KaaS or Kubernetes which provides the opportunity to build a SQL 2019 Big Data Cluster. In theory, you could say the Kubernetes platform is PaaS with the flexibility of IaaS. Now we are mixing aaS’s together for maybe yet another acronym. :)
Have you started noodling around with SQL 2019 yet? No? Why not? Microsoft has done an incredible job ramping up the product for its launch. I highly recommend jumping in and starting to play with it!
The core Microsoft SQL Server team has put together this excellent reference for SQL 2019 to get you started.
As a DBA, you can then provide PaaS in a few different ways. Again, DBaaS is one form of PaaS. You have a true Data Virtualization layer with SQL 2019 that can also provide PaaS for sourcing across all the data sources – simply with a T-SQL script and PolyBase External Table. That you will enable! There are also many self-service reporting enablement features with PowerBI.
Here is where I like to say XaaS enables data virtualization!
The Beneficial Impact of the Hybrid DBA on Various BUs
I also hear, from the App owners and IT decision makers, that the database team must react to requests, at a much faster rate than the previous norm.
The hybrid DBA will accelerate the cycles for many, if not all, of these business units in the following ways:
Product Managers and Enterprise Architects
- Reduced/eliminated waits for infrastructure
- Simultaneous features/projects
- Improved and reliable software quality
- Governance around data management and access
Infrastructure Administrators
- Drastic reduction in turn-around times
- Operational simplicity
- Reduced human errors through automation
Developer teams
- Accelerate iterations of testing and development
- Secure data sets – infused into the DevOps cycles
- Production deployments with database alters
LOB Executives
- Lower CapEx for storing multiple copies
- Improved employee productivity
- Faster time to market
Embrace the Containerized World
Big Data Clusters run on a Linux platform on containers within a Kubernetes Cluster. Remember, SQL Server on Linux is the same codebase as SQL Server on Windows. With the only difference being the SQLPAL (SQL Platform Abstraction Layer) and a host extension. This host extension allows SQL Server to interact with the Linux kernel. The hybrid DBA will need to understand how containers are managed and provisioned. Ensuring a solid storage foundation is paramount here, before embarking on containers in production. (*Hint:* Dell Technologies is really good at this!). Additionally, the hybrid DBA should also embrace PowerShell, Bash, R and Python. All in the name of more intelligent tooling across a wide range of modern development platforms and technologies.
If, as a DBA, you are concerned, worried, or downright scared of a containerized world and orchestrator like Kubernetes, no need to worry at all. In fact, there are similarities that you will absolutely love. With K8s there is a declarative object configuration management technique, which is recommended in production.
The Declarative Perspective and Release of SQL 2019
Something else is declarative that we know and love: SQL Server. Declarative languages let users or administrators express a desired state or query. In the case of SQL Server, they want to retrieve as a result, providing broad instructions about what tasks are to be executed and completed. Then, the declarative engine goes to work. You then deal with the results, not the process or automation. Kubernetes and SQL Server, from a declarative perspective, act exactly the same way. I say this because data professionals, especially the DBA, are wired a certain way. They like patterns and they like to declare, “Here is my work to do, go make it happen”.
With the release of SQL 2019, the Hybrid DBA will continue to be in the driver’s seat. You will now administer from the edge (SQL Server Edge) to on-premises SQL Server to additional SQL workloads that may also be in a public cloud. To that end, any cloud, all with SQL Server. Companies large and small will benefit from intelligent SQL Server deployments.
Dell Technologies Is Here to Help You Succeed
Dell Technologies, as your company’s trusted advisor, is positioned and aligned to help you right now with our solutions and services! We also have whitepapers on SQL 2019 available for your reference to help determine which solutions are correct for your workloads.
Dell Technologies offers solutions and services to address hyper-scale data requirements and next-gen hybrid, including:
- Modernize Your SQL Server platform with Consulting Services
- SQL Server 2019 – Containers on Linux
- SQL Server Info Hub
- Winning the New Era of Data Management
- PowerMax Storage – Mission Critical Storage
- ZDNet Japan (Please use Google Translation to read in English)
Many more solutions will become available in the very near future. Why wait? A Dell Technologies Service Expert is ready and able to help you get your aaS in order!
Summary
The hybrid DBA needs to abide to the creed of #neverstoplearning. If you’re stuck and complacent, you will get passed up. There really is no excuse. The cloud playground options are abundant, at minimal or no cost, to play and learn within these environments. Everything in the aaS table above can be vetted and tested within a public or on-premises cloud environment.
Learning a new technology can be simple and fun. Do what I do. Make learning part of your daily life. Carve out a minimum of 2-hour blocks, 5 days a week, and maybe an additional Saturday or Sunday morning to simply play with tech and learn. It works, and you will be much better off in the long run. Personally, some of my best learning happens on a quiet Sunday morning when the email and phone requests are non-existent.
What changes are you willing to incorporate into your daily routine to keep your skills fresh and relevant?
Other Blogs in This Series
Best Practices to Accelerate SQL Server Modernization (Part I)
Best Practices to Accelerate SQL Server Modernization (Part II)
Introduction to SQL Server Data Estate Modernization
Recommended Reading
Running Containerized Applications on Microsoft Azure’s Hybrid Ecosystem – Introduction
Deploy K8s Clusters into Azure Stack Hub User Subscriptions
Deploy a Self-hosted Docker Container Registry on Azure Stack Hub