Need to run SQL script on 5000+ databases. How should I approach?

Question

I am developing a tool that will eventually be used to run a sql script on 5000+ production databases simultaneously. For now it will only be used to run scripts on our Dev and QA databases. But I want to design it in the most extensible way, and have no experience doing something like this, so I could use some advice.

Technology I'm currently using: C# .Net 4.0 ADO.Net SMO

EDIT: I suppose by extensible I only mean able to run the scripts on an arbitrary number of databases in the most efficient way possible.

Answer 1

What do you mean by extensible? Answering that may be the best way to find the answer you're looking for. To help:

1. Which features need to be expanded in the future?
2. Do you want to use plugins or just modify the main app's code?
3. Will anything "foundational" be changeable? (Requires careful use of interfaces.)

Of course, making it extensible and then never using that extensibility would mean you wasted your time. So, think about whether this is actually needed right now.

Answer 2

I would suggest you to use SQL 'Bulk input' method, That is the fast one I ever came accross with MS SQL. Cheers

Answer 3

First I would recommend double-check if you cannot use one of the existing management facilities, like Policy Based Management or Central Management Servers. Specially PBM, it covers many operations that traditionally required something like 'run this script on every database'. There are also many articles describing PBM in action, eg. Policy-based Management and Central Management Servers.

Another thing to consider is to use PowerShell instead of C#/ADO/SMO. PS deliverables are scripts that are easily changed/maintained in production, as opposed to compiled executables. Also the object pipe model of PS makes a lot of tasks easier in PS than in raw C#. PS can use multithreaded execution. See SQL Server PowerShell Overview.

Look into what other projects are out there than handle similar tasks. I myself have a project, dbUtilSqlcmd, that handles executing .SQL files in a ADO.Net environment (handling batch delimiter GO, handling sqlcmd variables :setvar and $(variable), handling :connect commands and so on).

Last, if you end up writing code instead of using PBM, your bigger problems are going to be threading and error reporting. Don't start a thread per server/database, 5000 threads are not viable. Use ThreadPool.QueueUserWorkItem instead. Better still, use the Tasks Parallel Library if possible.