檢查目前伺服器是否參與可用性組的功能，如果是，則它是否是主伺服器

我曾經在一個可用性組中有兩台伺服器，但是其中一個伺服器在修補時出現問題，長話短說，我從可用性組中刪除了所有數據庫。

我只在主伺服器中管理作業執行的方式主要是通過這個腳本來測試數據庫是否在主副本中：

 If sys.fn_hadr_is_primary_replica ('apcore') =1  
BEGIN 


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

END

但是現在目前伺服器是可用性組中唯一的伺服器，這個腳本

 select sys.fn_hadr_is_primary_replica ('apcore')

返回 null，因此不准確。

我一直在開發一個bit在以下情況下應該返回 1 的函式：

1 - 我們不屬於可用性組

2 - 我們是可用性組的一部分，我們是主伺服器

問題是：

此功能是否適用於分佈式可用性組？是否有任何其他我沒有想到的情況可能會阻止此函式返回預期值？

這是函式：該函式將在作業中使用並檢查我們是否應該執行作業（因為我們在可用性組中並且我們是主要的，或者我們根本不在可用性組中）。

--=========================================
-- 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 

我們使用分佈式可用性組（以及一些直接位於 AG 中的伺服器）並遇到了類似的問題。我們最終決定將此查詢作為我們的最終解決方案。它可能比它需要的更複雜，但它確實有效。

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

引用自：https://dba.stackexchange.com/questions/226266