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

i developing tool used run sql script on 5000+ production databases simultaneously. used run scripts on our dev , qa databases. want design in extensible way, , have no experience doing this, use advice.

technology i'm using: c# .net 4.0 ado.net smo

edit: suppose extensible mean able run scripts on arbitrary number of databases in efficient way possible.

first recommend double-check if cannot use 1 of existing management facilities, policy based management or central management servers. specially pbm, covers many operations traditionally required 'run script on every database'. there many articles describing pbm in action, eg. policy-based management , central management servers.

another thing consider use powershell instead of c#/ado/smo. ps deliverables scripts changed/maintained in production, opposed compiled executables. object pipe model of ps makes lot of tasks easier in ps in raw c#. ps can use multithreaded execution. see sql server powershell overview.

look other projects out there handle similar tasks. myself have project, dbutilsqlcmd, handles executing .sql files in ado.net environment (handling batch delimiter go, handling sqlcmd variables :setvar , $(variable), handling :connect commands , on).

last, if end writing code instead of using pbm, bigger problems going threading , error reporting. don't start thread per server/database, 5000 threads not viable. use threadpool.queueuserworkitem instead. better still, use tasks parallel library if possible.