View your Failover Cluster from the DMV’s

Just a quick post this time , I was fumbling around the DMV’s the other day looking for a specific Availability group DMV that I just couldn’t remember the name of – I think we have all been there before with some of the DMV names! 🙂

Whilst I was combing through various combinations of sys.dm_hadr and sys.Availability I stumbled across a couple of gems that I thought would be nice to share.

  • sys.dm_hadr_cluster
  • sys.dm_hadr_cluster_members
  • sys.dm_hadr_cluster_networks

But before we get carried away with what they show, let me show you what things look like from the Failover Cluster Manager so that we can be sure that I am not pulling a fast one.


2017-08-15 20_33_24-SQL01 [Running] - Oracle VM VirtualBox
Cluster is up and Running with SQL01 as the Current host


2017-08-15 20_33_37-SQL01 [Running] - Oracle VM VirtualBox
3 Nodes in the Cluster – SQL01 & SQL02 are up and SQL03 is down

So lets see what the DMV’s say:


2017-08-15 20_37_42-DC01 [Running] - Oracle VM VirtualBox.png
SQL01 & SQL02 showing as UP and SQL03 showing as DOWN , notice that it even shows that SQL03 does not contribute to the voting 🙂

Ok so this is all well and good it saves having to use Failover cluster manager to save some time in a crucial time critical situation – but what do we see if the cluster is down?


2017-08-15 20_31_49-SQL01 [Running] - Oracle VM VirtualBox
Cluster Down


2017-08-15 20_31_16-DC01 [Running] - Oracle VM VirtualBox
SQL will show you nothing when the cluster is down (this also occurs if the Cluster host is currently being moved)


So that’s it, just a quick one to share today that may just make life a little easier 🙂

Thanks for reading.

2 thoughts on “View your Failover Cluster from the DMV’s

Add yours

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at

Up ↑

%d bloggers like this: