This article is about a PowerShell script to find sql instances on remote server. Not to mention the remote server can be standalone or Cluster.
When I got the requirement to develop the script, I thought it is not a big deal. Generally speaking, PowerShell will have some direct commands to do so and my script is few searches away.
But when I put my feet in, I got to know it was not as easy as I thought.
Especially the real challenge comes in when you have to list the SQL Instances for a SQL Cluster. The script should be intelligent enough to find out if the server is cluster or standalone and accordingly list the SQL Instances.

Note:The script should run from a server where FailOver Clustering Feature is installed.

PowerShell script to find sql instance name on remote server
Param(
    [Parameter(Mandatory=$true)]
     $ComputerName
)

if ((Get-WMIObject -Class MSCluster_ResourceGroup -ComputerName $ComputerName -Namespace root\mscluster -ErrorAction SilentlyContinue) -ne $null)
 {  
    Import-Module FailoverClusters
    get-clusterresource -Cluster $ComputerName  -ErrorAction SilentlyContinue|
    where-object {$_.ResourceType -like “SQL Server”} | 
    get-clusterparameter VirtualServerName,InstanceName | group-object ClusterObject | 
    select-object @{Name = “SQLInstance”;Expression = {[string]::join(“\”,($_.Group | select-object -expandproperty Value))}} 
    } 
else {
$SQLInstances = Invoke-Command -ComputerName $ComputerName {
(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}
    foreach ($sql in $SQLInstances) {
       [PSCustomObject]@{
           ServerName = $sql.PSComputerName
           InstanceName = $sql
       }
   }  
   } 

Not to mention, you can execute it from PowerShell ISE or from PowerShell Command prompt. Let’s see both examples.

Execute The Script Through Windows PowerShell ISE:

PowerShell Script to find SQL Instances on remote serverOnce you execute the script you’ll be prompted to provide the Server Name as you can see in the last line in above screen shot.
Sample OutPut:PowerShell Script to find SQL Instances on remote server

Execute the Script from Windows PowerShell Command Prompt:PowerShell Script to find SQL Instances on remote server

Only difference is to provide the server name while running the script as shown in the screen shot. Also the SQL Instance “PC01” is a cluster. Hence you don’t have to bother if the server is standalone or cluster. Hope this helps.
Let me know if you have any questions or comments.