I'm running a Failover cluster on windows 2016 servers (2 nodes) where I set up an AG in SQL 14.0.2027.2 This works good and I'm happy. But when it failover I need to fix my logins so I need to run a query. It doesn't happen often but when it happens we are always too late-running this script. So is there a good way of running this script automatically right after failover and just send us one e-mail that the node's failover?
Add a comment
|
1 Answer
I can not see your script but I am assuming you get orphaned users on the database after the failover because the users are linked to SQL Server local logins with different SIDs on the different instances.
The best way would be to recreate the login with the same SID in both instances CREATE USER SID=<sid> so you do not have to fix orphaned users every time you failover.
If this solution doesn't suit your needs, perhaps you can set up an alert for event 1480 - AG Role Change, and run the script as the response action.
-
Agree, this is the way my company manages it too - new logins are added on both instances during initial setup and the failover just works then. Jan 7, 2021 at 15:46