Robert's SQL Blog

So before I will show an alternative way to achieve application lifecycle management let’s get a shortlist of what tools you need to even begin to use the Data-tier Applications. You must have:

• SQL Server 2008 R2
• Visual Studio 2010 Ultimate or Premium (Professional has limited power here)

So, by just using SQL Server 2008 R2 and Visual Studio 2010 Ultimate can I achieve the same as this awesome thing called a DACPAC? Good news, you can and I will show you how.

I’ve created the same database as in the first post. Now I will fire up VS2010 and choose a SQL Server 2008 Database project template. It looks the same as the Dacpac template but there are some important differences which we will notice later. First off you will have to import the schema either by using a script or connecting to the database and import it from there.

First off is to add a CustomerAddress column. This is the same as with the Dacpac. When that’s done make life easy for the sake of the demo and right click the project and choose Properties. Select the Deploy tab and put in the right connection string and live database name. This is just for the sake of not typing all the stuff in the cmdbox. But if you are in a life environment get some guidance here.

Then start off a VS2010 command line to be able to use VSDBCMD which is a command line tool to deploy databases. And if you were thinking that a DBA does not have VS2010 check out this page to see how that’s dealt with. Navigate to the debug folder of your project where all the output will be created. Notice the files in the screenshot below. Some of theme you will see in the Data-tier as well. Now run VSDBCMD /a:Deploy /dd+ /manifest:[Manifestfile]. This will deploy your database.

Now that was easy, nobody was hurt and no DACPACs were killed using this procedure. So let’s see how VS2010 and this SQL Server 2008 Project does scenario 2 where we add a column with a default constraint.

1. Add the OrderDate column as DATE data type with NOT NULL to the .sql script for the Orders table. Notice how this is trimmed down from the Data-tier version. There are no constraints here. So where are they? Well, where they belong, at the constraints folder. Nice to see everything nice and tidy.
2. So navigate to the Constraint folder, right click and choose Add Item. From the next screen choose Default Constraint. Yes, it’s there. It was not with the Data-tier Application, but it is supported on this SQL Server 2008 Project.
   
3. So, add the constraint and click Build followed by Deploy.
4. Fire up the command line again and run the same command as before and presto. Database updated.

And you might want to take notice of the next points as well:

• No database was copied, dropped or created.
• No data was copied unless absolutely necessary.
• You can specify file groups and files within the database project if you like.

What’s the catch? Nothing, this is all available in Visual Studio Premium and Ultimate as with the Data-tier applications. But wait,it gets better. This stuff works on older SQL versions as well. And it can do more,like Schema compare and data compare. It also implements with your build process and can be included in web deployment.

So we have a great tool (which we already had as Add-in for Visual Studio 2008) that does amazing things with a database. This tool has been around for somewhat two years now and is getting better with every release. And here comes SQL 2008 R2 to the rescue with the Data-tier application. It does less, does it worse and just creates a new database in essence. In my opinion it has the words EPIC FAIL written all over it. Maybe it’s time Microsoft took a good look inside its own organization and starts normalizing. There is some redundant data there (remember LINQ2SQL and the Entity Framework).

The way the Data-tier application is implemented at this point I will not use it. If in a future release it will be extended and there will be more (live) migration possibilities with Multi Server Management it will be a whole different story.

Last week I attended the European PASS Conference in Neuss and I delivered a session on the new DBA features in SQL 2008 R2. One of these new features is the Data Tier Application. The more I’m looking into the new features the more they seem useless to me. In this post I want to take you through some common scenarios and see how the dacpac handles these. In my next post I will show you an alternative way.

Scenario 1 – Add a simple column to the database

As a start I will take a simple database with a Customers table and an Orders table.

So first you will have to register this database as a Data Tier Application. Open up a SQL Management Studio and navigate to the database, right click and choose Tasks -> Register Data-tier Application. This will guide you through a simple wizard and register the database as a Data Tier Application.

Now the users comes in and wants to have a column in the Customer table so he can saves the Address if a customer supplies one. The address column will not be mandatory. So again, right click the database, choose Tasks -> Extract Data-tier application and go through the wizard. After the wizard has finished a dacpac will be created in the chosen folder. Remember this folder.

We will hand over the dacpac to the developer so he can import it into his Visual Studio Environment and add the desired column to the table. He will then change the build number and build the solution. This will create a new dacpac which he in turn can give to the DBA who can upgrade the existing database.

So, pretend to be a developer and open up Visual Studio 2010. Start a new project and choose the SQL Server Data-tier Application template. When the project is created you can right-click the project and choose Import Data-tier Application. This will fire up a wizard. Point the wizard to the earlier dacpac and import it. When the import is successful you can navigate to the Customer table and add the column as illustrated in the screenshot below.

The next step is to navigate to the project properties and raise the buildnumber, add a description if desired and build the solution.

Once the solution is build a new dacpac is created which the DBA can use to upgrade the database. To upgrade it, select the Management folder in SQL Server Management Studio and expand Data-tier Applications. Right click your application and choose Upgrade Data-tier Application. Step through the wizard and watch the result closely.

So, what just happened?

1. SQL Created a new database.
2. SQL created the tables.
3. SQL disabled the constraints
4. SQL changed the real database to SINGLE_USER
5. SQL changed the real database to READ_ONLY
6. SQL copied all the data from the real database to the copy
7. SQL renamed the real database.
8. SQL renamed the copy to reflect the old real name.

So, instead of an upgraded database, you now have two databases, old and new. Which is cool for back-out scenario’s but not so cool if you have a large database. First it will take forever to load the data, second you end up with twice the data. But wait it gets better.

Check out the file properties of the old one and the new one. To illustrate it better I will show you a screenshot of what happened at my system.

So,what do you notice? My database files changed. In the original database I specified a growth in MB for the logfiles which is changed to a percentage growth. The explanation is easy. As you might recall a new database was created. And as most new databases the model database is used as template. Same case here. So you lose all your carefully planned growths and predefined sizes. Cool,imagine upgrading your 1TB database and see SQL move over the data when the logfile is set to 1MB en 10% growth. If it’s done you can send your VLF count to Kimberly Tripp or Paul Randall for trying to achieve the most VLF’s in a log file. And if you we’re wondering how about multiple files in the primary filegroup: Gone!!! Multiple filegroups: not supported by Data-tier Applications.

Scenario 2 – Add a column with a default value

Next scenario is adding a column to the Orders table that will hold the OrderDate. If an order date is not supplied a default value of 01/01/1970 must be used. So the developer will continue on his solution and add the column and then the constraint. But something’s going on here. You cannot add the constraint in the constraint folder in the project, it simply does not exist. So, why not add it to the .sql like the primary key constraint.

This will result in an error: SQL02014: A Default constraint can exist only at the column level in an ALTER TABLE statement.

So, last alternative is to add a ALTER TABLE statement in the post deployment scripts like this.

But if you upgrade the database the constraint is not added. In fact the post deployment script is never executed and if you would take the effort to deploy the dacpac from within Visual Studio you will be presented with an error saying the Orders object is not available. This is because the post deployment is executed in the MASTER database context.

So to recap, a Data-tier application is just another way to script out a database but in a bad way. If you just script out the database using the SQL Management Studio filegroups will be scripted as wel as multiple datafiles. And I haven’t bothered to touch a different collation setting but in this whole experience it wouldn’t surprise me if the collation setting was reset to the instance collation.

But do not be afraid, in my next post I will show you the proper way using the same tools you use for the Data-tier Application to deploy and upgrade databases.

One of the new features in the upcoming SQL Server 2008 R2 release is the Data Tier Application. The Data Tier Application is a single unit (dacpac) which contains all objects for the database. You can deploy it to a R2 server (for now) or pass it to a developer to have him alter it in Visual Studio 2010 en pass it back to upgrade the database.

So, that’s all cool but there are some caveats. First, creating a dacpac. If you create (extract) one from an existing database it cannot contain all SQL Server objects. Actually refer to the list in Books Online with what can be there http://msdn.microsoft.com/en-us/library/ee210549(v=SQL.105).aspx. But in short, Extended Stored Procedures will not work as well as CLR functions.

Another thing to notice is what happens when you upgrade a Data Tier Application. Check out this screenshot. It’s the final page after you successfully upgrade a Data Tier Application.

What is important to notice is that your database is not actually upgraded but a second database is created with all the objects, following the data is copied. When that’s all done, the databases are switched and made available to the users. So, seems logical but what if you have a 1TB database. When you have it registered as a Data Tier Application and are upgrading it as such you will end up with two 1TB databases. Not a big problem per se, just something to remember.

Another thing to remember is what if you were to change the database. Say, for example, a VARCHAR column has a length that’s too short and the developer is on a holiday. As an experienced DBA you corrected the developer’s error but what if you upgrade the Data Tier Application. The alteration will be detected and you will not be able to upgrade unless you accept the possible loss of data.

There is a lot more to tell about this feature. The best way is to go through this excellent document on the Microsoft website. It explains all the (im)possibilities and has some great examples on using PowerShell in combination with Data Tier Applications.