asp.net – 间歇性SQL连接错误

我有一个ASP.NET(Sitecore)应用程序,日志在我们客户的生产环境中显示间歇性的SQL连接错误.例外情况如下：

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,DbConnection owningConnection,DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool,DbConnection owningObject,DbConnectionOptions options,DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject,DbConnectionOptions userOptions,DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject,DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject,UInt32 waitForMultipleObjectsTimeout,Boolean allowCreate,Boolean onlyOneCheckConnection,DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject,TaskCompletionSource`1 retry,DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection,DbConnectionInternal oldConnection,DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection,DbConnectionFactory connectionFactory,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,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

如果您提供的连接字符串无效,而您指定的服务器不存在或无法在网络上访问,则此错误是典型的.但是,该网站99％的时间都能正常运行.

在此示例中,错误来自Sitecore的RaiseQueuedEvents计划任务,但异常也是从其他各个地方引发的,包括在网站中点击URL时产生的http 500.

有趣的是它们是波浪形的,即在几秒钟的时间内可能有多达100个例外.

管理服务器的客户基础架构团队非常坚定地认为它不是网络问题,并且应用程序代码有问题,并且报告在这些异常似乎发生时数据库流量有所增加：

(All these are CLEAR SPIKES compared to usual performance)
– at 10:10:14 – number if user connections increased from 60 to 90
– at 10:10:14 – number of “batch requests / s” increased from around 60 to 650
– at 10:10:32 – “disk avg. READ time” increased from 1ms to 8.4ms
– at 10:10:32 – network utilisation spiked from 0.3% to 18%

有sql监视器注册没有网络丢弃,有
对服务器CPU利用率没有影响.

我不是网络或SQL性能的专家,但对我来说,这些统计数据似乎并不合理,或者是后续连接尝试接收“未找到网络路径”异常的原因;如果服务器很忙,我会收到超时异常吗？

我已经联系过Sitecore支持人员,他们迅速建议这是一个网络问题：

Based on these exceptions it doesn’t seem they are Sitecore related.
Messages clearly state that you have some kind of network error,so it
will be appropriate to investigate further along with your
Infrastructure team. I reviewed similar issues in our database and can
highlight the following areas.
– Remote connection was forcibly closed/disabled
– Server was offline
– Something related to wrong security context. Firewall and antiviruses may affect that.

目前,我们正在争吵;我的感觉是,错误消息表明它必须是一个网络问题,但他们的团队意见该网站在某种程度上被打破.

我怎样才能诊断出问题所在？代码/ sitecore可能存在一些问题,或者这是一个网络问题？

更新：网络详细信息

数据库服务器托管在不同的网络上,并通过我认为的VLAN进行联网.服务器负载均衡,*我认为*可以使用防火墙而不是适当的负载均衡器来完成.

更新2

问题是SQL配置为允许TCP和命名管道.有时它会尝试连接不使用标准SQL端口的后者.解决方案是在连接字符串中使用Data Source = tcp：xxx.xxx.xx.xxx为数据源/服务器添加前缀,以始终确保通过TCP连接

解决方法

我怀疑网络问题,所以我的方法是：

>我写了一个SQL脚本,可以从命令行运行,并显示相同的连接问题.
>我在Web服务器上运行该脚本,然后在数据库服务器上运行脚本.我记录了结果并对它们进行了比较.

我的测试显示,当脚本在数据库服务器上运行时,根本无法重现该错误,但是当从Web服务器的命令行运行时确实发生了错误.这证明支持我的预感,即问题与连接有关,并且与网站或数据库服务器无关.

这将注意力集中在防火墙的设置上,将网站的DMZ与SQL服务器所在的内部网络分开.这个防火墙是一个负载平衡的对 – 我们最终能够找到一个微妙的配置差异,这导致第二个盒子终止通过第一个盒子启动的连接.

这个特定问题似乎不太可能导致问题 – 但您可能会发现提出测试的整体方法可以帮助解决问题的原因有用吗？

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!