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).
Introduction
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.
Figure 1 – Register an existing database as Data-tier Application
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.
Figure 2 – Register Data-tier Application wizard – Welcome screen
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.
Figure 3 – Register Data-tier Application wizard – Set Properties
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.
Figure 4 – Register Data-tier Application wizard – Validation and Summary
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.
Figure 5 – Register Data-tier Application wizard – Completion screen
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.
Figure 6 – Verify the registered Data-tier Application
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.
Figure 7 – Delete registered Data-tier Application
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.
Figure 8 – Delete Data-tier Application wizard – Welcome screen
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.
Figure 9 – Delete Data-tier Application wizard – Choose deletion method
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.
Figure 10 – Delete Data-tier Application wizard – Selection Summary
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.
Figure 11 – Delete Data-tier Application wizard – Completion screen
Conclusion
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.
References
MSDN: Understanding
Data-tier Applications
MSDN: Implementing
Data-tier Applications
