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?

Reputation: 746

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

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?

Upvotes: 31

Answers (6)

Dmitry Bykov

Reputation: 11

It's may be, that SQL Server fully local and it have not remote access. By example, SQL Server Express is local. So, sqlcmd -L cannot find the local server because this server is not responding to the broadband access request.

I use this command

sc queryex | grep "MSSQL" It return, for example

Service_Name: MSSQL$SQLEXPRESS2 Service_Name: MSSQL$SQLEXPRESS It get list of all system services and find services with name constraints "MSSQL". It return list of system services for every MS SQL instances. But it return only local instances, on current machine, for remote server, on remote machine, use sqlcmd -L.

Upvotes: 0

eckes

Reputation: 10433

The SQL Browser service (if active) speaks SSRP/MS-SQLR on UDP Port 1434.

It is the component which is used for remote discovery of instances (and also determining their port). It helps also discovery in a network.

It is however disabled by default (good thing for surface reduction).

Upvotes: 3

Chas Long

Reputation: 61

From a command prompt (cmd.exe):

sc query|findstr "DISPLAY_NAME"|findstr /C:"SQL Server (" > myfile.txt

FOR /F " tokens=2 delims=()" %i in (myfile.txt) do @echo %computername%\%i

Upvotes: 6

Bill Greer

Reputation: 3166

I know this is an older post, but I did come to this post today looking for a way to find installed instances of SQL Server. Although the two answers given are helpful, I think they only pertain to installed and running instances of SQL Server. If SQL Server Configuration Manager is installed, all installed instances should be listed under the SQL Server Services node. Here you might find an instance that is installed, but not running. You can right click the instance and start it. I am sure there are other ways to do this; it is just a way that came in handy for me today and I thought I would share it.

Upvotes: 3

Jeswin

Reputation: 116

This might be helpful , i have tried this in Sql Server 2008 :

select * from sys.servers

Upvotes: 2

8kb

Reputation: 11406

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.

Upvotes: 14

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

Answers (6)

Related Questions