Intermittent SQL Connection Error

Question

I have an ASP.NET application (Sitecore) and the logs display intermittent SQL connection errors in our client workspace. An exception is the following:

Exception: System.Data.SqlClient.SqlException Message: 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) Source: .Net SqlClient Data Provider at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at Sitecore.Data.DataProviders.Sql.DataProviderCommand..ctor(IDbCommand command, DataProviderTransaction transaction, Boolean openConnection) at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass4.<CreateCommand>b__3() at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover) at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass12.<CreateReader>b__10() at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover) at Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters) at Sitecore.Data.DataProviders.Sql.SqlDataApi.<CreateObjectReader>d__6`1.MoveNext() at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source) at Sitecore.Eventing.EventQueue.ProcessEvents(Action`2 handler) at Sitecore.Eventing.EventProvider.RaiseQueuedEvents() Nested Exception Exception: System.ComponentModel.Win32Exception Message: The network path was not found 6420 16:53:53 ERROR Exception processing remote events from database: web Exception: System.Data.SqlClient.SqlException Message: 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) Source: .Net SqlClient Data Provider at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at Sitecore.Data.DataProviders.Sql.DataProviderCommand..ctor(IDbCommand command, DataProviderTransaction transaction, Boolean openConnection) at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass4.<CreateCommand>b__3() at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover) at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass12.<CreateReader>b__10() at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover) at Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters) at Sitecore.Data.DataProviders.Sql.SqlDataApi.<CreateObjectReader>d__6`1.MoveNext() at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source) at Sitecore.Eventing.EventQueue.ProcessEvents(Action`2 handler) at Sitecore.Eventing.EventProvider.RaiseQueuedEvents() Nested Exception Exception: System.ComponentModel.Win32Exception Message: The network path was not found 

This error is typical if you specified an invalid connection string where the server you specified does not exist or is not available on the network. However, the site functions normally in 99% of cases.

In this example, an error occurs from a scheduled Sitecore RaiseQueuedEvents , but an exception also occurs from various other places, including when a URL is sent to the site, resulting in an http of 500.

Interestingly, they appear in waves, i.e. there may be up to 100 of these exceptions in a couple of seconds.

Our client infrastructure team that manages the servers is quite adamant that this is not a network problem, and that something is wrong with the application code, and they reported that when these exceptions occur, there is an increase in database traffic:

(All this is CLEAR SPIKES compared to normal performance) - at 10:10:14 - number, if the number of user connections increased from 60 to 90 - at 10:10:14 - the number of "packet requests / s" increased from 60 to 650 - at 10:10:32 - "average disk time READ" increased from 1 ms to 8.4 ms - at 10:10:32 - network usage increased from 0.3% to 18%

There were no network drops on the sql monitor, there was NO impact on server CPU usage.

I am not an expert in network or SQL performance, but for me these statistics do not seem unreasonable or will be the reason for subsequent connection attempts to get a "network path not found" exception; if the server is busy, do I expect to get a timeout exception?

I contacted Sitecore support, which quickly suggested a network problem:

Based on these exceptions, it looks like they are not related to Sitecore. The messages clearly indicate that you have some kind of network error, so it will be appropriate to investigate further together with your Infrastructure team. I looked at similar problems in our database and can highlight the following areas. - The remote connection was forcibly closed / disconnected - The server was offline - Something related to the wrong security context. A firewall and antivirus can affect this.

We are currently on error; I feel that the error message suggests that this should be a network problem, but according to their team, the site somehow broke down.

How can I diagnose the problem? Could there be a problem with the / sitecore code or is it a network problem?

Update: Network Information

The database server is located on a different network and is connected to the network via VLAN. The servers are load balanced, which * I think * can be done using a firewall rather than with a proper load balancer.

UPDATE 2

The problem was that SQL was configured to use both TCP and Named Pipes. Sometimes he tries to connect to the latter, which does not use the standard SQL port. The solution was to prefix the data source / server in the connection string with Data Source=tcp:xxx.xxx.xx.xxx , to always ensure its connection via TCP

Answer 1

This was not related to Sitecore, but I had previously seen a very similar situation with a different content management system. I also had a similar problem with the fact that the infrastructure staff is convinced that the database server is in order and that the problem is with the website.

I suspected a network problem, so my approach:

• I wrote an SQL script that could be run from the command line and would find the same connection problem.

• I ran this script on the web server and I ran the script on the database server. I recorded the results and compared them.

My tests showed that the error could not be reproduced at all when the script was launched on the database server, but this happened when launched from the command line of the web server. This was evidence to support my guess that the problem was with the connection and was not related to either the website or the database server.

This focused on setting up a firewall sharing the website’s DMZ from the internal network on which the SQL server was running. This firewall was a load-balanced pair - and in the end we were able to find a subtle configuration difference that caused a second field to terminate the connections that were started through the first box.

It seems unlikely that this particular problem will cause a problem for you - but can you find a general approach when developing tests that can help localize the cause of the problem, useful?