Creating DACPACs for Deployments

How are you deploying updates to your databases? Do you run a SQL script that you receive from developers? Maybe you have a SQL job that takes a script stored at a specified location and applies it to all user databases. Another option for deploying updates that you may not be as familiar with is the use of DACPACs.

What is a DACPAC?

DAC is the abbreviation for data-tier application and is an item containing the objects of a database. When put together into a versioned file to be used for deploying in SQL Server, that package is called a DACPAC. You can point a DACPAC at an existing database to deploy changes or use a DACPAC to create a brand new database with tables, procedures, and the rest of the database objects built in.

Extract From Reality

In this post, let’s look at creating a DACPAC with SSMS. Note that this is not the only way to create a DACPAC. SQL Server Data Tools (SSDT) is another popular option.

Let’s create some changes to deploy to the StackOver2013 database. In our StackOverflow2013_Dev database, we’ll create a new stored procedure:

CREATE PROCEDURE GetReputation @Rep INT
AS
BEGIN
SET NOCOUNT ON;
	SELECT Id
		,CreationDate
	FROM Users
	WHERE Reputation >= @Rep;
END

And we’ll create a new index to go with it:

CREATE INDEX Reputation_Include_CreationDate
ON Users (Reputation)
INCLUDE (CreationDate);

With those changes in place and ready to deploy to prod, let’s create our DACPAC.

We’ll right-click on our database, hover over Tasks, and select Extract Data-tier Application.

On the introduction window, we can click Next.

We can specify what our DAC properties such as name, a version number, and a description. Let’s say this is version 2.0.0.0 and enter a brief description:

We’ll click Next, verify our summary looks as we expect, and click Next again to build the package.

Let’s navigate to where we saved our DACPAC and confirm that it’s there.

This weekend, we’ll take the next step and show how we can take this DACPAC and put it to good use.

Thanks for reading!