SQL Change Automation provides an effective way to generate new migration scripts within your project. The tool uses Redgate's SQL Compare, and SQL Data Compare engines. SQL Change Automation uses most of the factory default comparison and script generation options that ship with SQL Compare itself.

If fine-grained control is required over the script-generation behavior, then the standard SQL Compare settings can be customized at the project level.

Customizing database comparison and script generation options

In order to set the wanted script comparison and generation options, some editing of your SQL Change Automation project's .sqlproj file is required.

Choose from one or more of the available database comparison and script generation options. These are listed below along with the default value that SQL Change Automation uses:

Schema comparison options

<PropertyGroup>
  <!-- "Deploy all dependencies" SQL Compare option -->
  <SyncOptionIncludeDependentObjects>False</SyncOptionIncludeDependentObjects>

  <!-- "Add object existence checks" SQL Compare option -->
  <SyncOptionIncludeExistenceChecks>False</SyncOptionIncludeExistenceChecks>

  <!-- "Use DROP and CREATE instead of ALTER" SQL Compare option -->
  <SyncOptionUseDropCreateInsteadOfAlter>False</SyncOptionUseDropCreateInsteadOfAlter>  

  <!-- "Disable DDL triggers during deployment" SQL Compare option -->
  <SyncOptionDisableDdlTriggers>False</SyncOptionDisableDdlTriggers>

  <!-- "Ignore extended properties" SQL Compare option -->
  <SyncOptionIgnoreExtendedProperties>False</SyncOptionIgnoreExtendedProperties>

  <!-- "Ignore comments" SQL Compare option -->
  <SyncOptionIgnoreCommentsInBodies>False</SyncOptionIgnoreCommentsInBodies>

  <!-- "Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements" SQL Compare option -->
  <SyncOptionIgnoreAnsiOptions>False</SyncOptionIgnoreAnsiOptions>

  <!-- "Ignore collations" SQL Compare option -->
  <SyncOptionIgnoreCollations>True</SyncOptionIgnoreCollations>

  <!-- "Force column order" SQL Compare option (set to False to force column order) -->
  <SyncOptionIgnoreColumnsOrder>True</SyncOptionIgnoreColumnsOrder>

  <!-- "Ignore database and server name in synonyms" SQL Compare option -->
  <SyncOptionIgnoreSynonymDatabaseAndServerName>True</SyncOptionIgnoreSynonymDatabaseAndServerName>

  <!-- "Ignore tSQLt framework and tests" SQL Compare option -->
  <SyncOptionIgnoretSQLt>True</SyncOptionIgnoretSQLt>

  <!-- "Ignore DML triggers" SQL Compare option -->
  <SyncOptionIgnoreDmlTriggers>False</SyncOptionIgnoreDmlTriggers>

  <!-- "Ignore INSTEAD OF triggers" SQL Compare option -->
  <SyncOptionIgnoreInsteadOfTriggers>False</SyncOptionIgnoreInsteadOfTriggers>

  <!-- "Ignore filegroups, partition schemes and partition functions" SQL Compare option -->
  <SyncOptionIgnoreDataspaces>False</SyncOptionIgnoreDataspaces>

  <!-- "Ignore NOT FOR REPLICATION" SQL Compare option -->
  <SyncOptionIgnoreConstraintNotForReplicationAttribute>True</SyncOptionIgnoreConstraintNotForReplicationAttribute>

  <!-- "Ignore fill factor and index padding" SQL Compare option -->
  <SyncOptionIgnoreFillFactor>True</SyncOptionIgnoreFillFactor>

  <!-- "Ignore LOCK properties of indexes" SQL Compare option -->
  <SyncOptionIgnoreIndexLockAttributes>False</SyncOptionIgnoreIndexLockAttributes>

  <!-- "Ignore statistics" SQL Compare option -->
  <SyncOptionIgnoreStatistics>True</SyncOptionIgnoreStatistics>

  <!-- "Ignore data compression" SQL Compare option -->
  <SyncOptionIgnoreDataCompression>True</SyncOptionIgnoreDataCompression>

  <!-- "Ignore full-text indexing" SQL Compare option -->
  <SyncOptionIgnoreFullTextIndexing>False</SyncOptionIgnoreFullTextIndexing>

  <!-- "Ignore replication triggers" SQL Compare option -->
  <SyncOptionIgnoreReplicationTriggers>True</SyncOptionIgnoreReplicationTriggers>  

  <!-- "Ignore indexes" SQL Compare option -->
  <SyncOptionIgnoreIndexes>False</SyncOptionIgnoreIndexes>

  <!-- "Ignore permissions" SQL Compare option -->
  <SyncOptionIgnorePermissions>False</SyncOptionIgnorePermissions>

  <!-- "Ignore white space" SQL Compare option -->
  <SyncOptionIgnoreWhiteSpace>True</SyncOptionIgnoreWhiteSpace>

  <!-- "Ignore WITH NOCHECK" SQL Compare option -->
  <SyncOptionIgnoreConstraintWithNoCheckAttribute>True</SyncOptionIgnoreConstraintWithNoCheckAttribute>

  <!-- "Ignore identity seed and increment values" SQL Compare option -->
  <SyncOptionIgnoreIdentitySeedAndIncrement>False</SyncOptionIgnoreIdentitySeedAndIncrement>

  <!-- "Use case-sensitive object definition" SQL Compare option -->
  <SyncOptionCaseSensitiveObjectDefinition>False</SyncOptionCaseSensitiveObjectDefinition>
 
  <!-- "Ignore bindings" SQL Compare option -->
  <SyncOptionIgnoreBindings>False</SyncOptionIgnoreBindings>

  <!-- "Ignore event notification on queues" SQL Compare option -->
  <SyncOptionIgnoreQueueEventNotifications>False</SyncOptionIgnoreQueueEventNotifications>

  <!-- "Ignore brackets in object names" SQL Compare option -->
  <SyncOptionIgnoreSquareBrackets>False</SyncOptionIgnoreSquareBrackets>

  <!-- "Ignore WITH element order" SQL Compare option -->
  <SyncOptionIgnoreWithElementOrder>True</SyncOptionIgnoreWithElementOrder>

  <!-- "Ignore WITH ENCRYPTION" SQL Compare option -->
  <SyncOptionIgnoreWithEncryption>False</SyncOptionIgnoreWithEncryption>
 
  <!-- "Ignore authorization on schema objects" SQL Compare option -->
  <SyncOptionIgnoreSchemaObjectAuthorization>False</SyncOptionIgnoreSchemaObjectAuthorization>

  <!-- "Ignore STATISTICS_NORECOMPUTE property on indexes" SQL Compare option -->
  <SyncOptionIgnoreStatisticsNorecompute>False</SyncOptionIgnoreStatisticsNorecompute>

  <!-- "Consider next filegroups in partition schemes" SQL Compare option -->
  <SyncOptionConsiderNextFilegroupInPartitionSchemes>False</SyncOptionConsiderNextFilegroupInPartitionSchemes>
 
  <!-- "Ignore NOCHECK and WITH NOCHECK" SQL Compare option -->
  <SyncOptionIgnoreNocheckAndWithNocheckAttributes>False</SyncOptionIgnoreNocheckAndWithNocheckAttributes>

  <!-- "Ignore user properties" SQL Compare option -->
  <SyncOptionIgnoreUserProperties>True</SyncOptionIgnoreUserProperties>

  <!-- "Ignore user's permissions and role memberships" SQL Compare option -->
  <SyncOptionIgnoreUsersPermissionsAndRoleMemberships>False</SyncOptionIgnoreUsersPermissionsAndRoleMemberships>

  <!-- "Add WITH ENCRYPTION" SQL Compare option -->
  <SyncOptionAddWithEncryption>False</SyncOptionAddWithEncryption>

  <!-- "Decrypt encrypted objects" SQL Compare option -->
  <SyncOptionDecryptEncryptedObjects>True</SyncOptionDecryptEncryptedObjects>

  <!-- "Use database compatibility level" SQL Compare option -->
  <SyncOptionUseCompatibilityLevel>False</SyncOptionUseCompatibilityLevel>

  <!-- "Ignore dynamic data masking" SQL Compare option -->
  <SyncOptionIgnoreDynamicDataMasking>False</SyncOptionIgnoreDynamicDataMasking>

  <!-- "Auto map similar columns" SQL Compare option -->
  <SyncOptionNoAutoColumnMapping>False</SyncOptionNoAutoColumnMapping>

  <!-- "Add ONLINE = ON when creating indexes" SQL Compare option -->
  <SyncOptionOnlineIndexBuild>False</SyncOptionOnlineIndexBuild>
 
</PropertyGroup>

Data comparison options

<PropertyGroup>
  <!-- "Drop primary keys, indexes, and unique constraints" SQL Data Compare option -->
  <SyncDataOptionDropKeysIndexesAndConstraints>False</SyncDataOptionDropKeysIndexesAndConstraints>

  <!-- "Disable foreign keys" SQL Data Compare option -->
  <SyncDataOptionDisableForeignKeys>False</SyncDataOptionDisableForeignKeys>

  <!-- "Include timestamp columns" SQL Data Compare option -->
  <SyncDataOptionIncludeTimestampColumns>False</SyncDataOptionIncludeTimestampColumns>

  <!-- "Use case-sensitive object definition" SQL Data Compare option -->
  <SyncDataOptionCaseSensitiveObjectDefinition>True</SyncDataOptionCaseSensitiveObjectDefinition>

  <!-- "Force binary collation (case-sensitive)" SQL Data Compare option -->
  <SyncDataOptionForceBinaryCollation>True</SyncDataOptionForceBinaryCollation>

  <!-- "Trim trailing white space" SQL Data Compare option -->
  <SyncDataOptionTrimTrailingSpaces>False</SyncDataOptionTrimTrailingSpaces>

  <!-- "Disable DML triggers" SQL Data Compare option -->
  <SyncDataOptionDisableDmlTriggers>False</SyncDataOptionDisableDmlTriggers>

  <!-- "Disable DDL triggers" SQL Data Compare option -->
  <SyncDataOptionDisableDdlTriggers>True</SyncDataOptionDisableDdlTriggers>

  <!-- "Don't include comments in the deployment script" SQL Data Compare option -->
  <SyncDataOptionDontIncludeCommentsInScript>False</SyncDataOptionDontIncludeCommentsInScript>

  <!-- "Reseed identity columns" SQL Data Compare option -->
  <SyncDataOptionReseedIdentityColumns>False</SyncDataOptionReseedIdentityColumns>

  <!-- "Skip integrity checks for foreign key constraints" SQL Data Compare option -->
  <SyncDataOptionSkipIntegrityChecksForForeignKeys>False</SyncDataOptionSkipIntegrityChecksForForeignKeys>

  <!-- "Transport CLR data types as binary" SQL Data Compare option -->
  <SyncDataOptionTransportClrDataTypesAsBinary>False</SyncDataOptionTransportClrDataTypesAsBinary>

  <!-- "Compress temporary files" SQL Data Compare option -->
  <SyncDataOptionCompressTemporaryFiles>False</SyncDataOptionCompressTemporaryFiles>

  <!-- "Treat empty string as NULL" SQL Data Compare option -->
  <SyncDataOptionTreatEmptyStringAsNull>False</SyncDataOptionTreatEmptyStringAsNull>

  <!-- "Use checksum comparison" SQL Data Compare option -->
  <SyncDataOptionUseChecksumComparison>False</SyncDataOptionUseChecksumComparison>

  <!-- "Use maximum precision for floats" SQL Data Compare option -->
  <SyncDataOptionUseMaxPrecisionForFloatComparison>False</SyncDataOptionUseMaxPrecisionForFloatComparison>

</PropertyGroup>

To override a given option, copy the option to the top of the project file within a <ProjectGroup> element. For example, to have SQL Change Automation ignore DML trigger objects when importing from your connected database, add the following under the <Project> element of your project file:

Example option snippet

<PropertyGroup>
  <SyncOptionIgnoreDmlTriggers>True</SyncOptionIgnoreDmlTriggers>
</PropertyGroup>

CREATE OR ALTER for re-runnable scripts

In addition to the options supported by SQL Compare, SQL Change Automation also provides the UseCreateOrAlterForRerunnableScripts option. This defaults to False. If set to True, Programmable objects will be scripted using CREATE OR ALTER syntax instead of DROP and CREATE.
Changing this setting will not automatically update programmable objects which have already been scripted to the project. It is possible to regenerate these by deleting the existing scripts and rerunning the generate migrations step. Note that doing this will cause the programmable objects to be redeployed as their checksums will have changed.

Combining script generation settings with a SQL Compare filter

At times it may be useful to supplement changes to your script generation settings with a filter.

For example, if you wish to set the SyncOptionIgnoreUsersPermissionsAndRoleMemberships to True so that SQL Change Automation does not script out permissions for users, you may also want to add a filter to prevent the users from being scripted as well. See Filtering for more information and an example filter for this purpose.

Unsupported options

Options that are "locked" to ensure deployment reliability

A number of SQL Compare options would, if switched on, cause scripts to be generated that might not work within all of your target environments. In order to ensure that a given migration can be successfully executed against all of your databases, there are a number of options that are locked to the Off setting:

Ignore constraint names
Ignore system-named constraint names
Ignore DML trigger order
Ignore certificates, symmetric keys and asymmetric keys
Ignore check constraints
Ignore foreign keys
Ignore identity properties
Ignore nullability of columns
(Data Compare) Ignore underscores in object names
(Data Compare) Ignore spaces in object names
(Data Compare) Include indexed views
(Data Compare) Exclude identity columns

Other locked options

Additionally, a number of script generation options are locked, as follows:

Don't use transactions in deployment scripts = On. SQL Change Automation automatically includes transaction handling to your deployment. To prevent transactions from being used, see Transaction Handling in SQL Change Automation.
Don't add error handling statements to deployment scripts = On. SQL Change Automation uses the error-handling conventions of SQLCMD, which do not require the per-statement error handling of SQL Compare.
Add Database Use Statement = Off. SQL Change Automation automatically sets the database context prior to executing your migration script, so it is not necessary to include the USE statement in your scripts.
Do Not Output Comment Header = On. SQL Change Automation produces its own comment header in the .sql files that are output during the build process.
Ignore migration scripts for databases = On. This option relates to the SQL Source Control product and therefore is not relevant to SQL Change Automation script generation.
Database Project compatible script folder output = Off. This option relates to SQL Server Data Tools (SSDT) database projects and therefore is not relevant to SQL Change Automation script generation.
Don't use ALTER ASSEMBLY to change CLR objects = Off. If you would like to apply greater control to how your SQLCLR objects are deployed, we recommend using the method described in Deploying Assemblies.

SQL Change Automation 4

Comparison and script generation options