All we need is an easy explanation of the problem, so here it is.
I have two SQL Server instances working as the AlwaysOn AG. There is a Windows login in each instance created from a local Windows account and mapped to respective users in the AG databases. Say I’m adding a new database to the AG.
Is there a way to pre-provision a user mapped to the local Windows login from another (secondary) node without having to failover the AG – so that when the actual failover occurs, that user is fully operational?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
To pre-provision the local Windows database accounts, add the local Windows database users to the database before you add the database to the AG. Steps below.
- create the new database on the secondary
- create the secondary node local login and database user
- backup the database and drop from the secondary
- restore the database to the primary
- create the primary node local login and database user
- add the database to the AG
- backup the database and transaction log
- restore the database and log backups to the secondary
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂