July 16, 2014 at 1:33 pm
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.
July 17, 2014 at 4:08 am
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" 😉
July 17, 2014 at 6:00 am
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.
July 17, 2014 at 11:58 am
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.
July 17, 2014 at 12:05 pm
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.
July 17, 2014 at 4:10 pm
Sorry, bad grammar on my part!
I mean that is the expected behaviour.
July 18, 2014 at 7:37 am
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?
July 18, 2014 at 7:50 am
Orphaned users on the mirror.
Check sids between the primary and mirror
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 18, 2014 at 8:56 am
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!
July 18, 2014 at 9:04 am
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.
July 18, 2014 at 9:08 am
Urgh, mirroring nomenclature.
I tend to use:
Primary & Secondary - Server and FailoverPartner respectively (or, Primary = the server you expect to be the 'live' one most of the time).
These titles always refer to the same server.
Principal & Mirror - refer to whichever server is carrying out that role at the time.
So, the errors are on the Primary Server when it is carrying out the Mirror role.
July 24, 2014 at 2:59 pm
Agree with Gazareth.
The app will try Primary first. When it is acting as the mirror, the login attempt cannot open the mirror database, and thus fails.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply