Powershell - Collecting and Reporting on SQL Instances
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
- Job concurrency using Start-Job
Usability
- Use an input file such as a .TXT or .CSV to gather server names to process
- Automatic creation of Excel file
- Email report in HTML format
- Writing data to a reporting DB such as Microsoft SQL Server
- Logging to Window Events or a log file
- This can include information, warning, and errors
Comments
Post a Comment