C# 以编程方式查找sql​​ server的所有实例

Find all instances of sql server programmatically

提问by CuccoChaser

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'

我正在努力收集我们网络上的所有 SQL-Server 实例。它应该能够检测到正在运行的 SQl-Server 版本。我们运行的不同版本在“ SQL Server 2000”和“ 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

• 应用程序池
• iis 安装
• 服务器上的所有数据库
• 还有一些像这样的东西

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.

通过 WMI 查询，以上所有内容都可以正常工作。但是我无法通过 WMI 或 Visual Studio 2010 中的命名空间获得正确的 SQl-Server 实例。

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

我根据在 stackoverflow 和其他网站上找到的其他解决方案尝试过的一些方法：

1. WMI, using varying namespaces such as root\\Microsoft\\SqlServer\\ComputerManagement10with the ServerSettingsclass. But this only gives SQLSERVER and SQLEXPRESS without a version number. Making it kind of useless.
2. I also tried root\\CIMV2 Win32_Productincluding 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 SqlDataSourceEnumeratorfor 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.
   

1. WMI，使用不同的命名空间作为例如root\\Microsoft\\SqlServer\\ComputerManagement10与ServerSettings类。但这仅提供没有版本号的 SQLSERVER 和 SQLEXPRESS。让它变得毫无用处。
2. 我还尝试root\\CIMV2 Win32_Product包含一个 where like sql 子句。但这返回的数据比我正在寻找的要多得多。此外，查询本身非常慢。
3. 后来我在 Visual Studio 中找到了一些类SqlDataSourceEnumerator，例如。尽管这仅在某个服务正在运行并且某些端口打开时才有效。由于可能存在安全问题和可能不正确的数据，我们最好不要这样做
4. 我也看到有些人提到了一些其他的命名空间（一旦我再次找到它们就会写在这里），但是 msdn 表示我们将在不久的将来删除这些命名空间。
   

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

总结一下：我需要检索SQL-Server版本在 2000 和 2008 R2 之间变化的域上的所有已安装实例。

采纳答案by CuccoChaser

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 classlocated in system.ServiceProcess
• After all the SQLBrowser have been started I use the SqlDataSourceEnumeratorto enumerate through all the instances.

• 首先，我扫描域中的机器。
• 检查 SQLBrowser 服务是否正在运行，如果没有，启动它！这是由ServiceController class位于system.ServiceProcess
• 在所有 SQLBrowser 启动后，我使用SqlDataSourceEnumerator枚举所有实例。

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.

这可能不是最好的解决方案，有些人可能会觉得它有点脏。但就目前而言，这是我能得到的最好的解决方法。
希望这可以帮助将来遇到同样问题的任何人。

回答by Baddack

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

我使用了这里的代码：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.

效果很好。唯一要提到的是，这是针对 SQL 2005 - SQL 2008 的。您必须为 SQL 2012 检查 ComputerManagement11。