11 Jun 2012

Cannot update a database that has been registered as a data-tier application

Within SQL Server 2008 R2 a new feature was introduced, which was the data-tier application database. This feature allow developers / database administrators to package a database schema so that it could be deployed to any other database server and versioned. More about this feature can be found here: http://msdn.microsoft.com/en-us/library/ee240739(v=sql.105).aspx or http://dacguy.wordpress.com/

This feature has been included, as I have just found out, to previous versions: SQL Server 2005 via Services Pack 4 and 2008 via Services Pack 2. However SQL Server Management Studio, for SQL Server 2008, hasn't been updated with the tools for the new feature, you have to use the SQL Server 2008 R2 version. The reason for including the support to the previous version might have something to do with the release of the new SQL Server Data Tools (SSDT), the replacement to visual studio database professional, which uses DAC Packs and the framework to deploy a database.

I have been using the SSDT to deploy a new SQL Server 2008 (SP3) database for my current project. When I was trying to deploy an update to my database I was getting the following error: Cannot update a database that has been registered as a data-tier application without also updating its registration.

I was surprised to be getting this error, even know I did once registered my database as a data-tier application, but since then I had re-created the database a few times without registering as data-tier application. I was even getting the error when I manually drop and re-create the database. Dropping the database within SQL Sever 2008 doesn’t delete the data-tier application registration. So if another database is created with the same name the registration is then maintain when using the DAC framework.

To get around this problem you need to remove the data-tier application registration to this with SQL Server 2008 management studio you will have to run the following SQL statement: DELETE FROM [msdb].dbo.[sysdac_instances_internal] WHERE [instance_name] = '<Databasename,varchar,>' .

5 Jun 2012

How to create the old database project folders for SQL Server Data Tools.

I have been working with the new SQL server data tools database project. This is going to be the successor to database professional tool, also known as data dude. There have been several differences to how this product works.

One of them is the new tool project folder structure. There is now no folder structure within the project which means any file is automatically created into the root folder of the project. This is unlike database professionals, which had 2 comprehensive folder structures, the options were: by object type folder tree or schema related folder tree. My preference was always the object type folder structure, were script files which contain similar objects were all stored in the same folder e.g.: tables under a table folder, primary keys under the keys folder, views under a view folder etc.

If you missed the object tree folder structure, like I have, there is a method to create this structure very quickly within your new data base project by using the following steps:

  1. Create a new SQL server data tools project
  2. Once the project has been created, edit the project file.
    • Right mouse clicking on the project
    • Select Unloading Project option
    • Right mouse clicking on the project (which will be grey out)
    • Select the Edit Project option
  3. Then copy the contents of the file:DBProFolderList.xml into your new SQL server within the project node. I would recommend inserting before the closing tag to project.
  4. Reload the project.

However, once you have folder structure in place the tool will not honour it. You will have to spend time ensuring that object script files are created or moved into the correct location.  Also be aware that the schema compare tool, when reverse engineering into the project, uses the schema related tree folder structure to place newly created objects from the database.