sp_Blitz for All Servers

Are you a Microsoft SQL Server DBA managing one or more servers?  Then you should know about the First Responder Kit from Brent Ozar Unlimited.  This is a free set of tools that help you check the health of your SQL Servers.

Are you aware of SQL Server configuration settings that differ on your servers compared to best practices?  If not, then the FRK tool sp_Blitz is the tool for you.  This will evaluate your SQL Server and present you with a prioritized output list of actionable items for you to analyze. Sounds like an awesome tool right?  What if I told you I put together a PowerShell script that will allow you to perform this action against ALL your SQL Servers and store the results in a Central Database to analyze from one location and build your corrective action plan.

Let us walk through the steps in the script now to see what you can accomplish. A pre-requisite to run the script is to install the dbatools.io module and have it on the system you execute the script from.

Gather your list of servers:

$servers = Get-DbaRegisteredServer -SqlInstance "localhostsql2017"

This will load the

$servers variable with all the SQL Servers in my local Central Management Server.

Now that we have all of our servers we can proceed to gather all the sp_Blitz data.

Install or Update the First Responder Kit on each SQL Server:

Install-DbaFirstResponderKit -SqlInstance $s.Name -Database master -Force

This command will install the latest version of the FRK or update a previously installed version to the latest.

Execute sp_Blitz:

$results = Invoke-DbaQuery -SqlInstance $s.Name -Database master -Query "EXEC sp_blitz @CheckServerInfo = 1"

Now we are executing the sp_Blitz command with the @CheckServerInfo parameter to get extra information about our server. We are storing the results for each run in the

$results variable.

Store results to a database table:

Write-DbaDbTableData -SqlInstance $adminServer -Database Blitz -InputObject $results -Table 'spBlitzOutput'

Now that we have the results of sp_Blitz stored in our variable we can execute the

Write-DbaDbTableData command to save the results to the central database table.

View Results from All Servers:

Now you can see we can query our results for each server and return the Priority 1’s and get to work fixing issues.

Complete Script:

$servers = Get-DbaRegisteredServer -SqlInstance "localhostsql2017"
$adminServer = "localhostsql2017"
$i = 1
foreach ($s in $servers) {
    $install = Install-DbaFirstResponderKit -SqlInstance $s.ServerName -Database master
    $results = Invoke-DbaQuery -SqlInstance $s.ServerName -Database master -Query "EXEC sp_blitz @CheckServerInfo = 1"
    Write-DbaDbTableData -SqlInstance $adminServer -Database Blitz -InputObject $results -Table 'spBlitzOutput'
    Invoke-DbaQuery -SqlInstance $adminServer -Database Blitz -Query "UPDATE spBlitzOutput SET ServerName = '$($s.ServerName)' WHERE ServerName IS NULL"
    Write-Output "Server(s) $i of $($servers.Count) Complete."
    $i ++
}