Automatically Failover (Enable or Disable) Jobs Based on HADR Role

  • Eitan Blumin

    SSC Journeyman

    Points: 90

    Comments posted to this topic are about the item Automatically Failover (Enable or Disable) Jobs Based on HADR Role

  • pcd_au

    Ten Centuries

    Points: 1179

    A timely post for me and I look forward to understanding the content in more details. However a quick suggestion, rather that maintaining a list of jobs, how about using the 'category' of Jobs. Perhaps "<AGName>-Primary" and "<AGName>-Secondary", then enable and disable the jobs in the category?

    Also what are your suggestions for ensuring that the Jobs are on all servers and the jobs are the same?

    pcd

  • Eitan Blumin

    SSC Journeyman

    Points: 90

    pcd_au - Wednesday, November 14, 2018 3:35 PM

    A timely post for me and I look forward to understanding the content in more details. However a quick suggestion, rather that maintaining a list of jobs, how about using the 'category' of Jobs. Perhaps "<AGName>-Primary" and "<AGName>-Secondary", then enable and disable the jobs in the category?

    Also what are your suggestions for ensuring that the Jobs are on all servers and the jobs are the same?

    pcd

    Hi pcd,
    Thank you for the comment!
    Yes you are right, once could go about selecting jobs based on category too, that could definitely work.
    Simply replace the jobs query with one from msdb..sysjobs

    As for ensuring jobs are on all servers and the jobs are the same...
    First one would need to set up a linked server for each replica.
    Normally, setting up availability groups or database mirroring don't do that for you. So that's one extra step you'd have to do.

    Next, you could query from msdb..sysjobs and msdb..sysjobsteps on each linked server and compare their contents.
    It's kind of beyond the scope of this blog post, I think. But it shouldn't be too hard to do.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply