Data Compare & Sync

Omni Loader reads all selected tables and compares their contents in source and target database. This works efficiently on billions of records and across different database engines. Table structure between source and target does not need to be exactly the same but it needs to be compatible.

Every value in every row in every table is compared and differences noted.

Each table compared needs to have a primary or unique index in the source database which we will use to identify equivalent rows and slice the tables. While this is not a strict requirement (you can set up your custom comparison key), make sure to avoid non-unique keys, or non-indexed keys.

After comparison, you can examine reported differences in an easy-to-use grid, apply the optimal diff to the target database to bring it in sync with the source, or both.

This project type should be used when you want to compare the databases, or when you want to non-destructively bring the target in sync with the source, on-demand. Omni Loader will run the optimal set of INSERT+UPDATE+DELETE statements on target tables.

For this project type, target tables have to be in place. If you use different naming than in the source database, you can use Project options mapping rules to adjust detection of equivalent tables and columns.

It is not uncommon to run Full Table Copy project once, and afterwards run Data Compare & Sync project periodically.

Please note that this on-demand comparison and sync process is not transaction-aware. It runs with multiple database connections in parallel so write activity on the source during comparison will interfere with the detection and cause the referential constraint errors when apply the diff. That is usually not a problem because next run will pick up on changes missed.

This project type is great to run overnight when there is low or no write activity on the source. Full database comparison does cause a certain load on both databases so use your discretion when deciding how frequently to run this task, keeping in mind its potential impact on the system.