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?
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
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