none
Any way to increase SQL Azure's connection timeout?

    Question

  • We are getting some exceptions when connecting from an Azure web role to a SQL Azure database:

      System.Data.SqlClient.SqlException: 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.)

    I posted a whole thread on the Windows Azure forum, but so far no solution has been suggested:

      Exceptions connecting to SQL Azure: "An established connection was aborted by the software in your host machine"
      http://social.msdn.microsoft.com/Forums/en-US/windowsazure/thread/6002fbce-449c-4398-a2db-a16020dfeea0

    This does not happen on every call, but it happened over 70 times last week as our QA team was testing our web role.
    Both the web role and the SQL Azure database are in the same data center.
    We did set the maxconnection to 96 for connection pooling.
    We use .netTiers (http://nettiers.com/) for our ORM, it's similar to nHibernate.

    I have a gut feeling (which I can't prove) that this exception is occurring due to connection pooling.
    My guess is that the connections in the pool timeout at 10 minutes while SQL Azure times out at 5 minutes.

    I don't know how to change the timeouts in either, so I thought I'd ask here first: is there any way to increase SQL Azure's connection timeout?  In an on-premise SQL Server, I'd right click on the server in SSMS - but the timeout is not an option for a SQL Azure database using SSMS 2008 R2.

    Here's the full exception -

    MYMETHOD() Exception : System.Data.SqlClient.SqlException: 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.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at
     System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at
     System.Data.SqlClient.TdsParserStateObject.WriteSni() at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush() at
     System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc) at
     System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at
     System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at
     Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at
     MYPROJECT.DataAccessLayer.Utility.ExecuteReader(Database database, DbCommand dbCommand) in C:\vss\MYPROJECT\MYPROJECTBusiness\main\MYPROJECT.DataAccessLayer\Utility.cs:line 361 at
     MYPROJECT.DataAccessLayer.SqlClient.SqlMycAccountUserProviderBase.GetPaged(TransactionManager transactionManager, String whereClause, String orderBy, Int32 start, Int32 pageLength, Int32& count) in C:\vss\MYPROJECT\MYPROJECTBusiness\main\MYPROJECT.DataAccessLayer.SqlClient\SqlMycAccountUserProviderBase.generated.cs:line 638 at
     MYPROJECT.DataAccessLayer.Bases.EntityProviderBaseCore`2.GetTotalItems(TransactionManager mgr, String whereClause, Int32& count) in C:\vss\MYPROJECT\MYPROJECTBusiness\main\MYPROJECT.DataAccessLayer\Bases\EntityProviderBaseCore.generated.cs:line 241 at
     MYPROJECT.DataAccessLayer.Bases.EntityProviderBaseCore`2.GetTotalItems(String whereClause, Int32& count) in C:\vss\MYPROJECT\MYPROJECTBusiness\main\MYPROJECT.DataAccessLayer\Bases\EntityProviderBaseCore.generated.cs:line 228 at
     MYPROJECT.DeployManager.ClientResponseFacade.MYMETHOD(MYPROJECTPointClientRequest initialRequest) in C:\vss\MYPROJECT\MYPROJECT.Deploymanager\Main\ClientResponseFacade.cs:line 156
    Friday, December 04, 2009 8:22 PM

Answers


  • Your guess is correct -- an idle connections in the pool is cleaned up after about 8 minutes but the SQL Azure closes idle connections after 5 minutes.  There is a 3-minute window between SQL Azure closing an idle connection and before the pool ejects the the idle connection where the connections in the pool is stale.  If your application picks up the stale connection from the pool in that 3-minute then you get the "transport-level " error.

    We are working on a fix to increase the SQL Azure 5-min timeout to resolve the issue.

    I'll post something to the forum as soon as the fix is in.

    Sorry for the pain.

    Tony
    Tonyp
    • Proposed as answer by Cihan Biyikoglu Sunday, December 06, 2009 7:02 AM
    • Marked as answer by Yi-Lun Luo Saturday, December 12, 2009 6:10 AM
    Saturday, December 05, 2009 9:05 AM

All replies


  • Your guess is correct -- an idle connections in the pool is cleaned up after about 8 minutes but the SQL Azure closes idle connections after 5 minutes.  There is a 3-minute window between SQL Azure closing an idle connection and before the pool ejects the the idle connection where the connections in the pool is stale.  If your application picks up the stale connection from the pool in that 3-minute then you get the "transport-level " error.

    We are working on a fix to increase the SQL Azure 5-min timeout to resolve the issue.

    I'll post something to the forum as soon as the fix is in.

    Sorry for the pain.

    Tony
    Tonyp
    • Proposed as answer by Cihan Biyikoglu Sunday, December 06, 2009 7:02 AM
    • Marked as answer by Yi-Lun Luo Saturday, December 12, 2009 6:10 AM
    Saturday, December 05, 2009 9:05 AM
  • Any update! :0) thanks!
    mitnerd
    Sunday, March 28, 2010 10:41 PM
  •  

    The SU1 update of the service was deployed on Feb-17 and as of that date the "Idle timeout" has been increased to 30-minutes.  For more details about SU1 please see:  http://blogs.msdn.com/sqlazure/archive/2010/02/17/9965464.aspx

     

     


    Tonyp
    Monday, March 29, 2010 3:16 AM