I have a number of script files to be executed on remote sql server databases from client c# code. The same script files need to be executed on various servers in same sequence.
e.g there are following:
Files -- "file1.sql", "file2.sql", "file3.sql" &
Database Servers -- "server1", "server2", "server3" (this will be around 100) with
Database -- "testdb" present on each database server,
then I need to execute file1.sql then file2.sql and then file3.sql on testdb database on each of server1, server2 and server3 parallely.
Since I want to execute the scripts parallely on all servers, so I am planning to use Task Parallel Library's Parallel.for loop to create threads and execute parallely on database servers through c# code. I am planning to use smo objects to execute sql server scripts from c# code.
Please advise if what i am thinking makes sense to use tpl library to execute scripts on sql server from c# code or is there some other better approach that i can follow.
One important fact that will be there in production is that there will be around 100 databases(database servers).
All responses and advises are appreciated.
Thanks.
Some More Information Added: I think tpl's Task class uses thread pool thread, so if there are 100 threads to be created for execution on 100 databases, then it may be a problem as it may use all the thread pool threads(thread pool normally has 25 threads) and the server may get choked. Also as there are a limited number of thread pool threads and it is not a good choice to use threadpool threads for long running tasks, so would we need to implement our own implementation to manage the threads?
