Get Availability Group Secondaries

Applicability:

SQL Server 2000: N/A

SQL Server 2005: N/A

SQL Server 2008: N/A

SQL Server 2008R2: N/A

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 7 Jun 2020

Description

A slight repetition of functionality with the Get AG Details code, but this just returns a list of databases on the current server that are operating as AG Secondary roles.

Code

-- databases operating as Aailability Group Secondary

SELECT DISTINCT

dbcs.database_name AS [DatabaseName] -- database that is an AG secondary

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

INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs

ON arstates.replica_id = dbcs.replica_id

LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs

ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id

WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1