Why does SqlAzureExecutionStrategy not handle error: 19 - Physical connection is not usable

Question

Full exception:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)

Why isn't this handled by the SqlAzureExecutionStrategy? Especially because this happens during VIP swaps.

Is it a good idea to write an own DbExecutionStrategy that handles this one, or am I missing something?

Answer 1

From the profiler trace we observe that the same connection is used for each query database query. This is by design and as discussed early, i.e. when a connection is explicitly opened by the developer it tells EF not to open/reopen a connection for each command.

Well this certainly does not sound like general statement. What profiler trace? Why suppose connection explicitly opened by the developer and handled to the EF? I dont see anything like this in original question (and it is not common practice with EF).

So the questions remain unanswered: Why isn't this handled by the SqlAzureExecutionStrategy? Is it a good idea to write an own DbExecutionStrategy that handles this one?

Since I can see this error in my Azure service from time to time, I decided to test it. Here is my strategy:

public class ExtendedSqlAzureExecutionStrategy : SqlAzureExecutionStrategy
    {
        public ExtendedSqlAzureExecutionStrategy(int maxRetryCount, TimeSpan maxDelay) : base(maxRetryCount, maxDelay) 
        { }

        protected override bool ShouldRetryOn(Exception exception)
        {
            return base.ShouldRetryOn(exception) || IsPhysicalConnectionNotUsableSqlException(exception);
        }

        private bool IsPhysicalConnectionNotUsableSqlException(Exception ex)
        {
            var sqlException = ex as SqlException;
            if (sqlException != null)
            {
                // Enumerate through all errors found in the exception.
                foreach (SqlError err in sqlException.Errors)
                {
                    if (err.Number == 19)
                    {
                        return true;
                    }                    
                }
            }

            return false;
        }
    }

EDIT

Ok, so after some time and logging I can tell that the strategy based on

if (err.Number == 19)

is wrong. Actual SqlException object for this error has ErrorCode = -2146232060 and Number = -1 - I could not find any documentation for those, so I decided not to base strategy on them. For now I am trying trivial check:

public class ExtendedSqlAzureExecutionStrategy : SqlAzureExecutionStrategy
    {
        public ExtendedSqlAzureExecutionStrategy(int maxRetryCount, TimeSpan maxDelay) : base(maxRetryCount, maxDelay) 
        { }

        protected override bool ShouldRetryOn(Exception exception)
        {
            return base.ShouldRetryOn(exception) || IsPhysicalConnectionNotUsableSqlException(exception);
        }

        private bool IsPhysicalConnectionNotUsableSqlException(Exception ex)
        {
            var sqlException = ex as SqlException;
            if (sqlException != null)
            {
                return sqlException.Message.Contains("Physical connection is not usable");
            }

            return false;
        }
    }

EDIT 2:

It works. No more Physical connection is not usable errors at all, and no RetryLimitExceededException, so this error is in fact transient (solvable by retry), so I think it should be included in SqlAzureExecutionStrategy.

Answer 2

If you have explicitly opened the connection, this is a design decision. Microsoft says:

From the profiler trace we observe that the same connection is used for each query database query. This is by design and as discussed early, i.e. when a connection is explicitly opened by the developer it tells EF not to open/reopen a connection for each command. The series of Audit Login/Logout events to retrieve the customer entity or address entity are not submitted as we saw in Case #1 and #2. This means we cannot implement a retry policy for each individual query like I showed earlier. Since the EntityConnection has been assigned to the ObjectContext, EF takes the position that you really truly want to use one connection for all of your queries within the scope of that context. Retrying a query on an invalid or closed connection can never work, a System.Data.EntityCommandExecutionException will be thrown with an inner SqlException contains the message for the error. (see http://blogs.msdn.com/b/appfabriccat/archive/2010/12/11/sql-azure-and-entity-framework-connection-fault-handling.aspx)

Also, and my apologies if you have already seen this, but Julia at the Data Farm goes into quite of bit of detail for transient errors. I'm not sure if 'Physical connection is not usable' is considered transient - it's not included in the list for System.Data.SqlClient.SqlException in the code for SqlAzureExecutionStrategy - but it might be worth looking at: http://thedatafarm.com/data-access/ef6-connection-resiliency-for-sql-azure-when-does-it-actually-do-its-thing/ (and her follow-up, referenced in the linked article).

I haven't looked deeply into Azure since about 2012, but I hope this helps.