Enhancing Data Durability with SQL Server’s REQUIRED SYNCHRONIZED SECONDARIES TO COMMIT Feature

Introduction

SQL Server 2017 marked a significant advancement in high availability architectures by introducing the REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting. This feature enhances data protection and integrity within Availability Groups (AGs) by meticulously controlling the transaction commit process across replicas. Its role has continued to be pivotal in subsequent releases, including SQL Server 2022, showcasing Microsoft’s commitment to robust data protection mechanisms.

Understanding REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT

The REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting is instrumental in specifying the minimum number of synchronous secondary replicas that must acknowledge a transaction commit before the primary replica proceeds with its own commit. This mechanism ensures that a transaction is only considered complete once it has been securely recorded across multiple replicas, substantially minimizing the risk of data loss.

By default, this setting is configured to 0, mirroring SQL Server 2016’s behavior, where no additional synchronous secondaries are required for a commit to proceed on the primary. Administrators, however, have the discretion to modify this value to enhance data durability further, with the ceiling set at the total number of replicas minus one. It is crucial to note that this setting exclusively applies to replicas configured in synchronous commit mode.

Operational Implications

The implementation of REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT intricately affects transaction management within AGs:

• Transaction Commit Process: The primary replica is designed to wait for the predetermined number of secondary replicas to confirm the commit in their transaction logs. This synchronization ensures data replication and durability across the AG before marking a transaction as complete.
• Handling Unresponsive Replicas: In instances where a secondary replica becomes unresponsive, the primary classifies it as NOT SYNCHRONIZED and continues with its operations. The secondary replica, upon returning online and catching up, reverts to a synchronized state, maintaining data consistency.
• Performance Considerations: While this setting bolsters data durability, it may impinge on the performance of the primary replica. The latency introduced by awaiting commit confirmations from multiple secondaries necessitates a careful balance between data durability and optimal transaction processing performance.

SQL Server 2022: Distributed AG Lossless Failover

In SQL Server 2022, REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT gains additional significance in the context of Distributed Availability Groups, particularly concerning lossless failover. This configuration ensures that before committing a transaction on the primary, it is acknowledged on a specified number of secondary replicas, thus enhancing the durability and consistency of data across geographically distributed replicas.

Configuring REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT

Configuring this setting involves utilizing the ALTER AVAILABILITY GROUP statement, wherein the desired count of synchronized secondaries is specified. For example, applying REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1 requires that at least one secondary replica must commit the transaction prior to the primary.

SQL 2022 Configuration Steps

• Preparation: Halt all transactions on the global primary databases.
• Set Synchronous Mode: Modify the Distributed AG configuration to SYNCHRONOUS.
• Verification: Use the last_hardened_lsn and synchronization_state_desc queries to confirm that the LSNs are identical and synchronized.
• Role Adjustment: Change the global primary to the SECONDARY role.
• Apply Setting:

  ALTER AVAILABILITY GROUP [DAG] SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);

• Recheck last_hardened_lsn.
• Failover: Execute failover with ALLOW_DATA_LOSS.
• Revert to Asynchronous Mode:

  ALTER AVAILABILITY GROUP [DAG] SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0);

• Clear Setting: Adjust the Distributed AG configuration to remove REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT.

Conclusion

The inception of REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT in SQL Server 2017, and its enhancement in SQL Server 2022, underscores a sustained effort towards ensuring high data durability in availability and disaster recovery setups. By judiciously configuring this setting, database administrators can strike a meticulous balance between guaranteeing data durability and maintaining efficient system performance, customized to the nuanced requirements and architecture of their SQL Server environments.