DLM Automation 2

Using SQL Compare options with DLM Automation cmdlets

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.

DLM Automation cmdlets ignore SQL Compare options specified in SQL Source Control

SQL Source Control also uses SQL Compare. The default options used by the DLM Automation cmdlets aren't the same as those used by SQL Compare.

If you specify SQL Compare options in SQL Source Control, these are not 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.

If you don't specify the -SQLCompareOptions parameter, the defaults are applied.

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.

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 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.

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 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).


Didn't find what you were looking for?