Connection timeout in SQL Azure

Question

I use SQL Azure in my azure asp.net website. Once in a while, the sql aerver connection doesn't open, and I see this exception message(locally and on live site):

Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.

Can someone explain the cause of these, and a suitable fix for this?

Just retry the connection attempt. This is why there's Transient Fault Handling application block for Azure. — sharptooth

Luke Merrett Luke Merrett · Accepted Answer · 2013-07-12T11:57:08

As Sharptooth indicated in his comment; you will get transient errors more frequently on SQL Azure. Microsoft have provided the Transient Fault Handling Block which caters for the common connection issues; allowing you to define a detection strategy and retry policy.

See HERE for a brief overview of what the transient fault handling block does and HERE for a guide on how to add it to your application (Thankfully you can use NuGet).

The handling block primarily works like a try/catch block around your connection; which catches the common connection errors using a DetectionStrategy then retries the connection if the error matches one stated in the strategy.

We use LinqToSQL with the workspace pattern; the block wrapping our connection looks like this:

private const int DELAYMILLISECONDS = 250;

private const int MAXRETRIES = 10;

public static ApplicationDatabaseDataContext Create(string connectionString)
{
    var policy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(
        MAXRETRIES, 
        TimeSpan.FromMilliseconds(DELAYMILLISECONDS));

    var applicationDatabaseDataContext = 
        policy.ExecuteAction(() => GetDataContext(connectionString));

    return applicationDatabaseDataContext;
}

We have used the block for a while now and it mitigates the majority of these issues for us.

Connection timeout in SQL Azure

1 Answers