SQL Connection resiliency by using SQL Connections with retries and exponential backoff

[CESARDELATORRE]

The current SQL Server check is pretty basic. I know it was in "pending to be improved".
It is like the following:
https://github.com/aspnet/HealthChecks/blob/dev/src/Microsoft.Extensions.HealthChecks.SqlServer/HealthCheckBuilderSqlServerExtensions.cs

                    //TODO: There is probably a much better way to do this.
                    using (var connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        using (var command = connection.CreateCommand())
                        {
                            command.CommandType = CommandType.Text;
                            command.CommandText = "SELECT 1";
                            var result = (int)await command.ExecuteScalarAsync().ConfigureAwait(false);
                            if (result == 1)
                            {
                                return HealthCheckResult.Healthy($"SqlCheck({name}): Healthy");
                            }

                            return HealthCheckResult.Unhealthy($"SqlCheck({name}): Unhealthy");
                        }
                    }

Since this library will be pretty much used in microservices environments and cloud environments with for instance Azure SQL DB, there are many cases where you can have transient failures in the SQL connection that should be avoided with a retry strategy.
Entity Framework Core allows to implement a retry with exponential backoff pretty easily, so we might want to evolve the code above and use this mentioned approach that I explained at my blog:
https://blogs.msdn.microsoft.com/cesardelatorre/2017/03/26/using-resilient-entity-framework-core-sql-connections-and-transactions-retries-with-exponential-backoff/

Basically, with something like this:

          options.UseSqlServer(Configuration[“ConnectionString”],
                                           sqlServerOptionsAction: sqlOptions =>
                                           {
                                                 sqlOptions.EnableRetryOnFailure(maxRetryCount: 5,
                                                 maxRetryDelay: TimeSpan.FromSeconds(30),
                                                 errorNumbersToAdd: null);
                                           });

[JuergenGutsch]

+1 this was done last November in a pretty early state. It is kinda prototype.