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:
AGC.name -- Availability Group
, RCS.replica_server_name -- SQL cluster node name
, ARS.role_desc -- Replica Role
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
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?