azure-sql-databasedapperresiliency

How to make Dapper resilient for SqlAzure?


I recently found out Entity Framework has a very easy way to make connections resilient in SQL Azure. Is there a recommended way to accomplish the same in Dapper?


Solution

  • The fastest way to protect against connection issues in C# against Azure is the Microsoft Transient Fault Handling Block.

    For example the below code would retry upto 3 times with 1 second intervals in-between when attempting to open a connection to a Windows Azure SQL Database:

    var retryStrategy = new FixedInterval(3, TimeSpan.FromSeconds(1));
    
    var retryPolicy = 
      new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);
    
    retryPolicy.ExecuteAction(() => myConnection.Open());
    

    FixedInterval is the back off policy, so it will try, wait 1 second, try again, etc until it's tried 3 times.

    SqlDatabaseTransientErrorDetectionStrategy is simply does a check on the exception thrown, if it's a connection exception that should be retried, it will tell the RetryPolicy to execute the action again. If it is not a connection exception, then the action will not be executed and the original exception will be thrown as normal.

    As for when you should use it with Dapper; you can safely retry opening connections and read operations, however be aware or retrying write operations as there is a risk of duplicating inserts, trying to delete a row twice etc.

    More detail here, this library can be found as a NuGet Package here which includes the detection strategies for Windows Azure.