查看: 2375|回复: 0

[SQLServer] C#程序错连SQL Server镜像库问题

发表于 2018-4-2 08:00:02

运维中遇到的一个问题,SQL Server配置了镜像,C#程序在连接SQL Servr数据库时出现登录失败,应用程序日志如下:

C#程序错连SQL Server镜像库问题

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,如果响应慢并且在等待时间内未完成,则进行重连,如下:
C#程序错连SQL Server镜像库问题
链接: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》这篇文章。



回复

使用道具 举报