The correct way to solve the problem of connecting to the database

Question

When I try to connect to the database, I get the following error:

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

Now sometimes I get this error, and sometimes not, for example: When I run my program for the first time, it successfully opens the connection, and when I run the second time, I get this error the next time I start my program again, then I do not get the error.

When I try to connect to one database server via SSMS, then I can successfully connect, but I get this network problem only in my program.

The database is not in my LOCALLY . She is on AZURE .

I am not getting 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 two things:

1) ConnectionTimeout: Should I increase the connection time for the connection and this will solve my unusual connection problem?

2) Retry attempts policy:. Should I implement a retry join mechanism as shown 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 these two options above.

Can someone suggest me what would be the best way to deal with this problem?

Answer 1

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

TROUBLESHOOTING: Does your application code include retry logic and error handling temporarily? Enabling the correct repetition logic and transients correcting errors in the code should be a universal practice, both inside and in the cloud, either IaaS or PaaS. If this feature is missing, application problems may be caused by Azure SQLDB and SQL Server in Azure VM, but in this case the latter is recommended compared to the first.

Repeated repetition logic is recommended.

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

 var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(2)); var retryPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy); 

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

 // 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, visit this documentation.

Answer 2

Use the new version of .NET (4.6.1 or later), and then use the built-in fault tolerance features:

ConnectRetryCount, ConnectRetryInterval and connection timeout.

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

Answer 3

Are you using SQL Express or Workgroup Edition? If so, the server may be too busy to respond.

To eliminate network issues from the command line, run "PING -t SqlServername". Does every ping come out or is it lost? This may be an indicator of network interruptions, which may also cause this error.

The error message indicates that you are using named pipes. It's right? If not, it can help in the diagnosis.

Depending on how far your Azure database is, delays due to routers and firewalls sometimes upset Kerberos and / or related timings. You can overcome this by using the port in the connection string to avoid going to port 1434 to list the instance. I assume that you are already using the fully qualified domain name. For example: server \ instance, port

Answer 4

It is possible that the connection may fall. "Distributed computing errors" :). This may be a network connectivity issue. Could be at either end.

I would recommend: (assuming the firewall is enabled for your Azure device)

• Ping the server and see if there are any losses.

ping (server) .database.windows.net

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

The above three should help you determine where the problem is.

I think your repetition logic is fine.

Regarding the question

Timeout increase Only if you are sure that your request will take a long time. If you need to increase the latency for a simple insertion, this could be a network connection.

Repeated Logic As already published, this is now part of the framework that you can use, or the one you created should be fine. Ideally, it is good to have repeat logic, even if you are confident in connectivity and speed. Just in case:)

Answer 5

All applications that interact with the remote service are sensitive to transient errors.

As mentioned in other answers, if your client program connects to an SQL database using the .NET Framework System.Data.SqlClient.SqlConnection class, use .NET 4.6.1 or later (or .NET Core) so you can use the reuse function connectivity.

When you create a connection string for your SqlConnection object, coordinate the values ​​between the following parameters:

ConnectRetryCount . The default value is 1. The range is from 0 to 255.

ConnectRetryInterval . The default value is 1 second. The range is from 1 to 60.

Connection timeout . The default value is 15 seconds. The range is from 0 to 2147483647.

In particular, your chosen values ​​should make the following equality true:

 Connection Timeout = ConnectRetryCount * ConnectionRetryInterval 

Now, moving on to option 2, when the application has user retry logic, this will increase the total retry time — ConnectRetryCount time will be used for each user retry. for example, if ConnectRetryCount = 3 and user retry = 5, it will try to make 15 attempts. You may not need many attempts.

If you are considering only user attempt and connection timeout:

Connection timeout usually occurs due to network loss - a network with higher packet loss (for example, cellular or weak WiFi) or a high traffic load. It is up to you, you choose the best use strategy among them.

The following are recommendations to help resolve temporary errors:

• https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues

• https://docs.microsoft.com/en-in/azure/architecture/best-practices/transient-faults