Achieving SQL Server HA/DR With a Mix of Always On AGs and SANless SQL Server Failover Cluster Instances

The topic of mixing SQL Server Failover Cluster Instances (FCI) with Always On Availability Groups (AG) is pretty well documented. However, most of the available documentation documents configurations that assume the SQL Server FCI portion of the solution utilizes shared storage. What if I want to build a SANless SQL Server FCI using Storage Spaces Direct (S2D), can I still add a SQL Server AG to the mix? Unfortunately, the answer to this question is no. As of today, this combination of S2D based SQL Server FCI and Always On AG is not supported. I previously blogged about this S2D limitation here.

However, the good news is you CAN build a SANless SQL Server FCI with SIOS DataKeeper and still leverage Always On AG for things like readable secondaries. You still have to abide by the same rules that apply when mixing traditional SAN-based SQL Server FCI and Always On AGs, but other than that, it is exactly the same...mostly.

The only difference is if you decide to configure DataKeeper to use asynchronous replication to a DR node. DataKeeper Synchronous replication is commonly used between nodes in the same data center or cloud region, but you may want to replicate asynchronously to an additional node in a different region for disaster recovery. In this case, if you ever do have to bring the DR node online after an unexpected failure, you will have to scrap the Always On AG configurations and reconfigure them. This requirement is very similar to what Microsoft published here in regards to restoring asynchronous snapshots of SQL Server Always On AGs running inside VMs.

Essentially, a SANLess SQL Server FCI w/DataKeeper looks like a single instance of SQL Server as far as the Always On Availability Group Wizard is concerned. The configuration of the Always On AG is exactly the same as if you were creating just an Always On AG between two Standalone (non-clustered) SQL Server instances.

The real confusion arises in the fact that in this configuration, all the servers reside in the same failover cluster, but the SQL Server FCI is only configured to run only on the cluster nodes where SQL Server was installed as a Clustered SQL Server Instance. The other nodes are in the same cluster, but SQL is installed on those nodes as a Standalone SQL Server Instance, not a Clustered Instance. It's a bit confusing, but what is happening is that Always On AG's leverage the WSFC quorum model and listeners, so all the AG Replicas need to reside in the same WSFC, even though they typically do not run clustered instances of SQL Server. If you are completely confused, that is okay. Most people are confused when they first try to wrap their head around this hybrid configuration.

The real benefit in a configuration like this is that a SQL Server FCI can be a better and more cost effective* HA solution than Always On AG in many circumstances, but it lacks the ability to offer a readable secondary replica. Adding an Always On AG readable secondary replica becomes a viable option to address this need. And using SIOS DataKeeper eliminates the need for a SAN for the SQL Server FCI, which opens up the possibility of configuring SQL Server FCIs where nodes reside in different data centers, also known as Availability Zones in both Azure and AWS.

Please note that pictured below is just one possible configuration. Multiple FCI cluster nodes, multiple AGs, and multiple Replicas are supported. You are only limited by the limits of your version of SQL Server.

This article seems to document the setup steps pretty well. Of course, instead of shared storage for the SQL FCI, you will use SIOS DataKeeper to build the FCI, as I documented here.

As of SQL Server 2016, a scaled-down "Basic Availability Groups" became available in SQL Server Standard Edition, making this configuration possible even in SQL Server Standard Edition. Basic AGs are limited to a single database per Availability Group, a Single Replica (2-nodes). However, they do not support a readable secondary replica, so their use cases in this hybrid configuration are very limited.

Distributed AGs were introduced in SQL Server 2016 and are also supported in this hybrid configuration. Distributed AGs are very similar to regular AGs, but the Replicas do not need to reside in the same cluster or even in the same Windows Domain. Microsoft documents the main use cases of Distributed Availability Groups as follows:

• Disaster recovery and easier multi-site configurations
• Migration to new hardware or configurations, which might include using new hardware or changing the underlying operating systems
• Increasing the number of readable replicas beyond eight in a single availability group by spanning multiple availability groups

If you like the idea of SQL Server FCIs for high availability but want the flexibility of read-only secondary replicas of Always On AGs, this hybrid solution might just be the thing you are looking for. Traditional SAN-based SQL Server FCIs and even Storage Spaces Direct (S2D)-based FCIs limit you to a single data center. By leveraging SIOS DataKeeper to enable SANless clustering, this solution allows you to qualify for the 99.99 percent SLA that Azure or AWS guarantees when leveraging Availability Zones while also supporting Always On AGs for readable secondary replicas. It also opens up the possibility of eliminating the SAN and leveraging locally attached high-speed storage devices without giving up your SQL Server FCI.

Earlier, I promised I would tell you how to save money and do this all with SQL Server Standard Edition. If you can live with readable replicas that are point-in-time snapshot-based, you can skip Always On AGs completely and just use the SIOS DataKeeper target side snapshot feature to periodically take an application consistent snapshot of the volumes on the target server without impacting availability. Here's how...

You can create a 2-node SQL Server FCI with SQL Server Standard Edition and save a boatload of money on SQL licenses, yet still replicate the data to a 3rd node outside the cluster for reporting or DR purposes. If you take a snapshot of the volumes on this third server, these snapshots are read-right accessible, so you can mount those databases from a standalone instance of SQL Server to run month-end reports, copy to archives, or you might even want to use those snapshot to quickly and easily update your QA and Test/Dev environments with the latest SQL data.

I hope you found this helpful and informative. As always, if you have questions, add them here or reach me on Twitter @daveberm.