TSQL: Replicas Database

Query to return info about replicas.

SELECT  
	*
FROM    
	sys.availability_replicas r
	JOIN sys.availability_databases_cluster dc ON dc.group_id = r.group_id
	LEFT JOIN sys.dm_hadr_availability_group_states gs ON gs.group_id = r.group_id AND gs.primary_replica = r.replica_server_name

Query to return a list of Primary Replicas databases.

SELECT  
	dc.database_name
FROM    
	sys.availability_replicas r
	JOIN sys.availability_databases_cluster dc ON dc.group_id = r.group_id
	LEFT JOIN sys.dm_hadr_availability_group_states gs ON gs.group_id = r.group_id AND gs.primary_replica = r.replica_server_name
WHERE   
	gs.primary_replica IS NOT NULL
	AND 
	r.replica_server_name = @@SERVERNAME

Query to return a list of Non-Primary Replicas databases.

SELECT  
	dc.database_name
FROM    
	sys.availability_replicas r
	JOIN sys.availability_databases_cluster dc ON dc.group_id = r.group_id
	LEFT JOIN sys.dm_hadr_availability_group_states gs ON gs.group_id = r.group_id AND gs.primary_replica = r.replica_server_name
WHERE   
	gs.primary_replica IS NULL
	AND 
	r.replica_server_name = @@SERVERNAME