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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 4, 2010

Creating Data-Tier Applications in SQL Server 2008 R2 - Page 3

By Arshad Ali

Creating Data Tier Applications using SQL Server Management Studio (SSMS) SQL Server 2008 R2

To create a Data Tier Application (or a DAC package) in SSMS from an existing database, right click on the database in Object Explorer, click on Extract Data-tier application submenu under Tasks menu as shown below.

Extract DAC Package From SSMS 1
Figure 17 - Extract DAC Package From SSMS 1

The first screen of the wizard is a Welcome screen (by default this page appears but depending on your settings this page might not appear, in which case the wizard takes you to the second page directly) which gives you an understanding of what this wizard does for you.

Extract DAC Package From SSMS 2
Figure 18 - Extract DAC Package From SSMS 2

On the second screen of the wizard you need to specify the data-tier application name, its version, its description along with the location and name of the DAC package as shown below.

Extract DAC Package From SSMS 3
Figure 19 - Extract DAC Package From SSMS 3

The third screen is actually a review page, which lists your specified settings along with all the objects which can be extracted to the DAC package and which objects cannot be extracted, as shown below. Simply click on the Next button if all the objects can be exported (when all the objects of the database are supported under Data-tier application) to start the export operation.

Extract DAC Package From SSMS 4
Figure 20 - Extract DAC Package From SSMS 4

The fourth screen is the extract status page, which shows the status during and after the extraction process. Once the extraction is complete click on the Finish button to close the window and go to the specified location to get your DAC package.

Extract DAC Package From SSMS 5
Figure 21 - Extract DAC Package From SSMS 5

Viewing DAC Package Contents

As I said before, a DAC package is a single unit of deployment which contains all the objects to be deployed along with deployment intents in a zip format. As shown above you can create a DAC package when you build a data tier application in the Visual Studio 2010 or by extracting a data tier application from a SQL Server database. This single zip file contains everything needed for deployment and you can view the contents of this package by double clicking on the DAC package, which will bring up an “Unpack Microsoft SQL Server DAC Package File” utility to unzip the contents as shown; you need to specify the location where the contents will be un-zipped.

Viewing DAC Package Contents 1
Figure 22 - Viewing DAC Package Contents 1

After specifying the location, just click on the Unpack button in the above screen and contents will be unpacked on that location similar to this.

Viewing DAC Package Contents 2
Figure 23 - Viewing DAC Package Contents 2


SQL Server 2008 R2 with Visual Studio 2010 has introduced a new feature called Data-Tier Applications (also called DAC for short), which makes database development, deployment and management much easier. When you create Data Tier Applications and build it, it creates a single/self-contained unit of deployment called a DAC package that contains SQL Server instance objects, which are associated with the database, database objects and deployment intents.

In this article, I gave you an overview of Data-tier applications, then I talked about how it differs from database projects and finally I showed how you can create a DAC package using Visual Studio 2010 and SQL Server 2008 R2 Management Studio (SSMS). In my next article “Deploying Data-Tier Applications of SQL Server 2008 R2” my focus will be to deploy the created DAC package and analyzing different methods of deployment.


MSDN: Data-tier Application Overview

MSDN: Understanding Data-tier Applications

MSDN: Creating Data-tier Application Projects

» See All Articles by Columnist Arshad Ali

MS SQL Archives

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