C # program incorrectly connects SQL Server mirroring Library issues

Source: Internet
Author: User
Tags failover

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.