Use sp_Blitz to gather information on all servers in a centralized server/database

Question

I am using sp_Blitz, and want to use it to execute the checks on all of my SQL Servers and have it write the results to a single table.

Do I need to create linked servers on the central instance to connect to the other instances, or is there some other way to input the server names into the sp_Blitz stored procedure?

Answer 1

Brent Ozar folks already have got you 50% - Inserting sp_Blitz® Output Into a Table

My steps would be :

• Log the output to a local dbastats or some dba database.
• Have a list of all sql servers in your central server.
• use sqlcmd and opendatasource to query remote servers and dump all the info into centralized server .. make sure you include something like collection_datetime and server_name. You can use powershell (Write-DbaDataTable) as well.
• schedule above logic using sqlagent job as per your needs.
• Make sure you have proper purge policy defined locally per server. I would add custom column like notified = 0 (as default) and update to 1 when you dump the data into central database. The collection_datetime and server_name will help you find out what has changed or do aggregation per server instance.

Answer 2

If all your databases are in Azure then Elastic Database Jobs may be the answer, as the feature allows running tasks against groups of databases and storing one returned resultset per step per database in a table.

The feature is officially still (as of writing, 2019-06-27) in preview though.
I'm not aware of a similar simple tool that works with none-azure installations.

Answer 3

Or, just convert sp_blitz from an SP to a QUERY (compatible with your sql versions), and use SSMS to execute against all Registered Servers at once. simples … then save the results. ok ok qed.