Managing SQL Jobs in Availability Groups - Method Job Category

10 min read
Mar 26, 2020

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/

Get Email Notifications

No Comments Yet

Let us know what you think