Sql-server – Enable Job instantly after AOAG Role change

availability-groupssql serversql server 2014sql-server-agent

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.

  1. Create alert for 1480.
  2. Script out the enabling code for that particular job (T-SQL).
  3. 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.

Best Answer

Not sure if you can build a "trigger" to capture role changes.

I would probably set up a SQL Server Agent job that runs pretty frequently. Check

SELECT primary_replica
FROM sys.dm_hadr_availability_group_states;

and compare this value to a local table to detect when the primary replica changes.

Related Question