查询服务器是否参与可用性组

SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

请参阅此链接以获取更多信息..

https://www.pythian.com/blog/list-of-sql-server-databases-in-an-availability-group/

当服务器实例打开 AlwaysON 时，您可以通过以下方式确认SERVERPROPERTY ('IsHadrEnabled') = 1

如果您想获得更多详细信息，则可以使用以下查询：

--- get data into temp tables 

select * into #availability_groups from sys.availability_groups
select * into #availability_replicas from sys.availability_replicas

SELECT  
ISNULL(agstates.primary_replica, '') AS [Primary_AG_Server],
dbcs.database_name AS [DatabaseName]
 ,ar.replica_server_name as [Secondary_AG_Servers]

   FROM #availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN #availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local <> 1 -- is_local is not 1 .. so we get secondary server names
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
    group by agstates.primary_replica, dbcs.database_name, ar.replica_server_name

注意：如果您有混合服务器 - 一些使用 AlwaysON 而一些不使用，那么您将需要使用动态 sql，否则上述查询将在未配置为使用 AlwaysON 的服务器上失败。