mssql-jobs-hadr

Logo

MadeiraData/mssql-jobs-hadr
This repository contains solutions to properly control and maintain scheduled jobs on SQL Servers with either Availability Groups or Database Mirroring

SQL Server Jobs & AlwaysOn Interoperability

APPLIES TO: YesSQL Server YesAzure SQL Database (Managed Instance only) NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse


This folder contains a script which can be used to automatically enable or disable SQL Server jobs based on the Availability Groups role of their respective database(s).

The script will create one scheduled job, and one alert.

In this page:

Download

Prerequisites

The script only supports SQL Server versions 2012 and later, that have SQL Server Agent available (Express editions and Azure SQL Databases are not supported).

To install the script, simply run it on your servers involved in an HA/DR architecture.

You may change the values of the variables at the top of the script, if you want to customize the solution.

See the “Arguments” section below for more info.

If you’re in need of understanding the possible logical connections between your scheduled jobs, your databases, and your Availability Groups, you may use the following query:

Arguments

SET @MasterControlJobName = N'AlwaysOn: Master Control Job' sets the name to be used for the master control job.

SET @AlertName = N'AlwaysOn: Role Changes' sets the name to be used for the alert triggered by role change events.

[ SET @SpecialConfigurations = N'xml_value' ] is an optional XML parameter that can contain special configurations that specify when certain jobs should be enabled or disabled, based on a database role.

xml_value must be a valid XML expression. 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 run.

The XML should have the following structure:

<config>
<item type="job | step | category" enablewhen="primary | secondary | both | never | ignore" [ dbname="database_name" ] >item name qualifier</item>
[ ... ]
</config>

type is an attribute determining the configuration item type. Possible values:

Value Description
job Item represents a job name.
step Item represents a job step name.
category Item represents a job category name.

enablewhen is an attribute determining when the relevant job(s) should be enabled.

Value Description
primary Enable when on Primary only (this is also the default).
secondary Enable when on Secondary only.
both Enable when on both Primary and Secondary.
never Never enable (if you want certain jobs to always remain disabled).
ignore Ignore the jobs entirely (don’t disable or enable automatically).

dbname is an optional attribute used for explicitely setting which database should be checked.

This attribute can be useful for several possible scenarios:

item name qualifier is the name of the relevant item (job/step/category). This value is used in a LIKE operator, and therefore supports LIKE pattern wildcards such as %, _, etc. Please see the LIKE operator documentation for more info on LIKE expression patterns.

See the Examples section below for example values for this argument.

Permissions

Only members of the sysadmin fixed server role can run this script.

Remarks

Examples

A. Composite Example

The following example demonstrates a combination of several use cases:

SET @MasterControlJobName = N'AlwaysOn: Master Control Job'
SET @AlertName = N'AlwaysOn: Role Changes'

SET @SpecialConfigurations = N'<config>
<item type="job" enablewhen="secondary">Contoso %</item>
<item type="job" enablewhen="both">AdventureWorks Validation Checks</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="category" enablewhen="primary" dbname="ReportServer">Report Server</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>
<item type="job" enablewhen="never" dbname="audit">Do not run - %</item>
</config>'

The example above demonstrates the following use-cases:

See Also