Upgrading Data Tier Applications in SQL Server 2008 R2

December 2, 2010

Introduction

SQL Server 2008 R2 makes database development, deployment and management quite a bit easier with a new feature called Data Tier Applications (currently only for departmental applications). When you create and build Data Tier Application, it creates a self-contained single unit of deployment (called a DAC package) in zip format that contains the SQL Server instance objects, which are associated with your database application, database objects itself and deployment intents (also called prerequisite checks, which are executed before deployment to ensure the database is installed on the correct environment).

In my last article, Creating Data Tier Applications in SQL Server 2008 R2, I gave you an overview of Data Tier Applications, then I talked about how it differs from database projects and finally I showed how you can create a DAC package using Visual Studio 2010 and SQL Server 2008 R2 Management Studio (SSMS). In another article, Deploying Data Tier Applications of SQL Server 2008 R2, I talked about the different methods of deploying Data Tier Applications.

Changes are inevitable and like many other things in life, your application will also change over time, either to add new objects, update existing objects, drop deprecated objects, etc. So now, the question is how to upgrade an already deployed Data Tier Application to a newer version; what are different methods available for upgrade and what considerations should you take?

Understanding the Data-tier application upgrade process

The upgrade process does not directly upgrade the current database but rather it creates a new database with a temporary name (by appending the new version to the Data Tier Application), creates all the required objects in the new database, moves the necessary data from the old database to the newly created database and finally renames the old database with the temporary name (by appending the old version to the Data Tier Application) and renames the new temporarily created database with the name of the Data Tier Application. Please note, since two databases are being maintained side by side, you need to have at least double the space of the existing database before the upgrade.

The upgrade process of the Data Tier Application does a three-way schema comparison; it compares the deployed/registered Data Tier Application (current DAC definition in msdb) and the current database with the new version of the DAC package to be deployed. This three-way check results in the following scenarios:

• A table is the same in all three places and hence in the new database, the table will be created and data will be moved from the old database to new database by the wizard itself.
• A table is not available in the DAC definition or database but available in the DAC package, which means it's a new table being added in this new version; the wizard creates an empty table in the new database.
• A table exists in both the DAC definition and current database but is not available in the DAC package, which means this table existed before but has been removed in the new version and hence the wizard does not create that table in the new database.
• In these scenarios you have to take manual step of data transfer:

• A table is not available in the DAC definition but is available in the current database and DAC package. This means this table was not deployed last time and created later in the current database directly; hence wizard will create the table structure as per the DAC package and you need to manually move the data from the current database to new database.
• A table exists at all three places (DAC definition, current database and DAC package) but the structure in the DAC package is different. In this case, the table will be created as per the new structure in the DAC package. If the column names don't match, no data is transferred and you need to manually move the data from the old database to the new database.

Please make sure you review the comparison carefully so that you don't remove or not move data for a required table inadvertently; if the wizard reports possible data loss then do thorough research about the impact before the upgrade.

Similar to the deployment discussed in my last article, there are two ways to change your Data Tier Application and upgrade it, i.e. using Visual Studio 2010 and SQL Server Management Studio (SSMS). The third option, of course, is to use Windows PowerShell commands.

Upgrade Data Tier Applications using Visual Studio 2010

Visual Studio 2010 Professional, Premium and Ultimate editions offer the ability to create Data Tier Applications; you can find a feature list by edition on the Microsoft Product Comparison page.

You can do the changes in your Data Tier Application project in Visual Studio 2010. (To learn  about creating a Data Tier Application in Visual Studio, see Creating Data Tier Applications in SQL Server 2008 R2) and simply do the deployment. This time the IDE will ensure the deployed application on the target instance is upgraded to the newer version and the old version is retained by renaming it side by side.

Make sure you change the version of your Data Tier Application before building/deploying the project so that you can easily segregate the two versions. To change the version, right click on the Project in the Solution Explorer and click on the Properties menu, then under the Project Settings page change the version and its description appropriately as shown below:

Figure 1 - Changing the new version of Data Tier Application

To upgrade an previously deployed Data Tier Application, right click on the project in the solution explorer and click on the Deploy menu as shown below. The deployment actually includes building the upgraded DAC package with the new version from the project and then upgrades (this time Deploy will actually do the upgrade as the target instance already has a Data Tier Application with same name) the target with it.

Figure 2 - Upgrading Data Tier Application from Visual Studio

You can watch the Output window to see the progress details and status of upgrading the Data Tier Application on the target SQL Server instance similar as shown below.

Figure 3 - Review upgrade status in Output window

Upgrade Data Tier Applications using SSMS

As I discussed in Creating Data Tier Applications in SQL Server 2008 R2, you can also create a Data Tier Application (DAC package) by extracting from a database in SSMS. This could be another source for your Data Tier Application, I mean you can do development on your database in your local database environment, extract the latest DAC package, and deploy it on the target. Once you have the latest DAC package, right click on your deployed Data Tier Application on the target instance and click on the Upgrade Data-tier application menu as shown below to launch Upgrade Data-tier application wizard.

Figure 4 - Upgrade a Data-tier application from SSMS

The first screen is a welcome screen, which tells you about the procedure and steps of upgrading your Data Tier Application as shown below. By default, this screen appears every time, even though you can choose not to display this screen again by checking "Do not show this page again" checkbox.

Figure 5 - Upgrade Data Tier Applications wizard - Welcome screen

On the next "Select package" page, you browse and select your latest valid DAC package (with *.dacpac extension). The wizard will then read information such as name, version and description from it and display it in the bottom section on the screen as shown below. Please note the name of currently deployed Data Tier Application must be the same as it is in the new DAC package in order to continue the upgrade.

Figure 6 - Upgrade Data Tier Applications wizard - Select DAC Package screen

The next "Review Policy" page runs the prerequisite checks (deployment intents) if there are any; and list its result here. Your target environment must pass all these prerequisite checks before you can actually upgrade your data-tier application.

Figure 7 - Upgrade Data Tier Applications wizard - Review Policy screen

The next "Detect Change" page takes some time as it actually does the comparison of the deployed/registered Data Tier Application (current DAC definition in msdb), current database with the new version of DAC package to be deployed and prompts for your permission if it finds a possible data loss after the upgrade. In that case, you need to move data from the renamed (old) database to the new database after the upgrade manually as discussed above in the different scenarios.

Figure 8 - Upgrade Data Tier Applications wizard - Detect Change screen

The next "Summary" page gives your summary of your selection, which you made throughout different screens of the wizard; if you want to change your selection, you can go back and change it or else you can click on the Next button to start the upgrade process.

Figure 9 - Upgrade Data Tier Applications wizard - Summary screen

The final "Upgrade DAC" page displays the status of the upgrade. Notice there a couple of tasks that are performed during upgrade process. For example, a new database is created with a temporary name, objects are created in the new database, the existing database is set to read-only mode (if it is not already in read-only mode) and data is moved from the existing database to newly created database. Then the existing database is set back to read-write mode (if it was in read-write mode before upgrade), the existing database is renamed by appending the old version number to it and the new database is renamed with the Data Tier Application name, etc. The status of each step is shown under the Result column; if any of these fail, you can click on it to view the reason for the failure.

Figure 10 - Upgrade Data Tier Applications wizard - Upgrade DAC screen

Finally you can review your upgrade by connecting to the target server, as you can notice below, the old database is not dropped but rather renamed and retained (side by side) whereas a new database is created with upgraded objects.

Figure 11 - Verify the upgrade using SSMS

Conclusion

In this article, I talked about the upgrade process of Data Tier Applications, different considerations to take while upgrading a Data Tier Application and finally I showed you different ways to upgrade your Data Tier Application. In my next article "Register and Delete Data Tier Applications of SQL Server 2008 R2" my focus will be to show registering a database as a Data Tier Application and then deleting it if required.

References

MSDN: Understanding Data Tier Applications

MSDN: Creating Data-tier Application Projects

MSDN: Upgrading Data Tier Applications

Why Developers Should Consider SQL Server 2008 R2

» See All Articles by Columnist Arshad Ali