Detecting a SQL cluster

Question

I am trying to write some code that will determine if a list of SQL servers are up. I have tried to WMI, SQLDMO, SqlDataSourceEnumerator, and Pinging port 1433 of each server, with varying degrees of success (see results below).

Using SQLDMO and SqlDataSourceEnumerator, i found 3 out of 6, it has to be said that 2 of the 3 missing SQL servers form a cluster.

Pinging port 1433 found 4 out of the 6, the 2 missing are the 2 servers that form the SQL cluster.

WMI proved to be the least successful, in that it only found 1 out of 6 servers.

Here is the code I used to prototype the server discovery:

private void buildServerMap(bool useLibCOM)
    {
        sqlServersMap = new Dictionary<string, string>();

        if (useLibCOM)
        {
            //get all available SQL Servers     
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();

            ArrayList servs = new ArrayList();
            for (int i = 0; i < sqlServers.Count; i++)
            {
                object srv = sqlServers.Item(i + 1);

                if (srv != null)
                {
                    sqlServersMap.Add(srv.ToString(), srv.ToString());
                }
            }
        }
        else
        {
            System.Data.Sql.SqlDataSourceEnumerator enumSQL = System.Data.Sql.SqlDataSourceEnumerator.Instance;

            System.Data.DataTable table = enumSQL.GetDataSources();

            foreach (System.Data.DataRow row in table.Rows)
            {

                foreach (System.Data.DataColumn col in table.Columns)
                {
                    sqlServersMap.Add((string)row[col], (string)row[col]);
                }
            }
        }
    }

    private bool pingSqlServer(string server)
    {
        bool success = false;

        TcpClient client = new TcpClient();

       try 
        {         
            client.Connect(server, 1433);

            success = true;
        }
        catch (Exception)
        {

            //throw;
        }

        return success;
    }

    public StringBuilder buildWmiServerList(string path, string sqlServer, string sqlServerServiceName, StringBuilder sb, out bool reportNeeded)
    {
        reportNeeded = false;

        try
        {
            ManagementScope ms = new ManagementScope(path);

            ms.Connect();

            ManagementObjectSearcher searcher = new ManagementObjectSearcher("SELECT * FROM Win32_Service WHERE Started = TRUE AND DisplayName='" + sqlServerServiceName + "'");
            searcher.Scope = ms;

            if (searcher != null && searcher.Get() != null)
            {
                foreach (ManagementObject service in searcher.Get())
                {
                    sb.Append(sqlServer + " SQL Server service - RUNNING\n");
                }
            }
        }
        catch (Exception e)
        {
            sb.Append(sqlServer + " SQL Server service - UNVERIFIABLE\n");
            reportNeeded = true;
        }

        return sb;
    }

Any ideas in how to resolve/detect SQL servers that form a SQL cluster?

Answer 1

i don't know about DMO which is deprecated anyway but in SMO you can do Server.IsClustered.

you may also want to look at this: http://www.sqldbatips.com/showarticle.asp?ID=45

Answer 2

Using SMO yields the same results as

System.Data.Sql.SqlDataSourceEnumerator enumSQL = System.Data.Sql.SqlDataSourceEnumerator.Instance;

            System.Data.DataTable table = enumSQL.GetDataSources();

            foreach (System.Data.DataRow row in table.Rows)
            {

                foreach (System.Data.DataColumn col in table.Columns)
                {
                    sqlServersMap.Add((string)row[col], (string)row[col]);
                }
            }

My 2 SQL clusters are still not found, and using the cluster name still results in the SQL server not being detected.

No joy yet.

Answer 3

Why not try SQL ping? There is source code here so you can see how they do it.

Anyway, some thoughts:

Are you trying the the physical server, the cluster name, or the virtual server name(s) eg phys1, phys2, vclus, vserv1, vserv2 (assuming active/active)?

Are you using tcp or named pipes from your client? Have you tried tcp:vserv1? Named pipes can be funny on clusters IIRC if disabled then enabled, for example. The protocols used are listed in the SQL logs, as well as the tcp port that is used (named instance = random)

Can you create a system DSN on your client? From this, you can work out what port and protocol is used under HKLM\SW\Microsoft\MSSQLServer