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