Programmatically getting SQL Cluster Virtual Name

Question

I have written a Windows service to collect information from all of our SQL servers. The service gets installed onto each server, and utilizing WMI and SMO, inserts relevant system information back to a central database. In order to get the SQL information, I use the following c# code:

        List<Server> sqlServers = new List<Server>(); //List of Smo.Server objects
        string registrySubKey = @"SOFTWARE\Microsoft\Microsoft SQL Server";
        string registryValue = "InstalledInstances";

        try
        {
            RegistryKey rk = Registry.LocalMachine.OpenSubKey(registrySubKey);
            string[] instances = (string[])rk.GetValue(registryValue);
            if (instances.Length > 0)
            {
                foreach (string element in instances)
                {
                    Server s;
                    string serverInstance;

                    if (element == "MSSQLSERVER") //If default instance
                    {
                        serverInstance = System.Environment.MachineName;
                    }
                    else
                    {
                        serverInstance = System.Environment.MachineName + @"\" + element;
                    }

                    s = new Server(serverInstance);

                    if (s != null)
                    {
                        sqlServers.Add(s);
                    }
                }
            }
        }

The only problem I'm having is on our SQL clusters. For those of you unfamiliar with active/passive sql clustering, you cannot connect directly to one of the nodes. Instead the sql cluster gets a virtual name and another ip address that clients would then connect to. The current code I have will try to connect to NodeName\instanceName which obviously will not work on the cluster. Instead I need to programmatically find the SQL cluster virtual name that this node belongs to and connect to that instead. I thought I might be able to get this information from the MSCluster_Cluster WMI class, but that will only get me the cluster virtual name, not the SQL cluster virtual name. Thanks in advance.

Answer 1

Could you use a WMI query with the mscluster WMI classes?

http://technet.microsoft.com/en-us/library/cc780572(WS.10).aspx http://blogs.msdn.com/clustering/archive/2008/01/08/7024031.aspx

By query I mean interrogate all the cluster's resources/groups to locate the SQL Server network name.

Answer 2

With this code:

using System.Management;

ManagementObjectSearcher searcher = new ManagementObjectSearcher("root\\MSCluster", "SELECT * FROM MSCluster_Resource"); 

foreach (ManagementObject queryObj in searcher.Get())
{
    Console.WriteLine("Name: {0}", queryObj["Name"]);
}

I can see my SQL Cluster Name (2008CLUSTERSQL) as two of the outputs:

Name: SQL IP Address i (2008CLUSTERSQL)

Name: SQL Network Name (2008CLUSTERSQL)

Will this help? I guess you can extract the name of out it?

I got this code from WMI Code Creator (http://www.microsoft.com/downloads/details.aspx?FamilyID=2cc30a64-ea15-4661-8da4-55bbc145c30e&displaylang=en), a very useful tool to browse different WMI namespaces/classes/properties.

Harold