Proper way to deal with database connectivity issue

Question

I getting below error on trying to connect with the database :

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Now sometimes i get this error and sometimes i dont so for eg:When i run my program for the first time,it open connection successfully and when i run for the second time i get this error and the next moment when i run my program again then i dont get error.

When i try to connect to same database server through SSMS then i am able to connect successfully but i am getting this network issue in my program only.

Database is not in my LOCAL.Its on AZURE.

I dont get this error with my local database.

Code :

public class AddOperation
{
    public void Start()
    {
          using (var processor = new MyProcessor())
          {
              for (int i = 0; i < 2; i++)
              {
                  if(i==0)
                  {
                     var connection = new SqlConnection("Connection string 1");
                     processor.Process(connection);
                  }
                  else
                  {
                      var connection = new SqlConnection("Connection string 2");
                      processor.Process(connection);
                  }   
              }
          }
    }       
}

public class MyProcessor : IDisposable
{
    public void Process(DbConnection cn)
        {
            using (var cmd = cn.CreateCommand())
            {
                cmd.CommandText = "query";
                cmd.CommandTimeout = 1800;
                cn.Open();//Sometimes work sometimes dont
                using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                { 
                   //code
                }
            }
        }
}

So i am confused with 2 things :

1) ConnectionTimeout : Whether i should increase connectiontimeout and will this solve my unusual connection problem ?

2) Retry Attempt Policy : Should i implement retry connection mechanism like below :

public static void OpenConnection(DbConnection cn, int maxAttempts = 1)
        {
            int attempts = 0;
            while (true)
            {
                try
                {
                    cn.Open();
                    return;
                }
                catch
                {
                    attempts++;
                    if (attempts >= maxAttempts) throw;
                }
            }
        }

I am confused with this 2 above options.

Can anybody please suggest me what would be the better way to deal with this problem?

Answer 1

All applications that communicate with remote service are sensitive to transient faults.

As mentioned in other answers, if your client program connects to SQL Database by using the .NET Framework class System.Data.SqlClient.SqlConnection, use .NET 4.6.1 or later (or .NET Core) so that you can use its connection retry feature.

When you build the connection string for your SqlConnection object, coordinate the values among the following parameters:

ConnectRetryCount:  Default is 1. Range is 0 through 255.

ConnectRetryInterval:  Default is 1 second. Range is 1 through 60.

Connection Timeout:  Default is 15 seconds. Range is 0 through 2147483647.

Specifically, your chosen values should make the following equality true:

Connection Timeout = ConnectRetryCount * ConnectionRetryInterval

Now, Coming to option 2, when you app has custom retry logic, it will increase total retry times - for each custom retry it will try for ConnectRetryCount times. e.g. if ConnectRetryCount = 3 and custom retry = 5, it will attempt 15 tries. You might not need that many retries.

If you only consider custom retry vs Connection Timeout:

Connection Timeout occurs usually due to lossy network - network with higher packet losses (e.g. cellular or weak WiFi) or high traffic load. It's up to you choose best strategy of using among them.

Below guidelines would be helpful to troubleshoot transient errors:

1. https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues

2. https://learn.microsoft.com/en-in/azure/architecture/best-practices/transient-faults

Answer 2

Use a new version of .NET (4.6.1 or later) and then take advantage of the built-in resiliency features:

ConnectRetryCount, ConnectRetryInterval and Connection Timeout.

See the for more info: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues#net-sqlconnection-parameters-for-connection-retry

Answer 3

As you can read here a retry logic is recommended even for a SQL Server installed on an Azure VM (IaaS).

FAULT HANDLING: Your application code includes retry logic and transient fault handling? Including proper retry logic and transient fault handling remediation in the code should be a universal best practice, both on-premises and in the cloud, either IaaS or PaaS. If this characteristic is missing, application problems may raise on both Azure SQLDB and SQL Server in Azure VM, but in this scenario the latter is recommended over the former.

An incremental retry logic is recommended.

There are two basic approaches to instantiating the objects from the application block that your application requires. In the first approach, you can explicitly instantiate all the objects in code, as shown in the following code snippet:

var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), 
  TimeSpan.FromSeconds(2));

var retryPolicy =
  new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);

In the second approach, you can instantiate and configure the objects from configuration data as shown in the following code snippet:

// Load policies from the configuration file.
// SystemConfigurationSource is defined in 
// Microsoft.Practices.EnterpriseLibrary.Common.
using (var config = new SystemConfigurationSource())
{
  var settings = RetryPolicyConfigurationSettings.GetRetryPolicySettings(config);

  // Initialize the RetryPolicyFactory with a RetryManager built from the 
  // settings in the configuration file.
  RetryPolicyFactory.SetRetryManager(settings.BuildRetryManager());

  var retryPolicy = RetryPolicyFactory.GetRetryPolicy
  <SqlDatabaseTransientErrorDetectionStrategy>("Incremental Retry Strategy");   
   ... 
   // Use the policy to handle the retries of an operation.

}

For more information, please visit this documentation.