Get Availability Group Secondaries


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


Author: ChillyDBA

Date: 7 Jun 2020


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.


-- databases operating as Aailability Group Secondary SELECT DISTINCTdbcs.database_name AS [DatabaseName] -- database that is an AG secondary FROM master.sys.availability_groups AS AGLEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstatesON AG.group_id = agstates.group_idINNER JOIN master.sys.availability_replicas AS ARON AG.group_id = AR.group_idINNER JOIN master.sys.dm_hadr_availability_replica_states AS arstatesON AR.replica_id = arstates.replica_id AND arstates.is_local = 1INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcsON arstates.replica_id = dbcs.replica_idLEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrsON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_idWHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1