Introduction
Change Management is probably one of the most crucial tasks that developers and/or administrators face today. The risks for production systems associated with every new release or even patch are immense. And although these risks can never be completely eliminated, one can minimize them by an extensive testing period and a sophisticated change management workflow.
The purpose of this paper is to explore how this essential task of change management in SQL Server environments can become more reliable, secure and effective by using a specialized tool that is designed exclusively for this purpose: Embarcadero Change Manager.
Embarcadero Change Manager
Embarcadero’s Change Manager application suite is a sophisticated set of tools to compare data, schema, and configurations. Data can even be compared across database and RDBMS system boundaries. Change Manager supports multiple database platforms. The version tested supports the following platforms:
IBM DB2 LUW
Oracle
Microsoft SQL Server
Sybase Adaptive Server
Installation, System Requirements, supported SQL Server versions
You can download a fully functional 14-day trial edition from the product’s homepage, which you can find at http://www.embarcadero.com/products/changemanager/index.html
Embarcadero Change Manager can be installed on any machine meeting the following requirements:
Hardware:
1.2 GHz processor1 GB RAM
500 MB of disk space
SVGA (2 MB) high resolution monitor (1024 x 768 or higher) with high colour (16-bit)
Software:
Change Manager is supported on all Windows platforms with the exceptions of Vista and 64-bit Windows XP. Internet Explorer (with SP1) is required to be installed before Change Manager can be installed. As an aside is Change Manager also available for the Linux operating system. While SQL Server itself obviously doesn’t run on Linux, this version might be interesting for DBA`s and developers who need to connect to SQL Server from Linux.
The supported SQL Server versions are: SQL Server 2000 and SQL Server 2005.
The installation of Change Manager is completely seamless and wizard-driven.
Once the installation has finished, Change Manager can be launched.
Upon the first launch of Change Manager a very handy feature kicks in: the “Import Datasources” dialog. This dialog only appears when Change Manager detects that other Embarcadero tools, such as DBArtisan and certain versions of RapidSQL are also installed on that machine. These tools can import data sources, which in turn relieves you of the time-consuming task of registering all datasources again and again in each tool.
As you can see from the above screenshot, 1 data source has been imported. Once this one-time import operation has finished, the Change Manager Workbench is launched:
Comparing Data using CM/Data
One of the two main tasks that you can accomplish with Embarcadero Change Manager is the comparison and synchronization of data in two registered data sources. You can launch this part of Change Manager via the “Compare Data Now” link. This launches the Data Comparison Job Editor, which is shown in the screenshot above.
For every comparison project you need to define a source and a target. The source is that part which acts as “master” or “reference”, while the target is that part that is compared against the source and, if needed, updated to be an exact copy of the master once the comparison is done. Change Manager lets you easily select source and target of a comparison just by clicking on the “Select Datasource” link as is depicted in the screenshot above. Data sources can also be dragged in as sources and targets from the data source explorer.
After clicking on the “Select Datasource” link the Datasource Selection dialog appears. You use this dialog to tell Change Manager which SQL Server instance will act a either source and/or target of your comparison project. As you can see in the screenshot above, there is already one entry in the list of available servers. This entry came from the automatically started import of shared data sources among several Embarcadero products upon the first start of Change Manager. When the list is quite long, you can filter by entering search criteria in the textbox of this dialog. As you can see, you don’t need to know the full name of the server, as this search supports wildcard characters.
In case the SQL Server instance that hosts the database you wish to use in the comparison is not listed, you can easily add a new data source by clicking the “New Datasource” button.
The “Datasource Wizard” is invoked and reveals one of the biggest advantages of Change Manager: its ability to work in a heterogeneous database environment. The most commonly used systems are natively supported. In my case I will stick to the system I know best and that is SQL Server, although I have also successfully executed a data comparison job between SQL Server 2005 and Oracle 10g XE.
Once you have decided on the database system to use, you specify the connection parameters. This procedure is quite familiar to anyone using SQL Server and shouldn’t be difficult at all. After testing the connection, the new datasource is ready to be used.
Options
Now that the source and the target of the comparison have been set, it is time to have a look at the various comparison options which Embarcadero Change Manager offers.
One of the first notable options is the “Compression Comparison” option. As the name already indicates, the option can be used to compress the data on the server before it is send to the client. Under certain circumstances this option offers a great performance advantage.
Next to the execution options, Change Manager offers a variety of options that apply to how the data is compared. As you can see from the screenshot above, the option “Match Empty Strings with Nulls” is checked by default, while all other options have to be manually enabled, if desired.
Final category of options is the “Resolution Options” category. For the sake of security of your data, both options are enabled by default and should stay this way.
Mapping databases
At this point both source/target SQL Server instances and options have been set, now it is time to take care of the database pairs participating in the comparison.
By default matches Embarcadero Change Manager databases on the source and target instance of SQL Server that are of the same name. That means that database “master” on the comparison source instance is automatically matched with database “master” on the target instance, while a source database “Northwind2” has to be manually mapped to its target equivalent of “Northwind”, because there is no database of the same name within the target instance.
Custom mapping of databases with Change Manager is quite easy. Just expand the dropdown box and choose the databases.
For my example I am going to compare SQL Server’s “Northwind” database in the source instance to a database “Northwind2” in the target instance. “Northwind2” is an exact copy of “Northwind”, except that I have executed the following statement against the “Northwind2” database:
By executing this statement I have ensured that there is a difference in the data of both databases. This gives Change Manager something to find during the comparison.
You start the initial comparison between the participating databases by clicking on the “Inspect now” link. In the screenshot you can see that 100% of the tables in both databases are mapped. That means that Change Manager was able to automatically find a corresponding target table of the same name for each table in the source database. I’m tempted to say that this will come close to most real-world scenarios. However, if this happens not to be the case, you can manually map tables.
All you need to do in this case is to select the table pairs to be matched in the dialog shown in the screenshot above. Just place the cursor in the field holding the table name (either in the source or target row) and choose the correct table name from the dropdown box.
Comparison Settings
Default behaviour in Change Manager is that the Primary Key constraint is chosen to be the comparison key, if one exists. If no primary key exists but a unique constraint exists, Change Manager will fall back to using the unique constraint as the match key. Again, this will likely be the case for the majority for real-world scenarios. While I believe that every table should have a Primary Key (or at least a unique constraint or unique index), and that this key is the natural choice for the comparison, you can influence this default behaviour by picking a custom comparison key. For illustration purposes let us change the comparison settings for the Categories table. You can change these setting by clicking on the filter button with an ellipsis (“…”), on the right of each grid row.
Clicking the filter button brings up the “Columns, Comparison Key and Filter” dialog. In the screenshot above you see the default settings as automatically detected by Change Manager.
First thing to do is to change the comparison key from “PK_Categories” to “Custom”. Now you can not only change the column pairs acting as comparison key, but you also map columns as you like to, if that should be necessary.
Especially for larger tables with many rows it might be advantageous to be able to split the comparison of the whole table into smaller parts in order to avoid more performance penalties than absolutely necessary. One way to split the rows participating in to comparison is to use a custom WHERE clause to filter just those rows matching the criteria. Change Manager offers you the option of specifying such a custom WHERE clause on the second tab of the “Columns, Comparison Key and Filter” dialog.
Another interesting feature of Change Manager is its ability to specify two separated WHERE clause with the one being applied to the rows of the source table, while the other one is applied to be rows of the target table. This feature offers in addition to the other customizable settings a great flexibility in data comparison jobs.
Running a comparison job
Now that the basic work is done, it is time to run the comparison.
A click on the button shown in the screenshot above starts the actual comparison. But before we get to the comparison results, let me point out another handy feature of Change Manager.
Change Manager can generate a command line script from the currently active comparison project. You can use this script then to schedule your comparison jobs according to the usage patterns of the databases used in this comparison.
Let me now get back to the comparison results from my just-executed comparison job. The first thing to note is that a new tab is displayed upon successful completion of a comparison. When you switch to this “Results” tab, you see a screen similar to the one depicted in the image above. From the screen you get a good overview of the comparison results, as you can see a ratio of matched to unmatched rows, as well as an option to generate a SQL script that will resolve all differences in either direction.
From this overview you can further drill down the comparison results by clicking on the “View” results link. Here you can see what the comparison looks like at a table level . As expected, Change Manager has detected my manipulation of the Customers table and has marked 91 rows as “Different”. All other tables are identical in terms of rows compared.
Synchronizing data
In order to rectify this difference now and synchronize the data between source and target, I can double-click on that particular row in the grid to inspect the differences in detail and to decide how to proceed.
Now you inspect the differing rows in two grids as shown above. Change Manager automatically marks differing values in red so that you can easily notice any difference. Even at this point you still have several options how to proceed with the synchronization. You can make “Target” equal to “Source” and vice versa, or ignore the differences at all.
Note that only marked rows will be synchronized, so it is necessary to use the “Select All” button, to mark all rows checked before you proceed with the “Generate a SQL Script” step.
Embarcadero’s Change Manager does not support a direct synchronization option, but rather always generates a SQL script, which, of course, in turn can be immediately executed against the database. One clear advantage of this procedure is, in my opinion, the security aspect of this method. By adding this additional step you have another chance of thinking about what you are about to do. The risk of accidentally executing a script that might cause damage to production data is, again, reduced. The script can also be saved off, providing an audit of what changes were made.
In the above screenshot you see what a SQL script generated by Change Manager looks like. For the sake of simplicity I have chosen to synchronize just one row.
Comparing Configurations using CM/Config
One of the most remarkable features of Embarcadero’s Change Manager is that you can use it to compare server configurations. This again demonstrates the target audience for this tool. Administrators working in large server farm environments will undoubtedly highly value this feature as it is just a matter of minutes to compare one or more target server against a master server.
The GUI interface for configuration comparison jobs is almost the same as the one for data comparison job. Therefore in this chapter I will concentrate on the functionality rather than the GUI.
The first notable difference is the “Select Standard” link. Change Manager allows you to create standard settings that can be used as a comparison benchmark against other systems. Standards can contain static parameter values that are defined by a user. They can also contain dynamic parameter values that are linked to a particular server that is considered to be s “Standard”, . I will explain how to create such a “Standard” a little bit later on. Next thing you note is that you not only can use live sources, but also archived sources. As this feature needs to be explained in more detail, I will come back to “Archives” in just a moment.
As I mentioned some lines above, you can use Change Manager to compare one source against one or more target servers.
Once source and target(s) have been determined, you need to decide what configuration settings should be included in the comparison. The above list illustrates what this list of settings looks like when you run the comparison against SQL Server 2005 instances. All options are checked by default and will be included in the comparison.
That’s all Change Manager needs to know in order to execute the comparison job. Again, you have the option to have a script generated that can be scheduled according to your needs.
In the above screenshot you see the summary of the configuration comparison. As you might guess, the results from comparing a server with itself will never be really meaningful. So to provide a more meaningful example, I have deliberately inserted a screenshot from a Change Manager training presentation, which can be downloaded from the Embarcadero website.
You can easily determine if the target server complies with your source server in terms of configuration or not. And, if so, where both serves diverge in their settings.
Creating a “Standard”
Some lines above I mentioned that you can define a “Standard” server configuration. Now it is time to come back to this topic and explain this in more details. A Standard in Change Manager terms is a configuration that you define as you own “optimum” configuration. This standard can act then as reusable benchmark server against which other servers and their configuration will then be measured. A “Standard” makes it very easy to check if any newly-added servers comply with this standard, and to check whether a server still complies with the standards after some time has gone by, and changes to the server might have been made by the IT infrastructure department.
From Change Manager’s GUI you have access to the Standards pane that lists all currently defined “Standards” and from which you can create new Standards.
FFrom the context menu of the Standards pane choose New -> Configuration Standard to create a new Standard.
The steps to create a new Standard are fairly easy and straightforward. By now you are familiar with the GUI and know that you can either drag and drop a datasource to the Standard Sources pane or, alternatively, add a datasource by clicking on the appropriate links.
Thereafter you can specify for each single configuration setting the comparison operator and the standard type: whether it is a fixed value (“Static”) or linked to a database that is considered to be a standard “Linked”, nothing that if desired, different parameters within a single standard can be linked to different sources. Once this is done, your “Standard” is ready to be used in comparisons.
The Change Manager GUI offers you a shortcut to use a Standard in configuration comparison job. From the context menu of the Standards grid, just right-click on a particular treeview entry and select “Compare”.
This opens a new comparison job tab with the chosen Standard preselected as source. From there you then only need to add the comparison target (or targets) and run the comparison.
Working with Archives
A little bit earlier on I mentioned that you can not only use live data sources for configuration comparisons. Now it is time to come back to this and introduce you to another great feature of Change Manager: its ability to store and (re)use historical data in configuration comparisons.
At any time you are able to take a snapshot of the then-current configuration of any given data source that you have defined. In order to create such a snapshot just select Archive -> Configuration from the context menu of the particular server.
Change Manager opens an Archive tab for the chosen data source. In my case, this is a first time comparison, so no previous information about this server’s configuration exists, and thus, no information about historical records is displayed on screen.
After the configuration snapshot has successfully been taken, Change Manager automatically generates the historical tracking records, which you can find on the “Compliance” tab of the Workbench.
General options of Change Manager
Change Manager comes with a myriad of settings and options that allow you to tweak and tune almost every aspect of the tool and customize it to exactly suit your needs. Since I just have described the ability to measure the compliance of a server against a defined Standard, let me use this as showcase for how you can customize Change Manager.
Using the Preferences dialog you are able to define specific threshold when a server is “in compliance”. Ideally this should be the case when there is a 100% match between both configurations, but it doesn’t necessarily have to be 100%. You can change this default value to any arbitrarily chosen value you wish to use for “in compliance”. Likewise you are also able to define when a warning should be issued, or when a server’s configuration fails the compliance test.
While CM/Schema itself provides mechanisms to generated HTML reports and include them into mails, for example, all Change Manager components mechanisms to store a report output into.csv format. That way you can use the output from Change Manager with many other third-party applications and format it with your favourite tool, such as Microsoft Excel.
In the screenshot above I have chosen to output a configuration comparison to a command line batch file E:MyDemo.bat with the option to save Change Manager’s output in .csv format to a file.
After executing a .bat file you will notice that a file named “ConfigArchive20v5.csv” has been generated in E:. Note that you are able to customize the naming convention of the generated file. For demonstration purposes I just accepted the default settings here.
This file can now be opened with Microsoft Excel.
The above screenshot shows the output .csv file opened with Excel. Now you can use all built-in formatting and calculating functions in Excel to produce your final report. I like the option to produce output that is readable for Excel, because Excel makes it so incredibly easy to produce good-looking charts based on calculations in just a matter of seconds.
Comparing Schemata using CM/Schema
The part that I haven’t described yet is the part of comparing database structures. This part is covered by a separated tool named CM/Schema.
The installation of CM/Schema is straightforward and finishes without any problems. So, I’ll leave out the installation process altogether and focus on the functionality instead.
In the above screenshot you see the CM/Schema GUI. From the Task window you can start all essential tasks. Let’s have a look at each of these tasks:
Capture an Archive
CM/Schema allows you to compare live databases, and, additionally, create and compare archives. Think of archives as snapshots of a database in time. These archives can then be browsed, or used for other purposes. For example they can act as “reference” or “master” against which you can compare live databases, or other archives, to generate a historical report indicating what changed between points in time.. To capture such an archive, just click on the corresponding icon in the Task Window.
At this point it is worth mentioning, that all tasks CM/Schema can fulfil are completely wizard-driven. Thus, it is fairly easy to use the tool and achieve the results you expect.
Naturally, the first part in create an archive is selecting the SQL Server instance on which the databases resides that you want to capture. CM/Schema allows you to create archives of more than one database at a time. Just mark all databases that you want to include.
Once it is clear which databases will participate, the next step is to decide upon the database objects to be included in the archive. By default, all supported object types are checked. So, when you want to archive all object types, just accept this default setting. There is also an option to ignore system objects, which is useful in cases where you do not want to capture system objects such as the database catalog.
The next step is to decide what basic and additional operations should be performed on the archive. CM/Schema not only supports the creation of archives, but additionally also the extraction of the DDL in one large or multiple smaller files and, if you have specified and source control system, you can directly add the archive to your chosen system.
In CM/Schema lingo, the set of instructions it takes to create an archive is stored in a job. Each job has its own name and description.
Each job can be scheduled separately for later execution using the Windows Scheduler or your scheduling tool of choice. Jobs can also be executed immediately at this point.
Upon each successful execution of the job, a new archive version is added to the history.
All information captured during job execution and about the job itself can be viewed in the right grid.
Compare Databases
Another primary function of CM/Schema is the comparison of databases and/or archives. This task is also completely wizard-driven.
As I have mentioned some lines above you can use CM/Schema to compare live databases and archives in all possible combinations. For the purpose of my discussion here I will compare the archive I just have created with a live database.
Here the next step is to select the archive you wish to use in the comparison process. As you can see from the screenshot above, you can use any archive on any registered datasource for the comparison. Worth mentioning here is the fact that you can also use archives created with a previous version of CM/Schema.
This image illustrates the various options you can activate or deactivate for the comparison. There are several options which are activated by default, while others need to be manually activated. The above screenshot shows the default setting.
For databases with multiple users/schemas you can specify the scope of the comparison. The most comprehensive option is to compare all objects, irrespective of the owner. This is the default option. However, it is also the option that requires the most server resources and takes the most time. This can have a considerable effect on the runtime of the comparison process, especially on larger databases with many objects belonging to different users/schemas. For these cases, CM/Schema offers a feature to limit the scope of the comparison to either a specific user/schema, or to manually select a particular set of objects to include in the comparison.
The next step is to specify the objects being compared. From the above screenshot you might realize that certain object types specific to SQL Server 2005 are missing. For a pure SQL Server shop this might represent a drawback, but I’m tempted to say that most customers of CM/Schema run a heterogeneous database environment so that this drawback doesn’t weigh in too much.
A quite remarkable feature I would like to point out is the option of being notified only if differences are detected. So, everything is just working fine until you get a notification in your inbox.
After the comparison job is executed, you can examine the results in the main grid. In my example there is only one difference detected: tempdb.
A double-click on a list entry opens a new tab in which the particular comparison results are shown in detail. In the left pane you can see the code for the particular object in the comparison source, the right pane shows the corresponding code in the comparison target and the bottom pane shows the synchronization script.
At this point you have the choice to execute the synchronization script directly or schedule it for off-peak execution. From the screenshot above you see that you can execute the synchronization script for each detected difference independently from each other.
Migrate a Database
The process of “cloning” or “recreating” a database either completely or partially from either a live datasource or an archive is called “migrating a database”.
In this wizard-driven process you first select which datasource (either live database or previously captured archive) is used as migration source and to which database(s) the chosen objects should be transferred.
An interesting aspect of this feature is that you are enabled to roll out a database object to one or more target databases. That makes this feature highly suited for rolling out objects that are common to multiple databases at once. One typical application would be the roll out of common help procedures and/or function.
Monitor Datasource
In most environments, average users do not have access to privileges like creating and/or modifying permanent database objects. These tasks are typically carried out by the database administrator. However, what if you want (or need) to make sure? In these cases, the “Monitor Datasource” feature is a good choice. Monitor datasource is a roll-up of the “archive capture” and “compare” job types, capturing an archive and automatically comparing it against its previous version in a single step.
A “Datasource” is a SQL Server instance, which means that you can monitor multiple databases hosted on this instance at once.
Once you have finished the “Monitor Datasource” wizard, a job is created which can either be schedule to run periodically or started manually.
To start the job manually just right-click on the job’s node and choose “Capture New Version…” from the context menu.
Manage Schema Jobs
To effectively manage all jobs , CM/Schema offers an intuitive GUI from which you have full control over all functionality related to job execution.
As you can see from the above screenshot, some functions are directly available from the toolbar, while others are accessible via the context menu.
For example, if you want to restore a database to a previously captured point in time, you can do so by choosing “Restore” from the context menu of the treeview entry, for the snapshot that you wish to restore.
Apart from the various options and settings you can switch on or off in such cases, you need to be aware of the fact that such on operation can potentially cause data loss, particularly in cases where newly-created objects are to be dropped.
In common scenarios you may not want to restore all database objects to a previous stage, but rather only selected ones. This can also easily be done with CM/Schema. Just navigate in the treeview to the version entry that contains the snapshot of the database object that you want to restore. In my case above, I’m going to restore a stored procedure to a previous stage. Once you have marked the “Procedures” entry you will see all objects of that type in the grid view on the right. From there you can locate the procedure you want to restore and by double-clicking you open the embedded DDL viewer that shows you the DDL for this procedure in this version. This DDL viewer also you to directly restore the procedure, or compare it with other version, or even just view this procedure’s history.
When you decide that you want to view the procedure’s history, the above dialog opens; showing all history entries recorded for this object. From this dialog you can easily navigate to other captured versions in order to view and/or restore them.
Summary
Embarcadero Change Manager provides the following key features:
Compare and synchronize schema, data and configuration differences between data sources across RDBMS boundaries.
Compliance monitoring.
Automated execution of all its tasks via generated SQL scripts.
Advantages
Cross-platform environment. Change Manager supports Oracle, Sybase, DB2, and Microsoft SQL Server databases.
The tool does not require any additional objects to be created in the SQL Server system databases.
Well designed extensible user interface.
Easy and intuitive to use.
Fast and reliable, even on larger databases.
Disadvantages
Not 100% SQL Server 2005 ready.
Quite expensive.
A trial period too short. But you might get yourself into contact with Embarcadero to see if an extension of this trial period is possible.
Conclusion
Embarcadero Change Manager is a tool aimed at the high-end enterprise customer. The ability to work seamlessly with all major current database platforms ensures its unique position in its market. It is easy and intuitive to use and makes synchronizing databases a breeze. If you are in a position where you need to work with heterogeneous database systems, you should definitely have a look at this tool as it can make aspects of your working life much easier.