A data-tier application (DAC) is a logical database entity that defines all of the SQL Server objects - such as tables, views, and instance objects, including logins - associated with a user's database. A DAC is a self-contained unit of the entire database model and is portable in an artifact known as a DAC package, or .dacpac. Data-tier applications are the build artifact from SQL database projects and can be used as part of a comprehensive database lifecycle management and DevOps strategy.
Tooling support for data-tier applications enables developers and database administrators to deploy a .dacpac to new or existing databases. Deployments to an existing database updates the database model from the existing state to match the contents of the .dacpac. Developers build DACs from SQL database projects, a declarative development concept for building SQL objects that enables source control on the database schema.
A .bacpac is a related artifact that by default encapsulates the database schema and the data stored in the database. The primary use case for a BACPAC is to move a database from one server to another - or to migrate a database from a local server to the cloud - and archiving an existing database in an open format.
The lifecycle of a database application can involve developers and DBAs exchanging scripts and sharing single use integration notes for application update activities. While this process is acceptable in some circumstances, it can be difficult to integrate with Azure Pipelines and general development processes.
Data-tier applications enable declarative database development, simplifying the development process and providing a more consistent and predictable development experience. A developer can author a database with SQL database projects in their choice of integrated development environment (IDE). A SQL database project can be compiled to a DAC package locally or in an Azure pipeline. The DAC package is in turn deployed to a test, staging, or production database through an automated process or manually with a CLI or GUI tool. The .dacpac can be used to update a database with new or modified objects, to revert to a previous version of the database, or to provision an entirely new database. Conversely, a .dacpac can be generated from an existing database and used to establish a SQL database project based on the current database schema.
The advantage of a DAC-driven deployment over a migration-driven process is that the process enables the identification and validation of behaviors from different source and target databases. Tooling used during database deployment/upgrades has options to flag risky actions such as column size changes that might cause data loss and the ability to directly script the upgrade plan. This plan can be evaluated manually before proceeding with the update.
A DAC simplifies the development, deployment, and management of data-tier elements that support an application.
A DAC supports the following operations:
These capabilities can be found in SqlPackage, SQL Server Management Studio, Azure Data Studio, and SQL Server Data Tools.
A SQL project supports the following operations:
These capabilities can be found in Azure Data Studio, Visual Studio Code, and SQL Server Data Tools.
A .bacpac, on the other hand, is focused on capturing schema and data supporting two main operations:
These capabilities are supported by the tools SqlPackage, SQL Server Management Studio, Azure Data Studio, and the Azure portal.
Data-tier application artifacts and SQL projects can be used across multiple tools. These tools address the requirements of different user personas.
The following tools support the DAC package and BAC package format:
In these tools, a database can be extracted to a .dacpac or exported to a .bacpac. Conversely, a .bacpac can be imported into a new database or a .dacpac can be published to a new or existing database.
The following tools support the DAC package format in addition to providing editing of SQL database projects:
In these tools, developers can design a database in an unconnected, client-side development environment. The tools can be used to create a DAC package, deploy a DAC package to a database, and import a database package into a SQL project.
In general, DAC tools are capable of reading .dacpac files generated by DAC tools from previous SQL Server versions, and can also deploy DAC packages to previous versions of SQL Server. However, DAC tools from earlier versions can't read .dacpac files generated by DAC tools from later versions. At a minimum, DAC tools support the in-support versions of SQL Server at the time of their release.
A .dacpac is a compressed folder with a .dacpac extension, and similarly a .bacpac is a compressed folder with a .bacpac extension. An advanced user can unpack the file to view the multiple XML sections representing details of the origin, the objects in the database, and other characteristics. To unpack a .dacpac or .bacpac, replace the file extension with .zip and use a file compression utility to unzip the file.
In SQL Server Management Studio other actions can be taken on a database to register it as a data-tier application.
For more information about these actions, see the below tasks.
| Task | Article link |
|---|---|
| Describes how to use a new DAC package file to upgrade an instance to a new version of the DAC. | Upgrade a Data-tier Application |
| Describes how to remove a DAC instance. You can choose to also detach or drop the associated database, or leave the database intact. | Delete a Data-tier Application |
| Describes how to promote an existing database to be a DAC instance. A DAC definition is built and stored in the system databases. | Register a Database As a DAC |
| Describes how to review the contents of a DAC package and the actions a DAC upgrade will perform before using the package in a production system. | Validate a DAC Package |