2

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.

Improve this question
3
  • 1
    Why not just have the first step of that job check to see if it's running on the primary node or not? If no, exit early, if yes, do the archiving bit. Leave the job enabled with the normal schedule. No need to worry about triggers or watching the event log or anything.
    – Jonathan Fite
    Apr 12, 2017 at 12:50
  • 1
    This is over-engineering things, just have each job check to see if it is the primary, and if so execute the next step, otherwise fail with success.
    – Nic
    Apr 12, 2017 at 13:17
  • @Sil Ence, You can change your replica server from secondary to primary replica throgh 'failover ' options in secondary replica.
    – Md Haidar Ali Khan
    Apr 12, 2017 at 13:56

2 Answers 2

Reset to default
1

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.

Improve this answer
0

we use a simple primary node checker to only run jobs on the primary node of an AlwaysOn cluster. It's a little flaky with some things like SSIS packages so in those cases we have the first step as a tester and if the node is primary then proceed to step 2 and so on. If it's passive then stop the job.

For @dbname use a variable that is sysname or just hard code a database name

 IF (SELECT master.sys.fn_hadr_is_primary_replica (@dbname)) = 1
 BEGIN;
 <do your stuff>
 end
Improve this answer

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.