Control SQL Jobs based on HADR Role – Taking it to the Next Level

In one of my previous blog posts, Automatically Enable or Disable Jobs based on HADR Role, I provided a solution for automatically enabling or disabling scheduled jobs in combination with Availability Groups and Database Mirroring. In this blog post, I’m going to build upon my previous solution and improve it even further – taking it to the next level of automation!

Picking up where we left off

In my previous blog post, I provided scripts for two stored procedures (one for Availability Groups and one for Database Mirroring) which do the following:

• Receive a database name as a parameter (@DBName)
• Check whether the specified database is a member of a Primary/Principle in AG or Secondary/Mirror state on the current instance
• Declare a cursor which iterates through a specific list of jobs
• For each job, make sure it’s enabled or disabled based on the role of the specified database name

Unfortunately, the solution I provided had several glaring limitations:

• You must specify a single “main” database as a parameter for the procedure.
• If you have more than one logical group of jobs, you had to create multiple master control procedures and/or jobs.
• You had to create at least one stored procedure for the master control job(s) (how rude of me! asking you to create objects in your server… tsk tsk tsk).
• If you wanted to make your master control job(s) event-based, you had to do it on your own (you poor thing! how could I do this to you???)

In short, past me from 2018 was a clever but rather short-sighted dude (and possibly lazy?).

That’s a bad Eitan from 2018! Lazy and bad! Boo! 😝👎

Let’s knock it up a notch

Luckily for you, I’ve learned and improved a lot since then, and you get to enjoy the benefits!

• Don’t want to create any stored procedures anywhere? Done! Gone out the window.
• Don’t want to create more than one Master Control Job? No problemo!
• Want me to make your Master Control Job event-based for you??? You got it, buddy!

Lo and behold, my new and improved scripts, which implement the following logic:

• Create a single Master Control Job with an hourly schedule, and also set it to run when SQL Agent is started up. The Master Control Job will do the following:
  • Automatically detect which jobs have steps that run on databases that are involved in an HADR solution.
  • For each such job, automatically detect whether there’s at least one step run on a database which currently has the Primary/Principle role.
  • If so, make sure it’s enabled. Otherwise, make sure it’s disabled.
• Create an alert for a role/state change event, if such doesn’t exist yet, and set it to run the Master Control Job when triggered.
• No stored procedures
• No unnecessary hassle

What if I have jobs that I want to enable on Secondary replicas? Or I have jobs that don’t have database contexts?

Excellent questions, oh wise and observant reader! I figured you would ask that!

The script has only 1 optional XML parameter for special configurations, aptly named: @SpecialConfigurations

(I could’ve used a JSON parameter, but I decided that backward compatibility is more important than readability)

I decided to use a single configuration parameter because it gives us the best flexibility, while avoiding the clutter of too many simple parameters to maintain.

This XML parameter can contain a list of job names, job step names or a list of job category names, for which special use cases need to be applied. Specifically, where the jobs should be enabled:

• both – On both Primary and Secondary
• secondary – On Secondary only
• primary – On Primary only (this is also the default)
• never – Never (if you want certain jobs to always remain disabled)
• ignore – Ignore the jobs entirely (don’t automatically disable or enable)

Here is an example to illustrate the expected XML structure:

<config>
<item type="job" enablewhen="secondary">Contoso DWH Export</item>
<item type="job" enablewhen="both">AdventureWorks Validation Checks</item>
<item type="job" enablewhen="never" dbname="audit">Do not run - %</item>
<item type="step" enablewhen="secondary">Generate BI Report</item>
<item type="category" enablewhen="ignore">SQL Sentry Jobs</item>
<item type="category" enablewhen="both">Database Maintenance</item>
<item type="job" enablewhen="secondary" dbname="AdventureWorksDWH">SSIS AdventureWorksDWH Send Reports</item>
<item type="job" enablewhen="primary" dbname="WideWorldImportersLT">WideWorldImporters Delete Old Data</item>
</config>

As you can see, the XML has a root element called “config“, and each configuration element within is called “item“.

Each item has 3 attributes:

• type – specifies whether it’s a job, step, or a category
• enablewhen – specifies when the relevant job(s) should be enabled, depending on the relevant database role. Possible values: secondary / primary / both / never / ignore (default is primary)
• dbname – this is an optional attribute containing the database name to check for its role, based on which, the job will be enabled or disabled. You can use this attribute as a replacement/override for the database context of job steps. This can also be useful especially for jobs with steps that cannot have database context but you still want to control them based on a database role, such as SSIS packages, CmdExec, Powershell… Basically almost anything besides T-SQL steps.

The text value of each item element would represent the name of the job / job step / category. It is used with a LIKE operator, and as such, supports LIKE expression patterns. For example, you could use it to filter all job names starting with a text pattern, or containing a text pattern (e.g. 'Contoso - %').

Important Notes

• Your T-SQL job steps should be set to run on their destined databases. Don’t use any “USE” commands or 3-part-names while setting the database context to “master” or something like that. What you specify as the “target” database in the job step – that’s what my script will be using for its logic. Otherwise, you must remember to use the “dbname” attribute in the special configurations parameter accordingly.
• If you’re using special configurations at the step level, keep in mind that it’s enough for just one step to be enabled, in order for the script to enable the whole job. If you have multiple steps in such jobs, consider the possibility that they might be executed not when you necessarily intend them to. You can use something like the sys.fn_hadr_is_primary_replica system function to check for a database’s role, or query from the relevant system views.
• The scripts will automatically detect whether a T-SQL step’s context database is accessible or not. For example, if the database is a MIRROR, or a non-readable SECONDARY. If a database is found to be non-accessible, that would override any special configurations you may have had for that step. However, if another step within the same job should be enabled, then that would override the override (as mentioned above, it’s enough for one step to be enabled in order to enable the whole job). If you have such use cases, you should properly configure your job step outcomes to take this into consideration (i.e., set the “on failure action” to go to another step instead of failing the job).
• Generally, it would be best to avoid creating jobs that have one step run on a database in Availability Group A, and another step run on a database in Availability Group B. Otherwise, you’ll risk a scenario where group A might be PRIMARY on the server, while group B is SECONDARY, and your job could potentially fail! (unless the server is a readable secondary and your job step is only doing reads from the database, and/or you have appropriate AG role checks in place, and/or you have appropriate settings for the job step failure outcome).

Go Get It!

So, what are you waiting for? Go on and get it! It’s free!

▶ For Availability Groups

▶ For Database Mirroring

If this isn’t the first time you’re reading my blog, you’re probably asking yourself: why don’t these scripts are in my Gists?

Well, this is because I have even bigger plans for this solution in order to make it truly “complete”, and for that I’m gonna need a full-on repository…

But, you’ll have to wait for a future blog post on this, until the solution is ready 😉.

Since the scripts are contained in a GitHub repository with an MIT license, that also means you can feel free to create issues (if you find bugs or want to submit feature requests), and you can create pull requests from forks if you want to contribute!