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.