function to check whether the current server is involved in an availability group and if so, whether it is the primary

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

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

StackExchange Database Administrators Q#226266, answer score: 3