SQL Server and High Availability Configurations Reconsidered for the Cloud

The cloud provides flexibility, ubiquity, and storage integrity that make it appear very attractive from a high-availability (HA) and disaster recovery (DR) perspective. Yet organizations interested in moving their mission-critical SQL Server deployments to the cloud need to plan their approach to HA and DR. Traditionally, SQL Server HA and DR configurations have used SQL Server Failover Cluster Instances (FCIs) or SQL Server Always On Availability Groups (AGs). They both translate to the cloud, but the translation comes at a price.

The absence of shared storage in the cloud

In an on-premises implementation of a SQL Server FCI, failover is facilitated by the use of a shared storage area network (SAN) or network-attached storage (NAS) solution. But a shared, geographically isolated NAS or SAN is not viable in the cloud. The only way to overcome this limitation, other than using third-party tools, is to use the Storage Spaces Direct (S2D) feature of Windows Server. 

Using S2D, you can build a virtual SAN from locally attached server storage. You’ll need two or more servers to build your cluster, but an instance of SQL Server running on any of the VMs in the cluster can interact with storage in the virtual SAN.

As I noted, though, there is a price attached to this option. You can’t create an S2D cluster that spans data centers. Not only does that preclude you from using S2D as a robust DR solution for SQL Server in the cloud, but it also means you can’t build HA solutions that span data centers. You are limited to a single region, single data center deployments, which puts your cluster at greater risk than it would be when spanning “Availability Zones” in Azure and AWS (or simply “Zones” in GCP), which of course means your cluster spans multiple data centers. Single data center deployments preclude you from qualifying for the 99.99% availability SLA, too, which is commonly considered a requirement for highly available deployments.

There's also another price associated with Storage Spaces Direct: You'll need to be running Windows Server 2016 or later and SQL Server 2016 or later. If you're trying to find an HR/DR solution in the cloud for an earlier version of SQL Server, the Storage Spaces Direct approach won't work for you.

The need to replicate data in the absence of shared storage

An Always On AG provides another option for HA in the cloud. And, because an Always On AG can be configured to span data centers, it provides the DR resiliency—and the 99.99% availability option—that you can’t get from S2D in the cloud.

Always On AG does not use shared storage—Virtual SAN or otherwise. Always On AGs actively replicate data from the primary to the secondary instance of SQL Server (in fact, it can replicate data to as many as eight secondary instances). If SQL Server fails on the primary VM, a secondary instance can immediately pick up the workload with minimal disruption to the end-user.

Yet there is a price to this approach, too. Always On AGs don’t replicate all the databases in SQL Server. Your user-defined databases can be replicated, but your master database (MSDB) and other system-defined databases are ineligible for replication. In the event of a catastrophic failure, you could lose all of them if you have not taken other steps to synchronize the information stored in those databases. Always On AGs for SQL Server are pricey, too, as they require the Enterprise Edition of SQL Server 2012 or later. If you support a large number of databases in SQL Server, that poses other issues for you: Always On AGs have only been tested with as many as 100 SQL Server databases (and only as many as 10 AGs). You may be able to run more databases or AGs than that, but then you’re in untested territory.

Deploying SQL Server for HA and DR in the cloud

Despite the limits of the options native to Windows Server, and despite the configuration quirks of the cloud itself, you can configure SQL Server for HA and DR in the cloud. S2D may meet your needs if you're already running SQL Server 2016, and a 99.95% availability guarantee is sufficient. If you need 99.99% availability or DR support, Always On AGs provide an alternate approach to SQL Server availability in the cloud. You'll need to use the Enterprise edition of SQL Server 2012 or later for this, and you'll need to manage system database availability separately. If you're running more than 100 databases on your SQL Server, you'll want to take a very close look before you commit, but otherwise, this is a viable option.