Upgrading Data Tier Applications in SQL Server 2008 R2


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?

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:

Changing the new version of Data Tier Application

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.

Upgrading Data Tier Application from Visual Studio

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.

Review upgrade status in Output window

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.

Upgrade a Data-tier application from SSMS

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.

Upgrade Data Tier Applications wizard - Welcome screen

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.

Upgrade Data Tier Applications wizard - Select DAC Package screen

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.

Upgrade Data Tier Applications wizard - Review Policy screen

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.

pgrade Data Tier Applications wizard - Detect Change screen

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.

Upgrade Data Tier Applications wizard - Summary screen

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.

Upgrade Data Tier Applications wizard - Upgrade DAC screen

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.

Verify the upgrade using SSMS

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

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Latest Articles