SQL - Managing Jobs in Availability Groups

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

• @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)

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

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

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

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

Replica Validation Step: Configured - job updated Not Configured - the job doesn't have the validation step

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

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

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