Part 1: Object level source control using the edit-merge-commit model

This 3-part sponsored article surveys several different methodologies for database development, examines their strengths and weaknesses, and illustrates how Red Gate's comparison tool, SQL Compare, can be incorporated into each model. The intention of the article is not to recommend one approach over another, but to describe how Red Gate software fits in to the model you choose to adopt, and improves it. The article breaks down as follows:

• Part 1: Object level source control using edit-merge-commit (EMC) – Developers and testers work on real, isolated instances of a database. Individual changes are tested, validated then merged into the source control system, using SQL Compare. The EMC source control model is prevalent with open source systems such as Subversion, but also other popular Source Control systems such as Vault. SQL Compare supports the EMC model "out-of-the-box".
• Part 2: Object level source control using checkout-edit-checkin (CEC) – as above, except that the CEC model is primarily used with Microsoft's Visual SourceSafe, and is supported by SQL Compare via the SQL Changesetadd-in.
• Part 3: Object level source control using the offline model – Developers and testers directly edit the SQL within the relevant creation scripts, rather than working on "live" database. The changes are then validated  and merged. SQL Compare offers native support for this model

NOTE:
---------
There is a fourth model, referred to as the ad-hoc merging model, which does not rely on a source control system to co-ordinate database changes, and instead involves the ad-hoc merging of development instances. This model has limitations but has been proven to be effective for smaller development projects, and is described here: http://www.simple-talk.com/sql/sql-tools/a-database-development-model-using-sql-compare/.
---------

Development models necessarily take into account a number of factors that vary from organization to organization, including resources, timescales, and budget. One theme, however, is emerging with increasing prominence: there is growing recognition of the value of applying sound change management principles to the database development process. The challenge for many businesses is how to respond to this, without substantial investment or costly upheaval to existing workflows.

What do we mean by change management?

The purpose of change management is to protect the integrity of the database design throughout the development process. On a practical level, this means the ability to identify what changes have been made, when, and by whom; and a means to scrutinize and – where necessary – undo individual modifications. Another common requirement is the facility to access and distribute the most recent iteration of a database schema under construction at any point during the development cycle. In an environment where a number of developers work in parallel to edit a database design, integrating their activities can prove difficult without a clear set of agreed processes.

A source control system is typically the critical application around which a change management regime is structured. The source control application enforces good practise by providing a central repository for all work on the database to be stored in on a regular basis, and offers features such as security, file histories, merging, branching, labeling, auditing and reporting, that can be utilized within a project to offer a greater degree of control, and to minimize risk.

This still poses the question of how to actually edit and save the database design. Making changes to a database does not automatically leave any permanent record of what was altered, and how. A tool such as SQL Compare allows different versions of the database schema to be synchronized and upgrade scripts to be saved.

Furthermore, SQL Compare 6 Professional gives the developer the option to work on a database schema at the level of individual database objects. A database schema can be saved as a set of SQL object creation scripts, which can be synchronized with a live database; this provides a more granular option for storage of the database design, and offers an economic and simple solution to the issue of source control.

This article describes how to incorporate SQL Compare 6 Professional into a workflow that maximizes the benefits of working with files in the context of a source control solution that uses the Edit-Merge-Commit model.

Object level source control using Edit-Merge-Commit

Object level version history and labeling is considered by many to be the basis of database change management best practice. It allows you to place your source control system at the heart of the development process, and provides a means to control and track changes to your database design in a detailed and meticulous manner. The object creation scripts functionality in SQL Compare Professional 6 means that you can now work at the level of object scripts, and still use SQL Compare to compare and synchronize your database modifications.

The Edit-Merge-Commit Model

In the traditional Checkout-Edit-Checkin (CEC) source control model, as supported by Microsoft's Visual SourceSafe, a developer checks out a script from the source control system, performs the necessary edits and then checks it back in. From the point a developer checks out a particular script, it is "locked" and cannot be modified by any other developer until checked back in. This model completely removes any possible conflicts that could arise from multiple developers modifying the same script, but necessarily can also slow down the development process.

Many developers, certainly many of the developers at Red Gate Software, regard the CEC model as somewhat out-dated, and instead prefer to use a more "optimistic" form of the source control model, known as Edit-Merge-Commit (also sometimes referred to as CVS style). Using the "edit-merge-commit" method of source control allows files to be modified on your local machine without having to specifically check files out of a repository or make them writeable prior to the edit. Any developer can edit any script at any time. Once a developer's edits on a script are complete, the changes are committed back to the source control system. If the script has not been modified in the meantime, the commit goes ahead immediately. If the script has been modified, the source control system will highlight this and suggest how the sets of changes can be merged. The developer (or third party tools) can influence how this merge process should work.

So, the process would work something like as follows:

1. Before synchronizing, ensure that the target scripts folder has been added to your source control system, so that any modifications or additions are monitored.

2. Run the synchronization, with the scripts as the target schema, using SQL Compare 6 Professional. The files that will be edited or created at this stage are identified before the synchronization:

Figure 1

3. Files corresponding to deleted objects are not deleted during synchronization. These files must be identified individually, and removed from source control. Typically they will be empty, and therefore around 0 KB in size. 

Use of a source control system with a client that is integrated into your file explorer allows you to easily identify empty files:

Figure 2

The example in Figure 2 shows the Tortoise SVN client (used with the Subversion source control system).

4. Using your source control application, identify any modified files in the designated folder and commit them to the repository. If there are files to be deleted, remove these first.

Figure 3

If other developers have been editing the same files, a merge will be required before the files can be saved back to source control.

Case Study: Red Gate Website Upgrade

To provide a "real life" example, the Red Gate web developers recently employed SQL Compare 6 to manage database changes and migrations while developing version 5 of the redgate.com website.

The developers elected to use the EMC model in this development project, which was supported by their source control system (Sourcegear Vault, running in CVS mode). There were two developers and a tester on the project, and the process they adopted can be roughly described by the following diagram and description.

Figure 4

1. SQL Compare 6 was used to export the current production database schema to a set of SQL object creation scripts. A folder structure such that each object type was contained within a different folder.
2. The object scripts were added to the Vault source control system and labelled as the baseline (in this case, "v4 Schema") version.
3. Each developer/tester obtained the latest version of the object files from the source control system, and stored them in a local "Working Scripts" directory.
4. Each developer/tester used SQL Compare to ensure that the schema on their local instance was synchronized with the version under source control.
5. The developers updated the databases on their local machine (isolated SQL server instance). When a change was made and tested (and approved if appropriate), the developer synchronized to their local scripts folder and then merged and committed any new and updated script files to the source control system. Some object scripts will now be identified in source control as being a later revision.
6. At frequent intervals, each developer/tester ensured that they had the latest set of files from the source control system, by using "get latest version" to obtain object files, and using SQL Compare to synchronize to their own database instance. All checked-in objects, irrespective of which individual made the change, were therefore propagated to all development machines.
7. When required, the set of object files in the source control system was labeled as the next version, and compared to the staging database to generate an upgrade script using SQL Compare. Any necessary modifications or fixes were made to the upgrade script, and then it was used to update the staging database.
8. A backup was taken of the production database before running the synchronization script.
9. The synchronization script was run against the production database.

Advantages

Each individual schema object script exists as a separate file under source control, allowing the history of any object to be tracked as the schema evolves in the project. In the situation where unwanted changes may have been included in a version of the schema, the affected objects can be reverted to their last correct version, leaving the rest of the schema intact.

Developers still develop on a real database, allowing them to validate changes and ensure they are proving the integrity of the database schema as they proceed with development. Source control features can be utilized to aid project management; for example, the use of comments when checking in each script, or to identify which individual has altered a particular object.

Merge situations (parallel development on the same objects) can be more closely controlled, by dictating that the two conflicting versions of the script file must be merged before being checked in.

Disadvantages

Use of source control requires a more disciplined and procedural approach, which may be unfamiliar to some developers who prefer to work in a more dynamic regime.

Source control applications require administration, management, may take time to set up, and can slow down projects when there are problems with the host server. Purchasing and integrating new software may be expensive.

For smaller projects, where there may be only a single developer, source control may be unnecessary and overly time-consuming. Individuals on the project have to be trained to use a particular system in operation, and to follow the correct processes.

Conclusions

The object level source control method offers an excellent solution for database development that adheres to the principles of trackable change management, while still allowing developers to apply changes directly to a real database. Using SQL Compare 6 Professional allows a database schema to be stored within the security of a source control application as a set of SQL creation scripts, and for these scripts to be compared with a real, modified database and updated with changes. In this way, changes to the schema can be controlled and audited in a detailed and comprehensive manner, at the level of component objects, without any requirement for scripts to be edited manually off line.