Register and Delete Data-tier Applications in SQL Server 2008 R2
December 17, 2010
SQL Server 2008 R2 allows its databases to be registered as Data-tier Applications without creating and deploying a DAC package. This article shows you how to register and delete a Data-tier Application using the wizards available in SQL Server 2008 R2 Management Studio (SSMS).
The Data-tier Application feature of SQL Server 2008 R2 makes database development, deployment and management much easier. It creates a self-contained single unit of deployment in compressed format that contains the SQL Server instance objects, which are associated with your database, the database objects and deployment intents (also called prerequisite checks, which are executed before deployment to ensure the database is installed on the correct environment).
In my last couple of articles I talked about creating Data-tier Application, deploying Data-tier Application and upgrading Data-tier Application using either Visual Studio 2010 or SQL Server 2008 R2 Management Studio (SSMS). However, what if you already have a database and you want to create a Data-tier Application for it In-Place? Is there any way to register an already deployed database as a Data-tier Application, if so, then how? Additionally, how do you delete a registered Data-tier Application from a SQL Server instance when required?
SQL Server 2008 R2 allows its databases to be registered as Data-tier Applications In-Place; in other words without creating a DAC package and then deploying it. You can delete a registered Data-tier Application from SQL Server 2008 R2 instance in the same way. There are two ways to register and delete a Data-tier Application i.e. using the wizards available in SQL Server 2008 R2 Management Studio (SSMS) and using PowerShell commands, though in this article I will be talking about using these wizards.
Register a database as Data-tier Application In-Place
If you have an existing database already deployed and you want to create a Data-tier Application for it, you can register it as Data-tier Application in-place, in other words you don't need to create a DAC package and then deploy it but rather you can simply register an existing database as a Data-tier Application. During registration, the associated metadata or DAC definition are stored in the msdb system database.
Right click on the desired database under the Databases node in Object Explorer and then go to Tasks -> "Register as Data-tier Application", click on it as shown below.
The first screen of the "Register Data-tier Application" wizard is a welcome screen, which tells about the different steps of the wizard as shown below.
On the second screen of the "Register Data-tier Application" wizard, you need to specify the name of the Data-tier Application, its version and optionally its small description as shown below.
The third screen of the "Register Data-tier Application" wizard gives the validation details and summary as shown below. If you remember from my earlier article, not all the objects are supported in Data-tier Applications and hence this screen provides a list of supported and unsupported objects by reading the underlying database.
The final screen of the "Register Data-tier Application" wizard shows the status of registration, it shows Success under Result column if the registration was successful or it shows a link, which when clicked on will show the reasons of failure.
To verify the registered Data-tier Application, expand the "Data-tier Applications" node under Management in Object Explorer and locate your application as shown below.
Delete a registered Data-tier Application
Whenever needed you can delete a registered/deployed Data-tier Application from a SQL Server instance and its associated metadata from the msdb using the "Delete Data-tier Application" wizard.
Go to the Data-tier Applications node under the Management node in Object Explorer and locate your application, right click on it and then click on "Delete Data-tier Application…" in the pop up menu as shown below to launch the "Delete Data-tier Application" wizard.
The first screen of the "Delete Data-tier Application" wizard is a welcome screen, which guides you through the deletion processes and steps as shown below.
The second screen of the "Delete Data-tier Application" wizard is a main screen where you actually specify what you want to do during deletion of Data-tier Application. You have three options. The first option simply removes the associated metadata (DAC definition) from msdb and let the database be there on the SQL Server instance. The second option removes the associated metadata and detaches the database from the SQL instance (which can be later attached if in case needed). Finally, the third option not only removes the associated metadata but also deletes the database from the SQL Server instance. Please note, after deletion of the Data-tier Application, it no longer exists on the SQL Server instance and hence it does not appear in SQL Server Management Studio under Management->Data-tier Applications. In none of the cases logins are removed.
The third screen of the "Delete Data-tier Application" wizard is a summary screen, which shows your selection for review; if needed you can go back and do the required changes and proceed further.
The fourth screen of the "Delete Data-tier Application" wizard shows the status of the Data-tier Application deletion as shown below. It shows Success under the Result column if the deletion was successful or it shows the error message for failure.
In this article, I talked about how to register a database as a Data-tier Application and how to register an already deployed database In-Place and then I talked about what different Data-tier Application deletion methods are available and how each of them differs from others.
In my next article, I will be talking about managing, monitoring and troubleshooting Data-tier Applications.