ReliableSqlConnection alternative for Azure SQL async calls
We are currently using ReliableSqlConnection for all calls to our Azure SQL Database. This works great. But unfortunately the corporate library has been in operation since 2013 and doesn't seem to be being developed anymore. But the main problem is that async db calls are not supported.
I searched SO and other places, but the only one I could find was Polly. And even Polly doesn't seem to be widely used and I haven't found an example for SQL Server. I know ADO.NET supports retry for SqlConnection.Open (), but not for commands, so this is not a real solution. I'm a little surprised that we seem to be the only ones having this problem?
source to share
I saw a pretty good example of using Polly in the eShopOnContainers project (demo on how to design microservice architectures ...)
Well, they use the Circuit Breaker implementation (look here: https://github.com/dotnet-architecture/eShopOnContainers/tree/dev/src/Web/WebMVC/Infrastructure ), I would say that in the same way as HTTP -connections for consuming leftovers, you can do the same with your async SQL connections ...
Use it like:
if (Configuration.GetValue<string>("UseResilientHttp") == bool.TrueString)
{
services.AddSingleton<IResilientHttpClientFactory, ResilientHttpClientFactory>();
services.AddSingleton<IHttpClient, ResilientHttpClient>(sp => sp.GetService<IResilientHttpClientFactory>().CreateResilientHttpClient());
}
else
{
services.AddSingleton<IHttpClient, StandardHttpClient>();
}
So, you can create a ResilientSqlClient and use it in a similar way.
I hope this helps you solve this problem.
source to share
Little known fact: Transient failover is now built into the connection string (.NET 4.6.1+). This document says:
If your client program connects to Azure SQL Database using a class Framework .NET
System.Data.SqlClient.SqlConnection
, , you have to use .NET 4.6.1 or later (or .NET Core), so you can use it retry the connection details of this function here .When you create a connection string for your object
SqlConnection
, you must coordinate values between the following parameters:
ConnectRetryCount
(The default is 1. The range is 0 to 255.)
ConnectRetryInterval
(The default is 1 second. The range is 1 to 60).
Connection Timeout
(The default is 15 seconds. Range is 0 to 2147483647)In particular, your chosen values should make the following equality true:
Connection Timeout = ConnectRetryCount * ConnectionRetryInterval
For example, if count = 3 and interval = 10 seconds, a wait time of only 29 seconds will not give the system enough time for its third and last connection attempt: 29 <3 * 10.
source to share