SQL: Always On Availability Group - Failover Cluster Members
Author: jason
Date: 2014-05-08 14:21:06
Category: Musing

Here is small script to review the Windows Failover Cluster Member status from inside SQL 2012.

Monitor Availability Groups: http://technet.microsoft.com/en-us/library/ff878305.aspx





Code

--Cluster Members
SELECT DISTINCT member_name, (CASE WHEN cm.member_name = gs.primary_replica THEN 'Primary' ELSE 'Secondary' END) [Owner]
, REPLACE(member_type_desc, '_', ' ') [member_type_desc]
, member_state_desc, number_of_quorum_votes
FROM sys.dm_hadr_cluster_members cm
OUTER APPLY (SELECT TOP 1 --This is a really lame way to deal with the diff between server and instance
(CASE WHEN gs.primary_replica LIKE '%\%' THEN
LEFT(gs.primary_replica, (CHARINDEX('\',gs.primary_replica)-1))
ELSE gs.primary_replica END) primary_replica
FROM sys.dm_hadr_availability_group_states gs
WHERE cm.member_name = (
CASE WHEN gs.primary_replica LIKE '%\%' THEN
LEFT(gs.primary_replica, (CHARINDEX('\',gs.primary_replica)-1))
ELSE gs.primary_replica END)
ORDER BY gs.primary_replica desc
) gs








jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009