3

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?

Improve this question
1
  • 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
    Commented Apr 11, 2017 at 9:24

3 Answers 3

Reset to default
6

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.
Improve this answer
5
  • 2
    Yep, in addition to doing sqlcmd, you can also use linked server queries, replication, log shipping, etc.
    – Brent Ozar
    Commented Feb 6, 2017 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
    Commented Feb 6, 2017 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
    Commented Feb 6, 2017 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
    Commented Feb 6, 2017 at 18:38
  • 1
    Log shipping actually works fine if you use a view that unions across several databases.
    – Brent Ozar
    Commented Feb 6, 2017 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.

Improve this answer
-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.

Improve this answer
3
  • 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
    Commented Jun 27, 2019 at 11:47
  • brentozar.com/archive/2016/04/…
    – Richard102
    Commented Jun 27, 2019 at 12:05
  • 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
    Commented Jun 27, 2019 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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