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.
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
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:-)
