1

Note: this is not duplicate question.

I have setup AlwaysOn. I have created SQL agent jobs manually on both the nodes. right now all the active jobs are running if node is PRIMARY.

Challenge

My DBA will disable few SQL agent jobs manually in only one node based on business requirements. Now how does it synchronizes to node 2? Will DBA have to manually disable these related jobs in all the secondary nodes ?

I am thinking of creating a table (this table will be part of availability group) where I maintain the state of the jobs. and during fail over I am planning to run the script to read the data from this table and set the state of the jobs on other nodes. Is it right way? or do we have any recommended steps?

Kindly suggest

kudlatiger
  • 269
  • 2
  • 4
  • 21

1 Answers1

2

There are several ways to automate this and you are heading down the right path...you can rely on your DBA to manually disable and enable (not very nice ;), you can keep track of job state in a table and enable and disable using a periodic job that runs, or you can categorize your jobs and respond to a role change alert...this is what I do.

A rough outline is:

  1. Pick a category for all the jobs that you want to only run on the current primary replica, I use the ag name
  2. Set up a SQL Agent alert for error number 1480 (AG Role Change) that will call a stored procedure in master via a job passing in the category name
  3. Build the sp to check to see if it is the primary, if it is enable all the jobs categorized with the parameter. If it is not the primary, disable all the jobs categorized with the parameter
  4. Build some functions to help recognize if the AG is Primary or Secondary

There is a very detailed SQL Magazine series of articles by Michael K. Campbell that give code examples for how to structure either solution, I started with that article and only had to make minor modifications.

IanG
  • 314
  • 2
  • 13