1 Comments

Since we moved from an on premises SQL Server to SQL Azure we experienced a lot of timeouts. This is the exact errormessage:


Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=3; handshake=29995;

After searching the web for a while it appeared that it is recommended to use the Microsoft Enterprise Library. Version 6 was released last April. So there are not so much code samples at the moment. But there is a free e-book! Developer's Guide to Microsoft Enterprise Library, 2nd Edition. You can download it for free from Microsoft's website or order a paperback from amazon.


I installed two nuget packages for the enterprise library.2014-01-03 16_35_03-Shop - Manage NuGet Packages

My data access class required these two additions to the usings:

using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;
  

And my function which returns a simple datatable looks like this now:

public static DataTable GetDatatable(SqlCommand com)
{
    DataTable dt = new DataTable();

    RetryStrategy retryStrategy = new Incremental("fixed", 5, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(2));
    RetryPolicy retryPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);
    retryPolicy.Retrying += new EventHandler<RetryingEventArgs>(retryPolicy_Retrying);

    List<RetryStrategy> retryStrategies = new List<RetryStrategy> { retryStrategy };

    var manager = new RetryManager(retryStrategies, "fixed"); // this name must match the name of the incremental

    try
    {
        RetryManager.SetDefault(manager); // you have to set the default
    }
    catch { } // but you cannot check if it is already set, that's why I used a try, catch

    try
    {
        retryPolicy.ExecuteAction(() =>
        {
            using (SqlConnection con = new SqlConnection(GetConStr()))
            {
                com.Connection = con;
                SqlDataAdapter da = new SqlDataAdapter(com);

                con.OpenWithRetry();
                da.Fill(dt);
            }
        });
    }
    catch (Exception e)
    {
        WriteDbError(e, com);
    }
    return dt;
}

I know that you can configure the enterprise library to use settings from the web.config. I decided to use hardcoded “variables” because they do not change (much). Please let me know if you have more improvements for the code above. I have a simple GetConStr() method which just returns the connection string from the web.config file.

So this code will solve about 99% of your azure timeouts!

Good luck! Let me know your opinion in the comments!

ps. Happy new year!

Shout it kick it on DotNetKicks.com

Pin on pinterest Plus on Googleplus Post on LinkedIn

Comments

Comment by Gavin

You can use RetryManager.SetDefault(manager, false) and get rid of the try block because it will now not throw an exception if the manager is already set.

Gavin