2

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?

  • This is more of an SSMS trick but you could group all your severs as a Registered Server Group. After you install the procedure on each local server, you can just execute the procedure against all servers and the Results Pane will display the results by server. – Queue Mann Apr 11 '17 at 9:24
5

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.
  • 2
    Yep, in addition to doing sqlcmd, you can also use linked server queries, replication, log shipping, etc. – Brent Ozar Feb 6 '17 at 17:34
  • @BrentOzar Hi Brent .. sorry I did not get the part you can also use replication, log shipping, etc. Linked server is an option but replication, log shipping .. I dont understand. Thanks ! – Kin Shah Feb 6 '17 at 17:37
  • 1
    Sure, on each server, run sp_Blitz and write it to a table. Then, use options like replication or log shipping to copy that table to a central server. For example, when I was a DBA, I had a DBAtools database on every server. I wrote utility tables into that database, and then either used replication or log shipping (really full backup/restore) to bring them to a central DBA tools server. – Brent Ozar Feb 6 '17 at 18:33
  • @BrentOzar Ok .. I understand that if many-to-one replication is implemented then its one possibility to aggregate info into central database. Logshipping just dont fit here since its at database level. OP want to have all info into a central database and so logshipping wont work in this situation. Personally, maintaining replication is another headache to maintain when the server(instance) count increases or changes. Good points though ! – Kin Shah Feb 6 '17 at 18:38
  • 1
    Log shipping actually works fine if you use a view that unions across several databases. – Brent Ozar Feb 6 '17 at 19:40
0

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.

-1

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.

  • I am not sure this is even possible/practical, as you have not provided any evidence of possible/practica I suspect the answer will not be well received. – James Jenkins Jun 27 '19 at 11:47
  • That link is about installing sp_blitz on all your servers, not converting it to a query then running it on against registered servers. I a,m not questioning the ability to run quriess agains CMS, I am questioning converting sp_blitz to a query. – James Jenkins Jun 27 '19 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.