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?
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