SQL Server Magazine

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

Prev. page     1 [2] 3     next page