Data-tier Application Overview

Important

To create and manage databases using the latest version of Data Tier Applications (DACPAC), install the most recent release of SQL Server Data Tools from Get Started with Microsoft SQL Server Data Tools.

A data-tier application (DAC) is an entity that contains all of the Microsoft SQL Server database and instance objects, such as tables and views, used by an application. A DAC provides a single unit for developing, deploying, and managing the data-tier objects instead of having to manage them as sets of separate scripts and objects. A DAC allows tighter integration of data-tier development with the development of the associated application code. It also gives administrators an application-level view of resource usage in their systems. A DAC can be deployed to either SQL Server 2008 R2 or SQL Azure.

Creating and Deploying Data-tier Applications

The process of creating and deploying a DAC is coordinated with the process of creating and deploying the executables for the application associated with the DAC:

  • A database developer creates a data-tier application project in Microsoft Visual Studio 2010. The database developer works with the application development team to design the data-tier objects required by the application code, and then adds the definitions of the objects to the data-tier application project. The database developer can also define server selection policies that specify the conditions an instance of the Database Engine or SQL Azure should meet to host the DAC.

  • To deploy the data-tier objects to an instance of the Database Engine or SQL Azure, the database developer builds the DAC project. The DAC build process creates a DAC package, which is an XML file defining all of the objects and policies in the DAC.

  • The database developer hands off DAC packages to developers for their unit testing, or to the test teams for quality assurance testing. The application developers and testers deploy the DAC to an instance of the Database Engine. When a DAC is deployed to an instance of the Database Engine, a copy of the DAC definition is stored in the msdb system database, and a user database is created to hold the database objects. Applications connect to the database associated with the DAC. A DAC can only be deployed to an instance of the Database Engine running SQL Server 2008 R2 or SQL Azure.

  • When the development of an application is complete, the development team builds the files required to deploy the application to production. The application developers build the application executable files and hand them off to the system administrators for deployment to the production application servers. The database developer builds the final version of the DAC package and hands it off to the database administrators for deployment to an instance of the Database Engine running in production.

  • Administrators use the Deploy Data-tier Application Wizard in SQL Server Management Studio to deploy the DAC into production. They can monitor and manage the database associated with a DAC using established procedures. However, administrators can gain additional information from the DAC itself:

    • Administrators who have installed SQL Server 2008 R2 and created a Utility Control Point can get additional information from the Utility Explorer in Management Studio. The Deployed Data-tier Applications node of the Utility Explorer presents an application-centered resource usage report that allows administrators to more quickly assess the health of their data-tier applications.

    • The Data-tier Application node of the Management Studio Object Explorer reports properties for each deployed DAC.

  • The DAC can be deleted from the instance of the Database Engine using the Delete Data-tier Application Wizard.

Warning

Visual Studio 2010 Service Pack 1 (SP!) includes a new DAC Framework 1.1 that changes the DAC upgrade from a side-by-side to an in-place upgrade, and also change the format of the DAC package files. While DAC Framework 1.1 supports packages created using the earlier DAC Framework 1.0, clients still running DAC Framework 1.0 cannot process DAC packages created by using DAC Framework 1.1. SQL Server 2008 R2 customers can upgrade to DAC Framework 1.1 by using the DAC Framework 1.1 Feature Pack download.

For more information about an example of creating a DAC, see Walkthrough: Creating a New Data-tier Application Project.

Importing Objects Into Data-tier Applications

When working on new applications, data-tier objects are created using the DAC project user interface. There are several methods for using the database objects from an existing application to create a data-tier application project for further development:

  • The production database administrator can use the Extract Data-tier Application Wizard in Management Studio to extract a DAC package that contains the definitions of all the objects in the existing database. The database developer can then import that DAC package into a DAC project in Visual Studio. DAC packages can be extracted from databases in SQL Server 2008 R2, SQL Azure, SQL Server 2008, and SQL Server 2005.

  • The database developer can use the Import Objects wizard in the data-tier application project to import objects from the database.

  • If there is a Transact-SQL script for creating the existing database, the database developer can import the script into a DAC project.

Data-tier Application Elements

A DAC contains the following elements:

  • DAC properties that define characteristics of the DAC. For example, each DAC has a name property and a version property whose values typically relate to the name and version number of the associated application.

  • Definitions of all the database objects used by the application, such as schemas, tables, views, and stored procedures. In SQL Server 2008 R2, DACs do not support all of the available SQL Server objects. For more information, see Features Supported in Data-tier Applications.

  • Definitions of the instance-level objects, such as logins, associated with the database objects and used by the application.

  • A server selection policy that defines the pre-requisite conditions an instance of the should have to host the DAC. The policy is defined using the Server Selection facet and can evaluate conditions such as the edition and default collation of the instance.

  • Data generation plans used to create meaningful test data.

  • Scripts that implement pre-deployment or post-deployment actions.

Data-tier Application Package

When a DAC is ready to deploy, it is built into a DAC package, which is an XML file that contains the definition of the DAC. The DAC package is a single unit of deployment. A DAC package is usually built in conjunction with the executable files for the associated application. When the system administrators deploy the executable files to the application tier or client computers, the database administrators deploy the associated DAC package to an instance of the Database Engine or SQL Azure.

For example, a development team is developing a three-tier application named Finance. Build 1.0.1.123 of the application passes all acceptance testing and is ready to deploy to production. The application developers hand off a version 1.0.1.123 Finance.exe file to production system administrators for deployment to the production application servers. The database developers hand off a version 1.0.1.123 Finance.dacpac file to the production database administrators for deployment to a production instance of the Database Engine.

A DAC package is a zipped file containing XML files that define the elements of the DAC. For more information about a definition of the DAC package file schema, see Microsoft SQL Server File Format Documents.

Data-tier Application Instance

When a DAC is deployed to an instance of the Database Engine, it forms a DAC instance:

  • A database is created using the default database properties for that instance of the Database Engine. The database objects defined in the DAC are created in the new database.

  • All instance-level objects defined in the DAC, such as logins, are created.

  • The original DAC definition and metadata about the DAC instance are stored in the msdb system database (the master database on SQL Azure).

After the instance has been deployed, users can start working with the objects and adding data to the database created for the DAC.

See Also

Concepts

Creating Data-tier Application Projects

Configuring the Server Selection Policy

Building Data-tier Application Projects

Deploying Data-tier Application Packages

Lifecycle Management for Data-tier Applications