When and How to Use Asynchronous Commit Mode for MS SQL Server Always On

The SQL Server Always On feature provides high availability (HA) and disaster recovery (DR) solutions for Windows and Linux-based SQL Server instances. Once you’ve defined the availability group, you can choose the required availability mode. The availability mode determines whether an availability replica can run in Synchronous commit or Asynchronous commit modes. 

This blog explains the asynchronous commit mode for MS SQL Server Always On Availability group.

Asynchronous-Commit Mode Replica

In the asynchronous commit mode replica, the primary replica does not wait for the secondary replica to harden the log. It immediately writes data to the local transaction log file and sends the transaction confirmation to the client. The secondary replica receives the data and commits the log records. In this case, the secondary replica might have latency for replicating data to a secondary replica database.

The secondary database always remains in a synchronized state. Although the gap between primary and secondary databases can be minimal, it never becomes synchronized with the primary.

The following figure depicts Asynchronous Always On with a single primary and secondary replica:

We might have multiple secondary replicas, depending upon the SQL Server versions. In the case of multiple secondary replicas, all secondary replicas act as individual units and receive transaction blocks from primary to harden on the secondary database.

The SQL Server 2019 enterprise edition supports up to nine availability replicas with one read-write primary and up to eight corresponding secondary replicas. The synchronous replica can be a maximum of five. However, if you are on the standard edition, it supports a basic availability group with two replicas with one database.

If you configure the primary replica in asynchronous commit mode, SQL Server overrides the individual availability mode configuration on the secondary replicas and commits transactions asynchronously.

The Asynchronous commit mode is suitable for disaster recovery and has the following advantages:

• The primary replica does not wait for acknowledgment from the secondary database. Therefore, it minimizes the latency or lag for replicating data to the secondary and getting an acknowledgment before committing a log block on the primary. Therefore, it provides minimal latency for transactions.

• The primary replica does not get impacted by the secondary replica failure.

• Suppose you have the replicas distributed globally. You might have a primary data center and a secondary data center located in different regions. You can use the asynchronous commit availability group to replicate data to the secondary data center SQL Server instance.

Failover Mode in Asynchronous Commit Mode

The asynchronous commit mode only supports forced failover with possible data loss. The data loss depends upon the sync lag between primary and secondary replicas. The forced failover should only be used when your primary replica is unavailable for an extended period and you need immediate data availability. Before the forced failover, if there are any transaction logs that the original primary replica has not sent to the secondary is lost. Forced failover is also called forced manual failover, as it is not automated failover.

With the forced failover, the failover target secondary replica becomes the primary and starts responding to the client requests. The older primary replica becomes a secondary replica if it is available.

When to Use Asynchronous Commit Mode

You should use the Asynchronous commit mode for the following scenarios:

• Disaster recovery solution

• Use asynchronous commit mode if the secondary instances are located at a far distance or have low network bandwidth between primary and secondary instances.

• If you have only one secondary asynchronous data, you should accept the risk of data loss in case of primary server failure. 

• Use asynchronous mode if you need to offload read requests to a secondary asynchronous database. 

• You can combine the asynchronous commit mode with the synchronous data copy. For example, two replicas in synchronous commit mode at the primary site and one asynchronous data commit copy at the secondary site. As shown below, we have Node 01 and Node 02 in synchronous mode while Node 03, 04 and 05 are in asynchronous mode. 

Wrapping Up

When your availability replicas are distributed over large distances, asynchronous commit mode in SQL Server is a great solution for low-latency disaster recovery.

If you’re ready to configure SQL Server Always On availability groups on Linux, check out this step-by-step tutorial for configuring asynchronous availability groups with SQL Server native T-SQL or ClusterControl.

If you’re looking for a way to deploy and manage your SQL Server availability groups from a single GUI, ClusterControl now offers support for availability groups and comes with a free 30-day trial, so you can evaluate it risk-free.

For more updates and best practices for managing your database infrastructure, follow us on Twitter and LinkedIn and subscribe to our newsletter.