We're probably all familiar with the importance of change management as it relates to the ongoing operation and maintenance of production IT systems—without a way to track and manage the updates we make, we're seriously handicapped when something unexpected occurs. Having an activity trail and known good versions of a system can make recovery much easier. Database change-management solutions apply the same concepts to the development and maintenance efforts of our database systems.

Below I review two change-management systems for databases, Embarcadero Technologies’ Embarcadero Change Manager and Quest Software’s Change Director for SQL Server. Both products support a core component of database change management—the management of changes to our database definitions—but there are significant differences between the products.

Embarcadero Change Manager 4.0
Embarcadero Change Manager 4.0 supports Microsoft SQL Server 2005 (though not all features with the current release) and SQL Server 2000, as well as Oracle, Sybase, and DB2. On-demand and scheduled jobs compare database data and schema either within the same database platform or between platforms, making this product useful for platform migration projects. You can also manage and monitor database server configuration parameters so you'll know if something changes that might affect database performance. Change Manager can also push configuration parameters to database servers, ensuring that your servers continue to adhere to server standards you set, and it lets you synchronize data and schema, but not configuration parameters, between servers.

Change Manager Architecture
Change Manager comprises three key components: CM/Config, CM/Data, and CM/Schema. Current versions of CM/Config and CM/Data are written in Eclipse, a cross platform development environment. A project is underway to rewrite CM/Schema in Eclipse, which will facilitate release of a Linux version of Change Manager. CM/Config and CM/Data share a common setup program and UI; CM/Schema is installed and managed separately. Change Manager uses standard database interfaces to query database servers for data, database server configuration parameters, and schema information, so no agent or stored procedures need be installed on participating SQL Server instances. An archive feature for CM/Config and CM/Schema lets you retain a point-in-time state for later comparison or synchronization.

Change Manager is licensed by datasource, but the definition of a datasource varies by database platform. In SQL Server, a datasource is an instance of SQL Server. A PDF document, "Change Manager 4.0 Evaluation Guide," walks you through the initial installation and use of CM/Config and CM/Data; another PDF, "Using Change Manager 4.0," describes the feature set and use of all three Change Manager components. Online Help, in .chm format, is also available.

Change Manager Installation
Change Manager installs on 32-bit versions of Windows XP Pro, Windows Server 2003, and Windows 2000, and it runs on both x86 and x64 versions of Windows Vista. I installed Change Manager on a Windows 2003 system, and the setup routines completed quickly and uneventfully.

I started my testing by opening the Eclipse-based UI for CM/Data and CM/Config, which is known as the Workbench. As Figure 1 shows, a tabbed interface on the left displays alternative views of configured objects—archives, datasources, and the jobs defined for them. The Datasource Explorer tab lets you place datasources within a hierarchy you define, and organizes jobs by datasource. The Change Explorer tab lists defined jobs organized by one of the seven fields available in a drop-down list—Name, DBMS, or Last Run, for example. Standards Explorer, found at the bottom of the left pane, displays the server configuration parameter archives you’ve saved with Change Manager.

As the first step to using Change Manager, a wizard helps you define datasources. You'll need to enter the server name; this wizard doesn't autodiscover datasources on the network. The wizard includes a button you click to test Change Manager's ability to connect to the server. You can enter a SQL Server user ID and password, or click the Allow Trusted Connections check box to authenticate using your Windows logon ID.

Working with Change Manager
Change Manager features powerful, easy-to-use database comparison and synchronization. An Options tab on the Data Comparison Job Editor lets you select the databases Change Manager will compare and gives you some control over how the automated mapping and comparison between databases, tables, and columns occurs. One of the options, Compression Comparison, can speed execution by causing Change Manager to calculate and compare table-level hash values at each datasource before transmitting the data to the system. If the hash values are the same, Change Manager assumes equality and moves on to the next table; if the values are different, Change Manager transmits the table in compressed format before performing a row-level comparison. Compression Comparison requires that you enable CLR support, and it's available only for SQL Server 2005; DB2 for Linux, UNIX, and Windows (LUW); and Oracle datasources. A Mapping tab gives you full control over which databases, tables, and columns participate in the comparison, and lets you manually map databases, tables, and columns between the two datasources when the names are different enough that the automated mapping isn’t sufficient.

With the comparison definition complete, you can run it and work with the results, or save the job for later use. A right-click menu from the list of saved jobs creates and saves a batch file (.bat) you can use to run or schedule the job using an external job scheduler. After you run the comparison, you'll see a Results tab next to the Mapping tab; it provides an overview of the outcome, including a match index percentage for each database. Selecting a View option from the results summary line for a database shows a Database Results tab with detailed differences in the data for each table and row. From the row-level results display, you can select sets of rows and synchronize them in either direction—making the target data match the source, the source data match the target, or some of both. Change Manager generates synchronization scripts for each datasource; you can run the scripts and save them for use outside of Change Manager.

Working with server configuration parameters is similar to database comparisons. Change Manager lets you create an archive to store an instance’s current settings. You can also create a standard, which is similar to an archive. It either contains the values saved in an archive, or it's linked to a single datasource, taking on the current configuration values of that datasource when it's used. Configuration comparison jobs, the real power of CM/Config, let you compare either a single datasource or a single standard with one or more datasources or archives, with a result of either pass or fail. The default comparison looks for equal values, but you can customize the test by selecting a different logical operator.

The output of a comparison job is displayed in the GUI and can be exported to a comma-separated value (CSV) file. When you use the Generate Command Line option to create a .bat, Change Manager writes the CSV output file to a target directory you choose. Archives and standards include most of the options configurable using the system stored procedure sp_configure; only the advanced option Cross DB Ownership Chaining is missing from a SQL Server 2005 configuration archive. Curiously, that option is included in linked standards, but not in fixed-value standards. Even more curiously, CM/Config doesn't let you push the values of a standard or an archive to other instances of SQL Server—though you can do this easily with CM/Schema.

As I mentioned, CM/Schema has a UI separate from the Eclipse-based GUI of CM/Data and CM/Config—but there's nothing shabby about CM/Schema’s GUI. As Figure 2 shows, the GUI provides icons for rapid access to CM/Schema’s many wizard-driven procedures. CM/Schema’s basic feature set is similar to CM/Data and CM/Config: It archives all or part of the set of schemas found on a datasource, compares a datasource to a live or archived schema, and synchronizes (pushes out) an archived schema to other datasources. Its cross-platform support can help with database migration projects.

Continued on page 2