From Server\Instance how do I determine service dependency?

Question

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

What's wrong with this answer then: http://stackoverflow.com/questions/2381055/check-if-sql-server-any-version-is-installed — OMG Ponies, Mar 05 '10 at 16:49
Good answer, but that gives me just the machine names and from that I cannot determine whether the dependency should be on MSSQLSERVER or MSSQL$SQLEXPRESS? — JD., Mar 05 '10 at 17:02

score 4 · Accepted Answer · answered Mar 05 '10 at 19:45

4

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');

answered Mar 05 '10 at 19:45

Remus Rusanu

288,378
40
442
569

@Remus: Thank you so much. That was a real eye opener. It has now dawned on me is that if the user chooses a remote host, would I have to create a remote dependency? If I did (if possible at all), would this be a bad thing should the server be swapped out in the future to another machine or if it is a local server and then it is upgraded (sql 2005 to 2008). Now, do I really need a dependency on SQL server at all given that our application is a custom web server? If a page is not shown, then the user would have to check if SQL server is running anyway. Please let me know what your thoughts are. – JD. Mar 06 '10 at 08:31
The service dependency order is important for purposes of the Service Control manager to track the order of service startup and shutdown. Between two local services, it makes sense to have this dependency declared. Between a local and a remote service, absolutely not. Traditional IIS/ASP web application do no declare such a dependency, primarily because of the *volatility* of this link between web aps and the database: the location of the SQL server can move as the result of a simple web.config change done in Notepad and would leave fake dependencies declared. – Remus Rusanu Mar 06 '10 at 16:14
Thank you so much. I will remove the dependency we have in this case. – JD. Mar 08 '10 at 09:22

score 2 · Answer 2 · edited Apr 10 '14 at 13:01

You can use the ServiceController.GetServices method to get a list of all of the services on the current machine. You can then use the ServiceName property of each one to determine whether or not each service you are looking for is installed. For instance:

ServiceController[] services = ServiceController.GetServices();

if (services.Any(x => x.ServiceName == "MSSQL$SQLEXPRESS"))
{
     this.serviceInstaller1.ServicesDependedOn = new string[] { "MSSQL$SQLEXPRESS" };      
}
else if (services.Any(x => x.ServiceName == "MSSQLSERVER"))
{
     this.serviceInstaller1.ServicesDependedOn = new string[] { "MSSQLSERVER" }; 
}

score 0 · Answer 3 · answered Mar 05 '10 at 16:50

0

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();

answered Mar 05 '10 at 16:50

Spencer Ruport

34,865
12
85
147

@Spencer: Yes say the user decides does not want sql server 2005, then the sql service will not be removed unless dependant apps are removed first. – JD. Mar 05 '10 at 16:59
Ah good point. I'll leave my answer up just in case anyone else has the same question/idea. – Spencer Ruport Mar 05 '10 at 22:28

From Server\Instance how do I determine service dependency?

3 Answers3