Configurable Retry Logic for Azure SQL Server

 

     You need to watch for Transient errors if you use SQL Server in Azure. Transient errors or Retriable errors can occur any time and your application should be smart enough to retry these failed operations. Azure might quickly shift hardware resources of your database to give you a better load-balance, when this happens your application might not be able to connect to the database. Since these reconfiguration events completes quickly, your application needs to be designed to handle these faults.This adds more complexity to your code because you need to write code to handle this manually. 

      Preview version of  Microsoft.Data.SqlClient library now supports RetryLogic function, you do not need to write any manual code to handle Transient or retriable errors anymore. You can enable Retrylogic function from the code or from the configuration file of your project. To use it, first you need to download and install the latest version of Microsoft.Data.SqlClient library. When I was writing this post, Version was still in Preview

     Next, I need to enable the retry logic function. As I said before, there are two ways to do it. I enable it by using the code in the following example. I use .Net Core 3.1 in this project and I need to add the following underlined code into my Startup.cs file.By adding the code in this level, I am forcing retryonfailure function in connection level. I want my code to retry 3 times when any transient or retriable error occurs. Also, I am adding SQL Error Code 2 to the list of errors. You get the Error Code 2 when client cannot connect to the SQL Server.

    You can use any of the signatures you like to enable RetryOnFail function.

EnableRetryOnFailer(int maxRetryCount)

EnableRetryOnFailer(int maxRetryCount, TimeSpan maxRetryDelay)

EnableRetryOnFailer(int maxRetryCount, TimeSpan maxRetryDelay, List <Addition Sql error codes>)

     We are in good shape to give it a try now. I stopped my SQL Server which will cause Error 2. when following code tries to get the product information, it throws the following error. It looks like It tried 3 times.

     This is great but How can I be sure that it did try three times. I setup an Insight Project to get all information about the events in my code. As you can see in the following picture. Application did try to get the data 3 times after it failed the first time. It tried to get the data 4 times total.

     I am very happy with what I see here. It is great to see new features like this for SQL Server developers.