How to Change Session Timeout for Availability Group Replica
Sometimes we get alwayson connection timeout errors on availability group replicas. Always on Connection timeout or alwayson session timeout is same and must be configured as per Microsoft recommendation. SQL Server Session timeout for availability replica is the time (in seconds) an availability replica will wait to get a ping response from connected replica before considering the connection as failed.
The connection timeout setting in AOAG applies only to identified secondary replica and its respective primary replica. Each secondary replica has its different session timeout value which works between the given replica and its primary replica. The default value for session timeout configuration is 10 seconds. Microsoft recommends to keep this setting value to 10 seconds or more to prevent system missing pings and false failure alerts.
Change Session Timeout for Availability Replica
This section will explain how to change or modify connection or session timeout value for an AOAG availability replica. This is needed sometimes if you are frequently getting connection timeout errors for your availability replicas. I will show two ways to modify session timeout in always on availability group. One is by using SQL Server Management Studio and another one by using T-SQL statement.
Modify Alwayson Connection Timeout using SQL Server Management Studio
Make sure you have connected to your SQL Server Instance which is hosting primary replica of always on availability group configuration. Follow below steps to change session timeout or connection timeout for identified availability replica where you are facing connection timeout errors 35206 or 35201.
- Launch SQL Server Management Studio and connect to the primary replica
- Expand folders once you connected to the primary replica instance. Click at plus sign to expand Always on High Availability node followed by Availability Groups node.
- Identify and click the availability group for which you have to configure connection timeout for one of its availability replica
- Identify the availability replica for which you have to change this value. Right click at this replica and click at Properties option to launch its properties page
- You can see Session timeout (seconds) option In the Availability Replica Properties dialog box. Use Session timeout (seconds) option to change the number of seconds for the session timeout period on this replica. Always keep it 10 or more as per your specific need.
We have successfully changed alwayson connection timeout for an availability replica using SQL Server management studio in above steps. Now, i will show you T-SQL approach to get this done in below section.
Change Alwayson Connection Timeout using T-SQL Statement
Here, i will show you how to change session timeout for an availability replica using T-SQL statement.
- Launch SQL Server Management Studio and connect to the primary replica.
- Open New Query window and run below ALTER AVAILABILITY GROUP statement.
ALTER AVAILABILITY GROUP group_name
MODIFY REPLICA ON ‘instance_name‘ WITH ( SESSION_TIMEOUT =seconds )
Make sure to replace group_name with your availability group name, instance_name with your secondary replica name for which you want to change session timeout value and seconds with appropriate time in seconds.
Once you will execute above statement, connection timeout value will change as per your specified value for your availability replica. Now, you can go ahead and monitor the connection timeout errors to see whether your issue has been fixed or not.
Here, i have explained how to modify or change session timeout for an always on availability group replica.
Read More:
- SQL Server Alwayson Interview Questions & Answers
- SQL Server Alwayson Error 35250: Joining database on Secondary Replica resulted in an error
- Understanding Backup Preferences for AlwaysOn Availability Group Databases
- Seven Computer Efficiency Tips for Less Experienced Users - October 4, 2021
- Managing iCloud Storage More Efficiently - August 18, 2021
- How to Change Session Timeout for Availability Group Replica - July 22, 2021
