From Server\Instance how do I determine service dependency?

Question

Hi,

I have an installer where the user enters the data source in the connection string (i.e. DataSource="machinename\instance").

Using C# given the machinename\instance string, I need know if the service name is MSSQLSERVER or MSSQL$SQLEXPRESS

On one particular machine we have SQL Server 2005 (full) and SQL Express 2008 installed.

The reason I need to know this is in our wix installer, the main application to be installed has a dependency on SQL server, so we need to have the correct dependency for it to be installed. The dependency could be on the service MSSQLSERVER or MSSQL$SQLEXPRESS and I can have both these services installed on a machine.

JD

Answer 1

Do you see a significant harm in depending on both?

You can get a list of running services using:

using System.ServiceProcess;
// ...
ServiceController[] sc = ServiceController.GetServices();

Answer 2

SQL Server services are named as either MSSQLSERVER (default instance) or MSSQL$INSTANCENAME (named instances). You can determine if it's a named instance either from the connection string (if is in the form host\instance the is a named instance, if is host then is default instance) but the truth is that this is not reliable, because:

• the connection string may use a SQL client alias
• the connection string may connect to a named instance listening on the default port
• the connection string may connect to an explicit port and not specify the instance name

So a more reliable way is to connect and ask for the instance name:

SELECT SERVERPROPERTY('InstanceName');

If the return is NULL, the service name will be MSSQLSERVER, otherwise is MSSQL$... You can even put this straight into the query:

SELECT COALESCE('MSSQL$'+cast(SERVERPROPERTY('InstanceName') as sysname), 'MSSQLSERVER');