7

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?

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'
  • +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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.