Demo Transcript: Performing an In-Place Upgrade of a SQL Server 2012 Data-Tier Application

SQL11UPD04-TSCRIPT-04

This wiki is a transcript of a previously recorded video.

Performing an In-Place Upgrade of a SQL Server 2012 Data-Tier Application

Hi, I’m Greg Low. I’m a SQL Server MVP and part of the Microsoft Regional Director Program. In this demonstration, I want to show you the changes associated with upgrading data-tier applications in SQL Server 2012.

This is SQL Server Management Studio, and under the Management node, I have a list of any data-tier applications that are installed. This system currently doesn’t have any. So I’ll deploy a data-tier application. Say, next I’ll find the package file. And so I have an initial version with a .dacpac file of the data-tier application. This has been one built by our developers. The package is validated. We can choose locations for where the database would be created. And we deploy it.

Now it’s important to understand the data-tier applications, if I refresh this, that we have an entry here under Management for the data-tier application. In addition, under Databases in the node, we’ve also got an entry in here for that same database. Now, the database itself you shouldn’t update, or do work on directly on the schema. It should always be done via the data-tier application that’s been created.

Now in this case, I’m going to modify some data, so let’s grab a file that’s going to make some changes. And we’ll just insert some data into the application. Now we’ve received an update to the data-tier app. So we step back down to the data-tier applications folder and say, I want to upgrade the data-tier app. Now in previous versions, this did a very, very different process. What it would’ve done is created a new database, moved the schema details into the new one, and then performed a migration of all the data from the old database across to the new one. At the end, you’d end up with an upgraded version of the database, you’d also still have the previous version of the database, renamed with basically a GUWord appended to the end of the file name. This is changed in this version of the program, and it was one of the main requests that came from end users.

So let’s say, let’s find the package that’s been involved in the upgrade. Again, it verifies the version of this. It makes sure that the underlying database still was the way that we left it in the previous version as it expects. Now we have a choice as to what occurs if something went wrong during the upgrade process. Most of the time, if you have enough transaction log space to deal with this, you would choose to roll back on failure, and it means that you’d never end up in any situation where the work was either half done or, you end up with, you know, either half done or not done at all. You don’t end up with any sort of interim situation.

So I’ll say, next. This will provide us then an upgrade plan. So next we’ll say, let’s run and finish. And at this point, the data-tier application has been upgraded. This is now an upgraded version of it. And if we go back and look at our list of databases, and refresh this list, I’ve still only got the single BranchManagement database I had before. If I look at my tables, I still have the data I inserted in there before, not lost, and the migration’s been completed without creating a new copy of the data.

I hope you find this is a very useful addition to your data-tier applications.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)