SQL Azure Transient Failure Retry

We are having some issues with a database hosted in SQL Azure...

The service has encountered an error processing your request. Please try again. Error code 40143. A severe error occurred on the current command. The results, if any, should be discarded.

The Enterprise Library has the Transient Fault Handling block which would handle this situation with minimal effort. A few months ago there was some discussion about using the LightSpeed.ConnectionStrategy for Azure retries

(http://www.mindscapehq.com/forums/thread/317884)

Has this progressed any further?

Hi Bryn,

No we have not made any updates in the product for this yet but if you want to ping me an email I can send you the code for the ConnectionStrategy mentioned in that thread which hopefully should cover this for you.

Jeremy

Is the RetryConnectionStrategy or AzureDBConnectionStrategy that handles retries available? Or is there a Azure DB database provider that handles the 'specifics' of Azure SQL databases? Thanks! Peter

Hi Peter,

Here is the code mentioned above:

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using Mindscape.LightSpeed;

namespace Helpers
{
  public class LightSpeed
  {
    // error numbers from 
    // http://social.technet.microsoft.com/wiki/contents/articles/retry-logic-for-transient-failures-in-sql-azure.aspx
    // http://msdn.microsoft.com/en-us/library/ff394106.aspx
    static private HashSet<int> _sqlTransientErrors = new HashSet<int>(new int[] { 
        20, 64, 233, 10053, 10054, 10060, 40143, 40197, 40501, 40613    
    });

    // subset of above which require a new connection
    static private HashSet<int> _sqlConnectionErrors = new HashSet<int>(new int[] { 
        40197, 40501, 40613  
    });

    static private int[] _connectionRetryDelays = { 0, 100, 250, 750 };
    static private int[] _commandRetryDelays = { 0, 100, 250, 750 };

    public class AzureConnectionStrategy : ConnectionStrategy
    {
        private IDbConnection _connection;

        public AzureConnectionStrategy(LightSpeedContext context) : base(context) { }

        // can't override protected version
        public IDbConnection CurrentConnection 
        {
            get
            {
                return Connection;
            }
        }

        protected override IDbConnection Connection
        {
            get
            {
                if (_connection != null) return _connection;

                _connection = Context.DataProviderObjectFactory.CreateConnection();
                _connection.ConnectionString = Context.ConnectionString;

                SqlException lastException = null;
                foreach (int delay in _connectionRetryDelays)
                {
                    try
                    {
                        _connection.Open();
                        return _connection;
                    }
                    catch (SqlException ex)
                    {
                        if (_sqlTransientErrors.Contains(ex.ErrorCode) == false) throw ex;
                        Thread.Sleep(delay);
                        lastException = ex;
                    }
                }

                throw lastException;
            }
        }

        public void CloseConnection()
        {
            if (_connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }
        }

        public bool IsConnectionOpen
        {
            get { return _connection != null && _connection.State != ConnectionState.Closed; }
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (_connection != null)
                {
                    _connection.Dispose();
                    _connection = null;
                }
            }

            base.Dispose(disposing);
        }
    }
  }
}

Jeremy