• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

Determining Availability Group active/passive nodes in SQL Server 2012

Heyas,

If I have Availability Group setup with 2 nodes, how do I check which node is the active one and which is passive via the GUI?

Also are their SQL queries for this?

Thank you.
0
Zack
Asked:
Zack
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Try

SELECT
   AGC.name [AvailabilityGroup],RCS.replica_server_name [SQL cluster node name], ARS.role_desc [Replica Role] , AGL.dns_name [Listener Name]
FROM
 sys.availability_groups_cluster agc INNER JOIN sys.dm_hadr_availability_replica_cluster_states rcs
   ON RCS.group_id = AGC.group_id INNER JOIN sys.dm_hadr_availability_replica_states ars
   ON  ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL
   ON AGL.group_id = ARS.group_id
WHERE
 ARS.role_desc = 'PRIMARY'

Open in new window

0
 
ZackGeneral IT Goto GuyAuthor Commented:
Thank you for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now