New in SQL Compare 10
Published 04 June 2013
- Migration script support
- Table mapping
- Creating a new database to compare
- Comparing revisions from SQL Source Control
- New command line switches
- New project options
- Connecting to SQL Server 2012
- Transaction Isolation Level application option
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:
- 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:
- 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:
- On the Project configuration dialog box, select Source Control as a data source, and then select Direct from source control:
- 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:
| Disables logging |
| Reports serious and fatal errors |
| Reports warning and error messages |
| Reports all messages in the log file |
The default is None.
For example:
sqlcompare /db1:WidgetStaging /MakeScripts: D:\Scripts Folder /LogLevel:Verbose
/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
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:
- In the SQL Compare Tools menu, click Application Options.
- Under Deployment scripts, click the Transaction Isolation Level box, and then select the level you want to set:
