Automatically enable/disable SQL Agent Job

Question

I want to be able to automatically enable/disable a SQL Agent job by determining which server is the active one and which is the passive one. In other words, if the primary server where the job is enabled, fails over to the secondary server, then the job will be disabled and the job on the secondary server (now the primary) will be enabled.

I found a script that does exactly what I need and modified slightly to my standards: http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-28-additional-options-tackling-jobs-failo

ALTER procedure [dbo].[SQLAgentJobFailover] (@agname varchar(200))
AS
BEGIN 
   declare @is_primary_replicate bit
   declare @job_name VARCHAR(100) = 'MySQLAgentJobName'
   declare @job_enabled bit

   select @is_primary_replicate = master.dbo.fn_hadr_group_is_primary(@agname)

   declare job_cursor cursor for 
   select s.name from msdb.dbo.sysjobs s
   inner join msdb.dbo.syscategories c on s.category_id = c.category_id
   where c.name = @agname
   order by name

   open job_cursor
   fetch next from job_cursor into @job_name
   while @@fetch_status = 0
   begin
          select @job_enabled=enabled from msdb.dbo.sysjobs where name = @job_name
          if @is_primary_replicate = 1
          begin
                 if @job_enabled = 1
                       print @job_name+' enabled on primary. do nothing'
                 else 
                 begin
                       print @job_name+' disabled on primary. enable it !'
                       exec msdb.dbo.sp_update_job @job_name = @job_name,@enabled = 1
                 end
          end 
          else if (@is_primary_replicate = 0)
          begin
                 if @job_enabled = 1
                 begin
                       print @job_name+' enabled on secondary. disable it !'
                       exec msdb.dbo.sp_update_job @job_name = @job_name,@enabled = 0
                 end
                 else 
                       print @job_name+' disabled on secondary. do nothing'
          end
          fetch next from job_cursor into @job_name
   end
   close job_cursor
   deallocate job_cursor
END

GO

Also here is the function script:

ALTER FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit 
AS
    BEGIN 

            DECLARE @PrimaryReplica sysname; 

            SELECT @PrimaryReplica = hags.primary_replica 
            FROM 
                    sys.dm_hadr_availability_group_states hags
                    INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
            WHERE
                    ag.name = @AGName;

            IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)
                    RETURN 1; -- primary

            RETURN 0; -- not primary

    END; 
GO

However, when I execute on the secondary server like this:

exec master.dbo.SQLAgentJobFailover @agname = 'CorpAnalyticsAG'

It says the command completed successfully however the job isn't disabled.

I have no idea why.

Below is my AG name

Any ideas?

Answer 1

One of the problems is that the value you are setting your @job_name variable to is being over written in the cursor definition. Because it is not actually enabling or disabling the job the cursor definition is not likely returning the job you actually want to enable or disable within its results set which could be due most likely to @agname value that is being passed and/or the cursor select definition.

Because you only want to deal with one job you really don't need the cursor definition but you still need to test if the primary replica is the same as the @@SERVERNAME