See correction to this article

The Tracking Database
To complete the change-tracking system, we needed a tracking database that would help us keep track of where these changes had been applied, by whom, and when—and where they hadn't. VSS can do this for an individual piece of code but can't keep track of, say, all changes made to a given server. We built a small Microsoft Access tracking database containing one table, named DatabaseChanges. Table 1 shows the columns we used to track the changes.

Because we were dealing with only three servers, we denormalized into three columns—[DateAppliedToServer1], [DateAppliedToServer2], and [DateAppliedToServer3]—the set of dates that indicated when a change was applied to each server. If you have more environments than the basic three (say, separate environments for performance testing, staging, or training) or if they're more complex than we've discussed, we recommend applying the database-design practices that Michelle Poolet describes in Solutions by Design, "Real-Life Database Design," April 2001, InstantDoc ID 19948. This article describes how to correctly model even simple databases such as our Access database.

How It Worked
Once we had the version-control system set up, we put it to work. Whenever the client requested a change, a developer would make the necessary modifications and pass the script (as generated by Enterprise Manager's scripting facility, wherever applicable) by email to the person serving as the version controller. The version controller, after reviewing the revised script for compliance with naming and coding conventions, would check it into VSS along with the necessary version number and comments. Then, the version controller would make an entry in the Access table. When the change had to be applied to more than one server, the version controller entered the change date into the appropriate [DateAppliedToServer] column for that server and change. Generally, the version controller first applied the changes to the test environment; only after an OK from the quality-assurance team were the changes shifted to the production environment. This process enabled us to track, among other things:

  • All changes to a given object
  • All changes by a particular person
  • All changes made between two points in time
  • Any changes applied to Server1 that hadn't been applied (either intentionally or accidentally) to Server2 or Server3
  • All changes that had been incorporated into a build
  • Whether the appropriate people had acted on a particular change request and whether the correction had been made to the database

The database-layer version controller had to coordinate with his or her counterpart for the other application layers (e.g., presentation layer, business logic layer) before shifting a database change into a test or production system. This coordination ensured that all related components of an application-level change request were included in the build.

This system gave us the flexibility of backtracking to any point in time or recreating the database from scratch. For example, to recreate the database, we could follow six easy steps:

  1. Run the script in the DatabaseCreation folder
  2. Run the latest version of the DatabaseSchema script
  3. Run the latest version of the DatabaseSchemaChanges script
  4. Run the latest version of the DatabaseAllViews script
  5. Run the latest version of the DatabaseAllStoredProcs script
  6. Run the latest version of the DatabaseDataLoadingScripts script

We also occasionally used the database backup, which we created through SQL Server's database backup and restore facility, to restore the database to an earlier point in time and then apply all changes from that point forward. This was basically a shortcut whenever there were too many changes to apply individually, because it restored all tables, views, and stored procedures simultaneously rather than separately as with scripts. However, because database-backup schedules rarely coincided nicely with database-development change schedules, this method proved unreliable for version management.

This version-management system evolved to meet our various needs. You can handle this part of a database-development process in many ways; you just have to work out one that best suits your situation. You might improve on our method by integrating version control across all application layers (i.e., having a coordinated version-management system); this also brings out the interrelationships among components across application layers. Alternatively, you could use tools such as Embarcadero Technologies' DBArtisan Change Manager or Quest Software's Schema Manager for finding and tracking differences between two versions of the same database or two databases at different stages of the development life cycle and synchronizing the two environments.

End of Article

Prev. page     1 2 [3]     next page -->
CORRECTIONS TO THIS ARTICLE:
(Correction: Figure 7 is incorrectly referenced as Figure 8 in the text and caption.)



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Good article. I follow a similar paradigm and repeat on a fortnightly or monthly basis, depending on the rate of change. The key is to not forget logins and the CREATE DB statement etc. We also use the stored procedure versioning (e.g., CREATE PROCEDURE vyx;2). I tend to trust programmers with version management via standard headers and checking code into VSS as required (over and on top of standard full backups).

Thanks, Ck

Chris Kempster- May 23, 2002