One DBA's Ongoing Search for Clarity in the Middle of Nowhere


Yet Another Andy Writing About SQL Server

Friday, April 22, 2016

Where Is My Primary Replica Again?

We have many clients with multi-node Availability Groups - that is, AGs with more than two replicas.  One of the problems I have always had with Availability Group management via the GUI (admit it, you use the GUI sometimes all you non-PowerShell geeks) is the fact that most of the work needs to be done from the primary replica.  You can connect to the Availability Group manager on a secondary replica, but you can only see limited data about that particular secondary replica and can't see much about the other replicas, including *which* replica is the current primary replica!

To perform management you almost always need to connect to the primary replica, but how can I figure out which one is primary without just connecting to the instances one by one until I get lucky?
Enter the T-SQL:
SELECT AS AvailabilityGroupName
, HAGS.primary_replica AS PrimaryReplicaName
, HARS.role_desc as LocalReplicaRoleDesc
, DRCS.database_name AS DatabaseName
, HDRS.synchronization_state_desc as SynchronizationStateDesc
, HDRS.is_suspended AS IsSuspended
, DRCS.is_database_joined AS IsJoined
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as HAGS
ON AG.group_id = HAGS.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS HARS
ON AR.replica_id = HARS.replica_id AND HARS.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS DRCS
ON HARS.replica_id = DRCS.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS HDRS
ON DRCS.replica_id = HDRS.replica_id
AND DRCS.group_database_id = HDRS.group_database_id
ORDER BY, DRCS.database_name
This query can be run on any of the replicas, and it will return information about the Availability Groups and their member databases, *including* the name of the primary replica instance!
Hope this helps!


  1. Is [is_database_joined] accurate in master.sys.dm_hadr_database_replica_cluster_states? I have DB outside AAG never shows up.

  2. I find powershell is much easier for this. I have a function I use all the time that takes a param for a computer name and returns the SQL Instance Object. Then it is just three lines to get the primary from any host.

    $instance = get-csqlinstance 'yourhostname' #function that takes the computer name and returns the #Microsoft.SqlServer.Management.SMO.Server object of the instance

    $ags = $instance.AvailabilityGroups # get the ags on that server
    $pris = $ags | Select -Property Parent, Name, PrimaryReplicaServerName -Unique #return the Servername, agname, primary replica

    No joins quick and easy. You could also return any of the other info you have in the sql script. Just another way to go about it I find PowerShell is much better than SQL at many things.