While I was doing some research for a client on how to accomplish a specific task, I happened across a BOL topic that caught my attention. With the release of version 3.0 of the Microsoft.Data.SqlClient, there is now a new Configurable Retry Logic that is built-in to the SqlClient. To use this feature you have to download the updated Microsoft.Data.SqlClient package from NuGet.
Internal Retry Logic Providers
There are four retry logic providers that are built-in to the new SqlClient. These are accessible as static methods under the Microsoft.Data.SqlClient.SqlConfigurationRetryFactory class, and provide the ability to create a SqlRetryLogicBaseProvider object that can then be used at either the SqlConnection or SqlCommand level by setting it to the RetryLogicProvider property of the object. The four default configurations that is either a fixed time interval between retries(CreateFixedRetryProvider), an incremental time interval between retries (CreateIncrementalRetryProvider), an exponential time interval(CreateExponentialRetryProvider), or no retry (CreateNoneRetryProvider) . The parameters that control the fixed, incremental, and exponential time for retry are set by passing in a SqlRetryLogicOption object that has the parameters required for the provider.
Creating a Custom Retry Logic Provider
If one of the built-in retry providers doesn’t meet your specific needs, there are three interfaces provided that allow for creating a custom retry logic provider, as well as an example implementation of a custom retry provider in the Books Online. This is really cool because you can choose the specific conditions where you want to perform a retry as a part of the configuration of the custom provider, which will allow handling custom error numbers and messages being raised by the application TSQL code.
Configuring Default Retry Behavior
Personally I think this is one of the most exciting parts of this feature! You don’t have to change existing code to implement all of the provider settings, it can be done with the application configuration file by adding either the SqlConfigurableRetryLogicConnection or SqlConfigurableRetryLogicCommand section to the configSections definition in the application configuration file. These will specify the default behavior when the AppContextSwitchOverride safety configuration switch is enabled in the configuration section.
Event Raised
What’s really great is that there is a Retrying event handler built-in to the provider that gets raised by the provider when it has to retry. The SqlRetryingEventArgs object returned by the event allows canceling the retry, provides information about the exception that caused the retry to occur, what the current delay for retry is, and how many retry attempts have been made. This makes it possible to provide an update to the end user that their query is still processing or is in retry but also allows for the option to cancel the request from retrying during the delay interval.
Initial Thoughts and Summary
I think this is a great new feature, and I really like that it can be set in the application configuration as a default behavior without having to go rework existing code for data access. The built-in providers offer multiple options that can also be set in the configuration file simplifying the deployment of retry logic to existing applications. While coding changes might be required to fully exploit the capabilities of this feature, I think the simplicity of being able to turn this on for existing applications is really fantastic. Imagine your AG failovers not having to cause application outages to your end users and generating exceptions because the application handles retry automatically…. This feature solves a huge problem that has plagued the adopters of Availability Groups for years in my opinion. YES this blog post was short on technical content and just a bunch of links to the Books Online, but I really don’t have a lot I can add to what is already in the documentation for this one. I am really looking forward to being able to play with this further.