/************************************************************************************************************
SQL AlwaysOn - Managing SQL Jobs in Multiple Availability Groups – Method – SQL Job Category

 * 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 AlwaysOn 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)


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



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

1 - Create the function fn_hadr_group_is_primary to detect if 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




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;



/*********************************************************************
	Permissions Issues
**********************************************************************

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

*/