Site icon Eitan Blumin's blog

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

Bam! Knock it up a notch!

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:

Unfortunately, the solution I provided had several glaring limitations:

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!

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

Don’t forget to do this on all servers

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:

Here is an example to illustrate the expected XML structure:

<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>

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:

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

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 😉.

Check out this nifty teaser banner!

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!

Exit mobile version