I work for Microsoft Corp in Redmond, Seattle, in the .NET product team, focusing on .NET, Azure and the Microsoft development platform. This blog is about technical tips and experiences about Software Architecture and Microsoft development technologies.

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

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).