Vfrdtyky

I don't know the exact windows/SQL server terminology so I might have said things a little different than actual technical terms. Please forgive.

I am connected to a host using windows credentials which I know is a SQL-Server.
Is there a way to find if that host is part of a cluster or just stand alone server using power shell/bash?

For example there are two nodes NODE_1 & NODE_2 that form a cluster and has 4 hosts named host_1, host_2, host_3, host_4. All these hosts are SQL - Servers. When I connect to any of these hosts using windows credentials and run hostname on bash, I get either (NODE_1 & NODE_2).

I tried following power-shell (on host_1 -> NODE_1) but it always says host is not clustered:

$server = hostname; # Tried commenting this line. $server still gets 'NODE_1'

$s=Get-WMIObject -query "select * from Win32_ComputerSystem" -ComputerName $server | select name

if ($s.Name -ne $server) { 

#if ($s -ne $server) {

Write-Output "$server is clustered"

} else {

Write-Output "$server is not clustered"

}

I think this because I am assigning node name 'NODE_1' to $server instead of 'host_1'. But I am unable to find a way to get 'host_1' using power-shell.

What I have tried: Replacing $server with 'host_1' manually gets correct result. But as this script will be part of a larger program that'll run on hundreds of SQL servers with different versions, I can't manually provide actual host-name as string. So I am trying to find a way to do this using power-shell.

I also tried these options to get 'host_1' but they all return 'NODE_1'.

PS SQLSERVER:> $Env:Computername

NODE_1

PS SQLSERVER:> hostname.exe

NODE_1

PS SQLSERVER:> [System.Net.Dns]::GetHostName()

NODE_1

PS SQLSERVER:> $env:COMPUTERNAME

NODE_1

May be host_1, host_2, host_3, host_4 are called listeners in SQL terms?

Question: Is there a way to get 'host_1' assigned to $server instead of 'NODE_1' using power-shell?

If there is another way to do this after making connection using windows/SQL credentials then I would definitely try it out.

Only constraint I have is I would not know if the host is clustered (and so has multiple DB instances) or standalone. So using SQL credentials might fail if I try making connection to an instance but my connection string doesn't have "$server=host-nameinstance"

Try this, $HostName can be the cluster name or the name of one of the nodes. It can be hardcoded instead of the variable used in this example.

$services = Get-WmiObject -class Win32_SystemServices -computername $HostName

foreach ($service in $services.partcomponent) { if ($service -match 'ClusSvc') { 'Clustered' }}