C#程序錯連SQL Server鏡像庫問題

運(yùn)維中遇到的一個問題，SQL Server配置了鏡像，C#程序在連接SQL Servr數(shù)據(jù)庫時出現(xiàn)登錄失敗，應(yīng)用程序日志如下：

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.

以下是出現(xiàn)該問題的web程序配置，
配置1：
web服務(wù)器：.net framework4.5
web.config：timeout=300s，Min Pool Size=10，無";Failover Partner=PartnerServerName".
配置2：
web服務(wù)器：.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

出現(xiàn)此問題的原因是鏡像數(shù)據(jù)庫的連接重試算法中存在錯誤，程序第一次連接的等待時間為timeout*0.08，如果響應(yīng)慢并且在等待時間內(nèi)未完成，則進(jìn)行重連，如下：

鏈接：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增大(預(yù)期值除以0.08)，設(shè)置Min Pool Size

已確認(rèn)通過該方法可以解決這個問題(已應(yīng)用到生產(chǎn)環(huán)境)

如果耐心看完所有資料的，可能會發(fā)現(xiàn)微軟的資料上說明配置了鏡像的sqlserver需要在應(yīng)用程序連接串中顯式指定Failover Partner，即";Failover Partner=PartnerServerName". 為什么應(yīng)用程序會在沒有顯式指定鏡像庫也會出現(xiàn)重連？
請看《Clarification on the Failover Partner in the connectionstring in Database Mirror setup》這篇文章。