Managing Data-tier Applications

After a data-tier application (DAC) has been deployed to an instance of the Database Engine, most management actions are done on the database associated with the DAC. However, certain extra considerations must be observed to maintain the relationship between the database and the DAC.

DAC Database Management Considerations

After deploying a DAC, most management actions normally taken with databases can be applied to the database associated with the DAC. These include actions such as backup and restore, managing transaction logs, and exporting and importing data. For more information, see Managing Databases.

A DAC introduces some additional functionality that impacts some database management actions:

  • When a DAC is deployed, a DAC instance is created in the msdb system database that records both the DAC definition and the database created to hold the DAC objects. If you change the database name, such as running ALTER DATABASE MODIFY NAME or detaching the database and reattaching it with a new name, the association between the database and DAC is lost:

    • You will no longer be able to upgrade the DAC instance.

    • The SQL Server Utility resource utilization reports for each DAC are collected from the DAC database. The SQL Server Utility can┬áno longer collect the utilization data for a DAC if you rename the database.

  • After a DAC is deployed, the metadata definitions in the DAC are not updated if objects in the associated database are created, altered, or dropped. This means that post-deployment database object modifications cannot be restored from the DAC, they can only be restored from a backup of the database. The DAC upgrade process may also not be able to transfer the changed objects or their data. For more information, see Upgrading Data-tier Applications.

  • There is no backup and restore functionality at the DAC level. All backup and restore actions are taken against the database. All changes made to the database after deployment can only be restored from a backup of the database.

  • In the event of a system failure, you must be able to restore both the msdb system database and the user database associated with the DAC. This requires backing up both the msdb database and the user database. If you only have a backup of the user database, you can restore it and use it as an unmanaged database. But if you do not have a backup of msdb, you cannot restore the DAC definition, and the SQL Server Utility will not be able to report resource utilization for the DAC.

DAC Management Actions

After a DAC instance has been deployed, you can delete the DAC and, optionally, the associated database. For more information, see How to: Delete a Data-tier Application.

You can extract a DAC package from an existing database, and use the package to start a new DAC project in Visual Studio. For more information, see How to: Extract a DAC From a Database.

You can register a DAC instance for an existing database. If the instance of the Database Engine is a managed instance, you can then monitor the DAC in the SQL Server Utility. For more information, see How to: Register a Database As a DAC.