Learn how SQL Server Always On hadr_sync_commit works, its modes, configuration steps, and more. Our SQL server support team is always here to help you.

Understanding SQL Server Always On hadr_sync_commit

In high-availability solutions, SQL Server Always On hadr_sync_commit plays a critical role in maintaining data consistency and minimizing data loss. If youmanage an Availability Group (AG) setup and want direct, actionable insights on hadr_sync_commit, this blog gives you everything you need, without unnecessary filler.

What is hadr_sync_commit?

In SQL Server Always On Availability Groups (AGs), the hadr_sync_commit option configures a database to use or skip synchronous commit mode.

Synchronous commit mode ensures that the system commits transactions to both the primary and secondary replicas before completing the transaction.

Synchronous Commit Modes

OFF:

When you set hadr_sync_commit to OFF, the system doesn’t enforce synchronous commit. It commits transactions on the primary replica without waiting for any confirmation from the secondary replica.

ON:

When you enable hadr_sync_commit, the system enforces synchronous commit. It only marks a transaction as committed on the primary replica after all secondary replicas acknowledge receipt and harden the transaction log.

Important Considerations

• Synchronous commit mode (hadr_sync_commit = ON) provides a higher level of data protection but can potentially introduce some latency because the primary replica must wait for acknowledgments from all secondary replicas before committing transactions.
• Synchronous commit mode is commonly used for critical databases where data loss is not acceptable, such as financial systems or healthcare applications.
• You can configure different databases within an AG to use different synchronous commit modes based on their importance and requirements.

How to Change the hadr_sync_commit Setting

You can change the hadr_sync_commit setting for a database using either SQL Server Management Studio (SSMS) or T-SQL.

To set it using T-SQL, use the following command:

ALTER DATABASE YourDatabaseName

SET HADR_SYNC_COMMIT = {ON | OFF};

Replace YourDatabaseName with the name of the database for which you want to change the setting.

Failover Behavior Explained

• If synchronous commit is enabled (hadr_sync_commit = ON) for a database and a secondary replica becomes unavailable, the primary replica will block transactions until the secondary replica comes back online or the administrator intervenes.
• If synchronous commit is disabled (hadr_sync_commit = OFF) and a secondary replica becomes unavailable, transactions on the primary replica will continue without waiting for the secondary.

[If needed, Our team is available 24/7 for additional assistance.]

Conclusion

Understanding and configuring SQL Server Always On hadr_sync_commit correctly is essential for managing the performance and data safety of your databases. Whether you’re optimizing for performance or aiming for zero data loss, knowing when to toggle this setting ON or OFF gives you full control over your AG behavior.