Many times database developers and database administrators work on enterprise applications, they make changes to the databases. The changes to the databases are captured as SQL scripts and they exchange on emails.
For small or medium databases, this may work very smoothly. But this approach does not scale well with bigger applications and databases.
Data Tier Applications (DAC)
A DAC is a database lifecycle management and productivity tool that enables declarative database development to simplify deployment and management.
A developer can use SQL Server Data Tools to author a database project. This project can then be built into a DAC Package (or DACPAC) file. This DACPAC file contains only Schema.
This DACPAC file can then be handed over to DBAs. The DBAs then can either deploy the databases in SQL Server or Azure SQL databases.
To complete the lifecycle, the DBA can extract the database into a DACPAC and hand it off to a developer to either reflect test or production adjustments, or to enable further database design changes in response to changes in the application.
The advantage of using DACPAC over scripts is, it enables DBA to identify and validate the behaviors from different source and destination databases.
During the upgrades, DACPAC not only warns DBAs about any data-loss scenario, but it also provides a upgrade plan. The DBAs can evaluate the plan and then utilize the tool to proceed with upgrade.
DACs also support versioning to help the developer and the DBA maintain and manage the database lineage through its lifecycle.
A DACPAC is a file with .dacpac extension, the file supports an open format. The file contains multiple XML sections. Each of the XML section represents the objects in database, origin details and other things.
There is a utility DacUnpack.exe, which can be used to unpack the file and inspect each section, if you are more curious.
A BACPAC is a Windows file with a .bacpac extension that encapsulates a database’s schema and data.
Like DACPAC, the BACPAC also stores the data in an open format. The schema contents are stored in form of XML. The format of this schema part is same as in DACPAC. The data part of the BACPAC is stored in the form of JSON.
The primary use case for BACPAC can be moving databases from one database server to another, or migrate a database from a local server to the cloud – and archiving an existing database in an open format.
Difference: DACPAC vs BACPAC
The DACPAC and BACPAC files are similar. But they are meant for different scenarios.
DACPAC is useful for capturing and deploying only schema, including creating new database or upgrading an existing database. The primary use of DACPAC can be to move the tested schema from test environment to production environment OR bringing production schema back to test environment for troubleshooting (or any other purpose).
BACPAC file is useful for capturing the schema and data both. It is mainly used for Import (from bacpac to database) and Export (from database to bacpac) of database.
You can use SQL Server Management Studio to extract a DACPAC from an existing database, and also perform other DAC operations.
The DacUnpack.exe can be used to further analyze the generated the .dacpac file.
I hope this article was helpful. Let me know your thoughts.