Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
Books Online has a really good page on this, including:
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.
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.
