When using a web enabled database like SQL Azure, it requires connections over the internet or other complex networks and because of this, you should be prepared to handle unexpected dropping of connections. Established connections consist of: connections that are returning data, open connections in the connection pool, or connections being cached in client side variables. When you are connecting to SQL Azure, connection loss is a valid scenario that you need to plan for in your code. The best way to handle connection loss it to re-establish the connection and then re-execute the failed commands or query.
The quality of all network components between the machine running your client code and the SQL Azure Servers is at times outside of Microsoft’s sphere of control. Any number of reasons on the internet may result in the disconnection of your session. When running applications in Windows Azure the risk of disconnect is significantly reduced as the distance between the application and the server is reduced.
In circumstances when a network problem causes a disconnection, SQL Azure does not have an opportunity to return a meaningful error to the application because the session has been terminated. However, when reusing this connection (like when you use connection pooling), you will get a 10053 (A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine) error.
If you are used to connecting against a single SQL Server within the same LAN and that server fails or goes down for an upgrade then your application will get disconnected permanently. However, if you have been coding against a redundant SQL Server environment you might already have code in place to manage reconnecting to your redundant server when you primary server is unavailable. In this situation you suffer a short disconnect instead of an extended downtime. SQL Azure behaves much like a redundant SQL Server cluster. The SQL Azure fabric manages the health of every node in the system. Should the fabric notice that a node is either in an unhealthy state or (in the event of an upgrade) a node is ready to be taken offline, the fabric will automatically reconnect your session to a replica of your database on a different node.
Currently some failover actions result in an abrupt termination of a session and as such the client receives a generic network disconnect error (A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) The best course of action in these situations is to reconnect and SQL Azure will automatically connect you to a healthy replica of your database.
Like any other database SQL Azure will at times terminate sessions due to errors, resource shortages and other transient reasons. In these situations SQL Azure will always attempt to return a specific error if the client connection has an active request. It is important to note that it may not always be possible to return an error to a client application if there are no pending requests. For example, if you are connected to your database through SQL Server Management Studio for longer than 30 minutes without having any active request your session will timeout and because there are no active requests SQL Azure can’t return an error.
In these circumstances, SQL Azure will close an already established connection:
Before we start writing code to handle connection loss, a few other SQL Azure errors would benefit from a re-establish the connection and then re-execute the failed commands or the query. They include:
The following code takes into account the latency associated with the internet and the potential for connections to get dropped. The code:
/// Generic Code that takes and input and executes
/// a statement against the SQL Azure
/// <param name="companyId"></param>
static void DoSomething(Int32 companyId)
// This is the retry loop, handling the retries session
// is done in the catch for performance reasons
for (Int32 attempt = 1; ; )
// Create a SqlConnection Class, the connection isn't established
// until the Open() method is called
using (SqlConnection sqlConnection = new SqlConnection(
// Open the connection
// Statement To Call
String sql = @"SELECT Color FROM Source WHERE Id = @CompanyId";
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
/// Do Something Wtih Color
catch (SqlException sqlException)
// Increment Trys
// Find Maximum Trys
Int32 maxRetryCount = Int32.Parse(
// Throw Error if we have reach the maximum number of retries
if (attempt == maxRetryCount)
// Determine if we should retry or abort.
static Int32 ConnectionRetryWaitSeconds(Int32 attempt)
Int32 connectionRetryWaitSeconds = Int32.Parse(ConfigurationManager.
// Backoff Throttling
connectionRetryWaitSeconds = connectionRetryWaitSeconds *
/// Determine from the exception if the execution
/// of the connection should Be attempted again
/// <param name="exception">Generic Exception</param>
/// <returns>True if a a retry is needed, false if not</returns>
static Boolean RetryLitmus(SqlException sqlException)
// The service has encountered an error
// processing your request. Please try again.
// Error code %d.
// The service is currently busy. Retry
// the request after 10 seconds. Code: %d.
//A transport-level error has occurred when
// receiving results from the server. (provider:
// TCP Provider, error: 0 - An established connection
// was aborted by the software in your host machine.)
<add name="SQLAzure" connectionString="Server=tcp:youserver.database.windows.net;
<add key="ConnectionRetrys" value="4"/>
<add key="ConnectionRetryWaitSeconds" value="5"/>
Do you have questions, concerns, comments? Post them below and we will try to address them.
I regularly use the Data Access Application Block from the Enterprise Patterns & Practices library. Is that suitable to use when connecting to SQL Azure and the scenarios\error codes you describe above?
We are working with Enterprise Patterns & Practices to get a change in their Data Access Application Block assembly; however there is no date for completion yet.
How can we accomplish the same in LINQ? Any sample code?
Any update on when the Enterprise Patterns & Practices Data Access app block will be updated to accomdate connections attemps to SQL Azure?
Can anyone update on this? When will the Enterprise Patterns & Practices Data Access app block will be updated to accomdate connections attemps to SQL Azure
The Transient Fault Handling Application Block is available here: msdn.microsoft.com/.../hh680934(PandP.50).aspx
For anyone establishing connections with SQL Azure, using the ReliableSqlConnection is certainly a huge improvement from the code sample in this blog post.