There are three ways to do change or configuration management of a database. Two of them are "traditional," and the third is LDM. Let's look at them all.

====== Manual list of changes ======


SQL is by its nature a change language. The individual statements in SQL are mostly focused on changing an existing database. While you use ''CREATE TABLE'' at first, every subsequent update will use ''ALTER TABLE''. Manipulating data is done with ''INSERT'' and ''UPDATE'' commands. To explicitly set the entire contents of a row is an elaborate form of update.
 
Not surprisingly, the easiest way to track the changes made to your database is by writing down the SQL statements that get issued to make those changes. If you insert a new column in a table, or change the data type of a column, you will use ''ALTER TABLE.'' Writing those statements down in a file is a quick and easy way to keep track of the changes you make. Create a file called ''update.sql'' and append the SQL statements to it one after another.
 
If you are trying to automate the deployment of these changes, you can protect each database change with a check to make sure the change has not been applied before -- maybe a table of change id numbers. Alternatively, you could force the developers to reset the ''update.sql'' to an empty file whenever they start developing. This lets you blindly execute the script, which most of the time will be empty, with each deployment. 

The CM system will track database changes by connecting them to the ''update.sql'' file, or however you store update scripts. The deployment process will operate by fetching successive versions of the update script, or iterating through an ordered list of update file names, until the target configuration is reached. 

Because the update scripts are generated based on the actual commands executed, this approach allows the updates to be split up or merged together when needed. In cases where a roll-back script is needed, recording the sequence of changes makes coding the reversion easier (which is not to say 'easy'). 

Tracking the updates does not, obviously, track the overall state of the database. When a /baseline/ is needed, some extra action will be required to produce a single set of scripts that generate the baseline configuration.

====== Computed list of changes ======

 
At the extreme other end of the spectrum, there are tools that can compare live database schemas with other schemas, or the SQL scripts that would create them. You can use these tools by establishing a rigorous standard for storing SQL scripts as part of your source code, so that for each object type (trigger, stored procedure, table, index, constraint, etc.) in the system, the mapping from entity to script is mechanically computable. Once all changes to the database (for a given work item) have been made, the tool is used to compare the "pre-" and "post-" images, and generate some SQL code that maps one to the other.

If the tool supports comparing SQL scripts, or comparing a live database against a set of scripts, then the comparison will match the live database against a set of checked-in scripts for generating the previous version of the schema. If the tool requires comparing two databases, you will have to devise a clever work-around. 

Automating deployment of the changes will require either computing the differences and storing them (as in the case above) or comparing the live state of the deployment target with the required state associated with the change and automatically synchronizing the two. I don't know of a tool that directly supports monitoring data changes in addition to schema changes. This can jeopardize the entire implementation. It is best if this approach is not used when the project involves a significant amount of metadata (business logic or other program data stored as table date).

Because the update scripts are generated mechanically by comparing the entire state of the database, before and after, there is little support for splitting up or merging two change sets. These operations will have to involve inspecting the generated update scripts and merging or reversing them from the development database. Splitting up a change set into two or more smaller units will therefore tend to lose data, unless the database configuration is saved away before you begin. Depending on the capability of the tool, it may or may not be possible to simply generate roll-back scripts for simple cases. Complex cases will always require hand coding. The mechanical approach may be configured to generate an empty roll-back section, which would be filled in by hand.

Because this development approach is based on extracting the entire state of the database, generating a single unified script to recreate the database at a given baseline will be trivial.

====== Longacre Deployment Management ======


Longacre Deployment Management (LDM) views all development in the context of deployment operations. Development activities produce updates to be applied against a particular target. This is exactly in accord with SQL, as described above. While source code tracking tools focus on explicitly replacing one version with another version, LDM abstracts the version selection activity as a "workspace update" operation. The result, from an LDM point of view, is that there are two kinds of activities: workspace update and database update. 

LDM assumes the ability to extract SQL update scripts. The mechanics of this are up to you to implement, depending on the tools you are using. The resulting updates are exported as part of a "bundle," along with a list of the workspace change sets that must be applied. Each bundle is archived, of course. But bundles cannot be modified--they are products. If a developer finds a bug in her work, she must deliver a different change set (or SQL update) in a later bundle to fix it. 
 
Once a bundle is archived, it can be applied at any time to any target platform. The act of deploying a bundle to a target creates a deployment record object. The deployment records connect bundles with locations, while the bundles serve as a link into the change request/task/work item part of the repository. Traversing the graph from either end provides the answers to the questions "which servers have received this update" (Change Request -> Tasks -> Bundles -> Deployment -> Location) and "what changes are deployed to this server?" (Location -> Deployment -> Bundles -> Task -> Change Request)
 
The low-level mechanics of LDM are closer to the "manual list of changes" approach than to the "computed list of changes." At the high level, LDM simply uses a different paradigm. There is no notion of "roll back". If a change is made in error, the LDM approach is to keep running and deliver a new change that fixes the problem(s) caused by the erroneous change.