If you are running SQL Server 2008 or SQL Server 2008 R2, what does July 9th, 2019 mean for you? With both of these versions of SQL Server reaching the end of their support lifecycle together, you will no longer be able to get critical security updates. This can cause serious security and compliance issues for your organization.

When these versions of SQL Server were released, they came with 10 years of support; 5 years of Mainstream Support and 5 years of Extended Support. If your organization still has SQL Server 2008/2008 R2 in production, how is your organization planning to address the risk? For organizations that are heavily regulated, this is a big concern.

You need to choose how you’re going to migrate and where you’re going to migrate to, and then make sure you’re not going to hit any roadblocks along the way.

Migration Assessment Tools

If you are planning an upgrade from SQL Server 2008/2008 R2, Microsoft has made things much easier to test and validate your environment. Numerous tools exist that can assist with migration assessments and even handle migration tasks, and they’re all slightly different. These tools include:

• Data Migration Assistant
• Microsoft Assessment and Planning Toolkit
• Azure Database Migration Service
• Database Experimentation Assistant

The Data Migration Assistant helps you to upgrade to a modern data platform. It does this by detecting compatibility issues that can impact functionality on the newer version of SQL Server and makes recommendations for performance and reliability improvements for the new environment. Your source can be SQL Server 2005+ with a target of SQL 2012+ and Azure SQL Database.

The Microsoft Assessment and Planning Toolkit has been around for many years and is often referred to as the MAP Tool. It’s great for doing an inventory of your current environment to find where SQL Server (and other applications) exist.

The Azure Database Migration Service integrates some of the functionality of existing tools and services to provide customers with a comprehensive solution for migrating to Azure. The tool generates assessment reports that provide recommendations to guide you through any changes required prior to performing a migration. This service currently requires a VPN or Express Route.

Finally, the Database Experimentation Assistant is a new A/B testing solution for SQL Server Upgrades and it’s a tool you should become familiar with. It leverages Distributed Replay to capture a workload and replay it against a target SQL Server. This can be used to test hardware changes or version differences of SQL Server. You can capture workloads from SQL Server 2005 and up.

Migration Options

On-premises upgrade: One of the easiest migration methods is to upgrade to a newer version of SQL Server. In this case, you have SQL Server 2012, 2014, 2016, or 2017 to pick from. I encourage clients to upgrade to the latest version that they can. SQL Server 2012 is already out of Mainstream Support and SQL Server 2014 goes out of Mainstream Support on July 9^th, 2019. Upgrading can be very time consuming and costly to organizations due to all the planning and testing involved, so moving to the latest version can increase the time before the next upgrade. There are also numerous performance and functionality improvements in SQL Server 2016 and 2017 that make migrating to SQL Server 2012 or 2014 a very poor choice at this time.

A common approach for on-premises upgrades is to build new and migrate, regardless of a physical or virtual environment. By building new, you can restore your databases and conduct numerous rounds of testing and validation to make sure everything works as expected before moving production.

Upgrade and migrate to an Azure VM: For organizations that are looking to migrate to the cloud, Azure Infrastructure as a Service (IaaS) is a great option. Running SQL Server on an Azure VM is much like on-premises. You specify the size of the VM (number of vCPUs and memory) and configure your storage for your I/O and size requirements. You are still responsible for supporting the OS and SQL Server for configuration and patching. Azure IaaS gives you the ability to easily scale your workloads by scaling the size of your virtual machine up or down as your workload needs change, as well as take advantage of Azure Active Directory integration, threat detection, and many other Azure benefits.

Migrate to Azure SQL Database: Another option you have is to migrate to Azure SQL Database. Azure SQL Database can be thought of as a Database as a Service and is part of Microsoft’s Platform as a Service (PaaS). Azure SQL Database functionality is database scoped, which means certain things such as cross database queries, SQL Server Agent, Database Mail, and more are not available. However, many customers that have applications that utilize a single database have been able to migrate to Azure SQL Database with minimal effort. You can quickly test for compatibility with Azure SQL Database by using the Data Migration Assistant. With Azure SQL Database, you can size your databases by DTU (Database Transaction Units) or vCores individually, or group databases into an Elastic Pool. Azure SQL Database allows you to scale your resources up and down with minimal effort and downtime.

Migrate to Azure SQL Managed Instance: A new option (as of 2018) is to migrate to Azure SQL Managed Instance. This is a new product that is currently generally available as of October 1^st for the General-Purpose tier. Managed Instance was built using the instance-level programming model. This means that functionality we are used to with the full version of SQL Server is supported. The goal of Managed Instance is to have 100% surface area compatibility with on-premises. All databases in the instance are on the same server, so cross-database queries are supported, as are Database Mail, SQL Server Agent, Service Broker, and much more. There are two pricing tiers; General Purpose, that includes a non-readable secondary for HA, and Business Critical, that has two non-readable secondaries and a readable secondary. Managed Instance is part of Microsoft’s PaaS offering, so you get all the built-in features and functionality of PaaS.

Move as-is to Azure Virtual Machines: Microsoft is offering three years of Extended Security Updates at no additional charge if you move your SQL 2008/SQL 2008 R2 instances to an Azure VM. The goal is to give you a bit more time to upgrade to a newer version of SQL Server when you are ready.

Pay to Stay: This isn’t a migration option, but you do have an option to purchase up to three years of Extended Security Updates. There are restrictions around this option. You must have active Software Assurance for those instances or Subscription licenses under an Enterprise Agreement. If this applies to you, then this option can buy you more time to plan and migrate off of SQL Server 2008/2008 R2.

Migration Best Practices

When performing any migration or upgrade, there are certain things you need to be aware of. First, you need baselines and I can’t stress this enough. Anytime you make a change to an environment, you need to be able to measure how that change impacts the environment. Knowing key performance metrics for your environment can help you when troubleshooting any perceived impact. You can manually collect these metrics using perfmon and DMVs or invest in a performance monitoring platform. I wrote about both techniques in more detail in a previous post, and right now you can get an extended, 45-day evaluation of SentryOne. Having baseline metrics for things like CPU utilization, memory consumption, disk metrics, and more can quickly let you know if things look better or worse after an upgrade or migration.

You should also note your configuration options within your instance. Many times, I’ve been asked to look at a SQL Server instance after an upgrade or migration and found that most of the default settings are in use. If the old system is still available, I’m able to query it and get the previous non-default values that were in place and apply those to the new environment to get them back to a known configuration. It is always good to review sys.configurations on your production server to consider making similar changes on your new environment (cost threshold for parallelism, max degree of parallelism, optimize for ad hoc workloads, and more.) Notice I wrote ‘consider’. If your core count or memory is different on the new server, you need to configure the settings taking the new server’s size into account.

What is your backout plan if things go wrong? Do you have proper backups you can go back to? In most cases with an upgrade or migration, you are moving to a new VM or physical server. Your failback may be to move back to the old server. If you have had data change in the new version of SQL Server, your failback is much more complicated. You cannot restore a SQL Server database backup from a newer version of SQL Server to an older version.

Conclusion

If you are still using SQL Server 2008 or SQL Server 2008 R2, you have a few options available to you to stay in compliance after July 9th, 2019. To stay on SQL Server 2008 or SQL Server 2008 R2, you can purchase Extended Security Updates or move to an Azure virtual machine if you qualify. If you can upgrade, you can migrate to a supported version of SQL Server on-premises or on an Azure VM or consider migrating to a managed solution such as Azure SQL Database or Azure SQL Managed Instance.