SQL Server Jobs & AlwaysOn Interoperability

APPLIES TO: SQL Server Azure SQL Database (Managed Instance only) Azure Synapse Analytics (SQL DW) Parallel 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
Arguments
Permissions
Remarks
Examples
See Also

Download

🔽 AlwaysOn - Master Control Job and Alert.sql

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:

AlwaysOn - Query to Inspect Job Details.sql

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:

For job steps that do not have a database context, such as SSIS, Powershell, or CmdExec steps.
For job steps configured with a database context other than the one actually relevant for the HADR check. For example, Report Server subscription jobs are automatically configured with the master database context, even though they’re actually dependent on the ReportServer database. If you include this ReportServer database in an HADR solution, you’re gonna have a problem. But if you add an item with dbname="ReportServer" for all jobs in the Report Server category, you’ll be just fine, as they’d be automatically enabled/disabled as needed.

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

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 the script will be using for its logic.
If there is a disconnect between a job step’s configured database context, and its actual intended database target, then you can use the dbname optional attribute to force-check a specific database for a given criteria of jobs.
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 more 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 sys.dm_hadr_availability_replica_states system view.
The scripts will automatically detect whether a T-SQL step’s context database is accessible or not. For example, if the database is 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 (for example, 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).

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:

All jobs with names starting with “Contoso “ should only be enabled on the SECONDARY.
The job named “AdventureWorks Validation Checks” should be enabled on both PRIMARY and SECONDARY servers.
Any job with a step named “Generate BI Report” should be enabled on the SECONDARY.
Any job with the “SQL Sentry Jobs” category will be ignored by the script, regardless of its status.
Any job with the “Database Maintenance” category should be enabled on both PRIMARY and SECONDARY servers.
Any job with the “Report Server” category should be enabled only when the ReportServer database is PRIMARY.
The job named “SSIS AdventureWorksDWH Send Reports” should be enabled on the SECONDARY only.
The job named “WideWorldImporters Delete Old Data” should be enabled on the PRIMARY only.
Any job with a name starting with “Do not run - “ should always remain disabled regardless of HA/DR role.