Minimizing Connection Pool errors in SQL Azure

  • Article

One of the most common errors observed when connecting to SQL Azure is – A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.). This issue happens when SqlClient grabs an invalid connection from the pool (connections in the pool become invalid due to throttling in the network or in SQL Azure itself) and returns it to the application. As a consequence, when it tries to effectively use the connection (like executing a command, for example); a SqlException exception is raised.

Let’s consider the code snippet below:

try

{

    // Let’s assume the connection string uses default connection pool set to true

    // and the connection pool was already previously created for this same

    // connection string

    using (SqlConnection connection = new SqlConnection("…"))

    {

        // If the connection pool in not empty,

        // even if the connection returned above is dead,

        // the SqlConnection.Open command below executes succesfully.

        connection.Open();

 

        SqlCommand command = new SqlCommand("select product_name from products");

        command.Connection = connection;

        command.CommandTimeout = 3;

 

        // The Sqlexception gets thrown here,

        // when it tries to send the command to SQL Server.

        SqlDataReader reader = command.ExecuteReader();

 

        while (reader.Read())

        {

            …

        }

    }

}

catch (SqlException ex)

{

    MessageBox.Show(ex.Message);

}

Currently, the Open method always succeeds, deferring any exception to the command execution itself. To work around this situation, you must add retry-logic every time you connect to SQL Azure from your application. There are plenty of articles and guidance related to this topic and my goal here is not to add one more, but to tell you how to minimize this particular issue.

On August 9th, 2011, Microsoft released the Reliability Update 1 for the .NET Framework 4 (found at https://support.microsoft.com/kb/2533523), which includes a fix to this problem. Basically, it forces SqlClient to check if the connection in the pool is dead before returning it to the application. If the connection is dead, SqlClient simply reconnects before returning it to the application. It’s important to note that that this fix does not add any additional roundtrip to the server. Instead, it just checks the socket status in the TCP layer, which is very fast and effective.

Now, it’s very important to have in mind that this fix does not substitute the need for retry-logic. This is still a recommended practice, especially when connecting to SQL Azure. Our intent is to just minimize the failures in order to improve the overall connectivity experience to SQL Server and SQL Azure.

Luiz Fernando Santos

ADO.NET PM