Any relation between SQL Server Service Name and Instance Name?

Question

My SQL Server's instance name is SQLEXPRESS and SQL Server Service Name looks like MSSQL$SQLEXPRESS. Is there any relation between instance name and service name? I'm trying to check SQL Server Service Status by name and I wonder can SQL Server Service name be different on another computers?

Answer 1

Yes, the service name is always MSSQL$<Instance Name> for a named instance and MSSQLSERVER for a default instance. I don't believe either can be altered or overridden, nor can I think of any reason why you'd want to.

You can enumerate the installed instances on a server via the registry, using Powershell for example:

Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'

• Shooter McGavin: +1 for the answer regarding instance names. That being said, I do prefer not to use the registry directly in powershell unless there isn't another way. For that part, I prefer the approach taken by @ThomasStringer. Using WMI is definitely a more reliable approach.

Answer 2

Mark showed you a way to get this information directly from the registry, but another way to do this would be through WMI, consumed by PowerShell:

Get-WmiObject -ComputerName "YourDestinationServer" -Namespace "root\microsoft\sqlserver\computermanagement11" -Class "SqlService" |
 Where-Object {$_.SQLServiceType -eq 1} |
 Select-Object ServiceName, DisplayName, 
 @{Name = "StateDesc"; Expression = {
 switch ($_.State) {
 1 { "Stopped" }
 2 { "Start Pending" }
 3 { "Stop Pending" }
 4 { "Running" }
 5 { "Continue Pending" }
 6 { "Pause Pending" }
 7 { "Paused" }
 }
 }}

The above command will give you the state of the SQL Server engine service(s) on a particular machine.

• Vas Gr: I can then assume that the following logic is correct then for named and unnamed instances as service name? svcname = instancename != string.Empty ? string.Format("MSSQL${0}", instancename) : "MSSQLSERVER"