Setting schema packaging options - SQL Packager

The schema packaging options are a set of advanced features that enable you to modify the behavior of SQL Packager when it packages the database structure. For example, you can set SQL Packager so that it ignores certain objects, or so that it does not script certain properties in the package (such as the collation order on columns).

You can also set:

• data options for packaging the data
• packager options for the .EXE or C# project

Ignore indexes

Ignores indexes, unique constraints, and primary keys when packaging the database structure.

Ignore statistics

Ignores statistics when packaging the database structure.

Ignore foreign keys

Ignores foreign keys when packaging the database structure.

Ignore check constraints

Ignores check constraints when packaging the database structure.

Ignore identity seed and increment values

For identity properties, ignores only the identity seed and increment values when displaying the objects that are available for packaging. Note that they will be included in the schema packaging script.

Ignore fill factor and index padding

Ignores the fill factor and index padding in indexes and primary keys when packaging the database structure.

Ignore permissions

Ignores permissions on objects when packaging the database structure.

Ignore DML triggers

Ignores DML triggers when packaging the database structure.

Ignore INSTEAD OF triggers

Ignores INSTEAD OF DML triggers when packaging the database structure.

Ignore bindings

Ignores bindings on columns and user-defined types. For example, sp_bindrule and sp_bindefault clauses will not be included in the schema packaging script.

Ignore WITH NOCHECK on foreign keys and check constraints

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

Note that foreign keys or constraints that are disabled, are not ignored.

Ignore constraint and index names

Ignores the names of indexes, foreign keys, primary keys, and default, unique, and check constraints when displaying the objects that are available for packaging. Note that they will be included in the schema packaging script.

Ignore filegroups, partition schemes and partition functions

Ignores filegroup clauses, partition schemes, and partition functions on tables and keys when packaging the database structure. Partition schemes and partition functions are not available for inclusion in the package when this option is selected.

Force table column order to be identical

If additional columns are inserted into the middle of a table, this option forces a rebuild of the table so the column order is correct following upgrade. Data will be preserved.

Ignore white space

Ignores white space (newlines, tabs, spaces, and so on) when displaying the objects available for packaging. Note that white space will not be ignored when the objects are created or upgraded.

Ignore comments

Ignores comments when comparing views, stored procedures, and so on. Note that comments will be included in the schema packaging script.

Ignore extended properties

Ignores extended properties on objects and databases when packaging databases.

Treat items as case sensitive

For databases with case-sensitive collation, enables objects with case-sensitive names to be packaged. For example, considers object names such as ATable and atable as different and performs case-sensitive comparisons on stored procedures, and so on.

You should use this option only if you have databases with binary sort order or case-sensitive sort order.

Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements

Ignores these SET statements when displaying available views, stored procedures and so on. Note that these statements will be included in the schema packaging script.

Ignore collation order

Ignores collation order on character data type columns when packaging databases.

Ignore full text indexing

Ignores full-text indexes, catalogs, and so on when packaging databases.

Do not include plumbing for transactional synchronization scripts

Removes transactions from the package to produce SQL code that is more readable.

If this option is not selected and the package fails, the script is rolled back to the start of the failed transaction. If this option is selected, the script is not rolled back. This can be useful for detection of errors within a script.

Add WITH ENCRYPTION option to stored procedures etc

Adds WITH ENCRYPTION when stored procedures, functions, views, and triggers are included in the package.

Note that if you use ADD ENCRYPTION on a SQL Server 2008 or SQL Server 2005 database, SQL Packager will not subsequently be able to display, or package the encrypted objects.

Do not use ALTER ASSEMBLY to change CLR types

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

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

Consider next filegroups in partition schemes

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

When this option is selected, if a partition scheme contains a next filegroup, SQL Packager considers the next filegroup for the upgrade if the filegroup is extended. The next filegroup does not affect the way in which data is stored. For a detailed explanation, see Understanding the Results.

To ignore next filegroups, clear the check box.

Include dependencies

When this option is selected, SQL Packager checks for object dependencies; if you excluded objects and other objects that you selected are dependent on the excluded objects, the excluded objects are packaged. For example, if you select a stored procedure and it references a table, even if you excluded that table, the table is still packaged.

By default, SQL Packager will include dependencies in the package. Clear the option if you do not want to include the dependencies. Note that clearing this check box may produce unexpected results or the script may fail. Roles and users are always included in the package.

Decrypt encrypted objects in 2008 and 2005 databases

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

When this option is selected, SQL Packager decrypts text objects in SQL Server 2008 and SQL Server 2005 databases which were created using the WITH ENCRYPTION option. Note that this option can have a significant performance impact on large databases.

When SQL Packager saves a snapshot, this option is set, and all encrypted objects are decrypted.

Ignore certificates, symmetric and asymmetric keys

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

SQL Server 2005 severely restricts access to certificates, symmetric keys, and asymmetric keys. Consequently, SQL Packager can package only the permissions of certificates and asymmetric keys; symmetric keys cannot be packaged.

Ignore trigger order

DML triggers can have an order specified, such as FIRST INSERT, LAST UPDATE, and so on. Select this option to ignore the trigger order for DML triggers when packaging databases. Note that the DDL trigger order is not affected.

Ignore event notifications on queues

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

Ignores the event notification on queues when packaging databases.

Ignore users' permissions and role memberships

When role-based security is used, object permissions are assigned to roles, not users. If this option is selected, SQL Packager creates or upgrades object permissions only for roles, and members of roles that are roles. Users' permissions and role memberships are ignored.

Ignore users' properties in comparison

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

If this option is not selected, SQL Packager compares user properties, such as the type of user (SQL, Windows, certificate-based, asymmetric key based) and any schema to identify differences. If a user is selected to be upgraded, SQL Packager upgrades the properties where possible.

If you select this option, users' properties are ignored, and only the user name is packaged.

Disable and later re-enable DDL triggers

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

DDL triggers can cause problems when you run the packaging script. Select this option to disable any enabled DDL triggers before upgrading the databases, and re-enable those triggers on completion.

Ignore the order of WITH elements

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), select this option to ignore the order of the WITH elements when packaging databases.

Ignore the lock properties of indexes

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

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

Ignore replication triggers

Ignores replication triggers when packaging databases.

Ignore identity properties

Ignores the identity property on columns when displaying the objects that are available for packaging. Note that the identity property will be included in the schema packaging script.