A primer on migrating SQL Server 2008/2008 R2 databases to the Azure cloud

System and database administrators are now being forced to do something about legacy SQL Server 2008 and 2008 R2 database applications. The reason is the end of Extended Support in July 2019. Extended Support will also end for its common companion Windows Server 2008 and 2008 R2 in January 2020. Upgrading to the latest versions is always an option, of course, but Microsoft is providing an attractive alternative when upgrades are not viable or cannot be cost-justified: Migrate the database to the Azure cloud and get three more years of Extended Security Update support at no additional charge over the standard virtual machine pricing.

This article highlights important considerations for migrating mission-critical legacy SQL Server 2008/R2 databases to the Azure cloud to help administrators make more informed decisions. Among the key considerations is knowing which options are and are not available.

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

The Azure cloud offers a wealth of services for running and protecting application software, but some of these do not support SQL Server 2008/R2. The biggest limitation involves the dependency Failover Cluster Instances (FCIs) have on shared storage. In an enterprise datacenter, shared storage can be provided with a storage area network (SAN) or network-attached storage (NAS). But both forms of storage are unavailable in the Azure cloud.

For legacy database applications that are not critical, there may be no need to use FCIs to assure high availability (HA). For most batch database applications, for example, manual backup and recovery processes should provide adequate uptime.

Unlike batch applications, most online transaction processing applications have more stringent recovery point and recovery time objectives that can only be satisfied with more robust HA and/or disaster recovery (DR) provisions. For DR, SQL Server 2008/R2 is supported by Azure Site Recovery, which is Microsoft’s DR-as-a-Service (DRaaS) offering. ASR replicates the entire active instance to a "warm" standby instance in another Azure Region. But its need for manual processes to detect and recover from a failure makes ASR suitable only 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. Its limitation of 10 Megabytes per second of WAN bandwidth per disk may also preclude its use for some applications.

It is worth noting that the two ways Microsoft has addressed the lack of shared storage do not support SQL Server 2008/R2. One is SQL Server’s own Always On Availability Groups that became available in SQL Server 2012. The other is Storage Spaces Direct that debuted with the Datacenter Edition of Windows Server 2016 and SQL Server 2016.

It is also worth noting that Azure’s money-back service level agreement guarantee does not assure uptime at the database or application level. The SLA effectively assures only "dial tone" or, more specifically, that at least one instance will have external network connectivity.

Assuring High Availability for SQL Server 2008/R2 Databases

For applications with a stringent RTO that requires an uptime of four-nine’s (99.99%) and an RPO of zero (no data loss), the only viable option for SQL Server 2008/R2 is to use FCIs. And that requires using third-party failover clustering software to enable synchronous data replication—sans SANs—across multiple Azure Availability Zones.

Third-party failover clustering solutions facilitate, at a minimum, real-time data replication, continuous monitoring capable of detecting failures at the database or application level, and configurable policies for failover and failback. Most are designed to integrate seamlessly with FCIs and Windows Server Failover Clustering (WSFC). Most are also designed to be application-agnostic to provide a universal HA/DR solution suitable for virtually all application software, including all versions of SQL Server.

One popular configuration is to use third-party failover clustering for HA and Azure Site Recovery for DR. This cost-effective combination replicates data synchronously across multiple Azure Availability Zones using virtual volumes that appear as shared storage to the SQL Server FCIs. ASR then replicates the pair of virtual machine (VM) images in the failover cluster (both the active and standby) asynchronously to another Azure Region in a Region Pair to protect against widespread disasters.

Another popular configuration, shown in the diagram, is to use the failover clustering software for both HA and DR. This option is also cost-effective and has the additional advantage of providing a single solution that is easier to implement, test, monitor, maintain and otherwise manage for all applications.

This cost-effective configuration consists of a two-node HA failover cluster spanning two Azure Availability Zones, along with a third instance deployed in a separate Azure Region to facilitate full recoveries from widespread disasters.

"Lifting and Shifting" an existing SQL Server 2008/R2 failover cluster from the premises to the Azure cloud is quite straightforward, and involves simply replacing the shared disk resources with the SANless failover cluster’s virtual volumes, and replacing the disk witness with a file share witness. The only other change involves configuring the Azure Internal Load Balancer (ILB) for client redirection, which also 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, it will be necessary to create one. Most vendors of failover clustering solutions provide detailed documentation, along with comprehensive, step-by-step guides for how to configure HA and/or DR clusters using services available in the cloud. Here is an example of one such guide specific to Azure: Step-By-Step: How to Configure a SQL Server 2008 R2 Failover Cluster Instance on Windows Server 2008 R2 in Azure.

The good news about having "mature" software and services is that you will not be navigating uncharted waters should you choose to migrate your SQL Server 2008/R2 databases to Azure.

Photo Credit: Roland IJdema/Shutterstock

David Bermingham is Technical Evangelist at SIOS Technology. He is recognized within the technology community as a high-availability expert and has been honored to be elected a Microsoft MVP for the past 8 years: 6 years as a Cluster MVP and 2 years as a Cloud and Datacenter Management MVP. David holds numerous technical certifications and has more than thirty years of IT experience, including in finance, healthcare and education.