Is it possible to get Cluster -> Availability Group Instance Node Map via WMI?

Question

I wish to enumerate all the instances within a SQL Server Always On Availability Group.

There is a Dynamic Management View within SQL Sever which provides this, but you need to be connected to a relevant instance to query this view.

Also, I can see in the registry for a node on the cluster that there is the mapping that I wish to get: HKLM\Cluster\Resources\{cluster_guid}\SqlInstToNodeMap

There is a WMI class that can enumerate all Availability Groups on the cluster, but I cannot see how (even by examining the PrivateProperties object) I can enumerate the SQL Instance to Cluster Node map through WMI - does anyone know if this is possible, or can provide a mechanism that doesn't require looking at the registry or querying SQL Server?

Thanks

Answer 1

Not sure if this helps but you could get it using the SMO classes?

Server srv = new Server("instance_name")

You can the access the AvailabilityReplics property of each AvailabilityGroup in the AvailabilityGroups collection to get the replica nodes, something like (untested)

Server srv = new Server("instance_name")
foreach(AvialbilityGroup grp in srv.AvailabilityGroups)
{
  Console.WriteLine("Found group {0} on instance {1}", grp.Name, srv.InstanceName);
  Console.WriteLine("  Replicas:");
  foreach(AvailabilityReplica replica in grp.AvailabilityReplicas)
  {
    Console.WriteLine("  Replica Name: {0}, Cluster Name: {1}", replica.Name, repica.Parent.Parent.ClusterName)
  }
}