Creating Data-Tier Applications in SQL Server 2008 R2

Thursday Nov 4th 2010 by Arshad Ali
Share:

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

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
> Figure 1 - 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

If your source is SQL Server database (and not DAC package), it might contain certain objects, which are not supported by Data Tier Applications; the third screen also gives an objects list, which cannot be imported into project, if it finds any, as shown below. For a complete list of supported objects can be found on MSDN.

Importing Data Tier Application 5
Figure 7 - Importing Data Tier Application 5

When you click on the Finish button, the import process starts and the status is displayed on the screen as shown below. Click on the Finish button to close the wizard after import.

Importing Data Tier Application 6
Figure 8 - Importing Data Tier Application 6

Once the project has been imported, a data tier application project structure will be created as shown below. You can browse through the imported objects using the Solution Explorer or Schema View window. You can change the definition of existing objects or author new objects in the project.

Data Tier Application Project Structure
Figure 9 - Data Tier Application Project Structure

Once the data tier application project has been created, you can change the project -- the location where the DAC package will be created after build, the name of the DAC package, version of the DAC package, when it will be built, etc. Right click on the project in the Solution Explorer and click on the Properties menu; a screen similar to that shown below will appear. In the Project Settings page you can specify the version, description etc.

Data Tier Application Properties - Project Settings
Figure 10 - Data Tier Application Properties - Project Settings

In the build page, you can specify default collation, which will be used in the generated DAC package during deployment, location where DAC package will be created, and name of the DAC package as shown below.

Data Tier Application Properties - Build
Figure 11 - Data Tier Application Properties - Build

In the Build Events page you can specify commands to be executed before and after the project is built.

Data Tier Application Properties - Build Events
Figure 12 - Data Tier Application Properties - Build Events

In the Deploy page, you specify a connection string if you want to build and deploy the project. Next you also specify the name of the database/application to be created on the specified connection string during deployment.

Data Tier Application Properties - Deploy
Figure 13 - Data Tier Application Properties - Deploy

Code Analysis is new feature, which analyzes the SQL code for any violation and reports it as a warning or error during build. On this page, you can select which rules you want to consider during building the project for analyzing.

Data Tier Application Properties - Code Analysis
Figure 14 - Data Tier Application Properties - Code Analysis

Data tier application has a cool new feature (also a much awaited feature) to declare deployment intent, which means you can check the pre-requisite for your application deployment even before deployment and if it satisfies the specified criteria/policy then deploy it. For example, I want to deploy my data tier application on SQL Server 2008 (Version: 10) and higher and this is what I have declared below. To define deployment intent, double click on the ServerSelection.sqlpolicy file under Properties in Solution Explorer, which will bring up a page like this.

Deployment Intent Declaration
Figure 15 - Deployment Intent Declaration

To build your project simply hit F6 or press CTRL+SHIFT+B or go to the Build menu and click either on Build Solution or Rebuild Solution menu as shown below. This will create a DAC package (with *.dacpack extension, you can watch the Output window for detail) in the specified location.

Building Data Tier Application
Figure 16 - Building Data Tier Application

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

Conclusion

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.

References

MSDN: Data-tier Application Overview

MSDN: Understanding Data-tier Applications

MSDN: Creating Data-tier Application Projects

» See All Articles by Columnist Arshad Ali

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