SQL Data Examiner synchronization settings

You can configure synchronization settings on the second step of the synchronization wizard. All options are distributed across three tabs which are described below.

General tab

The default values of the General tab options can be set in the Options window (Tools → Options → Synchronization):

Synchronize identity columns

The option affects the insertion performed by the program. If it is enabled, SQL Data Examiner inserts the values of identity (auto-increment) columns from the source database into the target database. Otherwise, the identity columns in the target database are specified automatically.

This option works similarly for different databases.

Fix current/last sequence values when applicable (PostgreSQL and Oracle only)

This option adjusts the current value of sequences, so that after synchronization there are no errors when inserting data in third-party utilities.

Disable triggers

The option disables triggers before synchronization, so they cannot be fired and there are no unplanned changes in the synchronization results.

Disable foreign keys

Tables are synchronized in turn. This may lead to the situation when one table has already been synchronized, but another one that refers to it by a foreign key has not. To avoid this, foreign keys can be disabled and enabled again after synchronization.

Disable UNIQUE constraints and unique indexes

Similar to how it is implemented for foreign keys, unique constraints and unique indexes can be disabled while synchronization is in progress to avoid their violation.

The option is available for the following databases: Oracle, Postgres, MySQL, and SQL Server.

SET ANSI_WARNINGS OFF (SQL Server only)

If enabled, SQL Data Examiner adds the command "SET ANSI_WARNINGS OFF" at the beginning of the synchronization script.

Learn more here:
https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver16.

Error handling

There are the following options to specify how the program behaves if an error occurrs during synchronization:

• On Error option - defines how SQL Data Examiner handles an error
• Rollback successful changes on error option - determines what the program should do with the changes that had been made before the error occurred

The On Error option has the following settings:

• Abort synchronization - the synchronization is terminated immediately.
• Continue synchronization but skip current table - the synchronization of the current table is interrupted, but the process proceeds for other tables (if present).
• Ignore error and continue updating current table- the current table is updated in spite of the error.

The following settings are available in the Rollback successful changes on error option:

• Rollback all changes - all changes will be rolled back in all tables.
• Rollback changes only in a table where an error occurs - the changes will be rolled back in one particular table.
• Don't rollback any changes - the changes will be left untouched.

Please note that not all Rollback successful changes on error settings can be applied to a certain On error setting, see the table below:

"On Error" setting	Application of "Rollback successful changes on error" settings
Abort synchronization	All three rollback settings can be used.
Continue synchronization but skip current table	Rollback all changes is not available: there is no point in rolling back all changes if synchronization continues.
Ignore error and continue updating current table	Only Don't rollback any changes setting is available.

The rollback options partially depend on performance options, e.g. the changes in the target database cannot be rolled back if Use Bulk Load option is applied (see the section Use Bulk Load). If you set a certain transaction isolation level, all changes should be reversible (see Specify a custom transaction isolation level section).

The options are applied to tables by default. However, if a user defines the synchronization order manually, the On error and Rollback successful changes on error options are applied to separate operations. More information about this is available in the section Synchronization Order.

Performance Options tab

Specify a custom transaction isolation level

This option prevents third-party readers from accessing the tables being modified. It can be applied only along with the "Rollback all changes" setting (for more information about rollback settings see Error handling section). By default, READ COMMITED is applied to SQL Server, Oracle and PostgreSQL databases, while REPEATABLE READ is applied to MySQL databases.

Batch mode: group consecutive SQL statements into a batch

If enabled, statements are not executed one by one but are accumulated in a batch. Only when the batch file reaches the volume specified in the "Batch size" setting, the commands are sent to the server for execution. This saves time, because server-to-server network interactions are minimized.

Large fields into files

If enabled, SQL Data Examiner saves large values (documents, videos, etc.) as files and inserts links to the files into the synchronization script. The minimum size of a value to be saved is configured in the "Threshold" setting.

The option is available for SQL Server databases and can be applied only when the target server is running on the same machine where the program is running. Otherwise, SQL Data Examiner will not be able to upload the file to the server.

Copy data direct from source database

This option considerably speeds up the synchronization. The data from the source database is not included into the synchronization script, the following syntax is used instead:

INSERT target_db.target_table ... SELECT ... FROM source_db.source_table WHERE ...

The option can be applied to SQL Server databases only and is available when target and source databases are on the same server.

Note: if this option is enabled, the script will transfer the data that is in the source database at the time of synchronization, not the data that is there at the time of comparison.

Use Bulk Load

If enabled, the INSERT commands are executed using bulk copy utilities: BCP utility for SQL Server and SQL*Loader for Oracle databases. This speeds up the synchronization process.

The "Use Bulk Load" option cannot be applied along with the following Rollback successful changes on error settings: "Rollback all changes", "Rollback changes only in a table where an error occurs". If the "Use Bulk Load" option is enabled, the Synchronization Order cannot be specified manually and all operations are executed in a strict order: DELETE, UPDATE, INSERT.

Synchronization Order tab

On this tab you can manually set the synchronization order of tables. By default, the operations are performed in the following order: DELETE, UPDATE, INSERT.

If you want to define in which sequence the operations will be performed, check the "Define synchronization order on operation level" checkbox, and you will be able to configure the order of operations manually. The On error and Rollback successful changes on error options will be applied to separate operations in this case.

In the table below we will describe how the program behaves in case of an error depending on the chosen On error and Rollback successful changes on error settings:

On Error option	Description	Available Rollback successful changes on error option	Description
Abort synchronization	The synchronization process will be stopped.	Rollback all changes	All changes made during all operations in all tables before the error will be rolled back.
		Rollback changes only in a table where an error occurs	All changes made during the operation before the error will be rolled back. The changes made during other operations in the same table and all completed changes in other tables will remain.
		Don't rollback any changes	None of the changes made before the error will be rolled back.
Continue synchronization but skip current table	The operation is stopped. Further operations for the same table and all operations for further tables (if present) will be performed.	Rollback changes only in a table where an error occurs	Only the changes made during the operation affected by the error will be rolled back.
		Don't rollback any changes	None of the completed changes will be rolled back, including the ones made during the operation before the error.
Ignore error and continue updating current table	Only the record affected by the error will be skipped, and the operation will be performed for the next record (if present).	Don't rollback any changes	No changes will be rolled back. This is the only rollback option available if an error is ignored.