Accelerate SQL Server modernization: Getting started
Wed, 03 Aug 2022 21:45:58 -0000
|Read Time: 0 minutes
In Part I of this blog series, I discussed issues concerning EOL SQL Server and Windows Server running on aging hardware, and three pathways to SQL Server modernization. In this blog, I’ll discuss how a combination of Dell Technologies Consulting Services and free tools from Microsoft can help you get started on your SQL migration journey and how to meet your SQL Server data modernization objectives.
Beginning Your SQL Migration Journey
During my client conversations, I hear three repeating pain points: (1) they would love to modernize, don’t have the cycles or staff to drive a solid iterative SQL migration approach through to completion, (2) they still need to “keep the lights on” and (3) despite having the skills, they lack the team cycles to execute on the plan.
This is where Dell Technologies Consulting Services can help you plan a solid foundational set of goals and develop a roadmap for the modernization. Here are the key points our SQL modernizations teams address:
- Discover the as-is SQL environment including the current state of all in scope SQL servers, associated workloads and configurations.
- Inventory and classify applications (which align to SQL databases) and all dependencies. Critical here to think through all the connections, reporting, ETL/ELT processes, etc.
- Group and prioritize the SQL databases (or entire instance) by application group and develop a near-term modernization plan and roadmap for modernization. Also an excellent time to consider database consolidation.
- Identify the rough order of magnitude for future state compute, storage and software requirements to support a modernization plan. Here is where our core product teams would collaborate with the SQL modernization teams. This collaboration is a major value add for Dell Technologies.
Additionally, there are excellent, free tools from Microsoft to help your teams, assess, test and begin the migration journey. I will talk about these tools below.
Free Microsoft Tools to Help You Get Started
Microsoft has stated they will potentially assist if there are performance issues with SQL procedures and queries. It is best to utilize the Query Tuning Assistant while preparing your database environment for a migration.
Microsoft provides query plan shape protection when:
- The new SQL Server version (target) runs on hardware that is comparable to the hardware where the previous SQL Server version (source) was running.
- The same supported database compatibility level is used both at the target SQL Server and source SQL Server.
- Any query plan shape regression (as compared to the source SQL Server) that occurs in the above conditions will be addressed (contact Microsoft Customer Support if this is the case).
Here are a few other free Microsoft tools I recommend running ASAP that will enable you to understand your environment fully and provide measurable and actionable data points to feed into your SQL modernization journey. Moreover, proven and reliable upgrades should always start with these tools (also explained in detail within the Database Migration Guide):
- Discovery – Microsoft Assessment and Planning Toolkit (MAP)
- Assessment – Database Migration Assistant (DMA)
- Testing – Database Experimentation Assistant (DEA)
Database engine upgrade methods are listed here. Personally, I am not a fan of any upgrade in-place option. I like greenfield and a minimal cutover with an application connection string change only. There are excellent, proven, migration paths to minimize the application downtime due to a database migration. Another place our Professional Services SQL Server team can provide value and reliability to execute a successful transition.
Here are a few cutover migration options for you to consider:
Feature | Notes |
Log Shipping | Cutover measured in (typically) minutes |
Replication | Cutover measured in (potentially) seconds |
Backup and Restore | This is going to take a while for larger databases. However, a Full, Differential and T-log backup / restore process can be automated |
Filesystem/SAN Copy | Can also take time |
Always On Groups (>= SQL 2012) | Cutover measured in (typically) seconds – Rolling Upgrade |
*Future – SQL on Containers | No down time – Always On Groups Rolling Upgrade |
A consideration for you — all these tools and references are great — but does your team have the cycles and skills to execute the migration?
3 Pillars to Help You with Your SQL Server Data Modernization Objectives
As I mentioned at the start of this blog, it makes perfect sense to use the experts from Dell Technologies Consulting Services for your SQL Server migration. Our Consulting Services teams are seasoned in SQL modernization processes, including pathways to Azure, Azure Stack, the Dell EMC Ready Solutions for SQL Server, existing hardware or cloud, and align perfectly with a server and storage refresh, if you have aging hardware (will be covered in future blog).
Within Dell Technologies Consulting Services, we support 3 pillars to help you with your SQL Server data modernization objectives.
Platform Consulting Services
Dell Technologies Consulting Services provides planning, design and implementation of resilient data architectures with Microsoft technology both on-premises and in the cloud. Whether you are installing or upgrading a data platform, migrating and consolidating databases to the Cloud (Public, Private, Hybrid) or experiencing performance problems with an existing database, our proven methodologies, best practices and expertise, will help you make a successful transition.
Data Modernization Services
Modernizing your data landscape improves the quality of data delivered to stakeholders by distributing workloads in a cost-efficient manner. Platform upgrades and consolidations can lower the total cost of ownership while efficiently delivering data in the hands of stakeholders. Exploring data workloads in the cloud, such and test/development, disaster recovery or for active archives provide elastic scale and reduced maintenance.
Business Intelligence and Analytics Services
By putting data-driven decisions at the heart of your business, your organization can harness a wealth of information, gain immediate insights, drive innovation, and create competitive advantage. Dell Technologies Consulting Services provides a complete ecosystem of services that enable your organization to implement business intelligence and create insightful analytics for on-premises, public cloud, or hybrid solutions.
We have in place, the proven methodologies with an focus, to drive a repeatable and successful SQL Server migration.
3 Approaches to Drive a Repeatable and Successful SQL Server Migration
Migration Procedures and Validation Approach
A critical success factor for migrations is ensuring the migration team has a well-documented set of migration processes, procedures and validation plan. Dell Technologies Consulting Services ensure that every team member involved in the migration process has a clear set of tasks and success metrics to measure and validate the process throughout the migration lifecycle.
Tools-based Migration and Validation
Our migration approach includes tools-based automation solutions and scripts to ensure the target state environment is right-sized and configured to exacting standards. We leverage industry standard tools to synchronize data from the source to target environments. Lastly, we leverage a scripted approach for validating data consistency and performance in the target environment.
Post Migration Support
Once the migration event is complete, our consultants remain at the ready for up to 5 business days, providing Tier-3 support to assist with troubleshooting and mitigating any issues that may manifest in the SQL environment as a result of the migration.
Considerations for the Data Platform of the Future
There are a number of pathways for modernizing SQL Server workloads. Customers need flexibility when it comes to the choice of platform, programming languages & data infrastructure to get from the most from their data. Why? In most IT environments, platforms, technologies and skills are as diverse as they have ever been, the data platform of the future needs to you to build intelligent applications on any data, any platform, any language on premises and in the cloud.
SQL Server manages your data, across platforms, on-premises and cloud. The goal of Dell Technologies Consulting Services is to meet you where you are, on any platform, anywhere with the tools and languages of your choice.
SQL Server now has support for Windows, Linux & Docker Containers. Kubernetes orchestration with SQL 2019 coming soon!
Additionally, SQL allows you to leverage the language of your choice for advanced analytics – R and Python.
Where Can You Move and Modernize these SQL Server Workloads?
Microsoft Azure and Dell EMC Cloud for Microsoft Azure Stack
Migrate your SQL Server databases without changing your apps. Azure SQL Database is the intelligent, fully managed relational cloud database service that provides the broadest SQL Server engine compatibility. Accelerate app development and simplify maintenance using the SQL tools you love to use. Reduce the burden of data-tier management and save time and costs by migrating workloads to the cloud. Azure Hybrid Benefit for SQL Server provides a cost-effective path for migrating hundreds or thousands of SQL Server databases with minimal effort. Use your SQL Server licenses with Software Assurance to pay a reduced rate when migrating to the cloud. SQL 2008 support will be extended for three years if those IaaS workloads are migrated to Azure or Azure Stack.
Another huge value add, with Azure Stack and SQL Server, is the SQL Server Resource Provider. Think SQL PaaS (Platform as a Service). Here is a quick video I put together on the topic.
The SQL RP does not execute the SQL Server engine. However, it does manage the various SQL Server instances, that can be provided to tenants as SKUs, for SQL database consumption. These managed SQL Server SKUs can exist on, or off, Azure Stack.
Now that is cool if you need big SQL Server horsepower!
Dell EMC Proven SQL Server Solutions
Dell EMC Proven solutions for Microsoft SQL are purpose-designed and validated to optimize performance with SQL Server 2017. Products such as Dell EMC Ready Solutions for Microsoft SQL helps you save the hours required to develop a new database and can also help you avoid the costly pitfalls of a new implementation, while ensuring that your company’s unique needs are met. Our solution also helps you reduce costs with hardware resource consolidation and SQL Server licensing savings by consolidating and simplifying SQL systems onto modern infrastructure that supports mixed DBMS workloads, making them future ready for data analytics and next generation real-time applications.
Leverage Existing Cloud or Physical Infrastructure
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. Dell Technologies Consulting Services has experience with all the major cloud platforms. We can assist with planning and implementation services to migrate, upgrade and consolidate SQL Server database workloads on your existing cloud assets.
Consolidate SQL Workloads
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, Dell Technologies Consulting Services will 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.
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. Use the free tools from Microsoft. 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.
Blogs in the Series
Best Practices to Accelerate SQL Server Modernization (Part I)
Introduction to SQL Server Data Estate Modernization
Related Blog Posts
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)
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.