We have a archival job which is set up in all three replicas which are part of AOAG (SQL Server 2014).
Here the connection string is AOAG Listener (which connects to only Primary Replica).
I have disabled the job in secondary replicas.
I have seen message_id 1480 (AG Role Change).
So now I should do the below step by step process to auto-enable the job at times of unexpected failover.
- Create alert for 1480.
- Script out the enabling code for that particular job (T-SQL).
- Write a PowerShell code for identifying the event id 1480 for role change and check for the current primary replica (T-SQL Code above) and auto enable (T-SQL script) . I will let this process run in all the replicas to identify the role change and auto_enable the archival job in secondary sync server AOAG.
Conclusion : In brief, I need to disable job in secondary replicas and enable them automatically as per the replica role change and automatically enable the archival job as per the replica state change to primary.
Please help me to get rid of this confusion.
I have checked the below links but would like to know step by step process.