Database update query does not complete when the secondary node's disk stops in AlwaysON availability group

Question

We are configuring an SQLServer AlwaysOn availability group.
Server A is the primary role, Server B is the secondary role.
Availability modes is Synchronous-commit mode

Versions:
WindowsServer 2019 StandardEdition
Microsoft SQL Server 2019 Enterprise

To investigate availability in the event of a failure,
We set Server B's disk to not be writable. By taking offline the disk where the database files are stored.
At this time, executing a query to update the database on Server A, the query was not completed while it was running.

Our expected behavior is that query execution will complete on ServerA without error, even in the event of a disk failure on Server B.

Question 1:
Is the expected behavior wrong?

Question 2:
Is there a way for the SQLServer feature to detect a failure and stop synchronizing to a secondary role?

Answer 1

Synchronous-commit mode

Now guess why it's called "Synchronous-commit (!)" mode..

See Supported Availability Modes: transactions wait to send the transaction confirmation to the client until the secondary replica has hardened the log to disk
So yes, your expected behavior is wrong.

Answer 2

Hi @(0919)小林智之 ,

> Can I set a timeout value from ServerA to ServerB for the write to complete?

No, you can’t. There is no setting for this.

If you under synchronous-commit mode, transactions wait to send the transaction confirmation to the client until the secondary replica has hardened the log to disk. When data synchronization begins on a secondary database, the secondary replica begins applying incoming log records from the corresponding primary database. As soon as every log record has been hardened, the secondary database enters the SYNCHRONIZED state. Thereafter, every new transaction is hardened by the secondary replica before the log record is written to the local log file. When all the secondary databases of a given secondary replica are synchronized, synchronous-commit mode supports manual failover and, optionally, automatic failover.

The session-timeout period prevents either replica from waiting indefinitely to receive a ping from the other replica. If no ping is received from the other replica within the session-timeout period, the replica times out. Its connection is closed, and the timed-out replica enters the DISCONNECTED state. Even if a disconnected replica is configured for synchronous-commit mode, transactions won't wait for that replica to reconnect and resynchronize.

Suggest you reading MS document Differences between availability modes for an Always On availability group and What is an Always On availability group to better understanding SQL AG.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

@Olaf Helper

Thanks for the replies.
I found my expected behavior to be wrong. I'd like to check more.

Can I set a timeout value from ServerA to ServerB for the write to complete?

Even if I have a session timeout set, the counter resets when pinged by the secondary server,
so if only the disk is broken, I will have to wait indefinitely for the query to complete on the primary.