Lately I have been working on Database Migrations. There are a lot of different ways to modify an existing database. Some I like much better than others. This time, due to internal requirements, I am working with an SQL Server database project, using the internal migration capability built therein.
This process is similar to those used historically with database comparison tools. It mimics some of the features of DB Compare from Red Gate. However, it doesn’t seem to handle very complicated migrations. When things get more difficult, such as renaming an object, database projects require some manual tweaking.
The administrator who set up my database migration build did some things to simplify the migration process for me. They created a pre-comparison script, and a post migration script. Using the pre-comparison script I can execute migrations that the automated process cannot handle. You can set the comparison to not execute if it results in data loss. So, if you make the manual migrations in the pre-migration script, then the automated comparison and migration process may complete successfully.
Using the post migration script, I am able to do some cleanup work. I can drop temporary working tables, add constraints, and even insert or modify data that couldn’t be done until the schema changes are completed.
Out of the box, this works ok. One important thing to know: any data manipulation scripts should not be based on the expectation of the state of the database. For example, do not insert records without first confirming that the records do not already exist. The same is true for any schema modifications. Don’t drop or add columns to a table without first determining their existence in the table.
Cheers,
Ben