Cool Tips‎ > ‎Clustering‎ > ‎

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