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:
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:
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:
|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.
|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:
masterdatabase context, even though they’re actually dependent on the
ReportServerdatabase. If you include this
ReportServerdatabase 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 Servercategory, 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.
Only members of the
sysadmin fixed server role can run this script.
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).
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: