Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 17, 2010

Register and Delete Data-tier Applications in SQL Server 2008 R2

By Arshad Ali

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.jpg
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.jpg
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.jpg
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.jpg
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.jpg
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.jpg
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.jpg
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.jpg
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.jpg
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.jpg
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.jpg
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

MSDN: Upgrading Data-tier Applications

» See All Articles by Columnist Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM