question

chandan_jha18 avatar image
chandan_jha18 asked

Database mirroring login failed

I have configured database mirroring between two servers at a distance 40 miles away from each other. Server specifications: SQL Server 2008,Standard Edition 64-bit This is same for principal,mirror and witness. The configuration is high-safety with automatic failover Initially we tested our .net application(web application) on both the principal and mirror and made sure that the login is not orpahned. Things run fine generally.But sometimes on the mirror server,I see login failed attempts: Login failed for user 'd0main\user'. Reason: Failed to open the explicitly specified database. [CLIENT: xx.xx.x.x] Message Error: 18456, Severity: 14, State: 38. This error appears 3-4 times a day but not more than that. My question to the experts is:If the principal is alive so why the application tries to connect to mirror.The default time-out for a .net webpage is 30 seconds,so is it possible that the application tries to connect principal and after 30 seconds even if principal is alive,it assumes that it is dead and thus tries to open a connection to mirror where it fails. Please help me with this problem.
databasedatabase-mirroring
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Are you sure it is your application trying to connect?
0 Likes 0 ·
KenJ avatar image
KenJ answered
When an application opens an ADO.NET connection to a mirrored database, it automatically caches the name of the mirror server. If it can't connect to the principal server (timeout, intermittent connection issue, etc), it will then try to connect to the mirror. These are probably the occasional connection attempts you are seeing. You might check for corresponding connection failures in your application's event log. This behavior is independent of the failover status of the mirrored database. MSDN has an article, Database Mirroring in SQL Server ( ADO.NET) ([ http://msdn.microsoft.com/en-us/library/5h52hef8.aspx][1]), that covers the behavior of the ADO.Net connection in a mirroring configuration. [1]: http://msdn.microsoft.com/en-us/library/5h52hef8.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chandan_jha18 avatar image
chandan_jha18 answered
KenJ, you are absolutely right.There was a time when the application server could not connect to principal and gave an error that the connection was refused by the host.May be some network issue was responsible.So even when principal was alive and running,the application tried to connect to mirror database.Hence,it gave a login failure error as a mirrored database cannot be read. You are on target from the word go.Thanks mate:-)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.