共用方式為

Minimizing Connection Pool errors in SQL Azure

  • 發行項

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

Comments

  • Anonymous
    November 05, 2011
    I assume for this to be effective in a Windows Azure server application this patch must be applied in the Azure instance. Do you know if Guest OS release 2.8 (very recently released) includes this?

  • Anonymous
    November 05, 2011
    How do we apply this patch to Windows Azure instance?

  • Anonymous
    November 05, 2011
    This patch is already applied in the Windows Azure Guest OS 2.8. For a complete list of applied patches in the Windows Azure Guest OS2.8, you can go to msdn.microsoft.com/.../hh560567.aspx Thanks

  • Anonymous
    November 11, 2011
    Are there any recommendations on how this would affect using an ORM such as EF, NHibernate or Linq-to-SQL where the connection logic is mostly abstracted?

  • Anonymous
    November 15, 2011
    How to resolve this issue if we are not using EF.

  • Anonymous
    November 16, 2011
    @Bikram: This improvement to SqlClient helps with the reliability of connections to SQL Azure independently of whether you are using EF.

  • Anonymous
    November 16, 2011
    @Ken: There are some blog posts with recommendations on how to apply retry logic with EF in SQL Azure scenarios, e.g.: blogs.msdn.com/.../sql-azure-and-entity-framework-connection-fault-handling.aspx The principles are generally applicable but you might want to adapt the patterns described in the article depending on the interception points available in the specific framework you are using.

  • Anonymous
    December 01, 2011
    When will EF  include SQL Azure retry logic under the hood, without the developer having to wrap every query with the retry logic?  Any visibility into this feature would be greatly appreciated.  Thanks and keep up the great work.

  • Anonymous
    December 11, 2011
    The comment has been removed

  • Anonymous
    March 27, 2012
    Hi Diego! My feature request is up to 18 votes now at the time of writing.  It's the top feature request for the "Azure" keyword.  Any chance this will be implemented soon?  Cheers, Matt

  • Anonymous
    March 27, 2012
    Oh, and here is the link: data.uservoice.com/.../2426525-automatically-perform-retry-logic-for-sql-azure

  • Anonymous
    September 25, 2012
    The comment has been removed

  • Anonymous
    September 25, 2012
    @pwinant - Some info from folks on the providers team... The reliability update fixes a different issue with different associated error message ("A transport-level error has occurred when sending the request to the server").  For more info, please refer to KB support.microsoft.com/.../2533523. The issue you are having seems to be related to an idle or just a valid non-pooled connection and in this case, the Reliability Update is ineffective.

  • Anonymous
    September 26, 2012
    The comment has been removed

  • Anonymous
    July 16, 2015
    msdn.microsoft.com/.../ee336243.aspx

  • "Code sample: Retry logic for connecting to Azure SQL Database with ADO.NET" The downloadable update for KB 2600217 probably includes 2533523, and more.