-- Functions

USE master;
GO

IF OBJECT_ID('dbo.fn_hadr_IsPrimaryAG','FN') IS NOT NULL
        DROP FUNCTION dbo.fn_hadr_IsPrimaryAG
GO

CREATE FUNCTION dbo.fn_hadr_IsPrimaryAG (@AGName sysname) RETURNS bit 
AS
BEGIN 
    DECLARE @primary_replica sysname; 

    SELECT    @primary_replica = h.primary_replica 
    FROM    sys.dm_hadr_availability_group_states AS h
            INNER JOIN sys.availability_groups AS g ON g.group_id = h.group_id
    WHERE    g.name = @AGName;

    IF  ( UPPER(@primary_replica) =  UPPER(@@SERVERNAME) ) RETURN 1; 

    RETURN 0; 

END; 
GO



IF OBJECT_ID('dbo.fn_hadr_IsPrimaryDB','FN') IS NOT NULL
        DROP FUNCTION dbo.fn_hadr_IsPrimaryDB
GO

CREATE FUNCTION dbo.fn_hadr_IsPrimaryDB (@DBName sysname) RETURNS bit
AS
BEGIN

    DECLARE @role_desc sysname;
            
    SELECT    @role_desc =  h.role_desc
    FROM    sys.databases as d
            INNER JOIN    sys.dm_hadr_availability_replica_states as h ON d.replica_id = h.replica_id
    WHERE    database_id = DB_ID(@DBName);

    IF ( UPPER(@role_desc) = N'PRIMARY' ) RETURN 1;
    RETURN 0;

END;
GO

-- SQL Server 2012

SELECT dbo.fn_hadr_IsPrimaryAG('AG1');
SELECT dbo.fn_hadr_IsPrimaryDB('test');
GO
-- SQL Server 2014 and above

SELECT dbo.fn_hadr_is_primary_replica ('test');
GO

-- Code example

IF master.dbo.fn_hadr_IsPrimaryAG('AG1')=1
BEGIN
    PRINT 'This is the Primary the Availability Group';
END
ELSE
BEGIN
    PRINT 'This is NOT the Primary Availability Group';
END
GO

IF master.dbo.fn_hadr_IsPrimaryDB('test')=1
BEGIN
    PRINT 'This is the Primary Database in the Availability Group';
END
ELSE
BEGIN
    PRINT 'This is NOT the Primary Database in the Availability Group';
END
GO

-- Job code
USE test;
GO

IF ( master.dbo.fn_hadr_IsPrimaryAG('AG1')=1 ) AND ( master.dbo.fn_hadr_IsPrimaryDB('test')=1 )
BEGIN
    INSERT INTO T DEFAULT VALUES;
END
ELSE
BEGIN
    PRINT 'This is NOT the Primary Database in the Availability Group';
END

USE master;
GO

IF ( master.dbo.fn_hadr_IsPrimaryAG('AG1')=1 ) AND ( master.dbo.fn_hadr_IsPrimaryDB('test')=1 )
BEGIN
    INSERT INTO test.dbo.T DEFAULT VALUES;
END
ELSE
BEGIN
    PRINT 'This is NOT the Primary Database in the Availability Group';
END

-- Execute job sp
USE master;
go

IF OBJECT_ID('dbo.ExecuteSQLAgentJob','P') IS NOT NULL
        DROP PROC dbo.ExecuteSQLAgentJob;
GO
CREATE PROC dbo.ExecuteSQLAgentJob    (    @jobId uniqueidentifier, 
                                        @availabilityGroup sysname
                                    )  as
BEGIN

    DECLARE @jobname sysname = NULL;

    IF  master.dbo.fn_hadr_IsPrimaryAG('AG1') = 1
    BEGIN 
        SELECT @jobname = name FROM msdb.dbo.sysjobs WHERE job_id = @jobId;
        IF @jobname IS NOT NULL
        BEGIN 
            EXEC msdb.dbo.sp_start_job @jobname;
        END
        ELSE
        BEGIN
            SET @jobname = 'Job "'+@jobname+'" not found.';
            RAISERROR (@jobname,2,1);
        END
    END
    ELSE
    BEGIN
        PRINT 'This is not the primary replica.';
    END

END
GO

select * from msdb.dbo.sysjobs

select * from sys.objects

exec dbo.ExecuteSQLAgentJob '554BBDDA-AE6D-4275-8B9C-D603454C0BD0', 'AG1'


-- Enable jobs on HADR
USE master;
go

IF OBJECT_ID('dbo.EnableSQLAgentJobOnHADR','P') IS NOT NULL
        DROP PROC dbo.EnableSQLAgentJobOnHADR;
GO
CREATE PROC dbo.EnableSQLAgentJobOnHADR    ( @availabilityGroup sysname )  as
BEGIN

    DECLARE @jobname sysname = NULL;

    IF  master.dbo.fn_hadr_IsPrimaryAG(@availabilityGroup) = 1
        BEGIN 
            exec msdb..sp_update_job @job_id = '226776EF-5E19-40BA-8A7F-8B52D34D24CB', @enabled = 1;
        END    
    ELSE
        BEGIN 
            exec msdb..sp_update_job @job_id = '226776EF-5E19-40BA-8A7F-8B52D34D24CB', @enabled = 0;
        END
END
GO

-- Alerts

select * from sys.messages
where language_id=1033 and text like '%availability%';