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