I have setup a Central Management Server and registered all of my SQL servers (prods and devs). I want to save the results of sp_BlitzFirst from running it against all of my servers to a centralized database 'DBAtools' on my test box for later analysis. What is the most efficient method for achieving this task?

Thank you so much in advance.

  • Actually, I've looked at that thread before posting the question. Is there a blog post somewhere that you can point me to on how to achieve that? Thank you. – Tom Apr 15 '20 at 19:18

There's nothing special about the data being built by sp_BlitzFirst. You can really zoom out and ask, "What's the most efficient way of moving data between any SQL Servers?"

Some of the approaches that folks have used include:

  • Pulling the data periodically from a central server via linked servers or openquery
  • Using their favorite ETL tool, like SSIS
  • Log shipping their management databases to a central location
  • Writing data movement jobs with PowerShell

There's no one right answer: it's just whatever data movement you're most comfortable with and most able to support. I don't ship a method with the First Responder Kit because frankly, I don't want to do support for this kind of thing. (It would come with all the baggage that comes with anytime people try to connect two SQL Servers to each other, especially over networks.)

  • Thanks Brent! Would you happen to know an example of using SSIS that someone have done that you can share here? – Tom Apr 16 '20 at 12:07
  • No. If you're familiar with SSIS already, then I gotta be honest: syncing tables like this will be a relatively trivial task for you. If you don't know SSIS, then teaching it is pretty far outside of the scope of what I do. Sorry about that. – Brent Ozar Apr 16 '20 at 16:02

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