Shulou(Shulou.com)06/01 Report--

For a problem encountered in operation and maintenance, SQL Server is configured with mirroring, and the login failure of the C# program when connecting to the SQL Servr database is as follows:

There is no exception log in the main SQL Server database, and the log record of the image library is 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 caused the problem

Configuration 1:

Web server: .net framework4.5

Web.config:timeout=300s,Min Pool Size=10, no "; Failover Partner=PartnerServerName".

Configuration 2:

Web server: .net framework4.7

Web.config:timeout=20s,Min Pool Size=10, no "; Failover Partner=PartnerServerName".

Microsoft explains as follows:

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.

Link: https://support.microsoft.com/en-us/help/2605597/fix-time-out-error-when-a-mirrored-database-connection-is-created-by-t

The reason for this problem is that there is an error in the connection retry algorithm of the mirror database. The waiting time for the first connection of the program is timeout*0.08. If the response is slow and the reconnection is not completed within the waiting time, reconnect as follows:

Link: 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 or above

2) Timeout increase (expected value divided by 0.08), set Min Pool Size

It has been confirmed that this problem can be solved by this method (applied to the production environment)

If you read all the data patiently, you may find that Microsoft's data states that the mirrored sqlserver needs to explicitly specify Failover Partner in the application connection string, that is, "; Failover Partner=PartnerServerName". Why does the application reconnect without explicitly specifying the mirror library?

Please read the article "Clarification on the Failover Partner in the connectionstring in Database Mirror setup".

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.