CODEWITHSUNDEEP
sql-serverfinddefaultinstance
MWinstead
MWinstead

Reputation: 1305

How to find a SQL Server 2008 instance, including default instance

I am creating a Windows form application that uses SQL Server 2008. In the setup project that I have I check for and install SQL Server 2008 if necessary on the target machine. What I need is a way to get a SQL Server 2008 - and ONLY 2008 - instance name.

Right now I am using the SmoApplication.EnumAvailableSqlServers function and checking the versions returned, but what I have found is that it does not display the default instance. This is problematic for me because if this program is being run after my installer setup SQL Server 2008 on the target machine, there are no named instances available - only the default instance.

I don't want to just blindly check and see if the traditional MSSQLSERVER default instance works for two reasons:

  1. It could be the default instance left over from a SQL Server 2008 install, which won't be able to access the DB used by my application. Skipping it and then checking for other named instances would most likely return me nothing.

  2. It could also be the default instance from a newer version of SQL Server 2012. While this instance would be able to access my DB, my experience is that it then "upgrades" the DB to the new SQL Server version, and earlier versions won't be able to access it. Since my application is intended to work with SQL Server 2008, this presents me with some problems.

SO I need to use any SQL Server 2008 named instance, or a default instance IF it is 2008.

Is there any way to do this?

Upvotes: 1

Views: 1413

Answers (1)

MAXE
MAXE

Reputation: 5122

Use should use the class SqlDataSourceEnumerator of the System.Data.Sql namespace (assembly System.Data.dll), this way:

SqlDataSourceEnumerator sdsEnumerator = SqlDataSourceEnumerator.Instance;

DataTable sqlServerInstances = sdsEnumerator.GetDataSources();

These class will not require to have Sql Server installed, but can retrieve informations about any Sql Server instance. Remind this:

"

Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call. If you plan to use this function to let users select a server from a list, make sure that you always also supply an option to type in a name that is not in the list, in case the server enumeration does not return all the available servers. In addition, this method may take a significant amount of time to execute, so be careful about calling it when performance is critical.

"

The method GetDataSource is what you need: here the documentation, returns a DataTable with all the informations you need:

ServerName Name of the server.

InstanceName Name of the server instance. Blank if the server is running as the default instance.

IsClustered Indicates whether the server is part of a cluster.

Version Version of the server (8.00.x for SQL Server 2000, and 9.00.x for SQL Server 2005, 10.00.x for SQL Server 2008, 10.50.x for SQL Server 2008 R2 and 11.00.x for SQL Server 2012).

You can find the full reference of the class SqlDataSourceEnumeratorhere.

Upvotes: 2

Related Questions