Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

Are you certain the error is coming from the application? Does the IP address point back to the application server? Or could it be some other application, like SSMS, trying to connect specifically to the mirror?

If not, make sure the principal server is not getting overloaded, if it has times of high CPU, it could fail to respond to a connection attempt, I would run Perfmon to see how it looks.

Also check the SQL error and server event logs on the principal server for any unusual errors that match up with the times of logins on the mirror.

share|improve this answer
    
Thanks Pablo and SQLACID.I went through the logs of mirror,principal and application server.At some point the application server had an error due to the network where it could not connect to principal and hence it tried to make a connection to the mirror and got failed as mirror can't be opened due to no-recovery state –  Chandan Feb 17 '11 at 10:34

Hi I hope this site answers the questions for you, as I am not sure what your question exactly is.

http://msdn.microsoft.com/en-us/library/ms190913.aspx

Or directly: To change the time-out value (high-safety mode only)

* Use the ALTER DATABASE <database> SET PARTNER TIMEOUT <integer> statement.

To view the current time-out value

* Query mirroring_connection_timeout in sys.database_mirroring.


Also check this article under Client Side Redirect: http://technet.microsoft.com/en-us/library/cc917680.aspx

The failover partner in the connection string is used as an alternate server name if the connection to the initial principal server fails. If the connection to the initial principal server succeeds, then the failover partner name will not be used, but the driver will store the failover partner name that it retrieves from the principal server on the client-side cache.

share|improve this answer
    
Sorry if my question was confusing! I want to know that when my principal is up and running why the application sometimes tries to connect to mirror database? I have already increased the partner-time out value to 20 seconds. –  Chandan Feb 16 '11 at 13:37
    
To be more precise: My principal is up for 3 days with no failovers to the mirror.But once or twice in a day,in the mirror server logs,I find the login failure attempt.Is it possible that the even if principal is up,the application server could not reach it and thus tried reching the mirror where it fails(and rightly so) –  Chandan Feb 16 '11 at 13:48
    
updated my post, it seems that the client for some reason is failing to connect to the main server and therefore falling back to the mirror. –  pablo Feb 16 '11 at 14:54
    
Thanks Pablo and SQLACID.I went through the logs of mirror,principal and application server.At some point the application server had an error due to the network where it could not connect to principal and hence it tried to make a connection to the mirror and got failed as mirror can't be opened due to no-recovery state. –  Chandan Feb 17 '11 at 10:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.