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.