Applications/DevOps

Best Practices to Accelerate SQL Server Modernization (Part I)

By   |  

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:

  1. The risk is too high to migrate. (I say, isn’t there a greater risk in doing nothing?)
  2. 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.
  3. 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 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 deliver 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 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 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)

Introduction to SQL Server Data Estate Modernization

About the Author: Robert F. Sonders

Robert F. Sonders has been with Dell Technologies for over 10 years. During his tenure he has been laser-focused on Microsoft workloads, both on-premises, and hybrid, specifically around the entire SQL Server ecosystem, both from an Administrative and Business Intelligence perspective. During this journey, Robert has lead SQL Server delivery project teams as a technical consultant architect and a pre-sales Solution Principal. Throughout his 25+ years of IT experience, Robert has been passionately engaged in implementations that have directly contributed to the advanced performance and productivity of diverse organization verticals in healthcare, financial, and e-commerce. Robert will always begin with understanding the unique challenges of the business verticals with a focus on first learning the client’s perspective (listening first) and then will align the subsequent requirements to formulate a solution that will uniquely fit their needs. He prides himself on staying bleeding edge with his respective “wheelhouse” technologies. His current role now aligns with core Dell Technologies workloads as a “Principal System Engineer – Data-Centric Workloads for Microsoft”, His major focus is on all things SQL Server and Azure Hybrid ecosystem, complementing all Account Teams and Systems Engineering teams for all pre-sales efforts. As an MCT (Microsoft Certified Trainer), the opportunity to present innovative Microsoft technologies, allows Robert to insightfully move clients to overcome any roadblocks. These types of engagements and conversations also allow Robert to provide a client feedback loop, back to Dell internal engineering, and even Microsoft product engineering teams. When he is not consumed with reading and learning he is traveling, handling AKC champion Pembroke Welsh Corgis, and vigorously enjoying the outdoors that surround his desert home. He resides in Scottsdale AZ with his wife and Corgis.