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=1 and run the cursor on the new primary.