Managing SQL Jobs in Availability Groups - Method Job Category
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.
- 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:
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; go2 - 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) endScript 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 goScript 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] goFix 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.
Share this
You May Also Like
These Related Stories
Is slave_exec_mode idempotent actually idempotent?
Is slave_exec_mode idempotent actually idempotent?
Oct 17, 2018
6
min read
Distinguish real SQL execution plans from fake ones!
Distinguish real SQL execution plans from fake ones!
Nov 3, 2016
7
min read
OLTP type 64 compression and 'enq: TX - allocate ITL entry' on Exadata
OLTP type 64 compression and 'enq: TX - allocate ITL entry' on Exadata
Dec 22, 2014
3
min read
No Comments Yet
Let us know what you think