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

  • What exactly is happening as a part of this process? What does the SSIS package do? – Thomas Stringer Apr 13 '12 at 15:02
  • @shark, I'll add more to the original question to explain the SSIS package – datagod Apr 13 '12 at 15:04

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.


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.

  • I can't use the Central Management Server functionality, because our particular environment does not have windows authentication. We are using SQL Server authentication, which is not supported by CMS for distributed queries. – datagod Apr 14 '12 at 3:59
  • If you already have all your servers as a Registered Servers you can run the query against it.(works both for SQL and Windows Authentication) - but still think PowerShell is the best solution. – DaniSQL Apr 16 '12 at 15:29

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.