Managing SQL Jobs in Availability Groups - Method Job Category

Method - Job Category

The procedure:

• 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.

• 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 - SQL AlwaysOn Managing SQL Jobs Multiple AG

The implementation:

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

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

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

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;

--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

• 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.