SQL Server Change Management Tools

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

CM/Schema has a more complete feature set than CM/Data and CM/Config, including support for registering schema archives and Data Definition Language (DDL) files with one of three source-code control systems, an interface to Windows Scheduled Tasks for job scheduling, and a greater variety of reporting options. (Note that Change Manager documentation uses DDL to refer to both DDL and Data Manipulation Language—DML—scripts, a practice I continue here.) The first time I started CM/Schema, it revealed another nice feature: It autodiscovered and registered SQL Server instances on my network.

CM/Schema offers several ways to select the objects you want to capture with a schema capture job, letting you filter schema objects on the datasource by database (in SQL Server), followed by object type and optionally by object owner. You can then deselect certain schema objects to achieve the desired scope. In addition to archiving the schema, CM/Schema creates DDL statements and reports the job’s results via email or Net Send.

The schema comparison feature includes quite a few options to let you limit the scope of comparisons and facilitate effective reporting. You can generate create, alter and drop statements for objects that differ from the source, and you can set options to ignore a dozen types of differences—such as differences in constraint names—that aren't significant in your project. Reporting options let you send either a summary or detailed report to a list of email addresses; you can suppress the email report if no differences are detected.

Email reports are created in HTML format. Each report begins with a summary that contains links that point to details located on a network share you configure. When you request a detailed report, the details appear after the summary. For example, if your comparison job definition has CM/Schema create DDL statements to synchronize the target database to the state of the source, these DDL statements are included in the detailed report but available via a link if you only get the summary report. In my testing, Change Manager produced one report email for each database included in the comparison. Each report also included a link to the report directory, where I found a single script incorporating all DDL statements needed to synchronize the database schema. When you select the Configuration object type, CM/Schema includes server configuration parameters in its reports, similar to CM/Config. However, CM/Schema also generates DDL to synchronize the server configuration parameters—something that CM/Config doesn't do.

When you run a comparison job from the GUI rather than as a scheduled task, CM/Schema provides a Difference Analysis window for objects synchronized via an alter or extended alter statement. In side-by-side panes, the window displays the DDL needed to create the objects as they exist on the source and target datasources, highlighting the differences. This lets you see very clearly the differences between the objects. Selecting one or more objects on the comparison report and then choosing Synchronize from the right-click menu directs CM/Schema to generate DDL to synchronize the target for the selected objects. Icons let you schedule the execution, do a stepwise execution with various flow control options, or simply execute the full script. Of course, you can also take the DDL from the report directory and run it by another means.

Change Manager Assessment

Overall, I was pleased with Change Manager’s design and capabilities. The current separation of CM/Schema from the other components was overshadowed by some of Change Manager’s nice features—such as the ability to work with multiple databases on a single server in the same job. Its support for several database platforms, including the ability to synchronize data and schema from one platform to another, is useful for database migration projects. The Difference Analysis window with its side by side comparisons is a useful and clever visual aid. The one-click method of generating .bat files makes it simple to schedule and run jobs outside of Change Manager.

Change Manager is particularly well suited for environments with ongoing application development and migration projects. I recommend that you give it a try to see how it would support your implementation projects, safeguard your database definitions, and expedite rapid recovery from problems.

Change Manager 4.5 is planned for availability in mid 2008. Enhancements include full integration of CM/Schema functions with the Eclipse-based interface, official support for all SQL Server 2005 features, enhanced reporting, and additional export file format support.

Quest Change Director for SQL Server 1.5

Quest Change Director for SQL Server 1.5 provides tools for monitoring and comparing database schema and for deploying updates to SQL Server databases throughout the enterprise. Key features include database schema versioning, object comparison, and scheduled or real-time deployment of schema changes to multiple SQL Server instances across the enterprise. Change Director runs on Windows Vista (including x64 versions), Windows XP, Windows 2003, and Windows 2000; it requires Microsoft .NET Framework 2.0. Change Director supports SQL Server 2005 and SQL Server 2000; it doesn't support any other database platforms.

Change Director Architecture

Change Director comprises four key components: Database Browser, Log Reader, Change Tracker, and Job Scheduler. It requires access to an instance of SQL Server where it places the Change Director Repository, which stores configuration information, schema versions (called snapshots), and the server and database object definitions collected by Change Tracker. Change Director lets you use multiple repository databases—you can configure each registered SQL Server instance to use a particular repository. Change Director is secured using standard SQL Server security: Users must be authorized to use both the repository database and the target databases, as described in the Change Director Help file.

Documentation is a significant weakness of the product. Change Director documentation is available only online in .chm format, and it appeared fairly superficial to me. In most cases, it describes features only in general terms, and it doesn’t include any screen shots, which can be very useful to help you understand a new product. For example, the Help documentation doesn't include a description of the Object Scope tab of the Synchronization Details window, which displays a pair of DDL scripts for a selected table side by side after you run a comparison job, but doesn't explain what they are.

Change Director Installation

I installed Change Director on a Windows 2003 system after first installing .NET Framework 2.0. I also installed Windows PowerShell, which is required for for command-line support. Next, guided by Change Director's wizards, I created the repository on the SQL Server instance I designated, and registered the first monitored SQL Server instance. You can also install the Change Tracker and Log Reader components on each monitored server through the wizard, or you can install them later. The registration wizard browses for SQL Server instances active in the domain or in other domains.

Working with Change Director

Change Director's four main functional components—Database Browser, Job Scheduler, Log Reader and Change Tracker—operate largely independent of one another. You use the Database Browser to compare live databases to one another, with an ability to create synchronization and rollback scripts to deploy or retract schema changes for production databases. Through the Database Browser, you create either Compare projects or Custom Scripts projects.

Compare projects let you compare one database within a SQL Server instance with one or more databases from the same or other instances. The Database Browser attempts to synchronize the target database schema with the source database—that is, alter the schema definition of the target database to be consistent with the source. You can choose to ignore a variety of common differences in the comparison (column order or constraint names, for example). You can also set options to perform certain actions before or after running the comparison—taking a schema snapshot, making a database backup, or running a custom script, for instance. You can customize the scope of the compare to eliminate any object or class of objects (e.g., a trigger, all triggers, a column, a table) from the comparison.

Custom Script projects are similar to Compare projects in that they let you apply changes to a target database, but instead of starting with a comparison of two databases, you provide a custom deployment script and a target database it applies to.

Continued on page 3

A third type of comparison job uses a database snapshot as its source. Database snapshots are point-in-time images of the structure of one or more databases. You can create a snapshot on demand from the right-click menu for a database or an instance of SQL Server. A snapshot wizard lets you select one or more databases within an instance and either record the snapshot on demand or schedule the snapshot job for one time or recurring execution. You access a database snapshot by selecting the Snapshots tab from any level in the Database Browser’s hierarchy that contains the database of interest. The tab displays one line for each database for which a snapshot exists. You can view a graphical representation of the schema represented by the snapshot through the Snapshot Viewer; you can also view and save the DDL required to create the schema. To create a comparison job using the snapshot as the source, select Compare from the right-click menu for the database in the snapshot list. In the resulting window, you select a snapshot from a chronological list for the database and select a target database to compare it to.

After you define the sources and targets for a job, the next step is for Change Director to perform an impact analysis. When the analysis completes, clicking Display Impact brings up a screen listing the objects in the target database that need to be updated to synchronize it with the source, the deployment script that will implement the changes, and—most significantly—a list of potentially unintended consequences of the deployment. Impact messages warn of objects that will be dropped, of other dependent objects that will be affected by a change, and of other changes that might cause the script to fail. Messages categorized as Critical will cause the script to fail; Warning messages might cause the script to fail. Informational messages simply point out something that might be worth looking into.

When you're satisfied that the job will make the correct changes to the target databases, you can schedule it for execution using the integrated Job Scheduler or run it immediately. A rollback script is created by a successful job execution, which you can run using a Custom Script job.

In my testing, Database Browser jobs worked effectively. I made some simple changes to a database table, and using the compare feature both with a snapshot and with the active database, I was able to deploy the changes to a target database. In another test, I deleted multiple objects from a database, including fields, triggers, and constraints. In this case, the impact assessment warned of multiple possible problems; not unexpectedly, the job failed to run to completion.

Log Reader is Change Director's data restoration component. It lets you load and review log records from SQL Server memory and online transaction log files, offline transaction log files, and log files contained in backup datasets created by SQL Server or by Quest Software’s backup and recovery tool, LiteSpeed for SQL Server. You must install a set of extended stored procedures for full support—without them, Log Reader is able to read only in-memory transaction logs for active SQL Server instances. Change Director global options let you specify how frequently Log Reader checks for log updates and whether to suppress the display of DDL statements, displaying only DML statements. You can also restrict the use of database full backups when reconstructing log files and limit the number of log records Change Director reads.

I tested Log Reader by using it to recover from a couple of common errors—a dropped table and an update issued without a WHERE clause. In the former case, I used Change Director's Recover Table Wizard, which had me select the Drop Table transaction, and it quickly restored the table and its data. In the latter case, I used the Undo/Redo wizard, which requires that you select the appropriate items from the DML/DDL tab or the Transactions tab. Working from Transactions, I needed only to select a single Update transaction; the wizard went to work quickly, restoring the data to its original state, and also letting me save a copy of the SQL script it used.

Change Director's Change Tracker component uses an agent to monitor configured SQL Server instances for changes to server and database objects, and for failed logins, then stores this information in the repository. This information lets you audit activity on your servers and know quickly when unanticipated changes occur. To roll back the changes, you use a Database Browser Comparison job or work with Log Reader. Change Tracker can monitor SQL Server clusters as long as all hosts in the cluster run the Change Tracker agent and are configured to use the same repository.

Change Tracker uses the SQL Trace facility to obtain information about server activity. SQL Server writes trace information to disk on the local server, and Change Tracker reads and analyzes the trace information, sending pertinent information to the repository. It suspends analysis of trace data during periods of high SQL Server utilization. Change Tracker has two modes: Full mode, which can handle about 600 statements per second, and Simple mode, which can process up to 4000 statements per second, both according to the product's documentation. Full mode processes all trace records; Simple mode skips analysis of T-SQL batches. A limitation of Simple mode is that it doesn’t detect Create Procedure statements where the procedure already exists and Alter Database statements on SQL Server 2000.

As Figure 3 shows, when you select Change Tracker in the Change Director GUI, you'll see a hierarchical display of groups, SQL Server instances, and databases on the left and a tabbed work area on the right. The Overview tab displays summary information about the object selected in the hierarchy. If you select a group, you'll see a graph summarizing changes within the group and a small status display for each server in the group. Clicking a server displays a graph of detected changes on the server, with a status for each database. Selecting a database displays a graph of change activity on the database along with some status information—size on disk, a count of tables. The other tabs in the work area show configuration options and alert reports when you select a SQL Server instance or database, with reports of failed logins also available from the SQL Server instance level.

By default, all monitoring and alerting functions are disabled upon installation of the Change Tracker agent. You start by providing Change Tracker with the SMTP server and sender email address that Change Tracker will use for email notification. I would prefer the option to use a default global setting instead of having to enter this information for each SQL Server instance. Next, turn on monitoring for selected databases by selecting Start Monitoring from the right-click menu for each database or from its Configuration tab in the work area. Configuration tabs at the instance and database levels present options to limit monitoring and alerting to selected server and database object types. When configuring instance or database monitoring options, you also have the opportunity to define Operators—an object that can include a name, an email address, and a Net Send destination. Operators are available globally throughout Change Tracker.

Configuring monitoring, alerting, and reporting options is easy. You select objects to monitor and alerts to send by clicking a check box next to the object name; for example, Extended Stored Procedures, Database Restores, Triggers, and Roles to cite but a few. You configure notification by clicking a check box next to the listed Operator names to enable Net Send notification, and by choosing either a Summary or Detailed email notification report to override the default Disabled option. At the server level, you can configure Operators to receive periodic reports summarizing alerts and failed login activity by selecting days of the week and time of day to receive the report by email.

For my testing, I created two Operators and selected monitoring for all objects. I used SQL Server Management Studio to change a server configuration parameter using sp_configure and to delete a trigger, a constraint, and a field from a monitored database. The events were immediately displayed in Change Tracker's Overview tab for the group, instance, and database, and the email notifications showed up shortly thereafter. I would have liked to see a feature letting an administrator roll back the change from the alert display screen in the GUI, but that isn’t currently a feature of Change Director.

Change Director Assessment

There are a few things that I thought could be added or improved upon with Change Director. The first is the documentation, as I mentioned. Next, Change Director supports using a snapshot as the source object in a comparison job, but this capability isn’t integrated with the standard Create New Compare project—you have to start from the list of snapshots. When reviewing options for object mapping, I expected to find lists of tables, columns, keys, and similar objects for the designated source and target databases; instead, I found an option to map objects to alternate filegroups on the target database, but no option to map other objects.

I really liked the Log Reader component of Change Director; it was particularly easy to use and an effective way to roll back unintended changes to databases. Change Tracker is an effective monitoring tool, providing real-time notification of unwanted database changes. These two features can be invaluable in support of production applications—if that characterizes your operational environment, I recommend you bring Change Director in for a trial.

Change Director 2.0 is projected for a 2008 Q2 release. Enhancements in that release are expected to include the ability to compare the schema contained in a live database or in a backup to the schema contained in a backup; automatic preservation of data deleted by deployments that remove database objects, letting you roll back the change when necessary; and an enhanced middle-tier feature to let you offload Change Director's database comparison processing to another system when desired.

Picking the Best

Embarcadero’s Change Manager and Quest’s Change Director both have their strengths and weaknesses. Change Director’s real-time alerts and Log Reader features are great for production application environments. But in the end, I found Change Manager to be the better product and award it my Editor’s Choice. Change Manager’s multi-platform support, data comparison, and synchronization features make it particularly well suited for users with ongoing development and migration projects.