This topic provides information that may help you to understand the results when you use SQL Packager to create or upgrade a database. You may also wish to refer to Troubleshooting.

Database diagrams

SQL Packager does not package or upgrade database diagrams.

System tables

SQL Packager does not package or upgrade system tables.

Encrypted database objects

If you are packaging a SQL Server 2000 database that contains an encrypted user-defined function, stored procedure, trigger, or view and you have system administrator permissions, SQL Packager decrypts the object and you can view its internal SQL in the schema packaging script. If you do not have system administrator privileges, you cannot package the encrypted object.

SQL Packager cannot decrypt views, stored procedures, functions, and DML triggers that are encrypted on a SQL Server 2008 or SQL Server 2005 database. Therefore, SQL Packager cannot display the SQL code for the encrypted objects, and cannot package them.

Column order

If you are upgrading a database, column order is not forced unless you select the Force table column order to be identical schema packaging option.

For example, the latest version database has a table that contains ColA and ColB, in that order, and the previous version database has the same table but with ColB then ColA. If Force table column order to be identical is not selected, SQL Packager considers the tables to be identical. If the option is selected, SQL Packager considers the tables to be different and upgrades the table.

Renamed columns

If you are upgrading a database, SQL Packager attempts to recognize renamed columns by the similarity of the names and the data types of the columns. When a renamed column is recognized as such, SQL Packager renames the column as appropriate.

However, if the names and data types are very different, SQL Packager may consider the renamed column to be a completely different column. In this case, if ColA in the latest version database is renamed to ColB in the previous version database, when SQL Packager creates the upgrade script, ColA will be created in the previous version database as a new column and ColB will be deleted. To avoid data loss, before you run the package you must take care to preserve any data in the two columns, and merge them following the upgrade.

Updated views

Following an upgrade, if a view has not been updated by the package and it contains a SELECT * statement, you must refresh it using sp_refreshview, to reflect any changes that have been made to the underlying objects on which the view depends. Refer to your SQL Server documentation for more information.

Replication

If objects that are used in replication are upgraded, errors may occur. For example, SQL Packager cannot drop a table if it is used for replication.

Users

In Microsoft Windows, users are a composite of the domain name or computer name and the user name, for example Computer1\WindowsUser1. If you are upgrading a database, SQL Packager references only the user name, so that Computer1\User1 and Computer2\User1 would be considered as the same. Therefore, if you intend to upgrade users, ensure that their user names are different.

SQL Packager upgrades changes to users, such as changes to permissions. However, SQL Packager does not upgrade modifications to user passwords.

New users are created with the password: p@ssw0rd.

Filegroups

SQL Packager supports the upgrade of databases that use multiple filegroups. However, you must ensure that the filegroups have been created on the target server prior to upgrade. If the filegroups do not exist, the upgrade will fail.

CLR assemblies

When a CLR assembly is to be updated, if possible SQL Packager achieves this by using ALTER ASSEMBLY.

If SQL Packager determines that it would not be possible to use ALTER ASSEMBLY, the relevant table is rebuilt twice:

• in the first rebuild, the CLR type columns are converted to nvarchar
  The CLR type columns are dropped and recreated.
• in the second rebuild, the nvarchar data is converted to the final CLR type

Data is preserved. 

To force SQL Packager to use the double-table rebuild method, select the schema packaging option. Do not use ALTER ASSEMBLY to change CLR types.

Partition schemes and functions

In SQL Server 2008 and SQL Server 2005, partition schemes can be specified for tables so that the table is stored in several filegroups. By default, SQL Packager ignores filegroups. However, if you select the schema packaging option Consider next filegroups in partition schemes, SQL Packager upgrades the files.

In certain cases, for example when a partition function changes from left range to right range, it is necessary to drop and recreate partition functions and partition schemes. In these cases, the table is rebuilt twice:

• in the first table rebuild, the content is saved to a temporary filegroup
• in the second table rebuild, the table is migrated from the temporary filegroup to a new partition scheme

Data is preserved. 

Certificates, symmetric keys, and asymmetric keys

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. To ignore all certificates, symmetric keys, and asymmetric keys, select the Ignore certificates, symmetric and asymmetric keys schema packaging option.

Extended properties on databases

Extended properties on databases are always packaged if they differ. If you do not want them to be packaged, select the Ignore extended properties schema packaging option.