Enumerate SQL Server Instances and TCP Settings

Enumerate SQL Server Instances and TCP Settings

These scripts require PowerShell. If you have multiple instances of SQL Server on the machine, they will enumerate them all.

Determine What SQL Server Services are Running

Get-WMIObject -Class 'Win32_Service' | Where-Object {($_.Name -match 'MSSQL(?:\$.+|SERVER)')} | Format-List -Property Name, State, ProcessID;
-----------------------------------------
Name      : MSSQL$MICROSOFT##WID
State     : Stopped
ProcessID : 0

Name      : MSSQLSERVER
State     : Running
ProcessID : 7696

Name      : MSSQLServerOLAPService
State     : Stopped
ProcessID : 0

Determine the SQL Server Protocol

[String]$NameSpaceName = (Get-WMIObject -NameSpace 'root\Microsoft\SqlServer' -Class '__NameSpace' | Where-Object {$_.Name -match 'ComputerManagement'} | Sort-Object -Property Name -Descending | Select-Object -First 1).Name; 

If ($False -eq [String]::IsNullOrEmpty($NameSpaceName)) { 

    Get-WMIObject -NameSpace "root\Microsoft\SqlServer\$($NameSpaceName)" -Query "SELECT * FROM ServerNetworkProtocol WHERE ProtocolName = 'Tcp'" | Format-List -Property PSComputerName, InstanceName, ProtocolDisplayName, Enabled; 

} Else { 

    'The SQL Server WMI provider does not appear to be installed on this machine.' | Write-Host -ForegroundColor 'Red'; 

}; 
----------------------------------------
PSComputerName      : SQLPROD01
InstanceName        : MSSQLSERVER
ProtocolDisplayName : TCP/IP
Enabled             : True