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

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 2

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 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.

Answer 4

Consider using Polly.

You could use a simple piece of code like -

RetryPolicy retryPolicy = Policy.Handle<Exception>()
            .WaitAndRetry(3, retryAttempt => 
TimeSpan.FromSeconds(retryAttempt));

var result = retryPolicy.Execute(() => someClass.DoSomething());

This will retry the request up to three times.

Answer 5

Are you using SQL Express or Workgroup Edition? If so, it's possible that the server is too busy to respond.

To rule out network problems, from a command prompt, do a PING -t SqlServername. Does every ping come back, or are some lost? This can be an indicator of network interruptions that might also cause this error, like a faulty switch. If they are all lost then (given that your database connection sometimes works) it is likely that ping is being blocked by a firewall somewhere: it may help diagnosis if you find that block and temporarily unblock it.

The error message indicates that you are using Named pipes. Are you using Named pipes on purpose? For most scenarios (including Azure database) I'd suggest enabling TCP/IP and disabling Named Pipes, in SQL Server Configuration Manager.

Depending how 'far away' your Azure database is, the delays because of routers and firewalls sometimes upset Kerberos and/or related timings. You can overcome this by using the port in the connection string to avoid the roundtrip to port 1434 to enumerate the instance. I assume you're already using a FQDN. For example: server\instance,port

Answer 6

It is completely possible that a connection can drop. "Fallacies of Distributed Computing" :). It could be network connectivity issue. Could be at any end.

I would recommend: (assuming firewall is enabled for your machine on Azure)

1. Ping the server and see if there is any loss.

ping (server).database.windows.net

2. tracert
3. telnet can also be your friend.

The above three should help you to pin-point where the problem is.

I think your retry logic is fine.

Regarding you question

Increase Timeout Only if you are sure that your query will take long time. If for a simple insert you have to increase timeout problem could be network connectivity.

Retry Logic As already posted, it's now part of framework which you can utilise or the one you created should be fine. Ideally, it's good to have retry logic, even if you are sure about connectivity and speed. Just in case :)

Answer 7

You should increase the timeout because the time taken to establish a connection to a SQL server has many steps, hence it takes some time when it goes for establishing the connection for the first time. After establishment of the connection, the connection is pooled in the memory for re-use in subsequent queries.

Please refer below link for more detailed understanding on connection-pooling: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

As you mentioned that this error generates sometimes, and not always, so there might be some network and connectivity factors for that. The default timeout for SQL connection is 15 seconds. I think if you change it to 30 seconds, it should work.