This repository contains solutions to properly control and maintain scheduled jobs on SQL Servers with either Availability Groups or Database Mirroring.

Using this solution, you can automatically control which SQL Agent jobs would be executed on which replica, based on several possible criteria (PRIMARY / SECONDARY database role, job name, job category, etc.).

After implementing this solution:

• No longer will you be needing to manually implement an HA/DR role check in each new job or job step that you create.
• Jobs would no longer be executing without actually doing anything because a database's role wasn't the right one.
• No more jobs would fail because they were executed on a secondary/read-only/unreadable database.
• MSDB job history tables would not be needlessly bloated.

This solution is an improved version of the scripts provided at the blog post Automatically Enable or Disable Jobs Based on HADR Role.

This solution is available for AlwaysOn Availability Groups for SQL Server 2012 and later. Click here for more details, or download below:

• 🔽 AlwaysOn - Master Control Job and Alert.sql

This solution is available for Database Mirroring for SQL Server 2008 and later. Click here for more details, or download below:

• 🔽 DB Mirroring - Master Control Job and Alert.sql

This repository also contains the "classic" versions of the scripts. Click here for more details, or download below:

• 🔽 ChangeJobStatusBasedOnHADR.sql
• 🔽 ChangeJobStatusBasedOnMirroring.sql

This solution is released under the MIT License, and is provided "as-is", as a free contribution to the professional SQL Server community.

This is an open-source solution. Please feel free to [create issues]({{ site.github.repository_url }}/issues) if you want to submit bug reports or feature requests.

You may also fork the solution to your account and submit pull requests if you want to contribute!

• Blog Post: Control SQL Jobs based on HADR Role – Taking it to the Next Level
• Webinar: How to HADR Your SQL Jobs

Tell your friends! Share this link: bit.ly/HADRMyJobs