0

For SQL Server Enterprise's Always On availability groups, how can you configure the timeout for Synchronous Commit mode? Is there a maximum? Can it work, for example, between continents?

Thanks

1

how can you configure the timeout for Synchronous Commit mode?

You can do it using SSMS, TSQL and powershell. Microsoft has documented this. Please see Modify the session-timeout period for an availability group replica. For example TSQL would look like

ALTER AVAILABILITY GROUP AccountsAG   
   MODIFY REPLICA ON 'INSTANCE09' WITH (SESSION_TIMEOUT = 15);  

Just note that the timeout value you put is the value which AG will take into consideration making the replica Async in functionality after primary looses connection to secondary replica. Means it will start committing data on primary without waiting for commit on secondary. BUT this does not means that log file will not grow on primary, it will, and primary will store all the log records so that when the secondary comes online it can send records to secondary and sync. If your secondary will be down for long time and you worry about logs being accumulated take the secondary out from the AG and this will allow committing of transaction logs on the primary.

Can it work, for example, between continents

Yes it will, but the timeout value you have to find out which best suits your environment. I have seen such configuration where value was 25 seconds due to network latency

| improve this answer | |
  • Regarding your note. Does it means that if the other node goes down, having a high value in the timeout will affect the availability of the AG ? (The transaction will not be commited on the primary before this timeout is reached and the AG switched to Async) ? – Dominique Boucher Oct 16 at 17:25
  • Answer to what you are asking is mentioned in This MS Doc it says 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. – Shanky Oct 17 at 8:49
  • Yes, I get that part... I'm trying to figure out what would be the side effect of setting this timeout value to a very high number. As long as they are in "sync", the primary will not commit a transaction until it has been commited on the secondary. If the secondary is down, the primary will not receive that commit and if you had set the timeout to, let's say 5 min, then it would take 5 min to swith to "async" and finally commit the transaction. Is this right ? – Dominique Boucher Oct 19 at 12:05
  • 'I'm trying to figure out what would be the side effect of setting this timeout value to a very high number`>> When you have geo WSFC with AG where locations are far and prone to network glitch its advised to keep timeout value high to avoid failover which happens due to network latency just coz latency was high. What if latency accepted between 2 DC on peak network is 10 sec ? So test it and keep latency a bit high so as to rule out latency failovers – Shanky Oct 19 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.