SQL Server - Is database part of an Availability Group?

Compatible from SQL Server 2012

Check databases membership in Availability Groups. This very nice handy script from Dimitri Janczak can help you in various situations when dealing with Availability Groups. You can handle conditional logic in your scripts by passing name of database and retrieving name of Availability Group is is joined to. And many more, I really like it.

TIP: Use conditions in WHERE clause to focus just on databases you need.

Script source: https://dimitri.janczak.net/2016/07/08/database-part-availability-group/

	db.name AS DatabaseName, 
		WHEN hdrs.is_primary_replica IS NULL THEN 'NOT REPLICATED'
		WHEN EXISTS ( SELECT 1 FROM master.sys.dm_hadr_database_replica_states AS irs WHERE db.database_id = irs.database_id AND is_primary_replica = 1 ) THEN 'PRIMARY'
	END AS AGReplica,
	COALESCE(grp.ag_name,'N/A') AS AGName,
	COALESCE(agl.dns_name,'N/A') AS AGListenerName
FROM sys.databases AS db
	LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS hdrs ON hdrs.database_id = db.database_id
	LEFT OUTER JOIN master.sys.dm_hadr_name_id_map AS grp ON grp.ag_id = hdrs.group_id
	LEFT OUTER JOIN master.sys.availability_group_listeners AS agl ON agl.group_id = grp.ag_id
-- WHERE db.name = N''


Script results screen:

Results screen

Script messages after execution:

("n" row(s) affected)

Script results detailed description:

Column nameData typeDescription
DatabaseNamesysnameName of database, unique within an instance of SQL Server or within a Azure SQL Database server.
AGReplicanvarcharRole of actual replica. Can be PRIMARY, SECONDARY or NOT REPLICATED when database is not part of any Availability Group.
AGNamenvarchar(256)Name of the availability group. This is a user-specified name that must be unique within the Windows Server Failover Cluster (WSFC) cluster.
AGListenerNamenvarchar(63)Configured network name (hostname) of the availability group listener.

Leave a Reply