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?
7
13
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'
answered Jan 3 '14 at 14:41
-
+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. – Shooter McGavin Nov 1 '16 at 15:37
6
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.
answered Jan 3 '14 at 18:58
-
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"; – Vas Gr Nov 19 '18 at 20:35
