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.
End of Article
Prev. page
1
2
[3]
next page -->
