Thursday, November 6, 2014

SQL Azure Database with EF 6: The connection is broken and recovery is not possible.

This week I was working on several integration tests for a line of business MVC app my company manages. Integration tests ran fine one at a time. Even two or three at a time. But when we ran all of them after 3 to 5 tests ran the database came toppling over with an error I have never seen before.

Problem

Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. --->

 System.Data.SqlClient.SqlException: The connection is broken and recovery is not possible.  The client driver attempted to recover the connection one or more times and all attempts failed.  Increase the value of ConnectRetryCount to increase the number of recovery attempts. --->

System.Data.SqlClient.SqlException: Database 'DBNAMEHERE' on server 'serveraddress' is not currently available.  Please retry the connection later.  If the problem persists, contact customer support, and provide them the session tracing ID of 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx'.
Login failed for user 'testadmin'.


Holy cow.

It isn't a horrible problem. We are hitting the database with a million requests on a small size VM. Of course if we send it a ton of requests in a short period it is going to fail. And this will never happen in production because there is only at most 7 people logged in to the service at once working at human speed. And if we simply rerun the test it passes. But surely it is solvable.

Solution

The solution to this problem is actually really simple. All it takes is a few lines of code. This should work in both a database first and code first approaches however, I have not tested it on a code first approach.

What we have done is added a class called "ContextConfiguration."

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

What this class does is sets up a custom DbConfiguration. In the constructor we set the execution strategy for a new SQL Azure Strategy. The two arguments for
SqlAzureExecutionStrategy() are the number of times to retry and the max delay time to let it retry. So based on the arguments above it will retry once at exponentially growing intervals until the 30 seconds has been exhausted.

We connect this to our entity framework instance by adding the following to our entityFramework tag in our web.config file.

codeConfigurationType="YourNamespace.ContextConfiguration, YourAssemblyName"

Remember to replace the YourNamespace and YourAssemblyName with the actual values for your project.

Also note this will only work on Entity Framework 6 and above.

Wrap Up

After we published this solution I haven't experienced this error since and have been able to run all our integration tests. It seems to be a good fit for what we are trying to accomplish. If you find any flaws feel free to let me know in the comments because I would love to know.


Sources:

http://msdn.microsoft.com/en-us/data/jj556606.aspx - Entity Framework Config

http://msdn.microsoft.com/en-us/data/jj680699.aspx - Code-Based Configuration


No comments:

Post a Comment