SQL Compare 10

New in SQL Compare 10

Migration script support

With SQL Source Control 3.0, you can 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 deployment script will fail because a default value is required; you can specify this value using a migration script.

For more details, see: Working with migration scripts.

Table mapping

With SQL Compare 10, you can map together tables and columns with different names. This can be useful to prevent data loss when deploying 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 deploy the table, the name change will be scripted using the sp_rename system stored procedure; the table isn't dropped and re-created.

To compare tables and columns that aren't 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.

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

Creating a new database to compare

With SQL Compare 10, you can create a new database, which you can then select to compare. This can be useful if you want to create a copy of the source database.

To create a new database:

  1. On the Project Configuration dialog box, select Database as a data source.
    Specify connection details for the server you want to create the database on.
    In the Database box, select Create Database:

    The New Database dialog box is displayed:

  2. Specify a name, default collation, and database files for the new database.

    By default, SQL Compare adds the same database files from the source database to the new database.

  • A primary data file and transaction log file are required when creating a database; you can't remove them
  • If you're creating a database on SQL Azure, you can't add database files or change the collation

Comparing versions from SQL Source Control

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

  1. On the Project configuration dialog box, select Source Control as a data source, and then select Direct from source control:

  2. In the Repository box, select a database linked to SQL Source Control, or click <Browse source control...> to specify a repository URL.
    In the Version box, select a specific revision from the source control history, or select the latest revision:

New command line switches

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

/AbortOnWarnings

Alias: /aow

Specifies that SQL Compare won't perform a deployment if there are any serious deployment warnings.

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

/IgnoreSourceCaseSensitivity

When you're 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're using it. These files are useful to us if you encounter a problem. For more information, see Logging and log files.

Arguments:

  • None

Disables logging

  • Error

Reports serious and fatal errors

  • Warning

Reports warning and error messages

  • Verbose

Reports all messages in the log file

The default is None.

For example:

sqlcompare /db1:WidgetStaging /MakeScripts: D:\Scripts Folder 
     /LogLevel:Verbose
You must use /LogLevel each time you want a log file to be created.

/ShowWarnings

Alias: /warn

Displays any warnings that apply to the deployment.

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 don't 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 don't 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 10:

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 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
If you select this option, you must also select the Add object existence checks option, or the deployment script will fail.

Ignore migration scripts for databases

When this option is selected, SQL Compare won't try to retrieve migration scripts when you compare a database.

(By default, when you compare a database that has an associated revision number, SQL Compare tries to connect to source control to retrieve any relevant migration scripts.)

This option can be useful if you've encountered an error connecting to source control when comparing a database.

Connecting to SQL Server 2012

SQL Compare 10 can connect to SQL Server 2012 (Denali) servers.

If you have any feedback on the new SQL Server 2012 features you want SQL Compare to support, let us know.

Transaction Isolation Level application option

SQL Compare 10 includes a new option you can use to set the transaction isolation level used in deployment scripts. This option is useful to prevent deployment errors when using linked servers.

To set the transaction isolation level:

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


Didn't find what you were looking for?