Compare and synchronize schemas of two SQL Server databases

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.

Description of the general parameters for comparing and synchronizing the structure of schemes of two MS SQL databases, including the choice of compared structures, the execution of preparatory scripts, the comparison in COLLATION, the parameters of objects body comparison and computed expressions
[Target database]

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

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.

Synchronize with

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

[Template database]

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

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 template 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 template 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.

Rebuild children and hard-dependant objects

This option controls the rebuilding of the dependent objects. Always use this option to make a valid synchronization script. You can turn off this option only in an analysis in order to get a report without 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 the column you must drop the index; to re-create 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 external 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 and identifiers.

Ignore comments

Ignore the comments.

Compare under collation

Compare the [identifiers] in brackets by means of the default database collation. Otherwise, the ordinal comparison will be applied.

Comparison of computed expressions

Additional rules of computed expressions comparison.

Ignore external whitespace and line breaks

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

Ignore the outer round brackets

Ignore the outer round brackets at the beginning and at the end.

Ignore any whitespace and line breaks

Ignore any whitespace and line breaks outside the string literals and identifiers.

Compare under collation

Compare the [identifiers] in brackets by means of the default database collation. Otherwise, the ordinal comparison will be applied.