Using SQL Compare options in SQL Release
Published 25 N 2014
SQL Release uses SQL Compare to compare database schemas in order to generate the update script and run pre-deploy and post-deploy checks. SQL Source Control also uses SQL Compare. SQL Compare has a range of options that allow you to configure what is compared between the schemas and modify certain other behavior of the comparison.
SQL Release uses a set of sensible defaults for these options whenever it runs a comparison. See Default SQL Compare options used by SQL Release.
The default options used by SQL Release are not the same as those used by SQL Compare.
If you have specified SQL Compare options in SQL Source Control, these are not used by SQL Release; the SQL Release defaults are used instead.
Using the -SQLCompareOptions parameter
Use -SQLCompareOptions with the New-DatabaseRelease cmdlet to modify the comparison behavior used to create the Database Update object. You can turn off the default options or specify additional SQL Compare options.
Turning off a default option
Use -SQLCompareOptions followed by a minus sign to turn off a default option.
Example
$staging = New-DlmDatabaseConnection -ServerInstance 'staging01\sql2012' -Database 'Staging' -Username 'sa' -Password 'P@ssw0rd' $test = New-DlmDatabaseConnection -ServerInstance 'test01\sql2012' -Database 'Test' -Username 'sa' -Password 'P@ssw0rd' New-DlmDatabaseRelease -Source $test -Target $staging -SQLCompareOptions '-ForceColumnOrder'
In this example, the ForceColumnOrder option is excluded from the SQL Compare options used by SQL Release. This means that SQL Release will no longer force table column order in the database schema update.
Adding additional options
Use -SQLCompareOptions followed by a comma-delimited list of options to specify additional SQL Compare options.
Example
$staging = New-DlmDatabaseConnection -ServerInstance 'staging01\sql2012' -Database 'Staging' -Username 'sa' -Password 'P@ssw0rd' $test = New-DlmDatabaseConnection -ServerInstance 'test01\sql2012' -Database 'Test' -Username 'sa' -Password 'P@ssw0rd' $options = 'IgnoreComments, ObjectExistenceChecks' New-DlmDatabaseRelease -Source -Target $staging $test -SQLCompareOptions $options
In this example, the IgnoreComments and ObjectExistenceChecks options are added to be used by SQL Release. You can use the alias rather the full option name, for example ic
for IgnoreComments
For a list of SQL Compare options you can include, see Options used in the command line