Compare and synchronize schemas of two SQL Server databases

Settings for comparing and synchronizing schemas of two databases, including in-COLLATION comparison, custom comparison options, selective SQL object types comparison, execution of prepare scripts, database backup, approval test, compatibility test

Task

A name of the task and the output directory of the task in a project directory. It can be changed before the task execution.

[Target database]

A target database to compare and synchronize. You can establish a connection to the online database or select a database structure file.

Approve target database

Allow the selected online database to be used as a target database for this task? This feature is designed to filter the target database selection to prevent accidental execution on a wrong database or on a database that does not meet certain requirements. For example, we can check if it belongs to our product, the required version, or has other required states, etc. Typically this feature is used in update packages.

See an example in the template file for more information. You can save it using the dialog box as a new file and edit it manually by following the instructions in the comments.

Test for compatibility

Test for application compatibility with the databases functionality. AutosyncDB covers the most commonly used types of objects, their properties and functionality, allowing you to effectively compare and synchronize them. However, there is some functionality that is not yet supported, is very rarely used, or is too localized for synchronization.

This test can only identifies KNOWN compatibility issues and provides additional information on them. Each message has a unique code and is characterized by a severity level (do not confuse with the severity level of errors in SQL), the threshold values for which can be specified in the task settings.

The compatibility test executes after the preparatory script has executed, but before the comparison, export, or sync script execution.

Severity levels range from 1 to 5. The higher the level, the more serious it is. The levels are assigned based on an assessment of the risk of implicit overwriting or possible loss of functionality during synchronization; its importance for the implementation of a specific architecture; belonging to the "main" or "extra" functionality; the complexity of managing this functionality using a manual script; and the degree of dependence on other database functionality. Also, the level can be increased if the functionality is directly related to security.

Max allowable severity level

Specifies the maximum allowable level of severity. Values must be in the range from 0 to 5, where zero does not allow incompatibilities and 5 tolerates any.

The compatibility test executes after the preparatory script has executed, but before the comparison, export, or sync script execution. If the compatibility test detects severity levels higher than this value, the task will be stopped, except for tasks from comparison only.

Log when severity level

Specifies the lowest severity level for messages to be logged. Values must be in the range from 1 to 5.

Ignore message types

Specifies message types to ignore. Use a comma as a separator.

Backup

Create a backup of the target database in a default server location before the updates.

Execute scripts

Execute a custom script to prepare the structure of the target database. This script will be executed before the comparison and synchronization. You have to do all special modifications in this script that can't be done by the application such as rename tables or columns, change type of an existing column with the particular data calculations and type casting, etc.

You can select the one SQL file or script listing file. To create a listing of SQL files just select them in the order of their execution in the Dialog.

Also you can use this feature just to run custom SQL files on the target database without the synchronization process.

Compare with

Compare and synchronize the target database structure with the source database structure.

[Source database]

A source database to compare and synchronize with. You can establish a connection to the online database or select a database structure file. Also the database structure can be taken from a package file (a source database structure in the package).

Common parameters

Alter database COLLATION

Use this option together with the SINGLE_USER access mode option to synchronize the default database collation. Changing the default database collation does not change the collation of existing columns. To align the collation of columns use the option for columns.

If this option is not set, the comparison algorithm will provide a comparison of databases in the default collation of the target database. Otherwise, it uses the collation of the source database.

Alter default COLLATION in columns

Use this option to align the collation of all columns with the collation of the same columns in the source database. If this option is not set, the comparison algorithm will consider equal different collations, if they match with the default collations in their databases.

Apply SINGLE_USER access mode

Turn off AUTO_UPDATE_STATISTICS_ASYNC and apply the SINGLE_USER access mode for the target database with ROLLBACK after 5 seconds. All the values will be restored at the end.

Resolve conflicts

Drop objects in the target database that conflict with the objects in the source if they need to be created or modified. This option includes checking for object name conflicts, conflicts of a clustered key/index, conflicts in object purpose (there can be no more than one clustered key in a table or default constraint for a column, two objects with the same name, etc.)

Replace tables with views or table functions

Allow replacing tables with views or table functions if they have the same name

Resolve dependencies

This option controls the resolving of dependencies between dependent objects. Always use this option to make a valid synchronization script. You can turn off this option only in a comparative analysis in order to get a report without recreating of dependent objects.

Often to change one object other objects must be deleted (and then re-created) that other objects depend on the first. For example: to alter a column you must drop the index; to change a scalar-valued function you must drop the default constraints, where it is used, etc.

Comparison of objects body

Additional rules of objects body comparison.

Ignore outer whitespace and line breaks

Ignore whitespace and line breaks at the beginning and at the end.

Ignore any whitespace and line breaks

Ignore any whitespace and line breaks outside the 'string literals', "double quoted string literals or identifiers" and [identifiers].

Ignore comments

Ignore the comments.

Compare in collation

Compare objects body in the default database collation except 'string literals' and "double quoted string literals or identifiers". Otherwise, the ordinal comparison rule will be used.

Comparison of computed expressions

Additional rules of computed expressions comparison.

Ignore outer parentheses

Ignore the outer parentheses at the beginning and at the end.

DISABLED

Set this option to change the DISABLED property of objects. Otherwise, the comparison algorithm will try to inherit these values ​​from existing objects.

It is not recommended the presence of disabled key-constraints and indexes in the source database. They superfluous dependencies on the table or view for which they were created, can disable another dependant indexes, and cannot always be properly processed by the comparison algorithm.

NOT FOR REPLICATION

Set this option to change the NOT FOR REPLICATION property of objects. Otherwise, the comparison algorithm will try to inherit these values ​​from existing objects.