Exploring Synchronous Commit Mode for SQL Server Always On

SQL Server Always On is a database availability solution that provides automated database replication and failover across standalone servers and SQL Server instances. It allows database administrators and developers to build solutions that guarantee database uptime and eliminate planned downtime.

Synchronous replication technology enables transactions to be written to multiple synchronous secondary replicas. It provides the capability to wait for all secondary replicas to receive and log transactions before being considered committed. It also ensures that all transactions are durable and committed on all replicas before returning success (Commit) to the user. Transaction Logs are shipped to each secondary replica, and the secondary replicas can serve read requests.

This blog will explore Synchronous commit mode for MS SQL Server Always On.

Synchronous Commit Mode Replica

SQL Server Always On Synchronous commit mode focuses on high availability over performance. The performance trade-off could be due to the increased transaction latency. It guarantees that a SQL Server transaction is committed on both the primary and secondary replicas. Once a user starts a transaction at the primary replica, SQL Server sends the transaction to the synchronous secondary replica. Once the secondary replica hardens the log to disk, it sends an acknowledgment to the primary replica. Upon receiving the acknowledgment from the secondary replica, the primary replica hardens the log and sends transaction confirmation to the user.

The following figure depicts synchronous Always On with a single primary and secondary replica.

Tech Community Microsoft

Once the initial data synchronization occurs, the secondary database state changes to SYNCHRONIZED. SQL Server ensures that while the database is in the SYNCHRONIZED state, it writes to both the primary and secondary replicas.

Failover Modes in Synchronous Data Commit Always On

Synchronous Commit Mode in Always On supports both Automatic and Manual failovers.

Automatic failover

The automatic failover requires the current primary replica and at least one secondary replica in synchronous commit mode with automatic failover mode configurations. It ensures that the database is quickly available without any data loss in case of issues with the primary replica.

Note: You can have up to three secondary replicas in automatic failover configuration for SQL Server Enterprise edition.

Automatic failover is possible in the following scenario:

• The secondary replica must be synchronized with the primary replica, i.e., all the secondary databases are synchronized.

• The Failover Clustering (WSFC) cluster must have a quorum.

The secondary replica takes over the primary role during the automatic failover and recovers all databases to serve client requests. If the old primary comes online, it takes over the secondary replica role and starts receiving log blocks from the new primary replica.

Note: If you have set failover mode as MANUAL for a primary replica, SQL Server cannot perform automatic failover even if the secondary replica is in AUTOMATIC failover mode.

Manual Failover

You can do a manual failover from the primary to the secondary replica. If the primary and secondary replicas are in the Synchronized state, you can initiate a failover to promote the secondary replica as Primary. This type of manual failover does not cause any data loss.

Reasons for Data Latency in Synchronous Data Commit

If you experience data latency issues between your primary and secondary replicas, it could be due to the following reasons:

• A network delay or glitch causes timeout issues between the secondary and primary replica.

• Suppose you suspend availability group synchronization manually on the secondary replica. In this case, the secondary database health is NOT HEALTHY, and it cannot synchronize log records until the secondary database is resumed and resynchronized.

• Changing the primary replica state to asynchronous commit mode changes the availability group into asynchronous mode irrespective of the secondary database configuration.

When To Use Synchronous Data Commit Always On

You should use synchronous data commit for Always On for critical, highly available databases that emphasize high availability over performance and to prepare the fully synchronous copy of the database in a primary data center.

You should use synchronous data commit for Always On for:

• Critical, highly available databases that emphasize high availability over performance.

• Prepare the fully synchronous copy of the database in a primary data center.

Wrapping Up

Depending on your specific use case, you can implement either synchronous or asynchronous data commit for your SQL Server Always On availability groups. If you’d like more clarification on which data commit mode will best fit your needs, first check out this article on when and how to use asynchronous commit mode for Always On.

Currently, ClusterControl supports asynchronous availability groups and offers a free 30-day trial — no CC required. To get familiar with ClusterControl for SQL Server Always On, you can refer to this post on setting up SQL Server Always On using ClusterControl and this deployment guide.

More helpful content will be coming your way with the latest tips and best practices for deploying and managing your SQL Server databases, so be sure to connect with us to stay in the loop. Follow us on LinkedIn and Twitter, and subscribe to our newsletter for regular updates.