Implementing Data-tier Applications

A data-tier application (DAC) defines the SQL Server Database Engine schemas and objects that are required to support an application. A DAC can be implemented using one of two processes:

A DAC can be authored and built using a SQL Server Data-tier Application project in Microsoft Visual Studio 2010.

A DAC can be extracted from an existing database by using the Extract Data-tier Application Wizard in SQL Server Management Studio.

The extraction and authoring build processes both produce a DAC definition that specifies the objects and metadata. The DAC definition is built into a DAC package, which is a file that contains a manifest of all the object and policy definitions included in the DAC. The DAC package can be used to:

Deploy the current version of the DAC to development, test, and production instances of the Database Engine and SQL Azure. Use the Deploy Data-tier Application Wizard to deploy a new instance of the DAC. Use the Upgrade Data-tier Application Wizard to upgrade an existing instance of the DAC that was deployed using an older version of the DAC.

Start development of the next version of the DAC in Visual Studio by importing the DAC into a new SQL Server Data-tier Application project in Visual Studio.

DACs are developed by using DAC projects in Visual Studio. A DAC project enables tight integration of the development of data-tier objects with the development environment of the application being coded to use the objects. A database developer authors the DAC by using a DAC project in Visual Studio. The DAC project can be integrated into the overall development solution for the application in Visual Studio Team System. As the application developers write the application code, the database developer codes the data-tier object definitions in the DAC.

For a new application, the database developer creates the DAC project, then starts adding objects to the project as they are developed. When starting the project for a new version of an existing application, the database developer can extract a DAC package from the current database used by the application, and import that into the DAC project.

The developer can add these elements to the DAC project:

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

For testing, the database developer can deploy the DAC project to a test instance of the Database Engine. Alternatively, the developer can build the DAC project, which creates a DAC definition in a DAC package. The DAC definition contains the metadata for all the elements defined in the DAC project. The DAC package is a zipped XML file that contains the DAC definition and is used to deploy or upgrade a DAC. Developers and testers can use the Deploy Data-tier Application Wizard to deploy the DAC to their test systems.

When development of the application is complete, the DAC project is built to create the production version of the DAC package, just as the application project is build to create the application executable files. The DAC package is handed off to the production database administrators, who deploy it to instances of the Database Engine running in production, or to SQL Azure.

After a DAC version has been deployed to production, development can start on the next version of the DAC. The database developer creates a new version of the DAC project with the definitions of all the data-tier objects as they exist in that version. When the project is built into a new DAC package, the same package can be used both for deploying a new instance of the DAC or upgrading an existing instance to the new version. The Upgrade Data-tier Application wizard compares the schema of the existing DAC instance and the DAC package, and dynamically performs the actions needed to transform the existing DAC instance to the new version of the DAC.

The Extract Data-tier Application Wizard is used to build a DAC definition based on an existing database. The DAC definition contains the metadata defining all of the objects in the database, any logins that map to security principals in the database, and the database collation and compatibility level. The DAC definition does not contain any user data from the source database. The DAC definition is built into a DAC package file.

The wizard can only extract objects that are supported in data-tier applications. After you have specified the properties such as the DAC name and version, the wizard loads all of the objects from the database and verifies they are supported in a DAC. The wizard then displays a summary page that groups the objects in three categories:

Objects with a red invalid entry icon are not supported in a DAC.

Objects with a yellow warning icon are themselves supported in a DAC, but have a dependency on an object that is not.

Objects with a green success icon are supported in a DAC and have no dependencies on objects that are not supported in a DAC.

If the wizard encounters any objects that are not supported in a DAC, it will list the objects in a summary report and not create the DAC package. If all objects have a green success icon, you can proceed with creating the DAC package.

The extracted DAC package can be used to:

Import the DAC into a DAC project in Visual Studio, where a database developer can start development on the next version of the DAC.

To improve security, SQL Server Authentication logins are stored in a DAC package without any password. When the package is deployed or upgraded, the login is created as a disabled login with a generated password. To enable the logins, log in using a login that has ALTER ANY LOGIN permission and use ALTER LOGIN to enable the login and assign a new password that can be communicated to the user. This is not needed for Windows Authentication logins as their passwords are not managed by SQL Server.