sp_Blitz for All Servers2 min read

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:

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:

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

Execute sp_Blitz:

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:

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:

 

 

9 Replies to “sp_Blitz for All Servers2 min read

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

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

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

  2. 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.

      1. Thanks. Fortunately I did not need the latest version for my example. I am sure the dbatools folks will get it figured out soon.

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

Leave a Reply

Your email address will not be published. Required fields are marked *