Replicating Jobs Between SQL Instances – Disable Selected Jobs
This is the final optional step in replicating jobs between SQL instances in an AlwaysOn Availability Group. We use SQL Reporting Services to generate reports for the business. Some of the reports are scheduled to run by business users. When you schedule a job in SSRS it creates a SQL Agent Job. We want to replicate this job in case we failover to the secondary, but we want it disabled so that we don’t run the report twice (or get an error because it tries to run the job on the secondary which is read only).
As discussed in all of the steps of our job we have called IF 0 = (SELECT master.dbo.fCheckIfPrimary('PrimaryReplicaName')) before running the step. We have added the reverse of that bit of code to every other step in every other job that we only want to run on the primary (i.e. change the 0 to a 1). For example, we only want to backup our databases on their primary server.
The problem we have with SSRS jobs is that we don’t control the contents of them. Sure we could change them all, but then whenever a business user creates a new schedule we’d have to edit that job too.
Instead, what we decided to do was to disable the replication on the secondary. Should we failover to the secondary we have the option to tell the business that scheduled reports won’t send (e.g. a planned maintenance window) and they’ll have to generate them, or we can manually enable the jobs on the secondary (e.g. a long unplanned outage on the primary server with no idea on if/when it will return).
The disable step is pretty simple, we just need to find all of the jobs and call the update job stored proc, passing in the jobid and enabled = 0. In our case to find the jobs is also pretty simple, SSRS creates all jobs setting the owner to the user that runs SSRS. We just want to find all jobs owned by the SQL Reporting Services user and disable them.
IF 0 = (SELECT master.dbo.fCheckIfPrimary('PrimaryReplicaName'))
BEGIN
DECLARE @SQL NVARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE owner_sid = suser_sid('DomainSSRSUser')
AND enabled = 1
DECLARE @jobid UNIQUEIDENTIFIER
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @jobid
WHILE @@FETCH_STATUS = 0
BEGIN
exec msdb.dbo.sp_update_job @job_id = @jobid, @enabled=0
FETCH NEXT FROM db_cursor INTO @jobid
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
We limit the jobs to those owned by the SSRSUser, but you could also do it by job name, by category, or by anything else that gets the list of jobs you want to disable.
In the event of a failover where we want to enable the jobs on the secondary, we can simply run this query again, changing the @enabled=0 to @enabled=1 and run the cursor on the new primary.