SQL Server Jobs & HA/DR

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

Cover Image

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.

Availability Groups

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

Database Mirroring

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

Classic Version

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

License

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

Contribution

This is an open-source solution. Please feel free to create 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!

mssql-jobs-hadr