Handling SQL Azure Connections issues using Entity Framework 4.0
The underlying platform within SQL Azure consists of many instances of SQL Server, each of which is managed by the SQL Azure fabric. The SQL Azure fabric is a distributed computing system composed of tightly integrated networks, servers, and storage. It enables automatic failover, load balancing, and automatic replication between physical servers.
Troubleshooting Connection-loss Errors
When you start using SQL Azure, you won’t notice anything different when accessing your database, but connection-loss is not uncommon when databases encounter resource shortages. A unique feature of SQL Azure is its ability to monitor and rebalance active and online user databases in the Microsoft data centers automatically. To achieve this, SQL Azure continuously gathers and analyzes database usage statistics and will terminate connections when necessary.
SQL Azure provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all SQL Azure customers, your connection to the service may be closed due to several conditions. For more info regarding SQL Azure and how it handles connections, see:
Therefore, this is an issue we have to handle in our ADO.NET code, and specifically, in this case, in our Entity Framework 4.0 code.
It is recommended that you implement the retry logic in your SQL Azure applications to handle the connection and transaction failures. Addressing such issues in SQL Azure applications is no different than addressing them in on-premise SQL Server applications.
The established connections are the connections that are returning data or the open connections in the connection pool, or the connections being cached in the client side variables. To provide a seamless user experience when a connection is closed, re-establish the connection and then re-execute the failed commands or the query.
In case you are using plain ADO.NET, you can check the link I wrote up-above. But if you are using Entity Framework, it must be implemented in a certain way (code down-below is just a possibility, you could do your own).
The problem we are trying to address is the issue of connection.Open() handing back timed out connections from the pool. The idea/solution is to manually take control of the connection by explicitly opening the connection on the context in an OnContextCreated partial method. What you can then do is test the connection to make sure that it is indeed a healthy connection, by running a low overhead command over it. It is not perfect because of the overhead of issuing the extra command, but it does work.
Basically, you should implement a ‘retry’ logic that you can incorporate into the OnContextCreated() method.
|
partial void OnContextCreated() { // Explicitly open the connection on the context so that the context does not // open/close every time requests are issued Connection.Open(); // Get the underlying store connection so we can issue a dummy command var storeConnection = (SqlConnection)((EntityConnection)Connection).StoreConnection; try { // Issue a low overhead test command new SqlCommand(“declare @i int”, storeConnection).ExecuteNonQuery(); } catch (SqlException) { // Close and reopen the connection // ADO.NET should take care of invalidating the pool // since an exception was already detected Connection.Close(); Connection.Open(); } } |
In case you are using one of the new T4 templates in EF 4.0 (POCO or Self Tracking Entities), you’ll need to change the T4 file.
For example, in our NLayered Sample-App (you can download it from http://microsoftnlayerapp.codeplex.com) we are using Self Tracking Entities, so we added code changing our T4 template file called ‘MainModuleUnitOfWork.Context.tt’. Basically we added the OnContextCreated() method and we’re referencing to it from the constructors of our UoW class implementation (EF Context, in our case, the class called MainModuleUnitOfWork).
T4 Template:
public <#=code.Escape(container)#>()
: base(ConnectionString, ContainerName)
{
Initialize();
OnContextCreated();
}
public <#=code.Escape(container)#>(string connectionString)
: base(connectionString, ContainerName)
{
Initialize();
OnContextCreated();
}
public <#=code.Escape(container)#>(EntityConnection connection)
: base(connection, ContainerName)
{
Initialize();
OnContextCreated();
}
POST UDATE!!
The AppFabric Customer Advisory Team posted a nice and detailed blog-post about this subject. Here it goes!:
Light
Dark
0 comments