sp_Blitz for All Servers » GarryBargsley.com

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 "localhost\sql2017"

1	$servers = Get-DbaRegisteredServer -SqlInstance "localhost\sql2017"

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

1	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"

1	$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'

1	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 "localhost\sql2017"
$adminServer = "localhost\sql2017"
$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 ++
}

$servers = Get-DbaRegisteredServer -SqlInstance "localhost\sql2017"

$adminServer = "localhost\sql2017"

$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 ++

}

11 Replies to “sp_Blitz for All Servers2 min read”

Hi Garry
Thanks for sharing this, great work! I’ve been wanting to do this myself for quite some now, but never got myself around it :).

I suppose the code above can be use to practically import any query results into a central table, right? I am looking at importing all data that can be collected using sp_BlitzFirst when outputting to a set of tables and few other tables of my own (local copy of autogrowth events, error log output, etc).

Thanks again.

Regards,
Alin

Matt Lee says:

July 28, 2020 at 9:30 am

Alin, did you ever figure that process out with blitzfirst?

i am in the same boat, i am trying to populate the server tab on the first responder kit dashboard. i would love to be able to just load it once and select by server, instead of changing the parameters each time i want to look at a different server.

Reply
1. Alin Selicean says:
  
  July 29, 2020 at 4:36 am
  
  Nope, didn’t had the time yet. Still on my backlog, sort of speak… If I don’t forget, I’ll post a note here when I’ve done it.
  
  Reply

I think there is a bug in Install-DbaFirstResponderKit. It also installs the old versions from the Depricate folder on top of the new ones. There is already an issue on github about this.

Andreas Jordan says:

July 15, 2020 at 10:27 am

Here is the link to the issue: https://github.com/sqlcollaborative/dbatools/issues/6531

Reply
1. gbargsley@gmail.com says:
  
  July 15, 2020 at 3:12 pm
  
  Thanks. Fortunately I did not need the latest version for my example. I am sure the dbatools folks will get it figured out soon.
  
  Reply

Great script! Although you’re expecting that internet access is granted from the remote SQL Servers, and in our case, it isn’t.

Andreas Jordan says:

July 21, 2020 at 1:51 am

Hi! The internet access is only needed from the managment server where you start the PowerShell script – not from the SQL Server. And there is a -LocalFile parameter so you can download the file manualy from https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/archive/main.zip and transfer it to the server where you start PowerShell.

Reply
1. gbargsley@gmail.com says:
  
  July 21, 2020 at 9:56 am
  
  Thank you Andreas for help with that clarification.
  
  Reply

Is it expected that the table is manually created first? The results cannot get inserted into the table automatically created by spBlitz:
(WARNING: [08:18:30][Write-DbaDbTableData] Failed to bulk import to [databaseName].[dbo].[BlitzResults] | The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.)

And when I add -AutoCreateTable to the Write-DbaDbTableData line, I get an error because ServerName does not exist:
(WARNING: [08:22:42][Invoke-DbaQuery] [EIGRPT1] Failed during execution | Invalid column name ‘ServerName’.)

Hi Mark, here is a stupid table definition for that. You should not change the order of the columns! but all “max” should be change for less column size.. BR Frank
USE [blitz]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[spBlitzOutput](
[Priority] [tinyint] NULL,
[FindingsGroup] [nvarchar](max) NULL,
[Finding] [nvarchar](max) NULL,
[DatabaseName] [nvarchar](max) NULL,
[URL] [nvarchar](max) NULL,
[Details] [nvarchar](max) NULL,
[QueryPlan] [nvarchar](max) NULL,
[QueryPlanFiltered] [nvarchar](max) NULL,
[CheckID] [int] NULL,
[ServerName] [nvarchar](max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

sp_Blitz for All Servers2 min read