Deploying Data-Tier Applications of SQL Server 2008 R2

Wednesday Nov 17th 2010 by Arshad Ali
Share:

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 discusses the different methods of deployment.

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

Deployment using SQL Server Management Studio (SSMS) SQL Server 2008 R2

To deploy an already created DAC package, right click on “Data-tier applications” under the Management node in Object Explorer of SSMS and click on “Deploy Data-tier Application” menu as shown below.

 Deployment From SSMS
Figure 9 - Deployment From SSMS

This will launch the Deploy Data Tier Application wizard. The first screen of the wizard is the Welcome screen, which tells what this wizard is going to do for deployment as shown below.

Deployment Wizard - Welcome Screen
Figure 10 - Deployment Wizard - Welcome Screen

The next screen of the wizard asks you the location and name of the DAC package, which you want to deploy. Then it reads details from the specified DAC and displays it in the bottom section.

Deployment Wizard - DAC Package Selection Screen
Figure 11 - Deployment Wizard - DAC Package Selection Screen

The Review Policy page displays all of the deployment intents defined in the package, its expected value and actual value on the target. If it passes the checks it lets you go on to the next page.

Deployment Wizard - Review Deployment Intents Screen
Figure 12 - Deployment Wizard - Review Deployment Intents Screen

In the Update Configuration page, you can change the default application (database) name and locations (locations for data file and log file to be created while creating database on the target server) available in the DAC package to something else.

Deployment Wizard - Update Configuration Screen
Figure 13 - Deployment Wizard - Update Configuration Screen

The Summary page of the wizard gives you a summary of your selections in the wizard. If you need to, you can go back and change your selection or click on the Next button to start deployment.

Deployment Wizard - Summary Screen
Figure 14 - Deployment Wizard - Summary Screen

The Deploy DAC page gives you the status of deployment during deployment or after deployment. If you notice, a new database is created with the database name appended with a GUID and then renamed to your original specified database name in the second last step of deployment as shown below. Once the database is created on the target server the DAC application is also registered in the target server as the final step.

Deployment Wizard - Deployment Status Screen
Figure 15 - Deployment Wizard - Deployment Status Screen

Verifying the deployed Data-Tier Applications

To view or verify the deployed DAC application and database, connect to SSMS then check if the database is available or not under the Databases node in the Object Explorer on the target server. Next verify if the DAC application was registered, expand Data-tier Applications node under Management in the Object Explorer and see if your DAC application is available or not. Select your application and press F7 to see the details about the selection.

 Verifying Deployment
Figure 16 - Verifying Deployment

Conclusion

In this article I demonstrated how you can deploy a Data tier application or DAC package, then I talked about verifying the deployed application. In my next article, I am going to tell you in detail, how you can upgrade your Data-tier application.

References

MSDN: Data-tier Application Overview

MSDN: Deploying Data-tier Application Packages

» See All Articles by Columnist Arshad Ali

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved