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/
SELECT DISTINCT db.name AS DatabaseName, CASE 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' ELSE 'SECONDARY' 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''
Results
Script results screen:
Script messages after execution:
("n" row(s) affected)
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| DatabaseName | sysname | Name of database, unique within an instance of SQL Server or within a Azure SQL Database server. |
| AGReplica | nvarchar | Role of actual replica. Can be PRIMARY, SECONDARY or NOT REPLICATED when database is not part of any Availability Group. |
| AGName | nvarchar(256) | Name of the availability group. This is a user-specified name that must be unique within the Windows Server Failover Cluster (WSFC) cluster. |
| AGListenerName | nvarchar(63) | Configured network name (hostname) of the availability group listener. |