SQL – Managing Jobs in Availability Groups

Posted in: Microsoft SQL Server, Technical Track

This blog post will show how to dynamically add logic at the beginning of jobs in Availability Groups in order to avoid job execution errors in the replica or replicas.

Problem

In SQL Availability Groups the SQL jobs have to be created in all replicas and you need to add logic at the beginning of each relevant job to make it execute on the primary database. (this only applies when the local replica is the primary for the database)

If you do not add the logic they will execute with success in the primary replica but they will fail in the secondary replica.

Procedure:

The approach will dynamically detect which jobs belong to databases in the Availability Group (AG) and create a first job step to detect if the AG’s role is the primary replica based on Database Name (using the function sys.fn_hadr_is_primary_replica). The jobs in the primary replica will proceed to the next step and the jobs in the secondary replica will be cancelled.

Script: SQL Managing Jobs in Availability Groups v01

Features:

  • Update the SQL jobs or Print only commands (Update by Default)
  • Readable Secondary Mode – Able to update job by Readable Secondary Mode
    • No readable (Default)
      • Why? You can have report jobs configured to get data from the secondary databases. Adjust this variable to your environment.
  • Creates job category for each Availability Group Name and assigns to the jobs. (Disable by Default)
  • Creates a validation job step to jobs with databases in Availability Groups.
  • Shows job configuration status.

Example of different replica roles behaviour:
WIN-OCFC1A15RTG\INST01 – AG1 Primary | AG2 Primary
WIN-NOTC5E3E3UG\INST02 – AG1 Secondary | AG2 Secondary

 

Example of different replica roles behaviour:
WIN-OCFC1A15RTG\INST01 – AG1 Secondary| AG2 Secondary
WIN-NOTC5E3E3UG\INST02 – AG1 Primary | AG2 Primary

 

How it Works:

1 – Custom Variables.

The script has three customization variables that allow to:

  • @debug: Insert job step in SQL Jobs or Print Only the t-sql script.
  • @ignoresecondary: Insert job step or Print Only considering the readable secondary role.

0 – No Readable (Default): Update only jobs with no readable secondary role (print if exists the other jobs).
1 – Read Intent Only: Update jobs with no readable secondary and read intent only role (print if exists the other jobs).
2 – Readable Secondary: Update jobs with no readable secondary and readable secondary mode jobs (print if exists the other jobs).
3 – Update All: Update all jobs with databases that belong to Availability Groups.

  • @createcategory: Creates a Job Category for each availability group name and assigns to the jobs of the availability groups (disable by default)

2 – Availability Groups and Databases.

Find all Availability Group names and their databases. Exclude all databases that do not belong to any Availability Group and get details of readable secondary mode.

 

3 – Jobs

Find all SQL jobs that have t-sql in their steps, or definitions of the databases, and are in an Availability Group.

  • Which jobs are updated?
    • If they reference a database related to Availability Groups.

 

4- Job Categories

Create job categories per Availability Group name and assign it to the jobs.

To enable you to change the variable @createcategory to 1

 

5 – Update jobs to dynamically detect replica role.

  • Insert or Print a first job step that detects the Availability Group role.
  • Jobs that already have a step named ‘get_availability_group_role’ are excluded.

Example of a job update:

 

Result:

  • TestDB1 – Collect CPU Usage: Job step added with success.
  • TestDB2 – Collect CPU Usage: Job not updated – Script Only.
    • Why? For AG2 the secondary role is readable and the variable is set:
      • @ignoresecondary = 0 (no readable – default)

 

If you want to update jobs that belong to readable databases:

  • Change the variable to @ignoresecondary = 2.
  • Or run the output script printed.

 

Example – First step created (Job: TestDB1 – Collect CPU Usage):

 

6 – Get Jobs Configuration Status

Get all jobs with databases in Availability Groups and their configuration status.

Replica Validation Step:
Configured – job updated
Not Configured – the job doesn’t have the validation step

 

Conclusion:

I hope you found this approach of dynamically updating all jobs helpful.

Permissions:

Note: When the job owner user is not a sysadmin assign the following permissions.

--REPLACE replace_job_owner_username for your job owner user account

--master - grant permissions
--execute fn_hadr_is_primary_replica 
--grant view database and server state
--grant select on tables: availability_databases_cluster, availability_groups_cluster, availability_replicas

use [master]
go
create user [replace_job_owner_username] for login [replace_job_owner_username]
go
grant execute on [sys].[fn_hadr_is_primary_replica] 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
grant select on [sys].[availability_databases_cluster] to [replace_job_owner_username]
go
grant select on [sys].[availability_groups_cluster] to [replace_job_owner_username]
go
grant select on [sys].[availability_replicas] 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

 

T-SQL Code:

You can find this script attached at the beginning of the post.

/*
********************************************************************************
SQL AlwaysOn - Managing Jobs in Multiple Availability Groups
********************************************************************************

Method - Find all jobs that perfom queries in databases that belongs to Availability Group and Insert a first step to check the Replica Role.

 * Author: Created by Gonçalo Cruz
 * You can contact me by e-mail at:	cruz@pythian.com
 * Date: 23/03/2020

Run the script in all Availability Groups Nodes.

Permissions issues? If the job owner user is not part of the SQL Server sysadmin role, you need to assign the specific permissions. (look at the end of this script)


########################
  READ ME (Options):
########################

------------------------------------------
1) Update Jobs Steps or Print Only Option:
------------------------------------------

By default find all SQL jobs that have in their T-SQL or definitions databases that are in Availability Groups and update them with a first step that validates the Primary Replica Role.
If you just want to get the output of script without updating the jobs change the variable @debug = 1

-- Update or Print Options:

Update Jobs	(DEFAULT)	-> @debug bit = 0 
Print Only Scripts		-> @debug bit = 1

--------------------------
2) Secondary Mode Options:
--------------------------
  
By default when the Job belong to a database that is in a AG configured as Readable Secondary or Read Intent Only -> Print Only Add Step Command for those Jobs	
If you want to Run jobs Always in the Primary Replica change the variable @ignoresecondary

-- Options:

NO READABLE (DEFAULT):

  @ignoresecondary int = 0  -- Update only Jobs with no readable secondary role (Print if exists the read intent only and readable secondary mode jobs)

READ INTENT ONLY:

  @ignoresecondary int = 1  -- Update Jobs with no readable secondary and read intent only role (Print if exists the readable seconday mode jobs)

READABLE SECONDARY:

  @ignoresecondary int = 2  -- Update Jobs with no readable secondary and readable secondary mode jobs (Print if exists read intent only mode jobs)

UPDATE ALL JOBS:

  @ignoresecondary int = 3  -- Update all Jobs with databases that belong to Availability Groups


---------------------------------------------------------
3) Assign Job Categories to Availability Groups SQL Jobs:
---------------------------------------------------------

Creates a Job Category for each Availability Group Name.
Assign Availability Group Name Category to each SQL job that uses a database in a Availability Group.
  
Don't Create Job Categories (DEFAULT)		-> @createcategory int = 0
Create Job Categories and assign to jobs	-> @createcategory int = 1

*******************************************************************************/

use [msdb]
go
set nocount on;


-- 1) Update Jobs Steps (0) or Print Only Option (1):
declare @debug bit = 1

-- 2) Secondary Mode Options: No readable (0); Read intent only (1); Readable Secondary (2); Update All Jobs (3):
declare  @ignoresecondary int = 0

-- 3) Create and assign Job Categories to Availability Groups SQL Jobs: Don't Create Category (0); Create and assign (1):
declare  @createcategory int = 1


-- script variables
declare @table_jobname_dbname table (agname varchar (200), databasename varchar(400),secondary_role_allow_connections int)
declare @joblist table (jobname varchar (400), agname varchar (200), databasename varchar(400),secondary_role_allow_connections int)
declare @distinctjoblist table (jobname varchar (400), agname varchar (200), databasename varchar(400),secondary_role_allow_connections int)
declare @distinctjoblisttocreatecateg table (jobname varchar (400), agname varchar (200), databasename varchar(400),secondary_role_allow_connections int)
declare @table_jobname table (agname varchar (200))
declare @flag int, @command varchar(max), @min_id int, @max_id int, @job_name sysname, @db_name sysname, @secondary_role int, @createcategoryflag int


--get ag names and respective databases
insert into @table_jobname_dbname 
select  groups.[name],databaselist.[database_name], secondary_role_allow_connections
from    sys.availability_databases_cluster databaselist
inner join sys.availability_groups_cluster groups on databaselist.group_id = groups.group_id
inner join master.sys.availability_replicas Replicas ON databaselist.group_id = Replicas.group_id and replica_metadata_id is null

set @flag = (select count (*) from @table_jobname_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, ag.secondary_role_allow_connections
  from sysjobs j
  inner join sysjobsteps js on j.job_id=js.job_id
  cross join @table_jobname_dbname ag
  where command like '%'+ag.databasename+'%' or database_name = ag.databasename
  group by j.name, ag.agname, ag.databasename, ag.secondary_role_allow_connections

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

insert into @distinctjoblist select distinct jobname, agname ,databasename, secondary_role_allow_connections  from @joblist l
inner join sysjobs j on l.jobname = j.name
where j.name not in (
        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')
        
--List of jobs with databases in Availability Groups
--select * from @distinctjoblist

--Create and assign Categories
if @createcategory = 1
insert into @distinctjoblisttocreatecateg select distinct jobname, agname ,databasename, secondary_role_allow_connections from @distinctjoblist
begin

set @createcategoryflag = (select count (*) from @distinctjoblisttocreatecateg)

  while @createcategoryflag > 0
  begin
    declare @jobname varchar (400)
    declare @agcategoryname varchar (200)
    set @jobname = (select top 1 jobname from @distinctjoblisttocreatecateg order by jobname asc)
    set @agcategoryname = (select top 1 agname from @distinctjoblisttocreatecateg 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 @distinctjoblisttocreatecateg where jobname = @jobname
    set @createcategoryflag = (select count (*) from @distinctjoblisttocreatecateg)
  end
end

--Insert or Print validation Job Step

insert into @table_jobname select jobname from @distinctjoblist


    if object_id(N'tempdb.dbo.#dataagjob',N'u') is not null drop table dbo.#dataagjob;
    create table dbo.#dataagjob (id int identity primary key, name sysname,agname varchar (200), databasename varchar(400), secondary_role_allow_connections int);

      -- get all job names that belong to AG details and exclude jobs that already have a step named 'get_availability_group_role'
      
      insert dbo.#dataagjob (name, agname, databasename, secondary_role_allow_connections)
        select distinct j.name, l.agname, l.databasename, l.secondary_role_allow_connections
        from dbo.sysjobs j inner join @distinctjoblist l on j.name = l.jobname
        where j.name not in (
        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'
        )		

      select  @min_id = 1, @max_id = (select max(d.id) from #dataagjob as d);

      -- loop through the table and execute/print the command per each job
      while @min_id <= @max_id
      begin;
          select @job_name = name from dbo.#dataagjob where id = @min_id;
          select @db_name = (select databasename from dbo.#dataagjob where id = @min_id);
          select @secondary_role = (select secondary_role_allow_connections from dbo.#dataagjob where 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


If sys.fn_hadr_is_primary_replica ( N''''' + @db_name + ''''' ) <> 1   
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  
else
begin
print ''''Primary Replica - Continue to next step''''
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 and @secondary_role = 0 and @ignoresecondary = 0
        begin
          print 'Job Name: "' +@job_name+ '"'+ char(10) +'Database: '+@db_name+' '+ char(10) + 'Result: Jobstep added with success!!!'
          exec (@command);
        end
      else if @debug = 0 and (@secondary_role = 0 or @secondary_role = 1) and @ignoresecondary = 1
        begin
          print 'Job Name: "' +@job_name+ '"'+ char(10) +'Database: '+@db_name+' '+ char(10) + 'Result: Jobstep added with success!!!'
          exec (@command);
        end
      else if @debug = 0 and (@secondary_role = 0 or @secondary_role = 2) and @ignoresecondary = 2
        begin
          print 'Job Name: "' +@job_name+ '"'+ char(10) +'Database: '+@db_name+' '+ char(10) + 'Result: Jobstep added with success!!!'
          exec (@command);
        end
        else if @debug = 0 and @ignoresecondary = 3
        begin
          print 'Job Name: "' +@job_name+ '"'+ char(10) +'Database: '+@db_name+' '+ char(10) + 'Result: Jobstep added with success!!!'
          exec (@command);
        end
      else

        begin
          print ' '
          print '-- **** PRINT ONLY ****'
          print ' '
          print '-- JOB -> "'+@job_name+'" -> NOT UPDATED!!! '
          print ' '
          print '--	   -> to add jobstep execute script bellow manualy'+ char(10) + '--	   -> Or change the variable @debug = 0 and the variable @secondary_role to respective Readable Mode and Execute the Script Again!'
          print ' '
          print '-- Atual @debug value = '+CAST(@debug AS CHAR(1))
          print '-- Atual @secondary_role value = '+CAST(@secondary_role AS CHAR(1))
          print ' '
          print '-- ========================================================================'
          print ' '
          print @command;
        end
      delete from dbo.#dataagjob where name = @job_name
      select @min_id += 1;
    end
    
--Get Jobs Status
select distinct jobname as [Job Name], agname as [Availability Group],databasename as [Database Name], [Replica Validation Step] = 'Configured' from @joblist l
inner join sysjobs j on l.jobname = j.name
where j.name in (
        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')
union all
select distinct jobname as [Job Name], agname as [Availability Group],databasename as [Database Name], [Replica Validation Step] = '>> Not Configured <<' from @joblist l
inner join sysjobs j on l.jobname = j.name
where j.name not in (
        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')


/*
--###############################################################################
--Permissions:  
--	When the job owner user is not a sysadmin assign the following permissions.
--###############################################################################


use [master]
go
create user [replace_job_owner_username] for login [replace_job_owner_username]
go
grant execute on [sys].[fn_hadr_is_primary_replica] 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
grant select on [sys].[availability_databases_cluster] to [replace_job_owner_username]
go
grant select on [sys].[availability_groups_cluster] to [replace_job_owner_username]
go
grant select on [sys].[availability_replicas]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/relational-databases/system-functions/sys-fn-hadr-is-primary-replica-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-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 *