What is DACPAC and how to use it?

Few days before someone asked me a question about DACPAC and BACPAC. To be honest I was really not aware of it and I replied that I have heard these terms for the first time. That person replied DACPAC is used for deploying and upgrading database objects. Even after the clear statement, my reply was like developers would answer this better. Even though DBAs are responsible for migration and deployments.

After few days, I got a requirement to migrate database schema (Only Schema) to another instance.

In such a case most of us would have used the traditional way to script out the database objects and run the script on another instance. But what if there are many objects?

I was exploring the easiest way to migrate the objects and got to know about DAC. After reading about DAC, I felt very sorry myself that I was not aware of such a simplest way of migrating and deploying database objects. I went to my friends who are senior developers and asked this question. None of them was aware of it which made me feel relax.

Jokes apart!

In this BLOG we will discuss about what is DACPAC and How to deployment using DACPAC?

What is DACPAC?

First, we will understand what is DAC. DAC stands for Data-Tier Application.

It is a logical database management entity that defines all of the SQL Server objects - like tables, views, and instance objects, including logins - associated with a user's database.

A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.

In most of the cases, if any database object needs to be deployed from one instance In another instance, then the developer gives SQL script and release note to DBA admins for deployment.

However, using DAC, A developer can author a database in a SQL Server Data Tools database project and then build the database into a DACPAC for handoff to a DBA. The DBA can deploy the DAC using SQL Server Management Studio to a test or production instance of SQL Server or Azure SQL Database. Alternatively, the DBA can use the DACPAC to upgrade a previously deployed database using SQL Server Management Studio.

The advantage of a DAC-driven deployment over a script-driven exercise is that the tool helps the DBA with identifying and validating behaviors from a different source and target databases. During upgrades, the tool warns the DBA if the upgrade might cause data loss, and also provide an upgrade plan. The DBA can evaluate the plan and then utilize the tool to proceed with the upgrade.

DACs also support versioning to help the developer and the DBA maintain and manage the database lineage through its lifecycle.

How to do Deployment using DAC?

For this POC we have used the database StackOverflow2010. 

Scenario: There is a requirement to migrate only database schema from one instance to another.

In order to complete the above task, follow the below steps.

Right-click on Database from where objects need to be export.

Go to Task-->Extract Data-tier Application

This will open the Introduction page of Extract a data-tier application wizard. 

Click on Next

On next window, you can see details like Application Name, Version, Description, and directory to save the DACPAC file. All these options are editable.

Let's save DACPAC package as StackOverflow2010_1_1.dacpac which we need further.

Click on next

The next page will be of Validation and Summary. 

Validate all the details like version, application name. 

once validated, click on next

The final step will be to create a package.

If there are any dependency errors, then this step will get fail. 

This is all about creating a DACPAC package. Now let's see how we should deploy this package to another instance or the same instance with a different name.  

For POC purpose, we will deploy the DACPAC package on the same instance with a different database name. let’s say the database name would be StackOverflow2010_from_DAC

In order to deploy the package as a new database, follow the below steps.

Right-click on Databasesà Deploy Data-tier Application

This will open the Introduction page of Deploy a data-tier application wizard.

Go through the introduction page. Once understood click on next.

On next window select DACPAC package which we saved before i.e. StackOverflow2010_1_1.dacpac 

Next, we have to give database name which would be restored by this package.

We will give the database name as StackOverflow2010_from_DAC

Click on Next

We will get Summary page for review.

Click on Next.

The final window will be DAC deployment.

The window displays the result of DAC deployment. We can save the deployment report on this page for tracing purposes. 

After successful completion of all the above steps, we can see the new database restored (with the only schema).

This is all about schema with database restore using DAC package (i.e., DACPAC).

Now let’s see, how would be the further deployments on the new database.

Scenario:

The development team has modified and created few objects. Now they want to deploy the new and modified objects to the database StackOverflow2010_from_DAC. However, this database contains the data. Database admin has to be ensured that, deployment should be done without affecting the data.

Let’s see how the deployment will be done.

A New Stored procedure is created and one table is altered in database StackOverflow2010 by development the team as below. 

Change 1:

-- Created New Stored Procedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO 

CREATE PROCEDURE USP_GET_Votes      

AS

BEGIN 

       SET NOCOUNT ON;

       SELECT * FROM [dbo].[Votes]

END

GO

 Change 2:

-- Add Website column in exting Users Table

Alter Table [dbo].[Users]

Add Website nvarchar(50)

Developer further, build a DACPAC package or asked DBA to generate a new DACPAC package of database StackOverflow2010 and deploy it on database StackOverflow2010_from_DAC in order to release the changes.

From DBA end deployment will be as below.

Right-click on Database StackOverflow2010_from_DAC-->Task-->Upgrade Data-tier Application

This will open Introduction page of Upgrade a data-tier application wizard.

Select the package which contains modified or new object details.

Click on Next

Next window is very useful and interesting, at this stage comparison of DACPAC objects and target database is done.

Note:

If any database object modification/deletion is done on the target database since the last DAC deployment, then we will get a warning at this stage.

We can extract the report of modified/deleted objects in XML format.

Also, we can will get a data loss warning if any data is going to delete.

Click on next to proceed further.

On next window, make sure we check the checkbox to rollback the changes in case of failure.

Click on Next

In the Next window, we can see the upgrade plan.

Objects which are going to change/create can be review at this stage. 

We can save action report in HTML format for the reference.

Ex:

On next window, review the summary report.

Click on next.

The final of this wizard is Upgrade DAC where changes get applied to the target database.

Now we have successfully deployed the objects on another database without any data loss.

In the next blog, we will discuss the BACPAC file. stay tuned.