SQL Server Error 35206 can be resolved with this handy guide from our experts at Bobcares.

At Bobcares, we offer solutions for every query, big and small, as a part of our Microsoft SQL Server Support Services.

Let’s take a look at how our Support Team is ready to help customers with SQL Server Error 35206.

All About SQL Server Error 35206

Our Support Team recently had an experience where a long-time customer came across SQL Server Error 35206 after migrating from Linux-based Oracle databases to Azure Cloud windows based SQL Server.

Interestingly, these are both high-end machines capable of running very large SQL Server databases with Alwayson configuration amid the replicas. Furthermore, these databases are highly transnational in nature, exceptionally large in size, and are also capable of running critical systems.

Unfortunately, the connection timeout (SQL error 35206) put a damper on the process, resulting in the following error message:

A connection timeout has occurred on a previously established connection to availability replica ‘bobtest’ with id [availability group id]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

Moreover, the issue may also occur under a different alwayson error 35201. This happens as a result of trying to establish the connection with an availability group but not succeeding due to a connection timeout error 35206. In this scenario, the error message looks something like this:

A connection timeout has occurred while attempting to establish a connection to availability replica ‘bobtest’ with id [availability group id]. 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.

If you have been at the receiving end of these errors, helps is at hand. Our Support Engineers have come up with innovative troubleshooting tips to resolve the issue:

• Fix Always on SQL Server Error 35206 and Error 35201
• Fix Always on Connection Timeout SQL Error 35206
• Use Default Network Packet Size
• Review Connection Timeout Setting

How to fix Always on SQL Server Error 35206 and Error 35201

These errors arise as a result of a connection timeout error between the corresponding replicas. According to our Support Engineers, this may be due to one of the following reasons:

• Availability databases stop synchronizing resulting in potential data loss.
• Logfile starts growing on the primary replica resulting in potential risk for an outage, especially if we do not resolve the issue in time.
• Recurring repetitive alerts for critical systems

As per our Support Engineers, we can fix the issue by going ahead with the cumulative update from Microsoft. However, if that does not help, proceed to the next section.

How to fix Always on Connection Timeout SQL Error 35206

Interestingly, this scenario occurs on high-end systems with over 24 cores CPU in addition to a highly transactional SQL Server. Our Support Techs recommend restarting the SQL Server service on secondary replica alone. It is critical to note that we do not restart the SQL Server on the primary replica.

This should resolve the connectivity issue between the replicas. However, it is a good idea to consider the next two troubleshooting tips as well if you have been facing the issue on a frequent basis.

How to use Default Network Packet Size

Although Microsoft suggests retaining the default value of the network packet size configuration, we tend to make changes as per our requirement. It is a good idea to revert to the default values with these steps:

1. First, connect to the SQL Server Instance in the SSMS (SQL Server Management Studio).
2. Then, right-click the SQL Server Instance and select properties.
3. Next, click the Advanced tab and revert the value to the default value of 4096 and click Ok.

How to review Connection Timeout Setting

The next option would be to take a look at the session or connection timeout settings. We have to ensure the session timeout setting for always on configuration is more than or equal to ten seconds. Alternatively, we can gradually increase session time out in case the network is slow between replicas.

We can do this via SQL Server Management Studio. Additionally, we have to check if we are connected to SQL Server Instance hosting the primary replica of alwayson availability group configuration.

• Launch SSMS and then connect to the primary replica.
• Expand folders after connecting to the primary replica instance. Then, select the plus sign and expand Always on the High Availability node and Availability Groups node.
• Locate and click the corresponding availability group.
• Identify the corresponding availability replica and right-click it and choose the Properties option.
• Change the session timeout period on this replica by entering the new value in the Availability Replica Properties dialog box. Our Support Techs recommend keeping it 10 or more as per requirement.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrated how to resolve SQL Server Error 35206.