My DACPAC Experience: How to create a DACPAC and deploy it to SQL Server

I recently started working with DACPAC’s at a customer. The developers wanted to use the Data-tier application feature to perform upgrades op the databases and they wanted my help to implement it.
In short, as MSDN is stating, a data-tier application (DAC) is a logical database management entity that defines all of SQL Server objects like tables, views, and instance objects.
It allows you to create a package of SQL Server objects and deploy that package on a SQL Server.

In this blog post, I’m going to explain how you can create a DACPAC with the SQL Server Data Tools (SSDT) and deploy it on your SQL Server.

How to create a DACPAC

My goal is to create a new database “MyDACPAC” on my SQL Server which contains 1 simple table Person.
The first step, is creating a database project in the SSDT

Now let’s create a simple table “Person”. Right click on you project, go to “Add” and select Table.
Just give your table a name and click add

My table will look like this

Of course, you can add more than just tables, like indexes, views, stored procedures, …
Go to the following link to get an overview of the DAC Support For SQL Server Objects http://msdn.microsoft.com/en-us/library/ee210549.aspx

Let’s imagine that this is the database that I want to create. The only thing that is left is actually creating a DACPAC.
Well, that’s the easy part.
Right click on your project and select Build

Switch to the output window and verify if the build succeeded.
As you can see, the build process created a DACPAC, that can now be deployed on my SQL Server

How to deploy your DACPAC

There are several methods for deploying your DACPAC onto your SQL Server.
Below, I will give you a list of the most common deployment methods I have used.

Deploy Database Wizard

This method will describe how you can deploy your database onto your SQL Server by using the Deploy Database Wizard in the SQL Server Management Studio (SSMS)

Open your SSMS, connect in the Object Browser to the server where you want to create your database.
Right click on “Databases” and select “Deploy Data-tier Application”

 

As soon as the wizard start, you first get of course a Welcome screen. Just click next to go to the next step.
In the following step, you need to select DAC package that you want to deploy. Just go to the path where you SSDT has created the DACPAC.

 

The next step is called Update Configuration, but the only thing you need to do here is specifying the database name. In my case, this will be “MyDACPAC”

 

The next screen will show you a summary, click next and wait until your database has been deployed.

When the deployment is finished, go to the Object Explorer in your SSMS and do a refresh of the Databases. You will notice that your database is indeed created as it was developed in the SSDT.

 

Publish Database with SSDT

A second method that I often used was directly deploying the database from the SSDT. This is the so called, “Publish” of the database. In this example, I’m going to deploy the same database to the same server, but of course, the previous database has been removed.

Go to your database project in the SSDT. Right click on the project and select the option “Publish”

First step is providing the Target server connection and the database name that you want to deploy

 

Select the check box “Register as a Data-tier Application” and simply press the “Publish” button.

As soons as the publication starts, the Data Tools Operations windows pops up and shows the progress of the publication.

Now, if you go back to your SSMS, and perform a refresh again of your databases, you will notice that the database is exactly created as it was developed in the database project.

 

SQLPackage.exe

The last method that I often use is the SQLPackage.exe. It’s a command line tool that allows you to automate the deployment of your DAC packages.
The default path of the tools is C:Program Files (x86)Microsoft SQL Server110DACbinSQLPackage.exe.
In this example, I’m going to deploy the same database to the same server, and once again, the previous database has been removed.

Open a Command Prompt and go to the path that I have just mentioned before

I’ve executed the following command:

sqlpackage.exe /Action:Publish /SourceFile:”c:usersvanhop1documentsvisual studio 2010ProjectsMyDACPACMyDACPACbinDebugMyDACPAC.dacpac” /TargetServerName:W7-009073SQL2012 /TargetDatabaseName:MyDACPAC

/Action – I want to publish my DACPAC
/SourceFile – the path where my DACPAC is stored.
/TargetServerName – The SQL Server where you want to create your database
/TargetDatabaseName – The database name that you want to use

When all goes well, you should get this kind of output

Now, if you go back to your SSMS, and perform a refresh again of your databases, you will notice that the database is exactly created as it was developed in the database project.

I think creating and deploying DACPAS is an extremely useful tool that you can use for database development.