Tip: Azure SQL Database and transient errors

If you are using Azure SQL Database you may occasionally experience following exception (this is example throw by native SqlClient library):

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  This failure occurred while attempting to connect to the routing destination.

What is this? 

Azure system may occasionally quickly shift hardware resources to better load-balance various workloads. During this reconfiguration time span, you may have connectivity issues to Azure SQL Database. If this happens, your client can receive transient error (transient fault).

How do you handle this?

 If you are using .NET Framework 4.6.1 or later you can simply update your connection string, without touching your code.

Simply add following to your connection string:

ConnectRetryCount=VALUE1_IN_SECONDS;ConnectRetryInterval=VALUE2_IN_SECONDS;Connection Timeout=VALUE3_IN_SECONDS;

where VALUE1_IN_SECONDS is number between 0 and 255, VALUE2_IN_SECONDS is number between 1 and 60, VALUE3_IN_SECONDS is number between 0 and 2147483647.

It is recommended that you set Connection Timeout to: ConnectRetryCount * ConnectionRetryInterval (in our case VALUE3_IN_SECONDS = VALUE1_IN_SECONDS * VALUE2_IN_SECONDS). It is not recommended to set this value to 0 because this indicates no limit (connect waits indefinitely).




Comments are closed