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,>' .


J-Man said...

Thanks for writing up your findings - sure it saved me a lot of time working out the fix.

Colin McNulty said...

Cheers fella, worked a treat.

Hilmar Nooitgedagt said...

Thanks, this gem saved me so much time.
After wasting time trying sqlpackage.exe, PowerShell and SSMS I just wish I had found it sooner :-)