What is the preferred method to roll out updates to hundreds of databases

Question

I am trying to determine the best way to roll out SQL scripts to hundreds of distributed servers. I have a slow SSIS package and a very fast OSQL/BATCH process.

I am on a project where we have several hundred database servers deployed in the field. I need to be able to routinely query the servers and/or apply database upgrade scripts.

When we only had 20 servers, I built an SSIS package to grab a list of servers and process them one at a time. The SSIS package works great, has tons of error handling and logging, etc. It is able to capture reult sets from each server and store them in a central location for further analysis. Due to the way I built it, however, it processes each server serially. 20 Servers takes about 10 minutes to process. I am at a point where this method is simply too slow.

Before I invest more time into re-writing this package to spawn other packages (one for each server), I was hoping to get some advice from others out there who faced a similar situation.

Would you use SSIS in this manner, or switch to powershell, or even OSQL?

Almost as a joke, I dug through my archives and pulled out a batch process I wrote 12 years ago to apply scripts on remote servers. I made a few tweaks (changed to OSQL) and it runs incredibly fast. I can process 100 servers in under 20 seconds. I launch it from a windows 7 machine, which does not choke on that many command windows being opened.

The downside to the OSQL/BATCH solution is that the batch file is hard-coded with servernames and passwords. But it is so fast I will continue to use it until I find a better solution.

SSIS Package - The current SSIS package does the following: - connect to a central server - retrieve a list of servers that need to be queried - using a forloop container, connects to each server (in series) and executes the query - if the query returns a result set, this information is captured and stored at central server - the central server is updated to reflect success/fail of each server - all errors are logged at the central server

Answer 1

I would adapt your fast solution to something more easily extensible, like PowerShell.

It would be trivial to have PS read a text file for a list of servers to run against, and another file or files for what commands to execute.

You could also add error handling and such like in your SSIS batch, and have it fire asynchronously so it would work on all your servers simultaneously.

Answer 2

PowerShell is THE answer, but if you've got a script already that's blazing fast, you could consider using the Central Management Server functionality. It doesn't do a lot of things I've been trying to make it do lately, but it does one thing super well which is hit many servers quickly with script if you're already in SSMS.