A Complete Guide to Modifying Session Timeout for Availability Group Replica

A Complete Guide to Modifying Session Timeout for Availability Group Replica

One of the common errors admins of SQL servers come across is the ‘Always ON Connection Timeout Error’. The connection timeout can occur on availability group replicas. Availability replicas are one of the many components of the availability group.

In case you’re not aware of, ‘SQL Server Session Timeout for availability replica’ is the total duration an availability replica will wait for a response from the connected replica before disconnecting. Once disconnected, the connection is said to have failed.

If you check the value of the duration, it must have been set to 10 seconds by default. Experts recommend that you set this value at either 10 seconds or more to avoid missing out on pings.

Furthermore, the settings you have applied for connection timeout has an effect on both the secondary and the corresponding primary replica. So there will be loss of pings there as well.

That being said, you can change the timeout duration.

How to Chance Session Timeout for Availability Replica?

If you keep getting the timeout errors like SQL errors 35201 or 35206, consider increasing the session timeout value.

You can do that in three ways. In the subsequent sections, learn about the three methods in greater detail.

Using SSMS

You can fix this issue using the SQL Server Management Studio (SSMS). Here are the steps you need to take:
  • Launch SSMS
  • Then connect to your primary replica
  • Next to the Always on High Availability option, click on the ‘plus’ sign and select Availability Groups node
  • Here select the relevant availability group
  • Next select the relevant availability replica. Right click on it and tap on Properties
  • When the Availability Replica Properties dialog box opens, change the value for Session timeout to 10 or higher
This should fix the session timeout issue related to availability group replica.

Using T-SQL Statement

The second way is to use T-SQL statement to fix this issue. Here are the steps you need to take:
  • Launch SSMS
  • Connect to primary replica
  • Open a New Query window
  • Execute the following statement:
Code:
ALTER AVAILABILITY GROUP group_name
MODIFY REPLICA ON 'instance_name' WITH ( SESSION_TIMEOUT =seconds )
When executing the above ALTER AVAILABILITY GROUP statement, make sure to replace the “instance_name” and “group_name” parameter with necessary values.

Using Powershell

The third option is to use Powershell. Here are the steps involved in this case:
  • Change the directory to the server instance where the primary replica is hosted
  • Execute the following command:
Code:
Set-SqlAvailabilityReplica -SessionTimeout 15 `
-Path SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\M
The SessionTimeout parameter in the above case is set to 15, which changes the session timeout value to 15 seconds.
Author
kumkumsharma
Views
1,824
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from kumkumsharma

Top