You can control how Deployment Manager deploys databases using database deployment options. These options change the behavior of the deployment, for example by excluding certain database objects. Database deployment options are similar to project options in SQL Compare.

Setting database deployment options

You can apply a database deployment option by adding a variable to a project and setting its value to true or false. For more information about what the database deployment options do, see the Default database deployment options and Other database deployment options sections below.

To set a database deployment option for a project:

1. In the Variables page in the project, click Add new.
2. In Variable name, select the variable for the option from the drop-down menu.

3. Under Value, type true or false.
   

   By default, a variable applies across the entire project but it can be limited to specific environments, targets or steps. For example, you can limit a variable so it only applies to one database in your testing environment.

4. If you want to limit where the variable will apply, use the Environment, Target and Step drop-down menus:
   
5. Click Save.

Default database deployment options

By default, the following options are set to true and will apply to database deployments. If you're happy with a default option, you don't need to add it as a variable. To turn off a default option, add it as a variable and set its value to false.

Decrypt post-SQL2000 encrypted objects

Variable: RedGateDatabaseOptionsDecryptPost2kEncryptedObjects

This option applies to SQL 2005 and later, and SQL Azure.

When this option is set to true, Deployment Manager decrypts text objects which were created using the WITH ENCRYPTION option.

Fail deployment on file parse error

Variable: RedGateDatabaseOptionsThrowOnFileParseFailed

If this option is set to true, Deployment Manager will fail the deployment if there is error reading the scripts folder in the package it is deploying. If this option is set to false, the deployment will continue with a warning.

Force column order

Variable: RedGateDatabaseOptionsForceColumnOrder

If additional columns are inserted into the middle of a table, Deployment Manager will force a rebuild of the table so the column order is correct. If this option is set to false, the additional columns are added to the end of the table which is faster than rebuilding it. Data will be preserved.

Ignore certificates, symmetric keys, and asymmetric keys

Variable: RedGateDatabaseOptionsIgnoreCertificatesAndCryptoKeys

This option applies to SQL 2005 and later, and SQL Azure.

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys. Consequently, Deployment Manager can't deploy all of the properties of a symmetric key.
If certificates, symmetric keys, and asymmetric keys are selected for deployment, only the permissions are deployed.

Ignore database and server name in synonyms

Variable: RedGateDatabaseOptionsIgnoreDatabaseAndServerName

This option applies to SQL 2005 and later, and SQL Azure.

Ignores differences in database or server names in synonyms when deploying the database.

Ignore filegroups, partition schemes, and partition functions

Variable: RedGateDatabaseOptionsIgnoreFileGroups

Ignores filegroup clauses, partition schemes, and partition functions on tables and keys when deploying databases.

Ignore fill factor and index padding

Variable: RedGateDatabaseOptionsIgnoreFillFactor

Ignores the fill factor and index padding in indexes and primary keys when deploying databases.

Contents

Ignore tSQLt framework and tests

Variable: RedGateDatabaseOptionsIgnoreTSQLT

Ignores the tSQLt schema and its contents, the tSQLtCLR assembly, and any schemas and their contents with the tSQLt.TestClass extended property set.

Ignore users

Variable: RedGateDatabaseOptionsIgnoreUsers

When role-based security is used, object permissions are assigned to roles, not users. If this option is set to true, Deployment Manager only deploys object permissions for roles, and members of roles that are themselves roles. Users' permissions and role memberships are ignored.

Ignore user properties

Variable: RedGateDatabaseOptionsIgnoreUserProperties

This option applies to SQL 2005 and later, and SQL Azure.

If this option is set to true, users' properties are ignored, and only the user name is deployed. If this option is set to false, Deployment Manager deploys user properties, such as the type of user (SQL, Windows, certificate-based, asymmetric key based) and any schema where possible.

Ignore whitespace

Variable: RedGateDatabaseOptionsIgnoreWhiteSpace

When comparing text objects to check if they have differences to deploy, white space differences (newlines, tabs, spaces, etc) are ignored.

Ignore WITH element order

Variable: RedGateDatabaseOptionsIgnoreWithElementOrder

If a stored procedure, user-defined function, DDL trigger, DML trigger, or view contains multiple WITH elements (such as encryption, schema binding, and so on), Deployment Manager will ignore the order of the WITH elements when deploying the database.

Use Migrations V2 scripts

Variable: RedGateDatabaseOptionsUseMigrationsV2

Use Migrations V2 scripts from SQL Source Control if they are available in the database package.

Other database deployment options

By default, the following options are set to false and won't apply to database deployments. To apply one of these options, add it as a variable and set its value to true.

Add object existence checks

Variable: RedGateDatabaseOptionsObjectExistenceChecks

Deployment Manager checks for the existence of objects affected by the deployment by adding IF EXISTS statements in the deployment script.

Add WITH ENCRYPTION

Variable: RedGateDatabaseOptionsAddWithEncryption

Deployment Manager adds WITH ENCRYPTION to stored procedures, functions, views, and triggers that are included in the deployment.

Clone database level properties for migrations

Variable: RedGateDatabaseOptionsCloneDatabaseLevelPropertiesForMigrations

When creating the temporary database to process migration scripts, Deployment Manager will copy the properties of the database set by the ALTER DATABASE SET command. For more information about how migration scripts work, see How migration scripts are used in deployment.

Consider next filegroups in partition schemes

Variable: RedGateDatabaseOptionsConsiderNextFilegroupInPartitionSchemes

This option applies to SQL 2005 and later. It doesn't apply to SQL Azure.

If a partition scheme contains a next filegroup, Deployment Manager considers the next filegroup in the deployment if the partition scheme is extended. The next filegroup does not affect the way in which data is stored.

Disable DDL triggers during deployment

Variable: RedGateDatabaseOptionsDisableAndReenableDdlTriggers 

This option applies to SQL 2005 and later, and SQL Azure.

DDL triggers can cause problems in the deployment. This option disables any enabled DDL triggers before deploying and re-enable those triggers following deployment.

Don't use ALTER ASSEMBLY to change CLR objects

Variable: RedGateDatabaseOptionsUseClrUdtToStringForClrMigration

This option applies to SQL 2005 and later. It doesn't apply to SQL Azure.

If CLR objects are to be deployed, this option forces two rebuilds of the table with conversion to and from strings to update the CLR objects, instead of using ALTER ASSEMBLY. For a detailed explanation, see Understanding the deployment.

Don't use transactions in deployment SQL scripts

Variable: RedGateDatabaseOptionsNoSQLPlumbing

If this option is set to false and the database deployment fails, the deployment script is rolled back to the start of the failed transaction. If this option is set to true, the script is not rolled back. This can be useful for detection of errors in a script.

Ignore bindings

Variable: RedGateDatabaseOptionsIgnoreBindings

Ignores bindings on columns and user-defined types when deploying databases. For example, sp_bindrule and sp_bindefault clauses will be ignored.

Ignore check constraints

Variable: RedGateDatabaseOptionsIgnoreChecks

Ignores check constraints when deploying databases.

Ignore collations

Variable: RedGateDatabaseOptionsIgnoreCollations

Ignores collations on character data type columns when deploying databases.

Ignore comments

Variable: RedGateDatabaseOptionsIgnoreComments

Ignores comments when comparing views, stored procedures, etc when comparing database to check if they have changes to deploy.

Ignore constraint and index names

Variable: RedGateDatabaseOptionsIgnoreConstraintNames

Ignores the names of indexes, foreign keys, primary keys, and default, unique, and check constraints when comparing databases to check if they have changes to deploy.

Ignore data compression

Variable: RedGateDatabaseOptionsIgnoreDataCompression

This option applies to SQL 2008 and later, and SQL Azure.

Ignores page and row compression for tables and indexes. When Ignore filegroups is selected, compression is automatically ignored for partitioned tables.

Ignore DML triggers

Variable: RedGateDatabaseOptionsIgnoreTriggers

Ignores DML triggers when deploying databases.

Ignore DML trigger order

Variable: RedGateDatabaseOptionsIgnoreTriggerOrder

DML triggers can have an order specified, such as FIRST INSERT, LAST UPDATE, and so on. Set this option to true to ignore the trigger order for DML triggers when deploying databases. The DDL trigger order is not affected.

Ignore encryption of object text

Variable: RedGateDatabaseOptionsIgnoreWithEncryption

Ignores WITH ENCRYPTION statements on triggers, views, stored procedures and functions.

This option overrides Add WITH ENCRYPTION.

Ignore event notification on queues

Variable: RedGateDatabaseOptionsIgnoreQueueEventNotifications

This option applies to SQL 2005 and later. It doesn't apply to SQL Azure.

Ignores the event notification on queues when deploying databases.

Ignore extended properties 

Variable: RedGateDatabaseOptionsIgnoreExtendedProperties

Ignores extended properties on objects and databases when deploying databases.

Ignore foreign keys

Variable: RedGateDatabaseOptionsIgnoreKeys

Ignores foreign keys when deploying databases.

Ignore full-text indexing 

Variable: RedGateDatabaseOptionsIgnoreFullTextIndexing

Ignores full-text catalogs and full-text indexes when deploying databases.

Ignore identity property on columns

Variable: RedGateDatabaseOptionsIgnoreIdentityProperties

Ignores the identity property on columns when comparing databases to check if they have changes to deploy.

Ignore identity seed and increment values

Variable: RedGateDatabaseOptionsIgnoreIdentitySeedAndIncrement

For identity properties, ignores only the identity seed and increment values when comparing databases to check if they have changes to deploy.

Ignore indexes

Variable: RedGateDatabaseOptionsIgnoreIndexes

Ignores indexes when deploying databases.

Ignore INSTEAD OF triggers

Variable: RedGateDatabaseOptionsIgnoreInsteadOfTriggers

Ignores INSTEAD OF DML triggers when deploying databases.

Ignore internally used Microsoft extended properties

Variable: RedGateDatabaseOptionsIgnoreInternallyUsedMicrosoftExtendedProperties

Ignores internally used Microsoft extended properties, for example, the extended properties created by the Query and View Designer Tools in SQL Server Management Studio.

Ignore LOCK properties of indexes

Variable: RedGateDatabaseOptionsIgnoreIndexLockProperties

This option applies to SQL 2005 and later, and to SQL Azure.

Ignores index PAGE LOCK and ROW LOCK properties when deploying databases.

Ignore NOT FOR REPLICATION

Variable: RedGateDatabaseOptionsIgnoreNotForReplication

Ignores the NOT FOR REPLICATION option on foreign keys, identities, check constraints and triggers.

If you select this option, the NOT FOR REPLICATION statement will not be displayed in the object creation script for foreign keys, identities, and check constraints when deploying databases.

In the case of triggers, the NOT FOR REPLICATION statement will be displayed in the object creation script, but will be ignored when checking for differences to deploy. When comparing triggers, make sure the Ignore whitespace option is set to true (which it is by default).

Check constraints and foreign keys that contain the NOT FOR REPLICATION statement in their definition will automatically be flagged as WITH NOCHECK. Use the Ignore WITH NOCHECK option to identify these objects as being the same; but this will apply to constraints in all objects.

Ignore owner authorization on schema objects

Variable: RedGateDatabaseOptionsIgnoreSchemaObjectAuthorization

This option applies to SQL 2005 and later, and SQL Azure.

Ignores authorization clauses on schema-qualified objects when deploying databases.

Ignore permissions

Variable: RedGateDatabaseOptionsIgnorePermissions

Ignores permissions on objects when deploying databases.

Ignore replication triggers

Variable: RedGateDatabaseOptionsIgnoreReplicationTriggers

Ignores replication triggers when deploying databases.

Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements

Variable: RedGateDatabaseOptionsIgnoreQuotedIdentifiersAndAnsiNullSettings

Ignores these SET statements when comparing views, stored procedures, etc to check if databases have changes to deploy.

Ignore square brackets in object names

Variable: RedGateDatabaseOptionsIgnoreSquareBrackets

Ignores starting and ending square brackets in object names which have been escaped using square brackets when comparing databases to check if they have changes to deploy. This applies to textual objects such as stored procedures, triggers, etc.

Ignore statistics

Variable: RedGateDatabaseOptionsIgnoreStatistics

Ignores statistics when deploying databases.

Ignore STATISTICS_NORECOMPUTE property on indexes

Variable: RedGateDatabaseOptionsIgnoreStatisticsNorecompute

Ignores the STATISTICS_NORECOMPUTE property on indexes and primary keys.

Ignore system named constraint and index names

Variable: RedGateDatabaseOptionsIgnoreSystemNamedConstraintNames

Ignores the names of system named indexes, statistics, foreign keys, primary keys, and default, unique, and check constraints when deploying databases.

Ignore WITH NOCHECK

Variable: RedGateDatabaseOptionsIgnoreWithNocheck

Ignores the WITH NOCHECK argument on foreign keys and check constraints.

Separate DML triggers

Variable: RedGateDatabaseOptionsSeparateTriggers

Deploys DML triggers separately from their tables.

Transaction isolation level

Variable: RedGateDatabaseOptionsTransactionIsolationLevel

Sets the transaction isolation level for the database deployment to one of the following: 

• Serializable
• Snapshot
• Repeatable read
• Read committed
  
• Read uncommitted

The default level is serializable.

For more information about transaction isolation levels, see http://technet.microsoft.com/en-us/library/ms173763.aspx

Use DROP and CREATE instead of ALTER

Variable: RedGateDatabaseOptionsDropAndCreateInsteadOfAlter

Deployment Manager replaces ALTER statements in the deployment script with DROP and CREATE statements for the following objects:

• Views
• Stored Procedures
• Functions
• Extended Properties
• DDL Triggers
• DML Triggers