Creating Data-Tier Applications in SQL Server 2008 R2


SQL Server 2008 R2 with Visual Studio 2010 has introduced a new feature called Data-Tier Applications that allows the database developer to author the database, build it and hand the DAC package to the database administrator for deployment. Read on to learn more…

Introduction

The collaboration between developer and database
administrator has always been a challenge in the application development life
cycle. Visual Studio 2005 Database Edition and Visual Studio 2008 Database Edition
with GDR bridged this gap to a great extent but it still lacked flexibility and
streamlined collaboration between the developer and database administrator for
deployment. With these releases both the developer and database administration
have to work together closely during deployment; in a nutshell even though
development became easier, the deployment was a real challenge because of being
iterative in nature and because several steps have to be performed manually.

SQL Server 2008 R2 with Visual Studio 2010 has
introduced a new feature called Data-Tier Applications (called DAC for
short), which makes database development, deployment and management much easier.
Although the first version of DAC is aimed at departmental applications (applications
which are not bigger in size), future versions will have support for
enterprise scale applications.

When you create a Data Tier Applications project (it shares
the same file extension as database projects i.e. dbproj) and build it, it
creates a self-contained unit of deployment called the DAC package (which has a
dacpack extension) that contains SQL Server instance objects, which are
associated with the database, database objects and deployment intent (also
called pre-requisite checks or deployment requirements of the application). In
other words, the database developer can author the database, build it and can
hand over the created DAC package to the database administrator for deployment.
The database administrator then simply goes and deploys the DAC package on the
required SQL instance or automates the process of deployment using PowerShell.

You can create a DAC package by
either using any of the following options, although in this article I will be talking about the first two
options:

  • Visual
    Studio 2010 for a new database or for any existing database
  • SQL
    Server 2008 R2 Management Studio for any existing database
  • PowerShell
    cmdlets for any existing database

Though a Data Data-Tier application can be extracted
from SQL Server 2000 or later versions, a DAC package can be deployed to SQL
Server 2008 SP2, SQL Server 2008 R2 and SQL Azure.

Creating Data Tier Applications using Visual Studio 2010

Visual
Studio 2010 Professional, Premium and Ultimate editions allow you to create
Data Tier Applications; you can find a list features by edition at Microsoft
Visual Studio
.

To
create Data Tier Applications, go to Visual Studio 2010 IDE (Integrated
Development Environment) and create a new project as shown below. On the New
Project Template page, select SQL Server under the Database node on the left and
in the detail pane, select the SQL Server Data Tier Application template. Next,
specify the name of the project and the location where you want to create the
project.

Data Tier Application Project Template

When
you click on the OK button, a new Data Tier Application will be created and the
structure of this project will be similar to that shown below. You can view
this project in either Solution Explorer or Schema View like this:

Exploring Data Tier Application

Figure 2 – Exploring Data Tier Application

So
far we have just created an empty Data Tier Application; it does not contain any
object so far. To continue from here there are three options, first start
creating required objects one by one in the IDE, second if you have already
created a script file then import it into the project and third import a
database from a SQL Server instance as a Data Tier Application as I am going to
demonstrate next. Right click on the project node in the Solution Explorer and click
on “Import Data-tier Application…” menu as shown below:

Importing Data Tier Application 1

Figure 3 – Importing Data Tier Application 1

The
first screen of the Import Data-tier Application wizard is a Welcome screen and
it explains the process of importing from a SQL Server database or from any
already available DAC package.

Importing Data Tier Application 2

Figure 4 – Importing Data Tier Application 2

The
second screen of the wizard lets you specify from where you want to import; if
you are importing from a SQL Server database you need to specify the connection
string to connect to that database or if you want to import from a DAC package
then you need to specify the location and name of the DAC package. You can
choose either of these two options, though for demonstration purposes I am
going to import from the SQL Server database as shown below.

Importing Data Tier Application 3

Figure 5 – Importing Data Tier Application 3

The
third screen of the wizard gives you a list of objects, which will be imported
from the specified source to the project as shown below.

Importing Data Tier Application 4

Figure 6 – Importing Data Tier Application 4

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Latest Articles