Is it safe to rely on SqlConnection retry logic while using SqlCommand?

Question

I was using Microsoft.Practice.TransientFaultHandling block for retry logic. Now I switched my application to .Net 4.8 and use the new build in retry logic for SqlConnection. I was wondering if I need a special retry logic for my SqlCommand (I used Polly before) or if this is also build in. There is no possibility to log a retry when relying on the build in functions which makes it really hard to test.

Microsoft states here :

"There is a subtlety. If a transient error occurs while your query is being executed, your SqlConnection object doesn't retry the connect operation. It certainly doesn't retry your query. However, SqlConnection very quickly checks the connection before sending your query for execution. If the quick check detects a connection problem, SqlConnection retries the connect operation. If the retry succeeds, your query is sent for execution."

I tested this by just disconnecting and reconnecting the internet within the retry time range and my command got executed after a while. So it seems to work for this simple scenario. But is it really safe to rely on this or do I still have to implement a retry logic for my SqlCommand?

Here is my code:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString); 
builder.ConnectRetryCount = 5;
builder.ConnectRetryInterval = 3;

MyDataSet m_myDataSet = new MyDataSet();
using (SqlConnection sqlConnection = new SqlConnection(builder.ConnectionString)) 
{
   try
    {
       sqlConnection.Open();
    }
    catch (SqlException sqlEx)
    {
        // do some logging                          
        return false;
    }
    try
    {
        using (SqlCommand cmd = new SqlCommand(selectCmd, sqlConnection))
        {                                         
              using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(m_myDataSet, tableName);
                }                          
        }
    }           
}

Answer 1

No, it's not really safe but it depends on your requirement. Let's say you're using the built-in ConnectRetryCount. As indicated in the MS quote you referenced, there is a small chance it won't retry the operation and will fail. If your application is UI-based, you might decide this small risk is acceptable and ask the user to try again. If, however, your app is running a critical background service where reliability is important, you should implement your own retry logic. You can do this in place of the built-in retry logic, or you can do both. I'd suggest the former to avoid the possibility of unintended effects arising from having two different retry mechanisms running together.

When implementing custom retry logic with Azure, the MS article you referenced recommends waiting 5 seconds before the first retry to avoid overwhelming the cloud service. It also recommends increasing the delay for each subsequent retry. Below is an implementation that does both.

Let's say this is your connection code to the Azure DB:

using (var myDataSource = new SqlDataAdapter(query, connectionString))
{
    myDataSource.Fill(destinationDataTable);
}

Here's the same code with custom retry logic using a simple synchronous implementation. This retries the query up to 5 times, starting with a 5-second wait and doubling the wait each time between subsequent retries:

bool querySuccess = false;
int numAttemptsMade = 0;
int retryInterval = 5000;  // in milliseconds
const int maxNumAttempts = 5;

do
{
    if (numAttemptsMade > 0)
    {
        Thread.Sleep(retryInterval);
        retryInterval *= 2;  // retry after 5 seconds, then 10, then 20, etc.
    }

    numAttemptsMade++;
    Console.Write($"Connecting to Azure DB. Attempt {numAttemptsMade} of {maxNumAttempts}...");

    try
    {
        using (var myDataSource = new SqlDataAdapter(query, connectionString))
        {
            myDataSource.Fill(destinationDataTable);
            querySuccess = true;
        }
    }
    catch (Exception ex)
    {
        Console.Write($"Error retrieving data: {ex.Message}");
    }
}
while (!querySuccess && numAttemptsMade < maxNumAttempts);