Managing SQL Jobs in Availability Groups – Method Job Category

Posted in: Microsoft SQL Server, Technical Track
Method – Job Category

This blog post will show how to setup SQL Jobs in multiple availability groups. This method will allow you to dynamically detect the role of the SQL Server replica and avoid job execution errors in the secondary replica.

When there is a SQL job that is trying to run update/delete/inserts to the secondary replica of a database, the job will fail because the target database is participating in an availability group and is currently not accessible for queries.

To avoid false-positive job errors you can use the method below to dynamically create a job category for each Availability Group name, detect which jobs belong to databases in the Availability Group, assign them the respective job category, and create a first job step to detect if the Availability Group’s role is the primary replica.

NOTE: If you use SQL job categories for another propose, please don’t use this method.

 

The procedure:

1 – Create the function fn_hadr_group_is_primary to detect if the role is the Primary Replica.

2 – Create a SQL job category for each Availability Group with the Availability Group Name.

3 – Assign Availability Group Name Category to each SQL job that uses a database in an Availability Group. Update with Availability Groups Name Category all the SQL jobs in their steps that are using databases that belong to one Availability Group.

  • Get all Availability Groups and databases.
  • Get all jobs that use Availability Groups databases in their job steps.
  • Update all job categories with the Availability Group Name.

4 – Get all SQL jobs with the Availability Group Name category and add a first step that detects Availability Group Role. The validation is based on the job category name vs availability group name.

  • This step created a ‘check’ if the role of Availability Group is Primary or Secondary.
  • Primary Role: job proceeds to the next step.
  • Secondary Role: stop job with cancelled status.

Script:

Script – SQL AlwaysOn Managing SQL Jobs Multiple AG
The implementation:

1 – Create the function fn_hadr_group_is_primary to detect if the role is the Primary Replica.

use master;
go
if object_id('dbo.fn_hadr_group_is_primary', 'fn') is not null
  drop function dbo.fn_hadr_group_is_primary;
go
create 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

 

2 – Create a SQL job category for each Availability Group with the Availability Group Name.

use msdb
go
set nocount on;

declare @table_agname_to_category table (agname varchar (200))
declare @flag int

insert into @table_agname_to_category select [name] from sys.availability_groups
set @flag = (select count (*) from @table_agname_to_category)

  print '====================================================================='
  print 'Create Job Categories with Availability Groups Name'
  print '====================================================================='


while @flag > 0
begin

  declare @agcategoryname varchar (200)
  declare @agcategorynamenotexists int
  set @agcategoryname = (select top 1 agname from @table_agname_to_category)
  set @agcategorynamenotexists= (select count (*) from [msdb].[dbo].[syscategories] where [name] = @agcategoryname)
    if (@agcategorynamenotexists = 0)
      begin
      print 'Job category created: '+@agcategoryname
      exec msdb.dbo.sp_add_category
      @class=N'JOB',
      @type=N'LOCAL',
      @name=@agcategoryname
      end
  delete top (1) from @table_agname_to_category
  set @flag = (select count (*) from @table_agname_to_category)
end

Script Output:

Job categories created with the same name as the Availability Groups:

 

3 – Assign Availability Group Name Category to each SQL job that uses a database in an Availability Group.

use msdb
go
set nocount on;
declare @table_agname_dbname table (agname varchar (200), databasename varchar(400))
declare @joblist table (jobname varchar (400), agname varchar (200), databasename varchar(400))
declare @distinctjoblist table (jobname varchar (400), agname varchar (200), databasename varchar(400))
declare @flag int

--get ag names and respective databases
insert into @table_agname_dbname 
select  groups.[name],databaselist.[database_name]
from    sys.availability_databases_cluster databaselist
inner join sys.availability_groups_cluster groups on databaselist.group_id = groups.group_id

set @flag = (select count (*) from @table_agname_dbname)

-- get all sql jobs that in their steps are using databases that belongs to availability groups 
while @flag > 0
begin
  insert into @joblist
  select j.name, ag.agname, ag.databasename
  from sysjobs j
  inner join sysjobsteps js on j.job_id=js.job_id
  cross join @table_agname_dbname ag
  where command like '%'+ag.databasename+'%' or database_name = ag.databasename
  group by j.name, ag.agname, ag.databasename

  delete top (1) from @table_agname_dbname
  set @flag = (select count (*) from @table_agname_dbname)
end

insert into @distinctjoblist select distinct jobname, agname ,databasename  from @joblist

print '====================================================================='
print 'Assign Jobs to Categories with Availability Groups Name'
print '====================================================================='

set @flag = (select count (*) from @distinctjoblist)

while @flag > 0
begin
  declare @jobname varchar (400)
  declare @agcategoryname varchar (200)
  set @jobname = (select top 1 jobname from @distinctjoblist order by jobname asc)
  set @agcategoryname = (select top 1 agname from @distinctjoblist order by jobname asc)
    begin
    print' '
    exec dbo.sp_update_job  
    @job_name = @jobname,  
    @category_name = @agcategoryname
    end
    print 'Job Name: "' +@jobname+ '"'+ char(10) +'Category Assigned: ' +@agcategoryname
  delete top (1) from @distinctjoblist where jobname = @jobname
  set @flag = (select count (*) from @distinctjoblist)
end
go

Script Output:

Which jobs are updated?

Jobs are selected when they refer to a database in the “database_name” or “command” columns from the sysjobsteps table that belongs to the Availability Group.

The script assigns the job category with the Availability Name.

 

4 – Get all SQL jobs with Availability Group Name Category and add a first step that detects Availability Group Role. The validation is based on the job category name vs Availability Group Name.

use msdb
go
set nocount on;

declare @table_agname table (agname varchar (200))
declare @agcategoryname varchar (200), @flag int

insert into @table_agname select [name] from sys.availability_groups


set @flag = (select count (*) from @table_agname)

while @flag > 0
begin

      set @agcategoryname = (select top 1 agname from @table_agname)

      if object_id(N'tempdb.dbo.#data',N'u') is not null drop table dbo.#data;
      create table dbo.#data (id int identity primary key, name sysname);

      -- get all job names with the ag category name and exclude jobs that already have a step named 'get_availability_group_role'
      insert dbo.#data (name)
        select distinct j.name--, s.step_name
        from dbo.sysjobs j inner join dbo.syscategories c on j.category_id = c.category_id
        where c.[name] = @agcategoryname
          except
        select distinct j.name
        from dbo.sysjobs j
        inner join dbo.sysjobsteps s on j.job_id = s.job_id
        where s.step_name = N'get_availability_group_role';

      declare @command varchar(max), @min_id int, @max_id int, @job_name sysname, @availability_group sysname;
      select  @min_id = 1, @max_id = (select max(d.id) from #data as d);

      select @availability_group = (select ag.name from sys.availability_groups ag where ag.name = @agcategoryname);
      --
      -- if this is instance does not belong to ha exit here
      if @availability_group is null
      begin;
        print @agcategoryname+' is the secondary replica in this node';
        return;
      end;

      declare @debug bit = 0; --<------ print only

      -- loop through the table and execute/print the command per each job
      while @min_id <= @max_id
      begin;
      select @job_name = name from dbo.#data as d where d.id = @min_id;
      print + char(10) + '====================================================================='
      print 'Insert Job Step to detect if this instance''''s role is a primary replica.'
      print '====================================================================='
          
      select @command =
      'use [msdb] ' + char(10) + '
      begin tran;
      declare @returncode int;
      exec @returncode = msdb.dbo.sp_add_jobstep @job_name=''' + @job_name + ''', @step_name=N''get_availability_group_role'',
              @step_id=1,
              @cmdexec_success_code=0,
              @on_success_action=3,
              @on_success_step_id=0,
              @on_fail_action=3,
              @on_fail_step_id=0,
              @retry_attempts=0,
              @retry_interval=0,
              @os_run_priority=0, @subsystem=N''tsql'',
              @command=N''
-- detect if this instance''''s role is a primary replica.
-- if this instance''''s role is not a primary replica stop the job so that it does not go on to the next job step

declare @rc int;
exec @rc = master.dbo.fn_hadr_group_is_primary N''''' + @availability_group + ''''';

if @rc = 0
begin;
  declare @name sysname;
  select  @name = (select name from msdb.dbo.sysjobs where job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))));

  exec msdb.dbo.sp_stop_job @job_name = @name;
  print ''''stopped the job since this is not a primary replica'''';
end;'',
          @database_name=N''master'',
          @flags=0
      if (@@error <> 0 or @returncode <> 0)
      begin;
        print ''-- rollback: ''''' + @job_name + ''''''' rollback tran;
      end;
      else commit tran;' + char(10) + '';

      if @debug = 0 
        begin
          print 'Job Name: "' +@job_name+ '"'+ char(10) +'Availability Group: '+@availability_group+' '+ char(10) + 'Result: Jobstep added with success!!!'
          exec (@command);
        end
      else
        begin
          print '-- print only '
          print '-- job -> '+@job_name+' not updated -> to add jobstep execute script bellow manualy or change the variable @debug bit = 0'
          print '====================================================================='
          print @command;
        end
      select @min_id += 1;
      end
      
      delete top (1) from @table_agname
      set @flag = (select count (*) from @table_agname)
end;

Script Output:

An example of the step added to each job:

 

Examples of job executions in different Availability Group scenarios:

 

 

 

 

 

Note: If the job owner user is not part of the SQL Server sysadmin role, you need to assign the following permissions.

--master - grant permissions
--execute fn_hadr_group_is_primary 
--grant view database and server state

use [master]
go
create user [replace_job_owner_username] for login [replace_job_owner_username]
go
grant execute on [dbo].[fn_hadr_group_is_primary] to [replace_job_owner_username]
go
grant view database state to [replace_job_owner_username]
go
grant view server state to [replace_job_owner_username]
go

--msdb - grant permissions
--execute sp_stop_job
--select on sysjobs and sysjobsteps tables

use [msdb]
go
create user [replace_job_owner_username] for login [replace_job_owner_username]
go
grant execute on [dbo].[sp_stop_job] to [replace_job_owner_username]
go
grant select on [dbo].[sysjobs] to [replace_job_owner_username]
go
grant select on [dbo].[sysjobsteps] to [replace_job_owner_username]
go

 

Fix SQL job errors:

  • Failed to update database “” because the database is read-only. [SQLSTATE 25000] (Error 3906). The step failed.
  • The target database, ”, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

References:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15

https://blog.pythian.com/list-of-sql-server-databases-in-an-availability-group/

email

Interested in working with Goncalo? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *