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 ?
Eprs_AdminSystem ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
You need to transfer login from SQL01 to SQL02 with the SID, otherwise logins and users mapping will be borken.

Here is the link which you can use transfer logins. You can use Method 3. A stored proc SP_help_revlogin will be created in Master database, just execute this sp, this will create login creation script with hashed password and SID. copy the script and execute it on secodary server.

What do you mean by "When the DB is on the SQL02 the DB is not reachable anymore."

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.
Eprs_AdminSystem ArchitectAuthor Commented:
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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

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?
Eprs_AdminSystem ArchitectAuthor Commented:
I can´t access it.
The failover was successful.
Deepak ChauhanSQL Server DBACommented:
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.
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:


Remember, that works only if the user exists already. if not you will have to create it.
Eprs_AdminSystem ArchitectAuthor Commented:
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.
Right, the problem is not on the database. On secondary it was restored from primary, i guess, and then was shown synced when added to the AG, which means it is exactly the same on both servers. The problem is with the login on the server. I suspect on primary has one SID and on secondary a different one.

What you need to do is to drop the login on the secondary and then recreate it from primary with the same SID and password.  Use the script showed here:


and execute it on the primary, where you could login. Then copy the CREATE LOGIN statement for your user and execute it on your secondary server where you failed over.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eprs_AdminSystem ArchitectAuthor Commented:
ok let me check this and I come back to you.
Deepak ChauhanSQL Server DBACommented:
@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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Eprs_Admin, do you still need help with this question?
Eprs_AdminSystem ArchitectAuthor Commented:
no not yet.
Eprs_AdminSystem ArchitectAuthor Commented:
I will close this ticket.
But I just can test it later when I can work with the production DB and the AG
Eprs_AdminSystem ArchitectAuthor Commented:
Tonight I have the maintenance window for it. :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.