• 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