Deploying Data-Tier Applications of SQL Server 2008 R2


SQL Server 2008 R2 Data-Tier Applications make database development, deployment and management much easier. When you create and build a Data Tier Application, it creates a single, self-contained unit of deployment called a DAC package. Arshad Ali shows you how to deploy the created DAC package and discuss the different methods of deployment.

Introduction

Data-Tier Applications of SQL Server 2008 R2 make
database development, deployment and management much easier. When you create a Data
Tier Application and build it, it creates a single, self-contained unit of
deployment called a DAC package in compressed format that contains SQL Server
instance objects that are associated with the database, database objects and
deployment intents.

In my last article “Creating Data-Tier Applications in SQL Server 2008 R2” 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 this article my focus is to deploy the created DAC
package and discuss the different methods of deployment.

A DAC package is very much like the Windows installer
MSI file of any application that contains everything required for deployment. You
can deploy a created DAC package to an instance of SQL Server 2008 SP2, SQL
Server 2008 R2 and SQL Azure by either using:

  • Visual
    Studio 2010 Premium, Professional and Ultimate editions
  • SQL
    Server 2008 R2 Management Studio

Deployment using Visual Studio 2010

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

Data tier application allows you to declare deployment
intents, which are nothing but the pre-requisite checks before deployment. To
define the deployment intent, double click on the ServerSelection.sqlpolicy
file under Properties in Solution Explorer as shown below.

Defining Deployment Intents 1

Figure 1 – Defining Deployment Intents 1

The
policy page allows you to define different policies (intents); for example, I
only want to deploy my data tier application on SQL Server 2008 (Version: 10)
and higher and this is what I have declared as you can see below.

Defining Deployment Intents 2

Figure 2 – Defining Deployment Intents 2

The
policy selection page allows you to include as many as intents you want to be
included with your DAC package; those added intents will appear in the bottom
section of the page as shown below.

Defining Deployment Intents 3

Figure 3 – Defining Deployment Intents 3

To
deploy your DAC package, right click on the project in the solution explorer
and click on the Deploy menu as shown below. The deployment actually includes
building the DAC package from the project and then deploys it on the specified
server.

Deployment from Solution Explorer

Figure 4 – Deployment from Solution Explorer

Alternatively,
you can deploy the project by clicking on the Deploy Solution submenu under the
Build menu as shown below; if the project is open in the IDE (Integrated
Development Environment).

Deployment from Menu bar

Figure 5 – Deployment from Menu bar

I
am using the same Data-tier application that I created in my last article “Creating
Data-Tier Applications in SQL Server 2008 R2
” for deployment. When
I tried deploying the build it failed because I didn’t specify the target SQL
Server instance where the DAC package is to be deployed, (see the highlighted
part in the below image).

Failed Deployment Status

Figure 6 – Failed Deployment Status

To
define the target connection string for deployment, right click on the project
in the Solution Explorer and then click on the Properties menu. In the project
properties screen select Deploy on the left and click on the Edit button to
specify the target connection string as shown below.

Defining Connection String For Deployment

Figure 7 – Defining Connection String For Deployment

Once
you have defined the correct target connection string, try to deploy again. This
time it will pass and you can monitor the Output window for deployment status.

Succeeded Deployment Status

Figure 8 – Succeeded Deployment Status

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