How Sitecore 8.2 controls the communication with SQL Server Always On?

Question

I was reading about Sitecore using SQL Server Always On, and I saw the ConnectionStrings example

<add name="master" connectionString="Data Source=*****;Initial Catalog=*****;Integrated Security=False;User ID=sa;Password=12345;ConnectRetryCount=10;ConnectRetryInterval=5" />

It came to my mind that SQL Server has a Health-Check Timeout Threshold by calling the sp_server_diagnostics stored procedure in order to verify the primary replica, and if in any case the primary replica becomes unresponsive, an automatic failover is initiated.

That being said, assuming that SQL had an issue and initiated a failover, how Sitecore knows and determine that is time to use the new parameters ConnectRetryCount and ConnectRetryInterval?

Answer 1

Sitecore just uses the standard .net SqlClient data provider. So these values are part of the standard .net connection string properties for 4.5+

According to the docs,

The ConnectRetryCount and ConnectRetryInterval connection string keywords let you control the idle connection resiliency feature.

The connection retry login is built into the SqlConnection object:

Connection versus command

The ConnectRetryCount and ConnectRetryInterval parameters let your SqlConnection object retry the connect operation without telling or bothering your program, such as returning control to your program. The retries can occur in the following situations:

• mySqlConnection.Open method call
• mySqlConnection.Execute method call

There is a subtlety. If a transient error occurs while your query is being executed, your SqlConnection object does not retry the connect operation, and it certainly does not retry your query. However, SqlConnection very quickly checks the connection before sending your query for execution. If the quick check detects a connection problem, SqlConnection retries the connect operation. If the retry succeeds, you query is sent for execution.

Should ConnectRetryCount be combined with application retry logic?

Suppose your application has robust custom retry logic. It might retry the connect operation 4 times. If you add ConnectRetryInterval and ConnectRetryCount =3 to your connection string, you will increase the retry count to 4 * 3 = 12 retries. You might not intend such a high number of retries.

So Sitecore will not handle the retries, if you have the right .net framework version and the values set, that would be handled by the SqlConnection object.

References:

• https://msdn.microsoft.com/en-us/library/ex6y04yf(v=vs.110).aspx
• https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues