SQLTeam.com | Weblogs | Forums

Find out Primary Server Name in AlwaysOn


#1

Hi all,

I have a two node Always On Availability cluster and one availability group.

When I'm on the secondary server I would like to know the name of the primary server.

I have this query:

SELECT
AGC.name -- Availability Group
, RCS.replica_server_name -- SQL cluster node name
, ARS.role_desc -- Replica Role
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id = RCS.replica_id

This only returns information about the secondary server if it is run on the secondary server.

However when I run this on the primary server it provides information on both the primary and secondary server.

Is there any way to determine the name of the primary server when the query is run on the secondary server?


#2

Select * From sys.dm_hadr_availability_group_states dhags