C#程序错连SQLServer镜像库问题
运维中遇到的一个问题,SQL Server配置了镜像,C#程序在连接SQL Servr数据库时出现登录失败,应用程序日志如下:
企业建站必须是能够以充分展现企业形象为主要目的,是企业文化与产品对外扩展宣传的重要窗口,一个合格的网站不仅仅能为公司带来巨大的互联网上的收集和信息发布平台,创新互联建站面向各种领域:湿喷机等成都网站设计、成都全网营销推广解决方案、网站设计等建站排名服务。
SQL Server主库无异常日志,镜像库日志记录如下:
Login failed for user 'XXX'. Reason: Could not find a login matching the name provided.[CLIENT:XXX.XXX.XXX.XXX]
Error:18456, Serverity:14, State:5.
Login failed for user 'XXX'. Reason: Failed to open the explicitly specified database.[CLIENT:XXX.XXX.XXX.XXX]
Error:18456, Serverity:14, State:38.
以下是出现该问题的web程序配置,
配置1:
web服务器:.net framework4.5
web.config:timeout=300s,Min Pool Size=10,无";Failover Partner=PartnerServerName".
配置2:
web服务器:.net framework4.7
web.config:timeout=20s,Min Pool Size=10,无";Failover Partner=PartnerServerName".
微软的解释如下:
This issue occurs because of an error in the connection-retry algorithm for mirrored databases.
When the retry-algorithm is used, the data provider waits for the first read (SniReadSync) call to finish. The call is sent to the back-end computer that is running SQL Server, and the waiting time is calculated by multiplying the connection time-out value by 0.08. However, the data provider incorrectly sets a connection to a doomed state if a response is slow and if the first SniReadSync call is not completed before the waiting time expires.
Note The slow response in this case may be triggered either by the server or by network latency.
链接:https://support.microsoft.com/en-us/help/2605597/fix-time-out-error-when-a-mirrored-database-connection-is-created-by-t
出现此问题的原因是镜像数据库的连接重试算法中存在错误,程序第一次连接的等待时间为timeout*0.08,如果响应慢并且在等待时间内未完成,则进行重连,如下:
链接:https://docs.microsoft.com/zh-cn/sql/database-engine/database-mirroring/connect-clients-to-a-database-mirroring-session-sql-server
解决方案:
1) .NET Framework升级到4.5.2及以上
2)Timeout增大(预期值除以0.08),设置Min Pool Size
已确认通过该方法可以解决这个问题(已应用到生产环境)
如果耐心看完所有资料的,可能会发现微软的资料上说明配置了镜像的sqlserver需要在应用程序连接串中显式指定Failover Partner,即";Failover Partner=PartnerServerName". 为什么应用程序会在没有显式指定镜像库也会出现重连?
请看《Clarification on the Failover Partner in the connectionstring in Database Mirror setup》这篇文章。
网页题目:C#程序错连SQLServer镜像库问题
文章链接:http://scyanting.com/article/jidhco.html