Understanding Data-tier Applications

Working With Data-tier Applications

Use the following processes to work with a DAC:

• First, create a DAC type, or DAC definition, that specifies the objects used by the associated application. The DAC definition is then built into a DAC package, which is the file used to deploy and upgrade the DAC. There are two ways to create a DAC definition and package:

  • Open a data-tier application project in Microsoft Visual Studio 2010 and specify the objects that comprise the DAC. When you build the DAC project, Visual Studio compiles a DAC definition and places it in a DAC package.

  • Extract a DAC definition from an existing database and create a DAC package by using the Extract Data-tier Application Wizard.

  For more information, see Implementing Data-tier Applications.

• The DAC package is deployed to an instance of the Database Engine using the Deploy Data-tier Application Wizard. The deployment of the DAC package is usually coordinated with the deployment of the application executable files. The deployment creates an instance of the DAC, which has two main parts:

  • The DAC definition is stored in the msdb system database (the master database in SQL Azure).

  • A user database is created and populated with the objects specified in the DAC definition.

  If the instance of the Database Engine is a managed instance in the SQL Server Utility, the DAC is uploaded to the utility control point as a deployed DAC,

  For more information, see Deploying Data-tier Applications.

• Applications connect to the database associated with the DAC. Administrators can monitor and manage the database associated with the DAC using established procedures. However, administrators can gain additional information from the DAC itself:

  • The Deployed Data-tier Applications node of the SQL Server Management Studio (SSMS) Utility Explorer presents an application-centered resource usage report that allows administrators to more quickly assess the health of their data-tier application instances.

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

• A DAC simplifies the process of supporting upgrades from one version of a database to another. When deploying databases by using Transact-SQL scripts, database developers have to build two sets of scripts for each version of the database after the first. One set of scripts can be used to upgrade an existing database to the new version, and the other set can be used to install a new database. When using DACs, the database developer only has to build one DAC package that describes the schema used by the new version of the application. You can use the package with the Deploy Data-tier Application Wizard to install a new instance of the DAC. You can use the same package with the Upgrade Data-tier Application Wizard to upgrade an existing DAC instance. The Upgrade Data-tier Application Wizard compares the two schemas and dynamically performs the actions needed to transform the existing DAC instance to the new version of the DAC.

  For more information, see Upgrading Data-tier Applications.

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

For more information about building and using a DAC, see the Data-tier Application Tutorials.

Data-tier Application Elements

A DAC definition contains the following elements:

• DAC properties that define characteristics of the DAC. For example, each DAC has an application 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. DACs created using SQL Server 2008 R2 or Visual Studio 2010 do not support all of the available SQL Server objects. For more information, see DAC Support For SQL Server Objects and Versions.

• 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 Database Engine 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.

• Files and scripts that can be embedded in the DAC definition when it is authored in Visual Studio. Examples are application documents, data-generation plans that specify how to create meaningful test data, or pre- and post-deployment scripts.

Data-tier Application Project

A DAC is typically developed in Visual Studio 2010 by a database developer using a Data-tier Application project. The database developer defines the data-tier objects required by the application the DAC is being developed to support. When the DAC is ready for the application developers or test team, the database developer builds the DAC project, which creates a DAC package file that can be used to deploy the DAC.

For more information about DAC projects, see Implementing Data-tier Applications.

Data-tier Application Package

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

For example, a development team is developing a 3-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 zip file containing multiple XML files. For more information about the DAC package file contents, 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 in 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.

Creating Data-tier Applications for Existing Databases

You can use the Extract Data-tier Application Wizard to extract a DAC definition from an existing database. The DAC definition contains the selected objects from the database, and associated instance-level objects such as the logins that map to database users. The wizard builds the DAC definition into a DAC package. The extracted DAC package is typically then imported to a DAC project in Visual Studio where development is started on the next version of the application. You can also use the extracted package to deploy the DAC to another instance of the Database Engine.

You can also use the Register Data-tier Application Wizard to register a DAC for an existing database. The wizard creates a DAC definition that includes the objects in the existing database, and then stores the definition in the msdb database (the master database in SQL Azure). If the instance of the Database Engine is a managed instance in the SQL Server Utility, the DAC registration is uploaded to the utility control point as a deployed DAC.

See Also

Concepts