scanning sql instances on your network

Andre Kamman posted a powershell script on how to scan your network for instances of sqlserver, it gives back the edition and version installed and can be found here.
Robert Hartskeerl changed it to his likings also, which can be found here.

Based on these 2 versions we changed the script to our needs also a bit, also we cheated a little by using quest tools.
Below the prerequirements and steps to run our version.

• Install .net framework 4.0

• Register 4.0 by adding 2 configfiles (powershell_ise.exe.config and powershell.exe.config) in the powershell folder, if not already there, usually in folder ..\WINDOWS\system32\windowspowershell\v1.0\.
  Add the 4.0 reference to these config files. (check google for details if needed)
  

• Install the quest activeroles tooling, which can be found here.
  Our script still uses a textfile with all the names from computers present in the network/domain.

• Create this textfile by running the below code, first line needs to run also if the quest tooling wasn't previously loaded.

#add-PSSnapin quest.activeroles.admanagement $FilePath = "d:\tmp\computers.csv" $Computers = Get-QADComputer -searchroot 'DC=domainname,DC=nl' -SizeLimit 0 -OSName 'Windows*' $Computers | Select-Object Name | Export-Csv $FilePath

• Open the newly created textfile and delete the first line with all the column info, also remove the "" quotes from all the computernames.

• Almost there, now run the real script which scans the network, see below.

Props goes to my colleague Paul Woudwijk who added some code to this script (pong function) which makes sure this script skips offline computers.
Also his aura usually makes this stuff work faster also.

#add-PSSnapin quest.activeroles.admanagement
$Count = 0
$Count2 = 0
$instances2 = @()
$Computers = Get-Content D:\tmp\Powdershell\computers.csv
$FilePath2 = "D:\tmp\Powdershell\result3.csv"

function Pong{
Param([string]$srv,$port=135,$timeout=100,[switch]$verbose)
$ErrorActionPreference = "SilentlyContinue"
$tcpclient = new-Object system.Net.Sockets.TcpClient
$iar = $tcpclient.BeginConnect($srv,$port,$null,$null)
$wait = $iar.AsyncWaitHandle.WaitOne($timeout,$false)
if(!$wait)
{
$tcpclient.Close()
if($verbose){Write-Host "Connection Timeout"}
Return $false
}
else
{
$error.Clear()
$tcpclient.EndConnect($iar) | out-Null
if(!$?){if($verbose){write-host $error[0]};$failed = $true}
$tcpclient.Close()
}
if($failed){return $false}else{return $true}
}

function Get-SqlInstances($hostName)
{
$ErrorActionPreference = "SilentlyContinue"
$instances = @()
$reg = $null
#$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $hostname)
$Hive = "LocalMachine"
$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]$Hive,$hostName,[Microsoft.Win32.RegistryView]::Registry64)
IF ($reg)
{
$regkey = $null
$regkey = $reg.OpenSubkey("SOFTWARE\\Microsoft\Microsoft SQL Server")
IF ($regkey)
{
foreach ($regInstance in $regkey.GetValue('InstalledInstances'))
{
IF ($regInstance -eq "MSSQLServer")
{
$versionKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\MSSQLServer\\Setup")
$serviceName = $regInstance
}
ELSE
{
$versionKey = $reg.OpenSubkey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstance\\Setup")
$serviceName = "MSSQL`$$regInstance"
}
IF ($versionKey)
{
$version = $versionKey.GetValue('PatchLevel')
$edition = $versionKey.GetValue('Edition')
if($version)
{
$instanceName = $hostName
$clusterKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstanceData\\Cluster")
if($clusterKey)
{
$hostName = $clusterKey.GetValue('ClusterName')
}
if($regInstance -ne 'MSSQLSERVER')
{
$instanceName = "$hostName\$regInstance"
}
$serviceStatus = Get-Service -name $serviceName
$instance = New-Object System.Object
$instance | Add-Member -type NoteProperty -name Name -value $instanceName
$instance | Add-Member -type NoteProperty -name DisplayName -value $regInstance
$instance | Add-Member -type NoteProperty -name Version -value $version
$instance | Add-Member -type NoteProperty -name Edition -value $edition
$instance | Add-Member -type NoteProperty -name InstanceName -value $instanceName
$instance | Add-Member -type NoteProperty -name InstanceID -value $regInstance
$instance | Add-Member -type NoteProperty -name ServiceName -value $serviceName
$instance | Add-Member -type NoteProperty -name Status -value $serviceStatus.Status
$instances += $instance
}
}
}
$regkey = $null
$regkey = $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL")
IF ($regkey)
{
foreach ($regInstance in $regkey.GetValueNames())
{
$regInstanceData = $regKey.GetValue($regInstance)
$versionKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstanceData\\Setup")
$version = $versionKey.GetValue('PatchLevel')
$edition = $versionKey.GetValue('Edition')
$instanceName = $hostName
$serviceName = $regInstance
$clusterKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstanceData\\Cluster")
if($clusterKey)
{
$hostName = $clusterKey.GetValue('ClusterName')
}
if($regInstance -ne 'MSSQLSERVER')
{
$instanceName = "$hostName\$regInstance"
$serviceName = "MSSQL`$$regInstance"
}
$serviceStatus = Get-Service -name $serviceName
$instance = New-Object System.Object
$instance | Add-Member -type NoteProperty -name Name -value $instanceName
$instance | Add-Member -type NoteProperty -name DisplayName -value $regInstance
$instance | Add-Member -type NoteProperty -name Version -value $version
$instance | Add-Member -type NoteProperty -name Edition -value $edition
$instance | Add-Member -type NoteProperty -name InstanceName -value $instanceName
$instance | Add-Member -type NoteProperty -name InstanceID -value $regInstanceData
$instance | Add-Member -type NoteProperty -name ServiceName -value $serviceName
$instance | Add-Member -type NoteProperty -name Status -value $serviceStatus.Status
$instances += $instance
}
}
}
}
$instances
}
#Get-SqlInstances('sal0005')

foreach($Comp in $Computers)
{
#Write-host $Count.ToString()
#Get-SqlInstances($Comp.Name)
IF(pong($Comp))
{
$instances2 += Get-SqlInstances($Comp)
}# else {Write-host 'Offline'}
$Count = $count + 1
IF($Count -eq 999)
{
$Count2 = $Count2 + 1000
Write-host $Count2.ToString() ' found ' $instances2.Count.ToString() ' SQLServers'
$Count = 0
}
}
$instances2 | Select-Object Name, DisplayName, Version, Edition, InstanceName, InstanceID, ServiceName, Status | Export-Csv $FilePath2
$instances2 | Select-Object Name, DisplayName, Version, Edition, InstanceName, InstanceID, ServiceName, Status | ft