Use Enterprise Manager and Visual SourceSafe to track database changes
A large database-driven software-development project is a complex entity with many developers working simultaneously. Within it, you'd probably find a development environment, a testing environment, and a production environment. But you need a way to keep track of which version of a software component is the most up-to-date: Which version exists in development, which in testing, and which in production?
In large projects, managing the development process—keeping track of what has been tested, what has been promoted to production, and what needs rework—can be a challenge. This is especially true when the database is part of a multi-layered application. Changes made in the various layers need to be synchronized with each other to avoid breaking the application. For example, a change in a Visual Basic (VB) component might require a corresponding change to a database object. Version control, though rarely discussed, is an important subject. To see how to develop your own version-control process, let's look at a system that we used to maintain version control of database objects and synchronize changes to those objects with changes to components in other application layers.
Requirements
Our project called for us to develop a database-driven Web site that used the latest (pre-.NET) Microsoft technologies: Active Server Pages (ASP), Visual Basic (VB), Microsoft Site Server, Microsoft Transaction Server (MTS), SQL Server 7.0, and Windows 2000. (SQL Server 2000 was still in the early stages of implementation within our enterprise.) We were already using Microsoft Visual SourceSafe (VSS) 6.0 for version control of VB and ASP code, so we decided to try it for managing database versions as well. VSS lets you keep track of a component's various development stages and lets you go back to any previous version you need to reference.
The way you design a version-management process depends on the specific project requirements. In our case, the requirements were to incorporate the newest technologies and gain the ability to
- regenerate from scripts the entire database, without production data, exactly as it was at any given point in the development life cycle
- apply or roll back individual changes regardless of the number and type of objects involved in the change (i.e., treat each change like an all-or-nothing transaction)
Any version-management effort starts with defining one version as the base version that you'll track all future changes against. As the base version, we chose the testing environment database at a specific point in time and gave it a version number of 1.00. The testing environment was under tighter control than the development environment (with restricted access and reporting by email of changes made), so selecting that as the base made sense. The number before the decimal gave the major version number. The number after the decimal gave the release number—referring to small changes to a major version that didn't require a change in version number. Build numbers were version numbers that we submitted for user-acceptance testing—we didn't submit all intermediate versions for this testing. The major version number changed (e.g., from 1.00 to 2.00) when either the total number of minor changes to the current major version exceeded a predetermined number, such as 10, or a major change happened, such as an addition in functionality or a significant change in the application's business logic. A minor change might be having a stored procedure return an additional column; a major change would be adding a whole set of stored procedures to do something the application didn't do before.
Setting Up
Before we look at the details of the database change-management process, let's examine the "ingredients" of a database that's ready to start receiving production data. In a production-ready database, you can have a database definition (the CREATE DATABASE statement); logins, users, roles, and permissions; tables, defaults, rules, user-defined data types (UDTs), triggers, and constraints; views; stored procedures; and preloaded data (for example, in certain lookup tables).
To track these items, we created some project folders in VSS, then created corresponding work folders by the same names in the test server's file system to classify and track the changes. VSS essentially has an internal database that a VSS administrator sets up. After the administrator gives permissions to other users for this database, the authorized users can create new folders for their requirements. The developer checks the required files into VSS from the working folder the first time. Whenever developers need to work on a checked-in file, they can check it out of VSS into the working folder. After working on the file, they must check it back into the appropriate project folder from the working folder. Here are the folders that we used.
DatabaseCreation. This project folder contained the creation script for a database in a SQL Server installation.
DatabaseSchema. This project folder contained the script to generate all the database's tables, defaults, rules, UDTs, logins, database users and roles, object-level permissions, triggers, and constraints. To generate this script, we used Enterprise Manager's scripting facility, selecting the options that Figure 1 shows.
Prev. page
[1]
2
3
next page 
