Powershell Script to Get SQL Instances Information Remotely

Retrieves SQL server information from a local or remote servers.
Also Pulls all instances from a SQL server and detects if in a cluster or not.
Local or remote systems to query for SQL information.
If specified, try to pull and correlate WMI information for SQL in matching up the service info to registry info.

Follow below process to execute the PS Script  :

Open Powershell ISE with Run as Administrator and copy the below script in powershell ISE and hit execute . After executing copy below command and paste it in powershell by changing server names :

Example :

Get-SQLInstance -Computername DC1   – for Single server information

Get-SQLInstance -Computername Server1, Server2 -WMI –  For multiple servers Information

PS script below :

Function Get-SQLInstance {
<#
. SYNOPSIS
Gets SQL server data from a local or distant server.

.DESCRIPTION
Gets SQL server data from a local or distant server. Pulls all instances from a SQL server and determines whether they are in a cluster.

.PARAMETER ComputerName
To query for SQL information on local or distant systems.


.FUNCTIONALITY
Computers

.EXAMPLE
Get-SQLInstance -Computername Node1

Caption : SQL Server 2008

Description
-----------
Retrieves the SQL information from Node1

.EXAMPLE
#Get SQL instances on servers 1 and 2 from WMI
Get-SQLInstance -Computername Node1, Node2 -WMI

#>
[cmdletbinding()]
Param (
[parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
[Alias('__Server','DNSHostName','IPAddress')]
[string[]]$ComputerName = $env:COMPUTERNAME,

[switch]$WMI
)
Begin {
$baseKeys = "SOFTWARE\\Microsoft\\Microsoft SQL Server",
"SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server"
}
Process {
ForEach ($Computer in $Computername) {

$Computer = $computer -replace '(.*?)\..+','$1'
Write-Verbose ("Checking {0}" -f $Computer)

#Boe's code is this. He outputs it directly, and I'm assigning it to allInstances later to correlate with WMI.
$allInstances = foreach($baseKey in $baseKeys){
Try {

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Computer)
$regKey= $reg.OpenSubKey($baseKey)

If ($regKey.GetSubKeyNames() -contains "Instance Names") {
$regKey= $reg.OpenSubKey("$baseKey\\Instance Names\\SQL" )
$instances = @($regkey.GetValueNames())
}
ElseIf ($regKey.GetValueNames() -contains 'InstalledInstances') {
$isCluster = $False
$instances = $regKey.GetValue('InstalledInstances')
}
ElseIf ($regKey.GetValueNames() -contains 'InstalledInstances') {
$isCluster = $False
$instances = $regKey.GetValue('InstalledInstances')
}
Else {
Continue
}

If ($instances.count -gt 0) {
ForEach ($instance in $instances) {
$nodes = New-Object System.Collections.Arraylist
$clusterName = $Null
$isCluster = $False
$instanceValue = $regKey.GetValue($instance)
$instanceReg = $reg.OpenSubKey("$baseKey\\$instanceValue")
If ($instanceReg.GetSubKeyNames() -contains "Cluster") {
$isCluster = $True
$instanceRegCluster = $instanceReg.OpenSubKey('Cluster')
$clusterName = $instanceRegCluster.GetValue('ClusterName')
$clusterReg = $reg.OpenSubKey("Cluster\\Nodes")
$clusterReg.GetSubKeyNames() | ForEach {
$null = $nodes.Add($clusterReg.OpenSubKey($_).GetValue('NodeName'))
}
}
$instanceRegSetup = $instanceReg.OpenSubKey("Setup")
Try {
$edition = $instanceRegSetup.GetValue('Edition')
} Catch {
$edition = $Null
}
Try {
$SQLBinRoot = $instanceRegSetup.GetValue('SQLBinRoot')
} Catch {
$SQLBinRoot = $Null
}
Try {
$ErrorActionPreference = 'Stop'
#Get from filename to determine version
$servicesReg = $reg.OpenSubKey("SYSTEM\\CurrentControlSet\\Services")
$serviceKey = $servicesReg.GetSubKeyNames() | Where {
$_ -match "$instance"
} | Select -First 1
$service = $servicesReg.OpenSubKey($serviceKey).GetValue('ImagePath')
$file = $service -replace '^.*(\w:\\.*\\sqlservr.exe).*','$1'
$version = (Get-Item ("\\$Computer\$($file -replace ":","$")")).VersionInfo.ProductVersion
} Catch {
#Use potentially less accurate version from registry
$Version = $instanceRegSetup.GetValue('Version')
} Finally {
$ErrorActionPreference = 'Continue'
}
New-Object PSObject -Property @{
Computername = $Computer
SQLInstance = $instance
SQLBinRoot = $SQLBinRoot
Edition = $edition
Version = $version
Caption = {Switch -Regex ($version) {
"^12" {'SQL Server 2014';Break}
"^11" {'SQL Server 2012';Break}
"^10\.5" {'SQL Server 2008 R2';Break}
"^10" {'SQL Server 2008';Break}
"^9" {'SQL Server 2005';Break}
"^8" {'SQL Server 2000';Break}
"^7" {'SQL Server 7.0';Break}
Default {'Unknown'}
}}.InvokeReturnAsIs()
isCluster = $isCluster
isClusterNode = ($nodes -contains $Computer)
ClusterName = $clusterName
ClusterNodes = ($nodes -ne $Computer)
FullName = {
If ($Instance -eq 'MSSQLSERVER') {
$Computer
} Else {
"$($Computer)\$($instance)"
}
}.InvokeReturnAsIs()
} | Select Computername, SQLInstance, SQLBinRoot, Edition, Version, Caption, isCluster, isClusterNode, ClusterName, ClusterNodes, FullName
}
}
} Catch {
Write-Warning ("{0}: {1}" -f $Computer,$_.Exception.Message)
}
}

#Get wmi information and correlate it if the wmi param was supplied!
if($WMI){
Try{

#Get the WMI data we're interested in.
$sqlServices = $null
$sqlServices = @(
Get-WmiObject -ComputerName $computer -query "select DisplayName, Name, PathName, StartName, StartMode, State from win32_service where Name LIKE 'MSSQL%'" -ErrorAction stop |
#This  matches MSSQLServer and MSSQL$*
Where-Object {$_.Name -match "^MSSQL(Server$|\$)"} |
select DisplayName, StartName, StartMode, State, PathName
)

#Correlate if we got WMI data and it wasn't empty!
if($sqlServices){

Write-Verbose "WMI Service info:`n$($sqlServices | Format-Table -AutoSize -Property * | out-string)"
foreach($inst in $allInstances){
$matchingService = $sqlServices |
Where {$_.pathname -like "$( $inst.SQLBinRoot )*" -or $_.pathname -like "`"$( $inst.SQLBinRoot )*"} |
select -First 1

$inst | Select -property Computername,
SQLInstance,
SQLBinRoot,
Edition,
Version,
Caption,
isCluster,
isClusterNode,
ClusterName,
ClusterNodes,
FullName,
@{ label = "ServiceName"; expression = {
if($matchingService){
$matchingService.DisplayName
}
else{"No WMI Match"}
}},
@{ label = "ServiceState"; expression = {
if($matchingService){
$matchingService.State
}
else{"No WMI Match"}
}},
@{ label = "ServiceAccount"; expression = {
if($matchingService){
$matchingService.startname
}
else{"No WMI Match"}
}},
@{ label = "ServiceStartMode"; expression = {
if($matchingService){
$matchingService.startmode
}
else{"No WMI Match"}
}}
}
}
}
Catch {
Write-Warning "WMI information could not be retrieved for '$computer':`n$_"
$allInstances
}

}
else {
$allInstances
}
}
}
}

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.