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.

The november CTP is now available to everyone for download (http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx). So, what’s new, besides all the BI stuff. Well more editions, the Data Center and Parallel Data Center. Check out these on the SQL Server 2008 R2 site. But another option that’s newly introduced is sysprep for SQL.

If you’re in the game of deploying operating systems across your enterprise there is a good chance you already know sysprep for Windows. With sysprep you clean out all the ‘user’ stuff on a clean installation and create an image out of it. You distribute this image on new machines and start it off. A mini-installation will start, final drivers will be installed and you will be presented with a brand new OS. And now, SQL joins the game.

The following steps will guide you through the process.

1. First, install Windows and apply all patches.
2. Then start the SQL installation to install prerequisites.
3. When everything is ready to install SQL select Advanced in the SQL Server Installation Center and click Image Preparation of a stand-alone instance of SQL Server.
   
4. Click OK after the Setup Support Rules have finished (and completed OK).
5. Accept the license terms and click Next followed by Install to install the Setup Support Files. Click Next after the installation is finished.
6. You are now presented with the familiar Feature Selection page, except this is not as familiar as you might remember. This is due to the fact that sysprep is only supported for the Database Engine and Reporting Services.
   
7. Select the options of your choice. In this example I will only select Database Engine Services. Click Next to continue the installation.
8. On the following page you will need to specify the instance id, eg INSTANCE1. Click Next to proceed to the next screen.
9. The following screen provides information so click Next through all of them and click Prepare on the final one.
10. Next step is to start sysprep to create an image of this installation. Sysprep is included on the Windows installation media or as a download for Windows 2003 and before. For the newer versions it’s in the %WINDIR%system32sysprep folder. For this example I will not go into much detail on sysprep and just start it, choose reseal or Enter System Out-of-Box Experience and let the machine shutdown.
11. After sysprep is finished you have an image which you can distribute within your organization, or you can safely copy it on your disk to create many temporary instances for demos.
12. So when you deploy the image and start it off to create a new machine you will be presented with a complete configuration option in the SQL Start menu.
     
13. You click this to complete the preconfigured installation. What amazed me that in the preparation stage I had to supply an instance id and in the finalization stage you must apply the instance name. I think of these as a couple, closely bound to each other. But this is still a CTP,perhaps things will change in the final product.
    

14.  

15. So all it took from start to finish was 15 minutes for a fresh SQL instance.

Of course you have to distribute an image and that will take some time,but you cut time from installing the binaries and all. It’s really an option targeted at Hyper-V deployments where you can create complete server images based on server roles.

With Windows 7 RC also Windows 2008 R2 RC came available. One of the new features in the core edition is the availability of the .NET Framework. Having the .NET framework would open the way to install SQL Server 2008 on core. Why install SQL on core? Well, because you can. Or because of the smaller footprint, the lack of a gui (which will scare of all the next-next-finish professionals). There isn’t a shutdown button making it impossible for newbie’s to accidently shutdown the server.

But, things can get complex. You cannot click through the numerous installation options. One way would be to create an installation file with a gui and use that to install on Windows Core.

So how does it work? Well, first you need to get a clean core installation. Pretty straightforward stuff there, put the dvd in, or whatever you are using to install from, and follow all the steps until you are greeted with a shiny, new, eh… command prompt. Check out the internet on how to rename the computer, assign a static ip address (if needed), create an user account and so on.

So to start off you must install some features,the .NET Framework,WOW64 and Powershell. These four commands will do the trick (case sensitive!!!):

• start /w ocsetup NetFx3-ServerCore
• start /w ocsetup ServerCore-WOW64
• start /w ocsetup NetFx3-ServerCore-WOW64
• start /w ocsetup MicrosoftWindowsPowerShell
• shutdown –r –t 0

After the server is restarted I created a local user account to run the SQL Server service using net user sqlserver secretpassword /add.

You can install SQL Server from the command line. More details about the different switches is in Books Online. The next command will get you started (for readability displayed on more lines):

Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER

/SQLSVCACCOUNT=”SQL2K8COREsqlservice” /SQLSVCPASSWORD=”secretpassword”
/SQLSYSADMINACCOUNTS=”SQL2K8COREsqladmin”

/AGTSVCACCOUNT=”NT AUTHORITYNetwork Service”

After that’s finished logoff and log back in and try to connect using sqlcmd –E. I forgot to set the SQL Agent to auto start during setup but with sc config sqlserveragent start= AUTO that’s easily fixed.

I wanted to change the authentication mode to Mixed. This can easily be done with SMO and a couple of Powershell lines:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$server = new-object "Microsoft.SqlServer.Management.Smo.Server" "{SQL Server}" $server.Settings.LoginMode = "Mixed"

$server.Settings.Alter()

To make sure the rest of the network can access your SQL Server, if needed, you will need to open up the firewall with netsh advfirewall add rule name=”SQL Server tcp 1433” dir=in action=allow protocol=TCP localport=1433

So, that’s all to get SQL Server 2008 running on Windows 2008 R2 Core. Next step is to evaluate the pros and cons running core.

At my work a user called use saying we couldn’t migrate to SQL 2008 because there was a big bug in it. It had to do with sorting data. Instead of properly sorting the data, as SQL 2000 did, SQL 2008 appeared to not sort the data at all. So, we did what every IT pro would do in this situation. Assume the user is always right and you’re not, and migrate everything back to SQL 2000 and call PSS to submit a bug in SQL 2008. Well, that’s what you should never do.

So we tracked the object that would display the user data to check if the sorting was in SQL or in the application. The sorting was in SQL and to be more precise, inside a view. Something like this:

create view Samples.MySortedView  as  select top 100 percent      column1,      column2  from       Samples.OrderByView  order by      column2 

So, not much wrong here you would say? Well, if you execute the statement within the view the data is sorted by column2. If you would execute select column1, column2 from Samples.MySortedView the data would not be sorted as you can see in this screenshot.

So? Big bug in SQL Server or not so smart SQL Developers. If you check the execution plan you will see why the view is not sorted.

The execution plan shows that the view is not sorted. The reason for this is actually explained in Books Online.

The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

This means that the TOP in a ORDER BY is only used to determine the rows that are returned. The query engine will first order the list and then return the top rows you specified. And it is here where SQL 2005/2008 differ from SQL 2000. SQL 2005/2008 will say,you want it all (100 percent) so there is no need to sort it first. The sorting is only to determine which rows are returned,and not in which order. That’s a side effect. To prove this you can change to view definition to something other than 100 percent and you will see the data will get sorted. Of course not the real solution. The real solution is to put the order by outside the view (select column1, column2 from Samples.MySortedView order by column2).

So, proven to the user, nothing wrong with SQL. It’s your developer that’s wrong, I suggest you upgrade the developer to a newer version.