Unobtrusive transient fault handling in Azure SQL Database with Entity Framework, Linq To Sql, NHibernate and ADO.NET

It’s fairly well known know that when connecting to an Azure SQL Database you need to protect your application from transient errors that occur when using it.

Luckily, Microsoft provide a library that takes care of most of the effort of detecting these errors and retrying your operations according to a retry policy.

The problem with this library is that most of the documentation out there tells you to essentially wrap every database call in your system in a retry call using the library. Let’s be honest – this is a crappy solution to the problem, particularly for legacy code bases where this might result in a major set of changes to the codebase.

Luckily, there is a way around it:

  • If you are using Entity Framework 6 then check out the new Connection Resiliency functionality, although note that the default implementation doesn’t retry for timeout errors (see the documentation of the below mentioned libraries to see why you might care about that)
  • If you are using NHibernate then you can use the library I’ve previously announced/released; NHibernate.SqlAzure
  • If you are using EntityFramework then the best unobtrusive solutions on the net previously have retries for connection establishment (that have other potential connection management side-effects) and save operations, but not command retries – I’d like to announce the release of a library I have created called ReliableDbProvider that solves these problems in an unobtrusive way (generally you will only need to change your config and possibly one or two lines of code)
  • If you are using ADO.NET (provided you use the Db Provider functionality for managing connections and commands) or Linq To Sql then the ReliableDbProvider library can be used as well

5 Replies to “Unobtrusive transient fault handling in Azure SQL Database with Entity Framework, Linq To Sql, NHibernate and ADO.NET”

  1. I like your ReliableDBProvider solution, but I can’t seem to get it working with an ObjectContext instead of a DbContext. Theres an invalid cast exception. Am I doing something wrong or it havent been tested with ObjectContext ?

    1. Hi Frederic,

      I must admit I didn’t try it with ObjectContext, but assumed it would work.

      I’m more than happy to make it work for ObjectContext, but is there any way you can help me get started by creating a breaking test and submitting a pull request with that breaking test to the repository on GitHub?

      Most of the main tests for it are specified using Entity Framework (via DbContext), but there is one example so far of using another provider at https://github.com/MRCollective/ReliableDbProvider/blob/master/ReliableDbProvider.Tests/MassiveTests.cs. You could do a similar thing for ObjectContext.

      If you aren’t in a position to do that then could you please raise an issue on the GitHub site and I’ll get to it (but it will take a bit longer).

      Thanks 🙂

  2. I found the problem, when using ObjectContext, you have to also change the provider name of the SSDL in the edmx file.

    1. Ah! Nice one.

      Any chance you can submit a pull request to my repository to change the readme to add relevant instructions to help out other people?

Leave a Reply

Your email address will not be published. Required fields are marked *