Generate a list of every SQL Instance on your Servers





To be successful as a Systems Administrator, you need to be able to collect information about your environment. This little guide will show you a simple and effective way to collect and report on every SQL Instance that is installed on a server in your domain.

Overview

I like to write functions and automated collection scripts, so I can have up to date reports. Sometimes it makes more sense to take someone else's code if for no reason other than saving time and effort.





I have been using Boe Prox's useful Get-SQLInstance for several years and thought I would share how I use it. Get-SQLInstance supports SQL versions 2000 - 2016.

Prereqs

You need an account with Administrative permissions on every server you want to run the script against

The account must have the ability to query AD for server names

Download Get-SQLInstance from https://gallery.technet.microsoft.com/scriptcenter/Get-SQLInstance-9a3245a0

Create a script folder and a report folder

Examples would be c:\scripts and c:\reports

Place the Get-SQLInstance in the scripts folder

It can be a great strategy to keep your scripts on a file share, so they can be accessed from any machine you wish.

Open Powershell or Powershell ISE AD Domain

Using Powershell ISE, create a new .ps1 named Report-SqlInstances.ps1 and place it in your scripts folder

Paste the following code into the editor and save My apologies for the interesting color coding.

# Load the Get-SQLInstance cmdlet . C:\scripts\Get-SQLInstance.ps1 # Create a list of all server names on the AD domain $servers = Get-ADComputer -filter 'OperatingSystem -like "*server*"' # Count the total number of servers to scan for SQL $count = $servers.count $i = 1 # Create an object to hold all our SQL data that will be returned $output = @() foreach ($server in $servers){ # Output the server name and the count Write-Host "[$i : $count] $($server.Name)" # Ping the server once prior to attempting to connect # This speeds up the script a bit if ((ping $server.name -n 1) -match 'reply'){ $data = Get-SQLInstance -Computername $server.Name # If any SQL data was found, add it to the output object if ($data){ $output += $data } } else { Write-Host " No Response" -ForegroundColor Yellow } # Increment the counter $i++ } # Export the list to a CSV file $output | Export-CSV -Path c:\reports\sqllist.csv -NoTypeInformation Stand Alone Servers To report on a single server, run this from the console

Get-SQLInstance -ComputerName "YOUR SERVERNAME" | Export-CSV -Path c:\reports\sqlinfo.csv -NoTypeInformation

Potential Improvements

There are some things I typically like to add to any report script. I am giving a list here to give you an idea of what you might like to add.

Performance