如何获取本地计算机上所有 MS SQL Server 实例的列表? [英] How to get a list of all the MS SQL Server instances on the local machine?

问题描述

这似乎是一个相当普遍的问题，但我所看到的答案都没有特别令人满意.我想获取本地计算机上安装的 MS SQL Server 实例的名称列表，无论它们是否已启动.出于本次讨论的目的，我只需要查找 SQL 2005 和更新版本的实例即可；我可以使用传统"方法(即查看注册表)处理 2000 年及更早版本.我需要的是它不依赖于 SQL Server Browser 服务(现在默认情况下它是禁用的)，并且即使应用程序是 32 位的，也会返回 64 位实例.

This seems to be a fairly common question, but none of the answers I've seen have been particularly satisfactory. I want to get a list of the names of the MS SQL Server instances installed on the local machine, regardless of whether they're started or not. For the purposes of this discussion, I'm OK with just finding instances of SQL 2005 and newer; I can handle 2000 and earlier using "legacy" methods (i.e., look in the registry). What I do require is that it not be dependent on the SQL Server Browser service (it's disabled by default nowadays), and that 64-bit instances are returned even when the app is 32-bit.

我看到的建议:

• 挖掘注册表:据说 不是推荐，因为注册表项没有记录；MS 将来可能会更改它们.更重要的是，据我所知，MSSQL 的 64 位实例位于 64 位 HKLM\SOFTWARE\Microsoft\Microsoft SQL Server InstalledInstances 中，而 32 位实例位于 32 位实例中，因此 32-bit 应用程序不会看到任何 64 位实例.

• Dig through the registry: Supposedly Not recommended due to the registry entries being undocumented; MS may change them in the future. More importantly, as far as I can tell, 64-bit instances of MSSQL go in the 64-bit HKLM\SOFTWARE\Microsoft\Microsoft SQL Server InstalledInstances, and 32-bit instances go in the 32-bit one, so a 32-bit app won't see any 64-bit instances.

使用 SQL WMI 提供程序进行配置上一篇博文中提到的管理.这似乎是最接近的，但尽管作者告诫避免使用注册表，因为它可能会发生变化，但事实证明 WMI 命名空间在 SQL 2005 和 2008 之间发生了变化:在 2005 年，它是 root\Microsoft\SqlServer\ComputerManagement，但在 2008 年它是 root\Microsoft\SqlServer\ComputerManagement10.未来会不会再次改变?也就是说，如果我必须为未来版本的 SQL 更新我的应用，这可能不是什么大问题.

Use the SQL WMI Provider for Configuration Management mentioned in the previous blog post. This seems to be the closest, but despite the author's admonition to avoid using the registry because it might change, it turns out the WMI namespace changed between SQL 2005 and 2008: in 2005, it's root\Microsoft\SqlServer\ComputerManagement, but in 2008 it's root\Microsoft\SqlServer\ComputerManagement10. Will it change again in the future? That said, it's probably not a huge issue if I have to update my app for a future version of SQL.

我在 WMI 方法中遇到的问题是 SqlService 类返回服务名称列表，而我想要实例名称.例如，我只想要 INSTANCE 而不是 MSSQL$INSTANCE.剥离MSSQL$"是微不足道的，就像处理默认实例的特殊情况一样，但它可靠吗?AFAIK，技术上没有理由不能重命名服务，同时保持实例名称相同.也就是说，除非有人有更好的方法，否则我想我会这样做(获取服务名称并去掉 MSSQL$).ServerSettings 类返回实例名称，但它没有看到我在我的机器上安装的 SQL 2008 R2 Express 的 64 位实例.

The problem I have with the WMI method is that the SqlService class returns a list of the service names, whereas I want the instance names. E.g., instead of MSSQL$INSTANCE, I just want INSTANCE. Stripping off the "MSSQL$" is trivial, as is handling the special case of the default instance, but is it reliable? AFAIK, there's technically no reason why the service couldn't be renamed, while keeping the instance name the same. That said, unless someone has a better method, I think I'll go with that (get the service names and strip off the MSSQL$). The ServerSettings class returns the instance name, but it doesn't see a 64-bit instance of SQL 2008 R2 Express that I have installed on my machine.

使用 SmoApplication.EnumAvailableSqlServers(true):这似乎取决于 SQL Server Browser 服务.如果浏览器服务已启动，则效果很好，但如果未启动，我只会得到一行，其中包含计算机名称作为服务器名称和一个空白实例名称.

Use SmoApplication.EnumAvailableSqlServers(true): this seems to depend on the SQL Server Browser service. It works great if the Browser service is started, but if it's not, I just get a single row with the computer name as the server name and a blank instance name.

使用 System.Data.Sql.SqlDataSourceEnumerator.GetDataSources():同样的问题，它依赖于 SQL Server 浏览器.

Use System.Data.Sql.SqlDataSourceEnumerator.GetDataSources(): same problem that it depends on the SQL Server Browser.

那么，还有其他更好的方法吗?

So, are there some other methods that might work better?

推荐答案

我可以使用 Powershell 在我的笔记本电脑上看到 2005 和 2008 SQL Server 实例:

I am able to see both 2005 and 2008 SQL Server instances on my laptop using Powershell:

Get-Service | Where-Object {$_.Name -like 'MSSQL$*'}

其他可以探索的可能性包括通过 RegisteredServers 命名空间.

Other possibilities to explore include enumerating through the RegisteredServers namespace.

这篇关于如何获取本地计算机上所有 MS SQL Server 实例的列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！