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'))

  SELECT	job_id
  FROM	msdb.dbo.sysjobs
  WHERE	owner_sid = suser_sid('DomainSSRSUser')
    AND	enabled = 1
  OPEN db_cursor   
  FETCH NEXT FROM db_cursor INTO @jobid

    exec msdb.dbo.sp_update_job @job_id = @jobid, @enabled=0
    FETCH NEXT FROM db_cursor INTO @jobid
  CLOSE db_cursor   
  DEALLOCATE db_cursor


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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a website or blog at

Up ↑