Environment

-> Below message can be seen in the SQL Server errorlog that has Always ON Availability group configured.

_{A connection timeout has occurred while attempting to establish a connection to availability replica ‘JBSAG3’ with id [322C3054-0352-4F45-A2C2-9328C354051F]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.}

-> When a Session timeout occurs between two availability replicas, the availability replicas assume that a failure has occurred and declares a soft error.

-> Failure due to soft error that might cause timeouts are as below,

• Network errors such as TCP link time-outs, dropped or corrupted packets, or packets that are in an incorrect order.
• An operating system, server, or database that is not responding.
• A Windows server timing out.
• Insufficient computing resources, such as a CPU or disk overload, the transaction log filling up, or the system is running out of memory or threads. In these cases, you must increase the time-out period, reduce the workload, or change the hardware to handle the workload.

-> Above details with regards to soft error is copied from this article.

-> Session timeout value essentially protects SQL Server instance from soft errors by avoiding it to indefinitely wait for a response from other replicas. Always on Availability group uses session time out mechanism by sending out a ping to all connected replicas at a fixed interval. The Replicas ping each other to show that they are still active. If no ping is received from the other replica within the session timeout period, the connection times out and the replica enters the disconnected state.

-> Check the existing Session time out value. Right click Availability group and click properties,

-> The default value for Session timeout is 10 seconds.

-> This message below is logged in SQL Server Error log usually when no ping is received from other replica.

_{A connection timeout has occurred while attempting to establish a connection to availability replica ‘JBSAG3’ with id [322C3054-0352-4F45-A2C2-9328C354051F]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.}

-> To identify the root cause of this issue we can start a continuous ping with a timestamp embedded into it till the issue occurs next time using below powershell command. 

From JBSAG1, ping JBSAG2, JBSAG3. 
From JBSAG2, ping JBSAG1, JBSAG2. 
From JBSAG3, ping JBSAG1, JBSAG2. 

ping.exe -t JBSAG1|Foreach{"{0} - {1}" -f (Get-Date),$_} > C:\temp\ping\JBSAG1.txt

ping.exe -t JBSAG2|Foreach{"{0} - {1}" -f (Get-Date),$_} > C:\temp\ping\JBSAG2.txt

ping.exe -t JBSAG3|Foreach{"{0} - {1}" -f (Get-Date),$_} > C:\temp\ping\JBSAG3.txt

-> When you check the output file after the issue occurred or message is evident in the SQL Server errorlog, you will be able to see a “request timed out” message in the output file.

-> With this details networking team can be contacted to understand the reason why there are ping drops.

-> It could be due to a temporary network issue or some VM level backups happening. Root cause and fix will be required from Networking team.

-> In the meantime, you can increase the session timeout value to a higher value. Increasing the value controls how many seconds that an availability replica waits for a ping response from a connected replica before considering the connection to have failed. By default it is 10 seconds.

-> I increased it to 60 till the network team came out with a solution. In my case it was a temporary ping drop, so 60 seconds value helped me.

-> How to change the session timeout value. Right click availability group and click properties,

-> Once the packet drop was identified (due to VM snapshot in my case) and fixed. I changed the session timeout value back to 10.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.