Figure 3 outlines the principal process of creating an update script:

1. Build database v1.0 from source control (target database).
2. Build database v2.0 from source control (source database).
3. Synchronize the two databases and record the synchronization process as upgrade script.
4. Generalize the automatically created upgrade script by rearranging and enhancing it with data transformations to prevent data loss.
5. Most important: Verify your upgrade script by upgrading a new database v1.0 to v2.0. Use the database comparison function of your DB sync tool to test whether the upgraded DB is truly identical to the database v2.0 created straight from the repository.

Steps 1 through 3 can easily be automated through scripting and the like. The intermediate result of Step 3 is a script specific to upgrading your development database from v1.0 to v2.0. However, this script is not yet suited to upgrade production databases. The following is a simple example that explains why. Let's say you performed a database refactoring and renamed a table from T1 to T2. A synchronization tool will only see a missing table T2 and an obsolete table T1 in the target database since it doesn't know the semantics of a rename operation. Therefore, during synchronization it will drop the obsolete table T1, create the missing table T2, and then populate the new table T2 with whatever data your development database v2.0 contained. If you applied the recorded upgrade script to a production database, the result would be data loss, as it drops table T1 during the upgrade.

The process of generalizing this specific upgrade script through enhancing and correcting will be more or less complicated, depending on how much guidance the synchronization tool provides. An excellent example for such guidance is shown in Figure 4, a screenshot taken from DbMaestro by Extreme. As you can see, the script is annotated with a number of warnings about potential data loss. Continuing our table rename example, such an annotated script would easily allow you to match the CREATE statement for table T2 and the DROP statement for table T1, and manually replace them with an sp_rename statement. In more complicated database changes like (de)normalizations and refactorings, you will actually need to create data transformations, e.g., through UPDATEs and INSERTs, and place them before any lossy schema changes.

Unfortunately, I'm not aware of any tool that currently recognizes operations, such as renames, and automatically creates the corresponding schema upgrade and data transformation operations. However, there are a number of best practices you can use to facilitate the process of creating a generalized upgrade script:

• During database development, check in often, preferably after each work item or refactoring step (just as when working with source code).
• Document the operations performed on each database revision, such as 'Normalized Table A into Table A and B' or 'Renamed Table C to D'. This will give better clues to the person generating the upgrade script.
• Rather than trying to create one huge upgrade script that spans dozens of revisions, create and concatenate multiple small upgrade scripts, each spanning only a few or even just one revision.

Summary
In this article I showed several techniques for addressing common problems in database development. A proper integration into source control is the foundation for any advanced technique such as concurrent development, testing, or creating upgrade scripts.

Prerequisite to each of these techniques is tool support (see the References section below). Speaking for myself, I have yet to find the perfect tool for all these tasks as each tool has its own strengths and weaknesses. Therefore, it might even make sense to work with two individual tools, depending on the tasks that you need to perform.

Lastly, I need to mention that for all the techniques described in this article I could only give a high-level introduction. Each one of them requires a certain amount of practice to use it efficiently, such as handling the check-out/check-in process confidently, consequently re-testing the build script before checking in, and getting an eye for manipulating automatically generated upgrade scripts. Nevertheless, you now should be on your way to establishing a reliable database change management process with predictable outcomes tailored to your own needs.

References

Ambler, S.W. (2003). Agile Database Techniques. Wiley.

Appleton, B., Berczuk, S., Konieczka, S. (2004). "Applying Agile SCM to Databases." Crossroads News. January. www.cmcrossroads.com/articles/agilejan04.pdf

ApexSQL: www.apexsql.com/

DbGhost: www.dbghost.com

DbMaestro: www.dbmaestro.com

Revision Control: http://en.wikipedia.org/wiki/Revision_control

SQL Tools: www.redgate.com