Moving SQL Server 2008/2008 R2 Databases to the Azure Cloud

Extended Support for SQL Server 2008 and 2008 R2 ended in July 2019, and Extended Support for Windows Server 2008 and 2008 R2 will end in January 2020. Upgrading the software to the latest versions is always an option, of course, but for a variety of reasons, that may not be viable or cost-effective for some legacy applications. Another option is to pay an additional fee to continue receiving Extended Security Update support for three more years. But for most organizations, the best option will be to get continued support for free by moving the databases to the Azure cloud.

This article highlights the considerations and challenges involved when migrating mission-critical SQL Server 2008/R2 databases to the Azure cloud and offers some useful suggestions to help avoid common pitfalls.

You may also like:  AWS vs. Azure vs. Google: Which Is the Best for Cloud Computing?

Running Legacy Software in a State-of-the-Art Cloud

The Azure cloud offers a full spectrum of services for running mission-critical applications, but only some of these support SQL Server 2008/R2. The most significant limitation derives from the dependency Failover Cluster Instances (FCIs) have on shared storage. Such storage in the form of a storage area network (SAN) or network-attached storage (NAS) is possible in enterprise data centers, but not in the Azure cloud where all storage is local.

Microsoft has addressed the lack of shared storage with two enhancements, but neither supports SQL Server 2008/R2. One is Storage Spaces Direct that debuted with the Datacenter Edition of Windows Server 2016 and SQL Server 2016. The other is SQL Server’s own Always On Availability Groups that became available in SQL Server 2012.

For legacy database applications that are not critical, Azure’s standard service level agreement (SLA) guarantees may be sufficient, eliminating the need to use FCIs to assure high availability (HA). For example, manual backup and recovery processes should provide adequate uptime for most batch database applications. It is important to note, however, that Azure’s money-back SLA guarantee does not assure uptime at the database or application level. In effect, the SLA guarantees only “dial tone” or that at least one instance will have external network connectivity.

Unlike batch applications, most online transaction processing applications have more stringent recovery point and recovery time objectives that will require implementing separate HA and/or disaster recovery (DR) provisions. Azure Site Recovery, Microsoft’s DR-as-a-Service (DRaaS) offering, replicates the entire active instance to a “warm” standby instance in another Azure Region, and it does support SQL Server 2008/R2.

The need for manual processes to detect and recover from a failure makes ASR suitable for those applications with a Recovery Point Objective (RPO) of a few minutes or more, and a Recovery Time Objective (RTO) of several minutes or more. ASR also has a limit of 10 Megabytes per second per disk of WAN bandwidth, which may be inadequate for some applications.

One final consideration is the operating system. When running SQL Server 2008/R2 on either Windows Server 2008 R2 or Windows Server 2012, Microsoft requires this hotfix to work with FCIs: https://support.microsoft.com/en-us/help/2854082/update-enables-sql-server-availability-group-listeners-on-windows-serv. Depending on the configuration, additional hotfixes may be needed. If possible, using Windows Server 2012 R2 or minimizes the need for hotfixes.

Failover Clustering for SQL Server 2008/R2 Databases

Protecting legacy SQL Server 2008/R2 databases with a stringent RTO of four-nine’s (99.99%) and/or an RPO of zero (no data loss) creates the need to use FCIs. And that, in turn, creates the need to use third-party failover clustering software to enable synchronous data replication — sans SANs — across multiple Azure Availability Zones.

Purpose-built failover clustering software facilitates real-time data replication and continuous monitoring capable of detecting failures at the database or application level. Most also integrate seamlessly with the Windows Server Failover Clustering (WSFC) feature used by FCIs and make it possible to configure different failover/failback policies for different applications.

One popular choice is to use third-party failover clustering software for HA and ASR for DR. With this cost-effective combination, data is replicated synchronously across multiple Azure Availability Zones via virtual volumes that appear as shared storage to the SQL Server FCIs. ASR adds DR protection by replicating the entire failover cluster (both the active and standby instances) asynchronously to another Azure Region in a Region Pair.

Another popular choice, shown in the diagram, is to use failover clustering software for both HA and DR purposes. This configuration is also cost-effective and affords the additional advantage of being able to protect virtually all application software, including all versions of SQL Server, in a single solution, making HA/DR configurations easier to implement, test, monitor, maintain and otherwise manage.

An existing SQL Server 2008/R2 failover cluster can easily be “Lifted and Shifted” from the premises to the Azure cloud. The effort involves simply replacing the shared disk resources with the SANless failover cluster’s virtual volumes, and substituting a file share witness for the disk witness. It is also necessary to configure the Azure Internal Load Balancer for client redirection, which requires running a PowerShell script on the local nodes to update the SQL Server cluster IP resource to listen for the ILB probe.

If no on-premises HA failover cluster exists to be lifted and shifted, one will need to be created, and most vendors of failover clustering software provide documentation detailing the steps involved. Here is an example of one such guide for Azure: Step-By-Step: How to Configure a SQL Server 2008 R2 Failover Cluster Instance on Windows Server 2008 R2 in Azure.

While there are some issues involved in migrating legacy SQL Server 2008/R2 applications to the Azure cloud, their “maturity” means that you should not need to navigate uncharted waters.

Further Reading

AWS and Azure Cloud, Head-to-Head

Better SQL Server CPU Defaults in 2019