Configuring comparison & script generation options
Published 17 October 2016
The SQL Change Automation tool-window provides an effective way to generate new migration scripts within your SQL Change Automation project. The tool uses Redgate 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.
Customising database comparison & script generation options
In order to set the desired script comparison and generation options, some editing of your SQL Change Automation project's .sqlproj file is required.
Start by opening your solution in Visual Studio. Right-click the SQL Change Automation project in the Solution Explorer and select Unload Project. When the project has been unloaded, right click it again and select Edit project.
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>True</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 square 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 users' 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> </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>
Save the project file, and right-click the project in the Solution Explorer and click Reload project (click OK when prompted to close the file).
Build the solution to clear the tool-window cache.
The next time you use the tool-window to import changes from the connected database, the specified comparison and script generation options should be applied.
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.
