Upgrading Data-tier Applications

The Upgrade Process

The SQL Server data-tier application upgrade process converts a deployed DAC to a different version of the same application. For example, to upgrade from Finance version 1.0 to Finance version 2.0. The following steps illustrate a simple deployment process:

• A database developer completes the development of a Finance version 1.0 DAC, and then builds a FinanceVersion1.dacpac DAC package.

• A database administrator deploys the Finance 1.0 DAC to production. The production instance of the database engine now has a deployed DAC that has an application name of Finance, a version of 1.0, and an associated database named Finance.

• The database developer then starts work on the next version, and when development completes builds a FinanceVersion2.dacpac.

• The database administrator plans the DAC upgrade process. They back up the Finance database. They review the contents of the DAC package and a report of the actions the upgrade will take to ensure they do not cause problems in the production database.

• The database administrator then performs a DAC upgrade specifying both the current deployed version 1.0 DAC and the version 2.0 DAC package. There are two options for performing the upgrade:

  • Perform an in-place upgrade by using a Windows PowerShell script that calls the DAC IncrementalUpgrade() method. The in-place upgrade alters the schema of the existing database to match he schema defined in the new version of the DAC.

  • Perform a side-by-side upgrade by using either the DAC Upgrade() method or the Upgrade a Data-tier Application wizard. The side-by-side upgrade creates a new database with the new schema and transfers the data from the original database to the new one.

• After the upgrade completes, the upgraded DAC still has an application name of Finance and an associated database named Finance, but the DAC version is now 2.0.

Changing Databases After Deployment

A database is said to have drifted from its associated DAC if changes are made to the database by using some mechanism other than upgrading the DAC. Examples include using a CREATE TRIGGER statement to add a new trigger, or using an ALTER TABLE statement to change the structure of a table. Some types of changes may prevent the DAC upgrade process from completing, depending on the specified upgrade options. The changes will also not be present in the upgraded database.

The DAC upgrade operation checks for differences between the schema of the current database and the DAC definition stored in the msdb system database. If it finds differences, it issues a warning dialog that lists the differences. You should only proceed with the upgrade after you have analyzed the reported differences and put in place processes or scripts to transfer any objects or data that the upgrade cannot transfer but are required in the new system.

After the upgrade completes, the database contains all objects defined in the new version of the DAC, and in the form specified in the new DAC. In most cases, this will be the form required by the version of the applications associated with the new version of the DAC and database. Take care in analyzing the upgrade change report, and only transfer objects that are needed in the new system. For example, it may not be an error if several objects present in the current DAC and database are missing from the new DAC. They may be associated with features that were dropped or redesigned in the new versions of the applications that use the DAC.

For an in-place upgrade, the changed objects are not retained. You must save the object definitions and data before starting the upgrade. For a side-by-side upgrade, the objects and data are retained in the renamed original database. All of the changed objects must be transferred to the new database manually.

For more information about ideas on how to script the objects to be transferred to the new database, see How to: Generate a Script (SQL Server Management Studio).

DAC projects can specify pre-deployment and post-deployment scripts. These are Transact-SQL scripts that can perform any action, including creating objects not supported in a DAC. If the original DAC contained a post-deployment script that created objects not supported in a DAC, those objects must be upgraded separately using a mechanism that allows for those objects being dropped during an in-place upgrade, or left in the original database by a side-by-side upgrade.

In-place Upgrade

An in-place upgrade performs the following actions:

• Verifies that the deployed DAC and the DAC package both have the same application name (for example, both names are set to Finance). It also evaluates whether the instance of the Database Engine meets the requirements specified in the server selection policy (if defined) in the new version of the DAC, and whether the existing database contains objects not supported in a DAC.

• Performs all of the modifications, such as CREATE, ALTER, and DROP statements, required to make the schema of the database match the schema defined in the new version of the DAC.

If the Ignore Data Loss option is set to True, the DAC upgrade may perform actions that remove data. For example, if a table exists in the current database but not in the schema of the new DAC, the upgrade drops the table. Before performing the operation, the database administrator must archive any data that may be needed after the upgrade.

In-place upgrades are supported on both SQL Azure and instances of the Database Engine. In-place upgrades require the DAC Framework 1.1 and a new DAC upgrade wizard, which are included in SQL Server 2008 R2 Service Pack 1 (SP1).

Side-by-side Upgrade

Side-by-side upgrades are not supported on SQL Azure, and will not be supported in the next version of SQL Server. The recommended upgrade mechanism is the in-place upgrade included in SQL Server 2008 R2 SP1.

A side-by-side upgrade performs the following actions:

• Verifies that the deployed DAC and the DAC package both have the same application name (for example, both names are set to Finance). It also evaluates whether the instance of the Database Engine meets the requirements specified in the server selection policy (if defined) in the new version of the DAC, and whether the existing database contains objects not supported in a DAC.

• Deploys the new version of the DAC from the DAC package. This creates a new database with a temporary name.

• Sets the original database to read-only if it is not already in read-only mode, and copies the data to the new database.

• If the original database was in read-only mode, the new database is set to read-only.

• The original database is renamed by appending a string to the end of the database name.

• The new database is assigned the original database name.

• After the database administrator has confirmed that the new database is operating correctly, the original database can be archived.

Database changes may affect whether the data in a table is transferred to the new database. Tables can be in these states regarding database changes:

• The table structure is the same in all three locations: the current DAC definition in msdb, the current database, and the new DAC. The table will be present in the new database and the upgrade operation will transfer the data to the new database. The transfer is done using an INSERT statement based on a SELECT of the old table.

• Table does not exist in the current DAC definition or database, but is defined in the new DAC. The table will be present in the new database, but have no data because there is no existing data to transfer.

• The table does not exist in the current DAC definition, but does exist in the current database and the new DAC. The table will be present in the new database, but the upgrade operation will not transfer the data. Manually transfer the data after the upgrade completes.

• The table exists in the current DAC definition and database, but not in the new DAC. The table will not exist in the new database. If the table is required in the new system, manually create the table and transfer the data after the upgrade completes.

• The table exists in all three locations, but the structure of the table in the current DAC definition and database are different than the structure of the table in the new DAC. The table will be present in the new database with the structure defined in the new DAC.

  • If the table and column names do not match, the upgrade operation will report that it has not transferred the data. Manually transfer the data after the upgrade completes.

  • If the table name and column names are the same, the upgrade operation will attempt to transfer the data. If the data type of one or more columns have changed and are not compatible, the INSERT statement will fail, which will cause the upgrade process to roll back.

You can use any mechanism to transfer the data to the new version of the table. You can use an INSERT statement that selects from the old version of the table. You can also use one of the bulk insert techniques discussed in Importing and Exporting Bulk Data.

See Also

Concepts

Change History