Notes & articles

Release notes - version 9.5 EAP

Thank you for participating in the SQL Compare 9.5 early access program.

SQL Compare 9.5 is still in development. If you have any comments, requirements or suggested improvements, please let us know.

What's new in SQL Compare 9.5?

• Migration script support
• Table mapping
• Comparing versions from SQL Source Control
• New command line switches
• New project options
• Connecting to Denali servers
• Transaction Isolation Level application option

Migration script support

SQL Source Control 3.0 enables you to create migration scripts between two versions of a database. Migration scripts are customizable change scripts that are committed to source control and re-used in deployment.

This can be useful, for example, if you add a NOT NULL constraint to a column in development and want to deploy this change to your testing environment. If you try to deploy this change using SQL Compare, the synchronization script will fail because a default value is required; you can specify this value using a migration script.

For full details, see: SQL Source Control 3.0 early access release notes.

Table mapping

SQL Compare 9.5 allows you to map together tables and columns with different names. This can be useful to prevent data loss when synchronizing tables or columns that have been renamed.

For example, if you map the table TableA in the source to the same table that has been renamed as TableB in the target, SQL Compare will compare the table as an object that exists in both data sources. When you synchronize the table, the name change will be scripted using the sp_rename system stored procedure; the table is not dropped and re-created.

To compare tables and columns that are not automatically mapped, click the Table Mapping tab of the Project Configuration dialog box:

The upper pane displays tables that are fully Mapped or have Partial mapping. The lower pane displays Unmapped tables.

Note that:

• If a table has a Partial mapping, some of its columns are not mapped, and cannot be compared.
• To set the column mappings for a table, click the Status link for the object you want to re-map.

Comparing versions from SQL Source Control

In SQL Compare 9.5, you can select specific versions of a source-controlled database to compare.

To do this:

1. On the Project configuration dialog box, select Database as a data source.
2. Specify connection details for a database linked to SQL Source Control.
3. In the Database box, under Source-Controlled Databases, select the database:

   

4. Select the Use version from source control check box.
5. In the Version box, select either the latest version, or a specific version to compare:

   

New command line switches

The following command line switches are new in SQL Compare 9.5:

/AbortOnWarnings

Alias: /aow

Specifies that SQL Compare will not perform a synchronization if there are any serious synchronization warnings.

If you use this switch and there are serious synchronization warnings, exit code 61 is displayed.

/IgnoreSourceCaseSensitivity

When you are creating a scripts folder using /makescripts, SQL Compare automatically detects the case sensitivity of the data source.

Use /ignoreSourceCaseSensitivity to disable automatic detection of case sensitivity.

/LogLevel:<level>

Alias: /log

Creates a log file with a specified minimum log level.

Log files collect information about the application while you are using it. These files are useful to us if you have encountered a problem. For more information, see: Logging and log files.

Arguments:

The default is None.

For example:

sqlcompare /db1:WidgetStaging /MakeScripts: D:\Scripts Folder
     /LogLevel:Verbose

Note: you must use /LogLevel each time you want a log file to be created.

/ShowWarnings

Alias: /warn

Displays any warnings that apply to the synchronization.

sqlcompare /db1:WidgetStaging /db2:WidgetProduction
     /ShowWarnings

/Version1:<version1>

Alias: /v1

Specifies the source control version of the first (source) database. To specify a version, the database must be linked to SQL Source Control.

To specify the latest version, type: HEAD

The following example compares version 3 of WidgetStaging with the latest version of WidgetProduction:

sqlcompare /db1:WidgetStaging /version1:3

     /db2:WidgetProduction /version2:HEAD

/Version2:<version2>

Alias: /v2

Specifies the source control version of the second (target) database. To specify a version, the database must be linked to SQL Source Control.

/VersionUserName1:<versionusername1>

Alias: /vu1

Specifies the username for the source control server linked to the first (source) database.

sqlcompare /db1:WidgetStaging /v1:3 /versionusername1:User1 /vp1:P@ssw0rd

     /db2:WidgetProduction /v2:HEAD /versionusername2:User2 /vp2:Pa$$w0rd

If you have a username saved in SQL Source Control, you do not need to specify it in the command line.

/VersionUserName2:<versionusername2>

Alias: /vu2

Specifies the username for the source control server linked to the second (target) database.

/VersionPassword1:<versionpassword1>

Alias: /vp1

Specifies the password for the source control server linked to the first (source) database.

sqlcompare /db1:WidgetStaging /v1:3 /vu1:User1 /versionpassword1:P@ssw0rd

     /db2:WidgetProduction /v2:HEAD /vu2:User2 /versionpassword2:Pa$$w0rd

If you have a password saved in SQL Source Control, you do not need to specify it in the command line.

/VersionPassword2:<versionpassword2>

Alias: /vp2

Specifies the password for the source control server linked to the second (target) database.

New project options

The following project options are new in SQL Compare 9.5:

Add object existence checks

When this option is selected, SQL Compare checks for the existence of objects affected by the deployment by adding IF EXISTS statements in the deployment script.

This option can be useful, for example, if you want to run the deployment script multiple times.

Use DROP and CREATE instead of ALTER

When this option is selected, SQL Compare replaces ALTER statements in the deployment script with DROP and CREATE statements for the following objects:

• Views
• Stored Procedures
• Functions
• Extended Properties
• DDL Triggers
• DML Triggers

Note that if you select this option, you must also select the Add object existence checks option, or the deployment script will fail.

Connecting to Denali servers

SQL Compare 9.5 enables you to connect to SQL Server Denali servers.

If you have any feedback on which new Denali features you want SQL Compare to support, let us know.

Transaction Isolation Level application option

SQL Compare 9.5 includes a new option enabling you to set the transaction isolation level used in synchronization scripts. You can use this option to prevent synchronization errors when using linked servers.

To set the transaction isolation level:

1. In the SQL Compare Tools menu, click Application Options.
2. Under Synchronization scripts, click the Transaction Isolation Level box, and then select the level you want to set: