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:
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:
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:
The SessionTimeout parameter in the above case is set to 15, which changes the session timeout value to 15 seconds.
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
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 )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
