SQL Server 2022 Always On Synchronous Mode Delay

Question

Hello

I currently have a SQL Server 2022 Always On cluster deployed, with three servers, one for the master and two for the synchronous replicas. During testing, the app exits after a successful login, and after 7 operations, there will be 1-2 automatic pop-up exit prompts with cookie exceptions. This is often caused by a delay in the replica synchronization process. What is the default delay interval value after the master and replica are deployed? How to correct this situation!

Thank you!

Answer 1

Hi @Lee l,

The availability mode determines whether the primary replica waits to commit transactions on a database, until a given secondary replica has written the transaction log records to disk (hardened the log).

• Asynchronous-commit mode An availability replica that uses this availability mode is known as an asynchronous-commit replica. Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgment from asynchronous-commit secondary replicas to harden their transaction logs. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.
• Synchronous-commit mode An availability replica that uses this availability mode is known as a synchronous-commit replica. Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency. Optionally, SQL Server 2017 introduced a required synchronized secondaries feature to further increase safety at the cost of latency when desired. The REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT feature can be enabled to require a specified number of synchronous replicas to commit a transaction before a primary replica is allowed to commit.

For more information, see Differences between availability modes for an Always On availability group.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Yes, in synchronous commit mode, REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT needs to be configured, but I have configured it to 2 because my cluster has one master node and two slave nodes. I can accept a delay of about 1s, but in my current situation, the status of cookie is sometimes not synchronized to the slave copy when logging in, which will cause our customers to automatically pop up the login when logging in, and when they log in again, it may appear once every 7-10 times, and sometimes once every 3-5 times. Is there any key point that I have not configured to optimize?