SQL AG 2012 R2
Hi Experts,
I have a SQL AG running on two nodes WIN 2012 R2.
The node SQL01 is the primary and the SQL02 is the secondary node.
When I go to the AG dash board and start the Failover Wizard from SQL01 to SQL02 , then I get problems.
When the DB is on the SQL02 the DB is not reachable anymore.
As I know the DB is always in sync mode but what about the local users ?
How to handle this ?
I have a SQL AG running on two nodes WIN 2012 R2.
The node SQL01 is the primary and the SQL02 is the secondary node.
When I go to the AG dash board and start the Failover Wizard from SQL01 to SQL02 , then I get problems.
When the DB is on the SQL02 the DB is not reachable anymore.
As I know the DB is always in sync mode but what about the local users ?
How to handle this ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I mean, when I make a failover and its finished, then my DB is not reachable anymore. I think it has to do with the users.
But I will check the hint from deepakChauhan
But I will check the hint from deepakChauhan
Can you describe the steps:
1. Where do you start the process, on primary or secondary
2. After fail over is done what "is not reachable anymore" means? You can't see it, you cant access it?
1. Where do you start the process, on primary or secondary
2. After fail over is done what "is not reachable anymore" means? You can't see it, you cant access it?
ASKER
I can´t access it.
The failover was successful.
The failover was successful.
This is because whenever you restore database from one server to another login and user mapping is broken.
Because logins does not exists on replica server or login's SID is different.
Because logins does not exists on replica server or login's SID is different.
That is valid for the SQL users only. Does the user exist on the second server and is that user mapped to have access to the database?
Here is how you fix the orphan users:
https://msdn.microsoft.com/en-CA/library/ms175475.aspx
Remember, that works only if the user exists already. if not you will have to create it.
Here is how you fix the orphan users:
https://msdn.microsoft.com/en-CA/library/ms175475.aspx
Remember, that works only if the user exists already. if not you will have to create it.
ASKER
OK, one question to Method 3: Create a log in script that has a blank password
The first steps are ok, I have to do this on the active DB in my AG.
But when I have the login script, then I have to do this on the second server right ?
I have to failover to my second server right ?
because otherwise the db is in snyc mode and not accessible.
The first steps are ok, I have to do this on the active DB in my AG.
But when I have the login script, then I have to do this on the second server right ?
I have to failover to my second server right ?
because otherwise the db is in snyc mode and not accessible.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok let me check this and I come back to you.
@OK, one question to Method 3: Create a log in script that has a blank password
Dont worry about this.. This works for both blank and non blank.
Dont worry about this.. This works for both blank and non blank.
Eprs_Admin, do you still need help with this question?
ASKER
no not yet.
ASKER
I will close this ticket.
But I just can test it later when I can work with the production DB and the AG
But I just can test it later when I can work with the production DB and the AG
ASKER
Tonight I have the maintenance window for it. :-)
One recommendation, when you do your manual fail over make sure you are logged in to the primary node and preferable from that box. I noticed that sometimes if you are doing that from a third party box the wizards can act weirdly.