Ykhjuy

I used to have 2 servers on an availability group, but there was a problem with one of them servers involved,while patching, and to make a long story short I removed all the databases from the availability group.

The way I managed execution of jobs only in the primary server was mainly through this script which tests whether the database is in the primary replica:

  If sys.fn_hadr_is_primary_replica ('apcore') =1  

BEGIN 





      EXEC [APCore].[app].[usp_upd_applicationStatusTimeExpired]



END

But now that the current server is the only server in the Availability group,
this script

  select sys.fn_hadr_is_primary_replica ('apcore')

retuns null, and therefore is not accurate.

I have been developing a function that should return a bit 1 in one of the following cases:

1 - we are not part of an availability group

2 - we are part of an availability group and we are the primary server

The question is:

Would this function work for a distributed availability group?
Is there any other situation that I haven't thought of that might prevent this function to return the expected value?

and here is the function:
the function is to be used inside jobs and check whether we should run the job (either because we are in an availability group and we are the primary, or we are not in an availability group at all).

--=========================================

-- scalar-valued function dbo.check_HADR_status

-- returns 1 when either primary or we are standalone 

-- (not part of an availability group)



-- USAGE:

-- SELECT MASTER.dbo.check_HADR_status()

--=========================================



USE MASTER

GO



IF OBJECT_ID (N'dbo.check_HADR_status') IS NOT NULL

   DROP FUNCTION dbo.check_HADR_status

GO



CREATE FUNCTION dbo.check_HADR_status()

RETURNS BIT

WITH EXECUTE AS CALLER

AS

BEGIN

    RETURN (SELECT CASE WHEN EXISTS (select * from sys.availability_replicas) THEN 

                            CASE WHEN  (   SELECT COALESCE(a.role_desc,'RADHE')

                                             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) LIKE 'PRIMARY' THEN 1 

                                 ELSE 0

                            END

                  ELSE 1

                  END)

END

GO





IF (SELECT MASTER.dbo.check_HADR_status()) = 1 

   BEGIN

      PRINT 'RUN THE JOB STEP'

   END

ELSE

   BEGIN

      PRINT 'DON''T RUN THE JOB AS WE ARE IN A SECONDARY REPLICA'

   END 

We use Distributed Availability Groups (and some servers that are just in a straight AG) and ran into a similar issue. We eventually settled on this query as our end all, be all solution. It's probably more complicated than it needs to be, but it does work.

DECLARE @DBName sysname;

DECLARE @IsPrimary BIT = 0;



--Determine if the database selected is online.

;WITH CTE_DAG

AS

    (   SELECT AG.[name] AS DAGName

               , AG.is_distributed

               , AR.replica_server_name AS UnderlyingAG

               , ARS.role_desc

        FROM sys.availability_groups AS AG

            INNER JOIN sys.availability_replicas AS AR ON AR.group_id = AG.group_id

            INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = AR.replica_id

        WHERE AG.is_distributed = 1)

     , CTE_LocalAG

AS

    (   SELECT AG.[name] AS LocalAGName

               , AG.is_distributed

               , AR.replica_server_name AS UnderlyingAG

               , ARS.role_desc

               , D.[name] AS DatabaseName

               , DRS.is_primary_replica

        FROM sys.availability_groups AS AG

            INNER JOIN sys.availability_replicas AS AR ON AR.group_id = AG.group_id

            INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = AR.replica_id

            INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS ARCS ON ARCS.group_id = AG.group_id

            LEFT OUTER JOIN sys.dm_hadr_database_replica_states AS DRS ON DRS.replica_id = ARCS.replica_id

                                                                           AND DRS.group_id = ARCS.group_id

            INNER JOIN sys.databases AS D ON D.database_id = DRS.database_id

        WHERE AG.is_distributed = 0

            AND ARCS.replica_server_name = @@SERVERNAME)

     , CTE_Composite

AS

    (   SELECT L.DatabaseName

               , L.role_desc

               , L.is_primary_replica

               , COALESCE(D.role_desc, 'NONE') AS DAG_Role

               , IsAllPrimary = CASE WHEN L.is_primary_replica = 1

                                         AND COALESCE(D.role_desc, 'NONE') IN ('NONE', 'PRIMARY')

                                          THEN 1

                                    ELSE  0

                                     END

        FROM CTE_LocalAG AS L

            LEFT OUTER JOIN CTE_DAG AS D ON D.UnderlyingAG = L.LocalAGName

        WHERE L.DatabaseName = @DBName)

     , CTE_Grouping

AS

    (   SELECT DatabaseName

               , SUM(IsAllPrimary) AS TotalPrimary

               , COUNT(DatabaseName) AS TotalCount

        FROM CTE_Composite

        GROUP BY DatabaseName)

SELECT TOP (1)

       @IsPrimary = 1

FROM CTE_Grouping

WHERE COALESCE(TotalPrimary, 0) = COALESCE(TotalCount, 0);