Retry Transient Failures Using SqlClient / ADO.NET With Polly

Our team maintains a private package that contains our strategy for database migrations. It runs migrations using FluentMigrator as well as other scripts.

Running SQL statements inside SQL Azure can sometimes result in a transient error. If this happens during startup it can result in a broken ASP.NET Core application, as migrations must succeed before the application can safely start. To prevent startup failures we want to retry transient SQL Azure failures.

Code

First, install Polly. Then, use it like this:

Policy
    .Handle<SqlException>(
        ex => SqlServerTransientExceptionDetector.ShouldRetryOn(ex))
    .Or<TimeoutException>()
    .WaitAndRetry(5, retryAttempt => // Adjust the retry interval as you see fit
        TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)))
    .Execute(() =>
    {
        // Your code here using SqlConnection, etc.
    });

Now, for SqlServerTransientExceptionDetector you have 2 options.

• Copy the code from src/EFCore.SqlServer/Storage/Internal/SqlServerTransientExceptionDetector.cs into your project.
  • Required a bit of editing, at least for me (Update the SqlClient using and remove JetBrains.Annotations if you aren’t using them already)
  • This assumes you’re OK to embed code with Apache License 2.0
• Reference it from the Microsoft.EntityFrameworkCore.SqlServer NuGet package.

It does note SqlServerTransientExceptionDetector is an internal API, but it works for now. There’s an issue to track moving it somewhere else: https://github.com/dotnet/SqlClient/issues/39.

I hope this helps make your applications more resilient!