USE master;
GO
IF OBJECT_ID(‘dbo.sp_hadr_isprimary’,’P’) IS NULL
EXEC (‘CREATE PROCEDURE dbo.sp_hadr_isprimary AS PRINT 1;’);
GO
–return 1=current instance is primary, 0=current not primary
ALTER PROCEDURE dbo.sp_hadr_isprimary
( @agname sysname = ‘AG_GROUP_NAME’ )
AS
DECLARE @PrimaryReplica sysname
, @ThisReplica sysname
, @retval int = 0;
BEGIN
SET NOCOUNT ON;
SET @ThisReplica = cast(ServerProperty(‘ServerName’) as 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(@ThisReplica)
BEGIN
SET @retval = 1
END
SELECT @retval “IsPrimary”
, @agname “AG_Name”
, @PrimaryReplica “PrimaryReplica”
, @ThisReplica “ThisReplica”
RETURN @retval
END;
GO
Example Usage:
declare @retval int
exec @retval = master.dbo.sp_hadr_isprimary
SELECT @retval “return_value”