Requirements and Limits

This page lists what a comparison needs, what it compares and how, and what it does not report.

Supported databases

Every Source Database can be compared with every Target Database of the list. Amazon S3 as a target cannot be compared, there is no database to read.

What has to be in place

• The Capture Process and the Apply Process are running. The notes of the chunks reach the Apply Process only through them.
• The tables are replicated by the Capture Process and exist in the Target Database. A table that the Capture Process does not replicate cannot be compared. The Apply Process takes the names of the Target tables and columns from its transformation rules, so renamed tables and columns are compared under their new names.
• The table repi.ValidateTables exists in the Source Database and in the Target Database. The prepare commands create it, and the command validate database warns when it is missing (see Validate Database).
• The logins have the rights that are listed in Database Privileges: the login of the Capture Process reads the tables and changes repi.ValidateTables, the login of the Apply Process reads the tables and creates a temporary table for the keys.
• MySQL as the Target Database: the server has to allow local_infile, the comparison loads the keys with LOAD DATA LOCAL INFILE.
• Redshift as the Target Database: the keys are loaded through the S3 bucket and the IAM role of the Apply Process (the parameters s3.bucketname and s3.iamrole).

Tables and keys

• The rows of the two databases are matched by the key. A table is compared when it has a primary key or a unique index. A table that has neither is skipped (the message is “No primary key or unique index. Skipped.”) unless you name the key columns yourself (the parameter keys, “Add key” in the Web UI).
• The repair merges the rows by the primary key or the unique index of the table. A table that is compared with key columns of your choice can be compared, but it should not be repaired.
• The columns that are compared are the columns of the table of the Source Database, except the columns that are excluded by name (excludecols) or by type (excludetypes).

How the data types are compared

Values that the databases write differently are converted to one form before they are hashed:

Large objects (text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), Oracle CLOB, NCLOB, BLOB, LONG RAW) are read completely and hashed as a whole. A big value makes the comparison slow. Exclude these columns (excludecols) or their types (excludetypes) when their content does not matter.

A function of a task (mapcols, maptypes) works on the converted text. It is the way to make two values equal that the databases write differently, for example to cut a date to its day. The function is used in the queries of both databases, so it has to be valid in both of them.

What is not reported

• Rows that exist only in the Target Database. The comparison starts from the rows of the Source Database.
• Differences of the structure: indexes, constraints, triggers, defaults and the data types themselves. Only the values are compared.
• Columns that are excluded, and the timestamp (rowversion) column of SQL Server.
• Rows that are outside of the where condition of the task.
• A NULL on one side and an empty string on the other, they are equal for the comparison (see the table above). Oracle does not distinguish them itself. In a replication between two SQL Server databases such a difference stays unnoticed.

A row that the Apply Process leaves out on purpose, for example with a data filter, is reported as missed, and a column that a transformation rule changes is reported as failed. Exclude such tables or columns from the task, or restrict the rows with the where condition.

Data that is changing

A comparison of a database that is in use is not a snapshot. The compare process reads a chunk, a moment later the note of the chunk is committed, and the Apply Process compares the rows after it has applied every change that came before the note. A row that is changed in the Source Database between the read and the note can therefore be reported as failed although the Target Database is right. Such a row does not fail again: run the repair (it reads the reported rows from the Source Database again and compares them once more) or compare the table again, and only the rows that still differ are real differences.

Running tasks

• One task of a Capture Process runs at a time, a second run is refused with the message “Process is active”. The tasks of different Capture Processes can run at the same time.
• The comparison reads the tables of both databases: the Source Database through the connection of the compare process, the Target Database through the Apply Process. The rows that are compared are read by the Repstance server and hashed there, so the comparison also uses the processor and the network of the server. A smaller Data Validation %, a where condition and the incremental mode reduce the load.
• When a readable secondary is configured for a SQL Server Capture Process (the parameter secondary), the compare process reads the chunks from the secondary. Keep it up to date: the changes that the secondary has not received yet are reported as failed rows.
• The files of the chunks wait in the trail folder until the Apply Process has compared them. A stopped Apply Process lets them pile up, and a big table with Data Validation % 100 makes big files. Check the disk space and start the Apply Process before the task.