A transport-level error has occurred (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Question

I have read both these SO questions and the MS Docs:

• Unexplained SQL errors in production environment - possibly network related
• "The semaphore timeout period has expired" SQL Azure
• https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues#retry-logic-for-transient-errors

And have the same error. I did not have any of these in my ConnectionString: ConnectRetryCount, ConnectRetryInterval or Connection Timeout.

This is a method of my DB class:

public DataTable ExecuteSqlCommand(SqlCommand com)
{
    var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(2));
    var retryPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DataContext"].ToString());

    com.Connection = con;
    SqlDataAdapter da = new SqlDataAdapter(com);
    DataTable dt = new DataTable();
    try
    {
        retryPolicy.ExecuteAction(() =>
        {
            con.Open();
            da.Fill(dt);
        });
    }
    catch (Exception e)
    {
        var telemetry = new TelemetryClient(); // app insights (azure)
        telemetry.TrackException(e);
    }
    finally
    {
        con.Close();
    }
    return dt;
}

So what would be better? Remove the retry stuff from my code and use the attributes in my connection string? Let the framework do the work? Or is my current retry code sufficient? I have the feeling that the enterprise lib and retry stuff is obsolete, but cannot find a good source to confirm my thoughts.

I am using 4.7 and also have EF 6.2, but most queries are just SqlCommands using the code from above.

Answer 1

Make sure the user/login you use on your connection string has access to the master database on your Azure SQL Database server also, not only to the user database. That will provide faster connections and timeouts may disappear.

Using SQL Azure Execution Estrategy may help you with this issue.

public class MyConfiguration : DbConfiguration 
{ 
    public MyConfiguration() 
    { 
        SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy()); 
    } 
}

public class MyConfiguration : DbConfiguration 
{ 
    public MyConfiguration() 
    { 
        SetExecutionStrategy( 
            "System.Data.SqlClient", 
            () => new SqlAzureExecutionStrategy(1, TimeSpan.FromSeconds(30))); 
    } 
}

For more information about SQL Azure Execution Strategy please visit this URL.