The following DLM Automation cmdlets use SQL Compare to compare database schemas:

• Invoke-DlmDatabaseSchemaValidation
• New-DlmDatabaseDocumentation
• Invoke-DlmDatabaseTests
• New-DlmDatabaseRelease
• Sync-DlmDatabaseSchema

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.

The DLM Automation cmdlets use a set of sensible defaults for these options when they run a comparison. See Default SQL Compare options used by DLM Automation cmdlets.

Using the -SQLCompareOptions parameter

Use the -SQLCompareOptions parameter with any of the cmdlets listed above to modify the comparison behavior. You can turn off the default options or specify additional SQL Compare options.

Turning off a default option

Use -SQLCompareOptions to specify a default option you want to turn off. The option must be preceded by a minus sign.

$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 the New-DLmDatabaseRelease cmdlet. This means that the cmdlet 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.

$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 the default SQL Compare options that the New-DlmDatabaseRelease cmdlet will use. 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 (SQL Compare documentation).