SQL Connection Errors in Microsoft Azure

Question

We have entity framework implemented in our Web API 2.0 code. To call database entities, we are using store procedure calls. Our entire application is hosted in Microsoft Azure cloud. Here are the two exception which we are facing.

Message : An error occurred while executing the command definition. See the inner exception for details.

InnerException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occured while attempting to connect to the Principle server.

One more exception we are facing is like:

Message : The underlying provider failed on Open.

InnerException: The connection was not closed. The connection's current state is connecting.

Note: Code is in C# Web API 2.0. We are using Entity Framework to call Store Procedure. Database is in SQL Server 2012. In web.config, the connection string looks like below:

  <add name="*****Entities" connectionString="metadata=res://*/Models.Database.*****.csdl|res://*/Models.Database.*****.ssdl|res://*/Models.Database.*****.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=*****;Failover Partner=*****;initial catalog=*****;user id=*****;password=********;MultipleActiveResultSets=True;Pooling=false;Connection Lifetime=2;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Also, this errors are not continuous, we guess this might be happening either network issues or while heavy traffic. But we are still not having any firm cause about this.

Please guide us with a solution for the same.

Answer 1

This looks like Transient Fault error to me. You should use EF6 (or later) and setup the Execution Strategies.