SQL Change Control
I have an idea for a utility I would like to write as a way to practice TDD and some other agile methodologies I have been reading up on, with a view to releasing it as an open source solution when i am done. I am not sure though if there is anything already out there which will suit the purpose. If you do know of something available, or a better alternative to solving my problem then please post in my comments section…
We have 10 sites running a WinForms based system each connecting to a local SQL2000 server. We use the application updater block to deploy new versions of the application to each site – with some additional tweaks to support decent change control. Updating the schema (or data) of the DB is not quite so easy though. All the SQL instances are accessible on the WAN, so when a change is required this means opening a connection to each instance in ISQL and running an update script. This is not a suitable solution though because it is too easy to miss a site, and there is no way of knowing which scripts were run where and what the last script to be run on any given DB was.
The solution I propose is a simple WinForms utility with an XML data store. You add a list of sites to the app (in the form of a SQL connection string or such). Then when you have a new script to run, you add it to the app and press play. The app then runs the script on each individual site and reports the success or failure, with the option of automatically rolling back the transaction at all sites if any one of them fails to succeed. The app also assigns the script a reference number and saves it to its XML data store. It also records the date and time the script was run for each site so that you can go back and see when changes were made, as well as see the last change that was made. Should you not wish to update a particular site within a run you should be able to unselect it from that run, but then when you do want to update that site the app should enforce a rule that all scripts are successfully run in the order in which they were added to the system. The XML data store could possibly be checked into source control.
What do you think? This app would certainly be useful to me; would anyone else find any use in it? Am I missing the point completely? Does something out there already provide this functionality (for free)?