Keeping Databases In-Synch With Your Source Control
Problem: Your development, test and production databases tend to accumulate cruft (aka. old stored procedures or schema) and you need a way to manage changes in a simple automated way.
Solution: I've had the great fortune to work with some excellent teams that have crafted elegant solutions to this problem. The following is a boiled down simple approach that provides a great way to tame database source control and upgrade issues.
Development Tools of Choice: VS.Net 2003 Ent., VSS, Nant, SQL Server 2K
Steps:
1. Using VS.Net and VSS, create 2 database projects. One to contain all objects that can be recompiled without data-loss (Stored Procs, UDFs, Views, Triggers). The second database project to contain a database_release.sql file to contain your schema changes for each release (make sure they are repeatable eg. "if exists ....") Use these 2 projects to manage all you database changes from release to release. Be sure to clean up any re-named or deleted objects from VSS.
2. Create an Nant build file that does the following:
- Allows you to call it with "dev", "test", or "prod"
- Does a recursive get from VSS on the database projects
- Applies an auto-revisioned version label to these projects in VSS
- Stage these files to a well known network location to appropriate sub-directory eg. "dev\AppXDatabase_1.03.22.31"
- Drops all re-runnable objects from the corresponding target database for dev, test or prod
- Apply repeatable schema changes for release
- Recursively apply all the re-runnable objects to the database
3. For each release you can wipe and build the release schema project as needed with new schema for the upcomming release.
Benefits of Approach: By doing this you get a versioned history of changes to your database. You also get versioned stage directories for dev, test and prod environments that have all the schema and re-runnable database objects at that point in time. These versioned stage directories correlate to a label in VSS. By automating the application of the schema and the dropping and recreating of the re-runnable objects your database will reflect your source control.