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.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s