An issue encountered in operations, SQL Server configured mirroring, and C # programs failed to log on when connecting to the SQL SERVR database, the application log is as follows:
There is no exception Log for the SQL Server main library, and the Mirror library log records are as follows:
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.
The following is the Web program configuration that occurs with this problem.
Configuration 1:
Web server:. NET framework4.5
Web.config:timeout=300s,min Pool size=10, none "; Failover Partner=partnerservername ".
Configuration 2:
Web server:. NET framework4.7
Web.config:timeout=20s,min Pool size=10, none "; Failover Partner=partnerservername ".
Microsoft's explanation is as follows:
This issue occurs because of a error in the Connection-retry algorithm for mirrored databases.
When the retry-algorithm was used, the data provider waits for the first read (Snireadsync) call to finish. The call was sent to the Back-end computer which is running SQL Server, and the waiting time was calculated by multiplying th E 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 snireads Ync call was not completed before the waiting time expires.
Note The slow response in the "May is triggered either by the server or by network latency.
Link: https://support.microsoft.com/en-us/help/2605597/ Fix-time-out-error-when-a-mirrored-database-connection-is-created-by-t
This problem occurs because of an error in the connection retry algorithm of the mirror database, where the wait time for the first connection of the program is timeout*0.08, and if the response is slow and is not completed within the wait time, it is re-connected as follows:
Links: https://docs.microsoft.com/zh-cn/sql/database-engine/database-mirroring/ Connect-clients-to-a-database-mirroring-session-sql-server
Solution:
1). NET Framework upgrade to 4.5.2 and above
2) Timeout increase (expected value divided by 0.08), set min Pool Size
It has been confirmed that this method can solve this problem (applied to production environment)
If you are patient with all the information, you may find that Microsoft's profile indicates that SQL Server configured with the mirror needs to explicitly specify failover Partner in the application connection string, i.e. "; Failover Partner=partnerservername ". Why does an application not explicitly specify a mirror library to be re-connected?
See the article "Clarification on the Failover Partner in the" ConnectionString in Database Mirror setup.
C # program incorrectly connects SQL Server mirroring Library issues