Microsoft SQL Server High Availability: Always On Availability Groups VS Failover Cluster Instances. What to choose and when?

Microsoft SQL Server is probably one of the most well-known and the most widely used database management system. When we talk about databases, there are two most important aspects to them: speed and availability. In this article, we will focus on the latter one as it very often turns out that your business revenue is tied directly to your data – something you simply cannot afford to lose.

Thankfully, Microsoft figured this out back in 2012 with its SQL Server introducing two options to our disposal: Always On availability groups (AG, that’s right, not AAG, not AOAG, just AG) and Always On Failover Cluster Instances (FCI) Also, we are not going to touch other replication mechanisms like storage replication, log shipping, etc.

Always On availability groups

First things first, there technically is Database Mirroring still present in SQL Server 2022, but it is considered a deprecated feature and Microsoft encourages the move to AG accordingly.

Very similarly though, Always On availability groups is a mechanism that creates a replicated environment (High Availability or Disaster Recovery depending on the availability mode) for a set of databases you specify. These databases are called Availability Databases. Hence, since they are in a group, such databases failover together and at the same time.

AG operates on a database level and each set of availability databases is hosted by two types of availability replicas: primary and secondary. Primary replica provides read and write access to the database and a set of up to eight secondary replicas is available to become primary in case of a failover. We’ve mentioned availability mode above and this basically determines if you’re going to build HA or DR for your databases as well as determines the failover options. There are two types of availability mode: asynchronous-commit mode and synchronous-commit mode. To put it simply, with asynchronous, some data loss is possible, with synchronous – no data is lost but at the cost of higher transaction latency. It all depends on your data significance (RPO in other words).

AG requires Windows Server Failover Cluster (WSFC). Each availability replica is hosted on a separate node in WSFC and for each AG, a separate cluster role is created. There is no witness role in Always On availability groups as it was with Database Mirroring. The quorum now depends on the number of nodes in WSFC and all nodes take part, it doesn’t matter if they host replicas or not.

Let’s stop here for a second. There are clusterless availability groups otherwise known as read-scale availability groups. However, this is nor High Availability nor a Disaster Recovery scenario (used only for manual failover). There is also MS SQL Server on Linux which allows configuring Always On availability groups but it requires Pacemaker for HA and DR scenarios. That being said, our main focus here is Windows.

Benefits of Always On Availability Groups

The benefits of using Always On availability groups are probably obvious, but still, let’s summarize them:

• AG supports up to 9 availability replicas (one primary and up to eight secondary);
• You are flexible with failover: planned manual failover or automatic failover (if we’re talking about synchronous-commit mode with no data loss) and forced failover in case of asynchronous-commit mode where data loss is possible;
• In order to make more use of your secondary replicas instead of just let them be and wait for a failover event, you can set them to Active and allow for example to perform backups from them or allow read-only mode to distribute the workload;
• Supports encryption and compression;
• Provides Always On Dashboard for monitoring Always On availability groups, availability replicas, and availability databases.

Restrictions of Always On Availability Groups

Of course, there are certain considerations or, better to say, restrictions to be taken into account when working with AG.

• Availability replicas must be hosted by different nodes within the same WSFC;
• You can use asynchronous-commit mode for all the replicas (one primary and up to eight secondary) or only up to five replicas (including primary) in synchronous-commit mode;
• Failover Cluster Manager should not be used to move availability groups to other nodes or for failover;
• SQL Server logins, linked servers, agent jobs etc. are not synchronized to the secondary databases;

Failover Cluster Instances

Always On Failover Cluster Instances (FCI) has a very similar goal as AG – deliver High Availability for your SQL Server. The main difference though is that FCI works on a server-instance level. FCI represents a single instance of an SQL Server that is deployed across Failover Cluster nodes. In case of hardware or software issues on the node, the instance is failed over to the other one.

Just as with AG, FCI runs in a Failover Cluster while quorum is maintained. The difference here is storage. Whereas AG does not need any shared storage, FCI requires some form of it. This can be cluster disks on an iSCSI or Fiber Channel SAN, Storage Spaces Direct, StarWind Virtual SAN or SMB file shares.

In this case, shared storage allows moving FCI among the nodes in the cluster and this can be done either manually whenever maintenance on other node is needed or automatically (in case of an actual failover event). Of course, there is only a single owner node of SQL Server instance at a time. Correspondingly, there are no specific replication settings as it was with AG since the failover is handled by WSFC and there is no data loss.

Benefits of Failover Cluster Instances

So, what are the benefits you get with FCI?

• High Availability at the SQL Server instance level;
• Both automatic and planned failover are available and managed from a Failover Cluster;
• Flexible shared storage options like iSCSI or FC SAN, SMB shares etc.;
• No need to reconfigure applications and clients associated with SQL Server instance during failovers.

Restrictions of Failover Cluster Instances

Despite providing a lot of benefits for SQL Server high Availability, there are certain downsides to Always On Failover Cluster Instances.

• No option to read from secondary databases as with AG since there is a single instance running, hence, no load balancing;
• Relying on a single SAN as shared storage creates a single point of failure;
• No Disaster Recovery options unless combined with AG.

Differences Between Always On Availability Groups and Failover Cluster Instances

Now that we have an understanding of what Always On availability groups and Always On Failover Cluster Instances are, as well as their main pros and cons, it is time to compare them one-to-one. So, let’s see what is common and what are the differences between these two. Also, an important note to be made: we are comparing strictly AG and FCI as they are. There is an option to use them in a combination but that would be another separate topic.

Conclusion

Always On availability groups and Always On Failover Cluster Instances serve similar purpose – provide higher availability for your SQL Server. Although the goal is pretty much the same, the mechanics behind the two are different. AG might be more complicated to implement and manage but provides broader options in terms of HA and DR. FCI is a more straightforward solution that works on the instance level but requires some form of shared storage. Again, it all depends on your specific infrastructure and goals you want to achieve. One or another (or combined) might be a better fit correspondingly.