2

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?

1 Answer 1

4

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.

  1. create the new database on the secondary
  2. create the secondary node local login and database user
  3. backup the database and drop from the secondary
  4. restore the database to the primary
  5. create the primary node local login and database user
  6. add the database to the AG
  7. backup the database and transaction log
  8. restore the database and log backups to the secondary WITH NORECOVERY
1
  • That is a good one :)
    – Timbalero
    Commented Apr 27, 2021 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.