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 thousandsor even tens of thousandsof 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.
||Database Systems Supported
||Red Gate Software
||Compares database schemas; synchronizes database schemas; easy to use; GUI
||Oracle and DB2
||Compares database schemas; synchronizes database schemas; documentation features; scheduling features
||SQL Server, Oracle, DB2, and Sybase
||Compares database schemas; synchronizes database schemas
|AllFusion ERwin Data Modeler
||SQL Server, Oracle, and DB2
||Compares database schemas; synchronizes database schemas; very large feature set for database management
Table 1. 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 |