Cfxtrjtrk

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);

the script below uses Jonathan Fite's script with slight modifications in order to return yes, it is the primary server in the following situations:

1) when there is no availability group - therefore the current server is the primary

2) there is an availability group, but only the current server is part of it - therefore it is the primary server

I could not test it in a distributed availability group environment, because I haven't got one.

I wanted to keep the original logic, and only add the extras if the parameter @DBName is null.

I have marked all changes by %%

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

               , is_primary_replica=CASE WHEN ARS.role_desc = 'PRIMARY' THEN 1 ELSE DRS.is_primary_replica END --%% -- changed to accomodate the situation when one single server in the availability group

        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

            LEFT OUTER JOIN sys.databases AS D ON D.database_id = DRS.database_id --%% -- changed to LEFT OUTER JOIN because databases may not be in the availability group

        WHERE AG.is_distributed = 0

            AND ARCS.replica_server_name = @@SERVERNAME





        UNION ALL



        --this will return true ONLY if there is no availability group at all

        --in this case we are the primary

        --%%

        SELECT @@SERVERNAME AS LocalAGName

               , 0 as is_distributed

               , NULL AS UnderlyingAG

               , 'PRIMARY' as role_desc

               , NULL AS DatabaseName

               , 1 as is_primary_replica

         FROM (VALUES (1)) AS X(A)

         WHERE NOT EXISTS (select * from sys.availability_replicas)



            )

     , 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 OR (@DBName IS NULL) --%% added the null option for @DBNAME



        )

     , CTE_Grouping

AS

    (   



    SELECT DatabaseName

               , SUM(IsAllPrimary) AS TotalPrimary

               , COUNT( case when @DBName IS NULL then 1 else DatabaseName end) AS TotalCount

        FROM CTE_Composite

        GROUP BY DatabaseName



    )

SELECT TOP (1)

       @IsPrimary = 1

FROM CTE_Grouping

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



SELECT @IsPrimary