Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Automatic Mirroring fail over and see Login failed errors in error log of current Mirror server Expand / Collapse
Author
Message
Posted Wednesday, July 16, 2014 1:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 414, Visits: 2,083
Hi,

We have high safety with automatic failover setup in our Sql Server 2008 R2 environment, and automatic failover is functioning without any issues. All the applications are also working as expected when the databases are failed over to Mirror server.

But i am seeing bunch of these error messages in SQL error log on the Current Mirror server(which was principal server before the failover). Can some one suggest is this a normal behavior or not?

Login failed for user 'XXXXXX'. Reason: Failed to open the explicitly specified database
Message
Error: 18456, Severity: 14, State: 38.[highlight=#ffff11][/highlight]


Thanks in advance.
Post #1593270
Posted Thursday, July 17, 2014 4:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 6,634, Visits: 14,213
muthyala_51 (7/16/2014)
Hi,

We have high safety with automatic failover setup in our Sql Server 2008 R2 environment, and automatic failover is functioning without any issues. All the applications are also working as expected when the databases are failed over to Mirror server.

But i am seeing bunch of these error messages in SQL error log on the Current Mirror server(which was principal server before the failover). Can some one suggest is this a normal behavior or not?

Login failed for user 'XXXXXX'. Reason: Failed to open the explicitly specified database
Message
Error: 18456, Severity: 14, State: 38.[highlight=#ffff11][/highlight]


Thanks in advance.

This link describes state 38 pretty well, most likely you have orphaned logins on the mirror partner. Synchronise the logins across from the Primary to the mirror


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1593464
Posted Thursday, July 17, 2014 6:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 2,068, Visits: 3,599
Expected behaviour, no?

The connection string specifies ServerName and FailoverPartner. So the app tries ServerName first, then FailoverPartner if unsuccessful.
The login errors you're seeing are the attempts of your app to connect to ServerName, which fail as it's the mirror and the database are unavailable. The connection then tries FailoverPartner and is successful.
Post #1593553
Posted Thursday, July 17, 2014 11:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 414, Visits: 2,083
Perry Whittle (7/17/2014)
muthyala_51 (7/16/2014)
Hi,

We have high safety with automatic failover setup in our Sql Server 2008 R2 environment, and automatic failover is functioning without any issues. All the applications are also working as expected when the databases are failed over to Mirror server.

But i am seeing bunch of these error messages in SQL error log on the Current Mirror server(which was principal server before the failover). Can some one suggest is this a normal behavior or not?

Login failed for user 'XXXXXX'. Reason: Failed to open the explicitly specified database
Message
Error: 18456, Severity: 14, State: 38.[highlight=#ffff11][/highlight]


Thanks in advance.

This link describes state 38 pretty well, most likely you have orphaned logins on the mirror partner. Synchronise the logins across from the Primary to the mirror



Hi Perry,

But i have created the logins/transfered the logins from Principal to Mirror using sp_help_revlogin. I do not see any orphan logins on the mirror server. Thanks.
Post #1593754
Posted Thursday, July 17, 2014 12:05 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 414, Visits: 2,083
Gazareth (7/17/2014)
Expected behaviour, no?

The connection string specifies ServerName and FailoverPartner. So the app tries ServerName first, then FailoverPartner if unsuccessful.
The login errors you're seeing are the attempts of your app to connect to ServerName, which fail as it's the mirror and the database are unavailable. The connection then tries FailoverPartner and is successful.


Hi Gazareth,
So as this is not expected behavior, can you please give me some insight on what should be i looking into to troubleshoot and find the cause of these failed logins. Interestingly all the websites are functioning fine. Is there anything i can look into IIS webservers end? to find out that the connection attempt is going to (old principal)current Mirror server instead to the actual current Primary server(old Mirror server). Thanks.
Post #1593758
Posted Thursday, July 17, 2014 4:10 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 2,068, Visits: 3,599
Sorry, bad grammar on my part!
I mean that is the expected behaviour.
Post #1593865
Posted Friday, July 18, 2014 7:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 414, Visits: 2,083
Gazareth (7/17/2014)
Sorry, bad grammar on my part!
I mean that is the expected behaviour.



But i am seeing like thousands of thousands login failure for every sec, is this really expected behaviour?

They stopped when i failed back the databases to primary and i don't see that behavior on Mirror server , why?
Post #1594050
Posted Friday, July 18, 2014 7:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 6,634, Visits: 14,213
Orphaned users on the mirror.
Check sids between the primary and mirror


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1594060
Posted Friday, July 18, 2014 8:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 2,068, Visits: 3,599
muthyala_51 (7/18/2014)
Gazareth (7/17/2014)
Sorry, bad grammar on my part!
I mean that is the expected behaviour.



But i am seeing like thousands of thousands login failure for every sec, is this really expected behaviour?

They stopped when i failed back the databases to primary and i don't see that behavior on Mirror server , why?


Because they never attempt to connect to the mirror in that situation.

If your connection string is Server=Server1;FailoverPartner=Server2

When Server1 is primary:
App tries to connect to Server1 -- Success!

When Server2 is primary:
App tries to connect to Server1 -- Failure <-- this causes the errors you're seeing
-> App tries to connect to Server2 -- Success!
Post #1594105
Posted Friday, July 18, 2014 9:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 2,068, Visits: 3,599
Perry Whittle (7/18/2014)
Orphaned users on the mirror.
Check sids between the primary and mirror


Perry, this is happening on the primary server when it's acting as the mirror; all the databases are in the restoring state, can't be orphaned logins.
Post #1594107
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse