Find all instances of sql server programmatically

Question

I know that there's quite a few subjects similar to this one, but none of them provided the correct answer I am looking for.

I am struggling to gather all the instances of SQL-Server on our network. It should be able to detect what version of SQl-Server is running. The different versions we have running vary between 'SQL Server 2000' and 'SQL Server 2008 R2'

To give you a little background information, currently I am developing on our local network, but later on it will be run on our servers to gather information.

Some of the information gathered is:

• application pools
• iis installations
• all databases on a server
• and some more stuff like this

This all above is working without a problem through WMI queries. But I can not get the correct instances of SQl-Server by WMI or namespaces within Visual Studio 2010.

Some of the things I've tried according to other solutions found around stackoverflow and other sites:

1. WMI, using varying namespaces such as root\\Microsoft\\SqlServer\\ComputerManagement10 with the ServerSettings class. But this only gives SQLSERVER and SQLEXPRESS without a version number. Making it kind of useless.
2. I also tried root\\CIMV2 Win32_Product including a where like sql clause. But this returns much more data than just what I am looking for. In addition the query itself is very slow.
3. Later on I found some Classes within Visual Studio such as SqlDataSourceEnumerator for example. Though this only works if a certain service is running and some ports are open. Which we preferably not do due to possible security issues and possible incorrect data
4. I also saw some people referring to some other namespaces (will write them here once I find them again), but msdn stated that these namespaces we're going to be removed in the near future.
   

To summarize: I need to retrieve all installed instances of SQL-Server on a domain with versions varying between 2000 and 2008 R2.

Solution 1

Ok so I resolved the issue. What I have done is a few things:

• First I scan the domain for machines.
• Check if the SQLBrowser service is running, if not, start it! which is done by the ServiceController class located in system.ServiceProcess
• After all the SQLBrowser have been started I use the SqlDataSourceEnumerator to enumerate through all the instances.

For those interested in the code:
Note: you need network admin rights to start it on the remote machines.

public void StartSqlBrowserService(List<String> activeMachines)
{
    ServiceController myService = new ServiceController();
    myService.ServiceName = "SQLBrowser";

    foreach (var machine in activeMachines)
    {
        try
        {
            myService.MachineName = machine;
            string svcStatus = myService.Status.ToString();
            switch (svcStatus)
            {
                case "ContinuePending":
                    Console.WriteLine("Service is attempting to continue.");
                    break;

                case "Paused":
                    Console.WriteLine("Service is paused.");
                    Console.WriteLine("Attempting to continue the service.");
                    myService.Continue();
                    break;

                case "PausePending":
                    Console.WriteLine("Service is pausing.");
                    Thread.Sleep(5000);
                    try
                    {
                        Console.WriteLine("Attempting to continue the service.");
                        myService.Start();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                    break;

                case "Running":
                    Console.WriteLine("Service is already running.");
                    break;

                case "StartPending":
                    Console.WriteLine("Service is starting.");
                    break;

                case "Stopped":
                    Console.WriteLine("Service is stopped.");
                    Console.WriteLine("Attempting to start service.");
                    myService.Start();
                    break;

                case "StopPending":
                    Console.WriteLine("Service is stopping.");
                    Thread.Sleep(5000);
                    try
                    {
                        Console.WriteLine("Attempting to restart service.");
                        myService.Start();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                    break;
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
    }
}

And then this is what I use to retrieve the instances.

public static void SqlTestInfo()
{
    SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
    DataTable table = instance.GetDataSources();
    DisplayData(table);
}

private static void DisplayData(DataTable table)
{
    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn dataColumn in table.Columns)
        {
            Console.WriteLine("{0} = {1}", dataColumn.ColumnName, row[dataColumn]);
        }
        Console.WriteLine();
    }
}

It may not be the best solution, some may find it somewhat dirty. But for now it's the best workaround I could get.
Hope this helps any people in the future having the same problem.

Solution 2

I used the code from here: http://msdn.microsoft.com/en-us/library/dd981032.aspx

Works great. Only thing to mention is this is for SQL 2005 - SQL 2008. You'll have to check for ComputerManagement11 for SQL 2012.