anaging the design and creation of database objects such as tables, stored procedures, and views is a fact of life for database developers. Because applications grow and change continually, additions and changes to database structures (or schemas) are a constant part of the development process.

Developers generally create the requisite database objects using a graphical user interface (GUI)-based tool such as Microsoft’s SQL Enterprise Manager for MS SQL Server. GUI-based tools offer a powerful, rapid way to create database objects because they automate the details of the work, allowing the developer to focus on improving performance. However, they also hide the SQL commands they use to create the new objects from the developer, which can cause problems when a developer needs to migrate structures between different databases.

The few developers who use a SQL editor to hand code every database object change can use the resulting scripts to make any future database migrations?if (and it is a very big if) in these shared common scripts they save every change in the correct order. When using this process, even one omitted statement can cause chaos.

I believe that using an automated migration tool for database structures is a superior solution to both using the standard GUI-based tools, a process based on paper lists and manual changes, and storing hand-coded statements.

This article details the challenges of pushing database changes from the development database to the testing, staging, and live databases that are used during the development process. It also explains why using an automated tool is the best way to deal with these challenges.

Database Structure Migration Between Development Stages
Many organizations use a range of databases for different stages of their development processes. They might have a development database, a test database, a staging database, and a live production database. Once an iteration of the application is finished and moved onto the next stage, the accompanying database structure needs to be migrated (from a staging database to a live database, for example). Organizations can make this migration in the following three ways:

1. Delete the target database and replace it with the new one. The advantage of this approach is that it is simple. The major drawback is that any data in the replaced database will be lost. This could be a disaster in a live database, but even a test database contains lots of data for the required range of test scenarios, which would be tedious and time-consuming to replace.
2. Using SQL Enterprise Manager (or a similar program) to migrate changes from the old database to the new one. In this scenario, a developer or DBA manually adds and modifies database objects via a tool such as SQL Enterprise Manager’s user interface. The major problem with this approach is that it is not a documented, repeatable process.
3. Create a script to convert one database’s structure to match that of another. This involves creating batches of SQL statements. These statements contain commands to make the database schema of the live database the same as that of the staging database.

Creating Migration Scripts
Of these three options, creating a script is clearly the most acceptable. It allows changes to be pushed from a single source database to multiple target databases without any data loss. What’s more, by documenting the changes between database versions, the process provides an audit trail that developers can track and reproduce.

Following this process, however, poses a number of problems. First, the organization must determine when to create the migration scripts. The scripts can be created either as each database object is modified throughout the development lifecycle or just prior to the actual migration, after database development is complete.

The first option relies on developers and DBAs remembering to create accurate, debugged batches of SQL statements whenever they change a database object, and then somebody collating all the changes for the ultimate migration. Unfortunately, in the real world this does not happen, so creating the scripts at the end of development is often the easiest and best practice.

The second problem is making sure the scripts are complete. If not all of the schema changes are pushed from one database to the other, the resulting application errors will be hard to find, reproduce, and fix. If the migration scripts are inaccurate, an application might work fine on the developer’s system but not on the systems that the QA team or customers use. The “it works fine on my computer” excuse will kick in and recriminations will follow.

With a large database and a complex application, tracking down issues caused by mismatched database schemas can take weeks. Since these problems are often not detected until very late in the software development lifecycle, they are especially frustrating and hard to manage and fix.

The third challenge is the sheer complexity of spotting differences in databases that might contain thousands?or even tens of thousands?of tables, stored procedures, views, and other database objects. If you’ve ever tried to track down differences in database schemas using printouts, a pen, and lots of paper, you no doubt recognize the potential value of an automated tool that excels at that task. Table 1 presents a list of such products for migrating database structures.

For example, Figure 1 shows how Red Gate Software’s SQL Compare, an automated tool that can compare database structures, displays a comparative list of differing database objects.

Figure 1: SQL Compare Displays a Comparative List of Differing Database Objects

Automation Makes Life Easy
A good automated tool features all of the following functions:

Gives you visual indications of differences in database objects. You need to be able to see, at a glance, how your databases differ before you migrate changes. Figure 2 shows the details that SQL Compare displays of the differences between two tables storing information for an ASP application.

Figure 2: SQL Compare Displays Details of the Differences Between Two Tables

Creates scripts in the correct order, taking dependencies and foreign keys into account. Database objects need to be dropped, altered, and created in the correct order or database updates will fail. If you alter the data type of a column that is part of an index, then you will need to drop and recreate the index. Figure 3 shows some of the script that SQL Compare automatically generates to make the changes shown in Figure 2.

Figure 3: SQL Compare Automatically Generates Scripts

A Life-changing Solution
Trawling through database schema with a pen and paper, spending weeks debugging incomplete, hand-written SQL scripts, and working late nights tracking down irreproducible application errors just days before product deadlines is not something that any developer or DBA likes doing. Nor is there any need for it. An automated cure can be life changing for database application developers and DBAs.