asp.net mvc - "The semaphore timeout period has expired" SQL Azure

Question

I am running a .Net MVC Azure Web Site with a SQL Azure database accessed using Entity Framework 6. Intermittently (1 in a thousand or so requests), I get the error "System.ComponentModel.Win32Exception: The semaphore timeout period has expired"

System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) ---> System.ComponentModel.Win32Exception: The semaphore timeout period has expired

There seems to be no reason for it and requests before and after the error and their interactions with SQL Azure are fine. Is there any way to handle or resolve this.

Answer 1

Azure SQL is very different than on premise SQL. When an Azure SQL Server gets overloaded or goes down, it will disconnect a number of connections and when you reconnect you will get sent to another SQL Server.

However with TCP connections you don't know if the other end has terminated the connection unless you actually send information down it, which is why this error occurs.

Once your code know the connection is terminated, it establishes a new connection on the next query, which will work just fine.

With Entity Framework 6 you can now deal with Transient Fault Handling with SQL Azure using Entity Framework

In your DBConfiguration class you need to set your SetExecutionStrategy and configure it. Just create a new class in your project and inherit from DbConfiguration.

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

Full details at Connection Resiliency / Retry Logic (EF6 onwards)