How to get a list of all the MS SQL Server instances on the local machine?

Question

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.

Suggestions I've seen:

• 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.

• 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.

  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.

• 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.

• 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?

Answer 1

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

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

Other possibilities to explore include enumerating through the RegisteredServers namespace.