December 2, 2016 at 12:51 am
Hi Gurus,
Please help to get the coding done for a scenario as below
I want to create a temp table which will have my AAG groups ( more than 1) , role_desc, jobname, job_status
Each job will be specific to the AAG group.
Deepening on the role_desc the job_status will be enabled or disabled. Hence deepening on my AAG role_desc the job will be enabled or disabled.
PLease help me with the code.
Cheers,
Got an idea..share it !!
DBA_Vishal
December 2, 2016 at 3:02 am
I need a code which will check the AAG name and its role , if it is primary then all the jobs status will enabled else disabled. We have multiple AAG and some time AAG will span across the replicas ( primary and secondary) hence we have to identify the AAGs and jobs assiciated with it and make it enable or diable in that replica where it is primary.
Cheers,
Got an idea..share it !!
DBA_Vishal
December 2, 2016 at 3:21 am
vkundar (12/2/2016)
I need a code which will check the AAG name and its role , if it is primary then all the jobs status will enabled else disabled. We have multiple AAG and some time AAG will span across the replicas ( primary and secondary) hence we have to identify the AAGs and jobs assiciated with it and make it enable or diable in that replica where it is primary.
Where is your DDL, sample data and expected results?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 2, 2016 at 4:23 am
I used the below also I set it to disable certain jobs if it was the secondary too, so when failing over all of this was automatic.
Simply set the jobs you want enabled in the <ENTER CODE> section
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
IF @RoleDesc = 'PRIMARY'
BEGIN
<ENTER CODE HERE>
END
December 2, 2016 at 4:24 am
I set it up to run every minute. So it was continually looking to see if it was the PRIMARY or SECONDARY.
December 5, 2016 at 8:48 pm
This will only handle if there is only single AAG. I am facing the issue with multiple AAG groups and AAG resides on multiple replicas making it primary due to failover.
Came up with a script below which is pending for testing but I think this will work.
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
Declare @name varchar(500)
Declare @initcnt int = 1
Declare @maxcnt int = 0
Declare @AAGname varchar(30)
Set @maxcnt = (select count(1) from msdb.dbo.sysjobs where name <> 'CHK_AAG_role')
Declare AAG_CUR CURSOR FOR
SELECT ARS.role_desc, AGC.name
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
ON
AGL.group_id = ARS.group_id
WHERE
RCS.replica_server_name = @@SERVERNAME
and ARS.role_desc = 'PRIMARY'
Open AAG_CUR
FETCH NEXT FROM AAG_CUR INTO @RoleDesc,@AAGname
print @RoleDesc + ' '+ @AAGname
WHILE @@FETCH_STATUS = 0
begin
if @RoleDesc = 'PRIMARY'
PRINT 'Enable JOBS '
-- Enable all the jobs
--while @initcnt <= @maxcnt
BEGIN
SET @name = (Select name from(select name,ROW_NUMBER() over (order by name asc) AS RN from msdb.dbo.sysjobs where name <> 'CHK_if_Primary')A where RN=@initcnt)
print 'loop'
EXEC msdb.dbo.sp_update_job
@job_name = @name,
@enabled =1
SET @initcnt = @initcnt + 1
END
FETCH NEXT FROM AAG_CUR INTO @RoleDesc,@AAGname
print @RoleDesc + ' '+ @AAGname
end
CLOSE AAG_CUR
DEALLOCATE AAG_CUR
Cheers,
Got an idea..share it !!
DBA_Vishal
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply