Deploying Data-tier Applications

A data-tier application (DAC) defines all the SQL Server Database Engine schema and instance objects (such as tables, views, and logins) required to support an application. A DAC is built into a DAC package, which is an XML file containing a manifest that defines all the Database Engine objects used by the application, and is used to deploy the DAC. A DAC simplifies the management of the data-tier objects by providing a single unit for deployment and management.

Deploying a DAC Package

You must build a DAC package file to deploy a DAC. The DAC package is the deployment mechanism for a DAC, the same way an application executable file is the deployment mechanism for an application executable. For more information about building a DAC package file, see Implementing Data-tier Applications.

Security noteSecurity Note

We recommend that you do not deploy a DAC package from unknown or untrusted sources. Such DACs could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema. Before you use a DAC from an unknown or untrusted source, unpack the DAC and examine the code, such as stored procedures or other user-defined code. For more information about viewing the contents of a DAC, see How to: Validate a DAC Package.

A DAC can be deployed to an instance of the Database Engine running SQL Server 2005 Service Pack 4 or later, or SQL Azure. DAC operations require the client tools from SQL Server 2008 R2, and may also require the DAC Framework 1.1. For more information, see DAC Support For SQL Server Objects and Versions

The Deploy Data-tier Application Wizard performs these main actions when it deploys a DAC package:

  • Requests deployment-time properties, such as the name of the instance of the Database Engine or SQL Azure where the DAC will be deployed.

  • Evaluates the DAC placement policy (if defined) against that instance, and reports the success or failure of the evaluation. If one or more of the policy conditions are not met, you can decide to select another instance, reconfigure the specified instance, or to ignore the evaluation results if you think they are not critical. Policy conditions that do not apply to SQL Azure, such as operating system version, always evaluate to false when deploying to an instance of SQL Azure.

  • Creates a database and all of the objects defined in the DAC. The database is created with the defaults for the instance of the Database Engine for properties such as recovery model. The database has one data file and one log file with the following properties:

    • The file locations specified in the deployment. File locations cannot be specified when deploying to SQL Azure.

    • The data file size is 3MB, with a 1MB growth increment and no size limit.

    • The log file size is 1MB, with a 10% growth increment and a 2TB size limit.

In SQL Server Management Studio, you can launch the Deploy Data-tier Application Wizard by navigating to the Management node under a server in Object Explorer, right-clicking the Data-tier Applications node, then selecting Deploy Data-tier Application…

After deployment, the database is managed like any other database. Configuration of the database is done using common mechanisms such as the ALTER DATABASE Transact-SQL statement, the database management dialogs in Management Studio, or using the SQL Server Management Objects in the SQL Server PowerShell provider. The deployment process should include steps to perform common configuration changes right after the DAC has been deployed, such as:

  • Creating files and filegroups, or changing the sizes of files.

  • Establishing and testing a backup and restore process.

  • Setting database options.

For more information, see Modifying a Database.

Upgrading a Deployed DAC

After deploying the first version of a DAC, the development team may develop a new version of the application and the DAC. For more information about upgrading a deployed DAC to a new version, see Upgrading Data-tier Applications.

Login Passwords

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.