SQL Server 2012 AlwaysOn - Job Management

Continuing on from my last post about AlwaysOn User Management..

Managing jobs so that they only ran on the primary or secondary replicas as required was something we desperately needed before we could migrate, this was due to a fairly large number of processing jobs that run on various schedules.

SQL Server manage jobs exactly the same as SQL logins in terms of they are not transferred or maintained through AlwaysOn as they are at instance level not DB level.

To get around this issue we decided to expand the failover detection method in my first post to also do a quick scan and set enabled/disabled on jobs as required.

To do this we added a new table to our management database as follows

We then expanded the failover stored procedure to cycle through the table and enable / disable each job based on the 3 flags,  using a cursor based approach

DECLARE enablejobs CURSOR local forward_only FOR 

  SELECT [job name], 
         job_id, 
         CASE 
           WHEN JS.[enabled] = 1 
                AND JS.onsecondary = 1 THEN 1 
           ELSE 0 
         END AS ToEnable 
  FROM   [dbo].[tbljobstates] JS 
         INNER JOIN msdb.dbo.sysjobs 
                 ON NAME = [job name] 
DECLARE @JobName VARCHAR(200) 
DECLARE @jobID UNIQUEIDENTIFIER 
DECLARE @Enable INT 

OPEN enablejobs 

FETCH next FROM enablejobs INTO @JobName, @jobID, @Enable 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @jobID= (SELECT job_id 
                   FROM   msdb.dbo.sysjobs 
                   WHERE  NAME = @JobName) 

      PRINT @JobName 

      PRINT 'EXEC msdb.dbo.sp_update_job @job_id=' 
            + Cast(@jobID AS CHAR(36)) + ',@enabled=' 
            + Cast (@Enable AS CHAR(1)) 

      FETCH next FROM enablejobs INTO @JobName, @jobID, @Enable 
  END 

CLOSE enablejobs 

DEALLOCATE enablejobs 

We also decided that all jobs should only be created/edited on a particular replica - one we called HA1 and created a simple SSIS package to transfer jobs  from the primary job box to each secondary on a nightly basis. Not the most efficient , but effective.