SQL Server Performance Forum – Threads Archive

Distributing Schema Changes to multiple locations

We have a product that uses the MSDE 2000 engine for the back end. We have approximately 1000 active customers. One of the big challenges we often come across is, how to cleanly, safely, and easily ditribute schema changes. Our customers are not technically skilled so we can’t count on much help from them. We need some way of distributing and automating schema changes. I’m open to any suggestions, techniques and third party tools. Thanks!
Jeff Jeff Gilbert

---

There may be some 3rd party tools to help, a company called Quest might do something, but Ive no idea whether these sorts of tools are worth the money I guess it depends what sort of design your system uses. If they are running client software provided by you, its easy to deploy changes via a script. Ive used this method for some racing analysis software I wrote. Just wrap the whole kaboodle in a transaction, and deploy the script along with the next version of the software. The software then reads the script and fires off each batch in turn, rolling back in the event of an error. Even if they dont already use your client software, its a fairly trivial thing to write. You could even have it check your website for new scripts that need downloading, then just get them to scheduleit to run every night.

---

Use data modeling tool (erwin, power designer, embarcadero…) to crate and maintain data model. Keep actual data model in version control system (source safe, subversion, cvs, perforce, vault). Generate the migration script between model versions from data modeling tool. After generation test it, generated scripts are not always perfect.

---

]]>