Retry Transient Failures Using SqlClient / ADO.NET With Polly

Written by Ken Dale
2
This post is days old.

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.

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!

Suggested reading

Comments