Every DBA managing multiple SQL Servers with multiple instances will know the issues with developers, project managers and others that don’t know the importance of the instance name when they request you to take some action.

So you can start some e-mail ping pong to get the instance name, open the server documentation or RDP to the server to find the installed instances. But in the time you would need to do this you can write yourself a PowerShell script to get the remote service information.

First step getting remote services information:

Get-Service -ComputerName <SQLServerHostName>

This gives you all the information you need:

But since I only need SQL Server information I will filter out all the other services using the Where-Object and the like operator:

Get-Service -ComputerName <SQLServerHostName> -name "MSSQL*"

As you can see we can now see the installed instances of SQL Server on our remote server. Only if the Instance name is too long you will see … at some point:

Since we only need the Name and ass a surplus the Status of our SQL Server Services we can format the output:

Get-Service -ComputerName <SQLServerHostName> -name "MSSQL*"|Format-Table -Property Name, Status

And now we have only the information we need:

Like in my previous post I will put the command in a PS1 script using a parameter and a Throw to be able to reuse the script:

param(
	[string] $compname = $(Throw "Provide a SQL Server name as first parameter")
)
Get-Service -ComputerName $compname -name "MSSQL*"|Format-Table -Property Name, Status, DisplayName

Executing the script will look like this:

Et voila, another point I can take of my list of items I can’t do on a Server Core…