Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

July 31, 2013

DAC and DACPAC

When you work with a SQL Server Database Project in Visual Studio 2012, two terms you will come across are "Data-tier application" or DAC, and "Data-tier application package" or DACPAC.

Data-tier application (DAC)

A data-tier application defines the database schema and associated database objects that are required to support an application. When you create a SQL Server Database Project, you have the beginning of the DAC for your application.

By the time you are ready to deploy the application, all of the database schema, data, and other scripts that are needed to support the application should be in the Database Project. Then the Database Project is the DAC.

Data-tier application package (DACPAC)

When you compile (build) a Database Project, the Data-tier application (Database Project) is packaged into a DAC package or DACPAC that is stored in the bin\Debug or bin\Release folder.

image

The DACPAC is basically a zip file that contains the database schema, any data or refactoring scripts, metadata, and anything else that SSDT would need to understand the desired end shape of the database.

If you change the .dacpac extension to .zip and open it, it will look something like this:

image

It’s just a set of XML files containing information on your database structure.

So the .dacpac basically contains THE definition of your database.

You can then use features of the SQL Server Data Tools (SSDT) to create a database with this DACPAC or update an existing database to match this definition (say to update a production database with your latest set of database changes).

The general idea behind the DAC is that when you are ready to deploy your application, you have two pieces:

  • The .exe and associated .dlls for the operations of the application. (The result of compiling all of your other projects that are part of your solution.)
  • The .dacpac for the database structure and stored procedures required for the application (The result of compiling the database projects that are part of your solution.)

You can then use these two pieces to deploy your application to test and then to production.

NOTE: It is also possible to generate a DACPAC directly from a database without using a Database Project, but I highly recommend using a Database Project if you are using Visual Studio and SQL Server and you are responsible for updating the database schema and/or stored procedures for your application.

For more information on database projects, see this link.

Enjoy!

2 Comments

  1.   RobTeixeira — August 1, 2013 @ 4:11 pm    Reply

    Correct, the note about DACPAC directly from the DB comes from the SQL Server DAC Framework (DacFX) capabilities. It allows non-Visual Studio people (think DBAs) to migrate schema from one system to another. SSDT just puts that same ability into a VS project. If you are building an app (as opposed to maintaining just a DB), then you definitely want to use the VS project, which has the nice side-effect of putting your schema in source version control (TFS, GIT, whatever).

    Unlike the older “data dude” DB Project, the SSDT SQL Project doesn’t “build” a single deployment script specific to a target DB. It “builds” a DACPAC that describes the schema, and can be used by DacFX (SqlPackage.exe being a wrapper around DacFX) to update any target DB.

    The major disadvantage of the SSDT SQL Project right now is that it doesn’t handle data migration at all. It just assumes you that you can include a pre and pose deployment SQLCMD script to do “whatever”. That’s a decent starting point, but it also requires step-by-step versioned deployments, which DacFX doesn’t fully support (properly anyway). That’s one of the reasons EF DB migrations don’t use SSDT or DacFX, and it’s the biggest PITA when trying to use SSDT SQL Project deployments.

  2.   DeborahK — August 1, 2013 @ 6:19 pm    Reply

    Thanks for the additional information, Rob!

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs