I have two node SQL Server AlwaysOn Availability Group configured in Synchronous with Automatic Failover mode.
Now since in Synchronous mode, the transactions need to commit on secondary replica first and after its acknowledgment only, it gets committed on the primary node.
Now if my secondary replica node goes down, will the transactions not commit only on my primary node, until unless my secondary comes up? Will my transactions not get hardened to disk?
Best Answer
Books Online has a really good page on this, including:
Put another way, the primary holds transactions for a short period of time, and it'll feel like there's a blocking lock that's stopping your transaction from completing. After the primary gives up on the secondary, the transaction will go through, but only be hardened on the primary - not the unavailable secondary.
The default session timeout period is 10 seconds.
If you want the primary to only accept transactions when a certain number of secondaries are available, you'll need to upgrade to SQL Server 2017 and use the new REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting.