Upgrading Data Tier Applications in SQL Server 2008 R2
December 2, 2010
Changes are inevitable and like many other things in life your application will change over time. The question is how to upgrade an already deployed Data Tier Application to a newer version; what are the different methods available for upgrade and what considerations should you take?
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:
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.