This is what we do, but we are PHP developers not VS so maybe Microsoft has a better approach, but the idea should be the same.
We keep a change log table in the database. Each time the update scripts execute this table is updated to reflect the current state of the database. It also keeps a history of changes (this allows you to spot customer database's that might of had special hotfixes installed that others don't have).
CREATE TABLE `changelogs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`version` varchar(45) NOT NULL,
`previous` varchar(45) NOT NULL,
`log` text,
`started` datetime DEFAULT NULL,
`finished` datetime DEFAULT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `created` (`created`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1$$
There are two types of updates from source control that the developers will need to know about.
The update is redundant, meaning they can simply delete a record from their database change logs and re-run the updates. The updates don't require a critical point in the schema.
The update is dependant, and they must rollback the database to a stable version so the updates can bring it forward. The updates require a specific schema.
While customers may be running version 2.0.0 of the database. Developers may only have to rollback to 2.9.9 to test upgrading to 3.0.0 (beta). You can also have downgrade scripts that perform quick hacks to the database to make the upgrades safe to run.
The update process is basically any automated technique of your choice. We use a directory in each project that stores a list of SQL files. The directory contents look like this.
install.sql
update.txt
update-2.0.0.sql
update-2.0.1.sql
update-3.0.0.sql
hotfix-3.0.1.sql
install.sql is our zero point entry script. It creates the database for new installs.
update.txt is our update guide for the deployment script, and the rest are SQL files to be executed.
The contents of the update.txt look like this.
#
# Defines the update mapping of which SQL files are executed.
# The order of this file isn't important, but do not create
# endless circular versions (i.e. update 3 to 4, then 4 down to 3).
#
# Usage for each line:
# [current_version]=update-[new_version].sql
#
3.3.14=update-3.4.0.sql
# DATE: 20130107 144921
3.4.0=update-3.4.1.sql
The only value the deployment script understands is the first current_verion part. That equals a file. When the deployment script executes it reads what the current version is from the changelogs table, and then executes the SQL file that matches that version. This steps the database forward in the schema changes. This process repeats until there are no more matches in the update.txt file (order in the file isn't important).
This approach works fine for us. We can single out customers with hotfixes, but still migrate then to formal releases later. Even install small SQL change files to bring them up to date from their hotfit.
The only problem with this approach is that it requires discipline by the developer. We also separate this process by software module, rather than one large update directory per application. For each module we prefix the changelog table. So we have multiple tables called things like documents_changelog, and users_changelog. That has worked well for us since modules are shared across multiple applications.
