How to get the Always On Availability Group DNS name per database

22 March,2021 by Jack Vamvas

Question: How can I get a list of databases on an Always On Availability Groups setup , along with the DNS name ? I need the  DNS name of the listener hostname 

 

Answer: This query will list a group of databases associated with availability groups in a Clustered Always On setup .  The sys.availability_group_listeners view as defined on Microsoft docs "For each Always On availability group, returns either zero rows indicating that no network name is associated with the availability group, or returns a row for each availability-group listener configuration in the Windows Server Failover Clustering (WSFC) cluster."

This query uses a left join to the sys.availability_group_listeners  view. If you execute the same query on a Clusterless Always On set up , you will return a list of databases within the Availability Groups but with NULL as the  DNS name. If you execute in a Clustered i.e with listeners , than the DNS_NAME column will be populated with the hostname of the availability group listeners. 

 

select distinct DB.name, GL.dns_name,AG.name 
from
sys.dm_hadr_availability_replica_cluster_nodes CN
join sys.availability_groups AG on AG.name = CN.group_name 
join sys.availability_replicas AR on AR.group_id= AG.group_id 
join sys.databases DB on AR.replica_id= DB.replica_id 
left outer join sys.availability_group_listeners GL on GL.group_id = AG.group_id

Read more on Always On 

Quick access to SQL Server Always On Availability cheatsheet

 How to fix Database might contain bulk logged changes that have not been backed up for Always On


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to get the Always On Availability Group DNS name per database


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer