In Podcast #19, Joel fails to answer the question posed to him by Ryan from San Diego; how, if you have a database per client of a web application instead of one database used by all clients, do you "go about providing updates and enhancements to all databases efficiently?"
I'm particularly interested in the answer to this. Does anyone out there have any thoughts on the matter? Did you structure a web application this way and how do you roll out changes to schema and code in such a scenario?
It's kinda difficult for us. We have a custom program that writes a lot of the sql code for the different databases for us. Essentially it writes the code once and then copies it over and over again along with placing the change database commands etc. It also makes sure that the primary key identities etc are in sync when they need to be. Beyond that I would look at Red Gate's products. They have saved us more than once here. With them you can easily compare the dbs and see what is differnt. A must when dealing with multiple copies.
Use a code generator / scripting language to implement the original schema and updates to it over time.
I've used Red Gate's SQL Packager for this in the past. The beauty of this tool is that it creates a C# project for you that actually does the work so if you need to you can extend the functionality of the default package to do other things like insert default values into new columns that have been added to the db etc. In the end you have a nice tool that you can hand to a technician and all they have to do to upgrade multiple DBs is point it to the database and click a button.
Red Gate also has a product called SQL multi-script that allows you to run scripts against multiple servers/dbs at the same time. I've never used this tool but I imagine if you're looking for something to use internally that doesn't need to be packaged up you'd want to look at that.