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.

This entry was posted in DACPAC.
16 comments on “My DACPAC Experience: How to create a DACPAC and deploy it to SQL Server
  1. Thanks for the great article. We are just starting to use DACPAC deploys also. I am curious to see the next article you do on it. Issues I am yet unsure of how to deal with are:

    1.) Deploy multiple DACPACs at the same time. For example, on my SQL Server I might have 10 databases, and therefore 10 DACPACs. How can I deploy them all at once instead of individually?

    2.) How can I exclude certain types of objects from being built in the dacpac?

    Thanks again.

  2. I’m interested in know if you have any experience in deploying dacpac via sqlpackage.exe to a 2012 always on cluster. What, if any, are the issues that I should be aware of?

    Thanks

    • Hi Doug,

      I’ve deployed a few DACPAC with sqlpackage.exe.
      Didn’t had any issues. And actually there is no difference if you should deploy it to a cluster or a stand-alone server.
      The functionality of SQL Server and DACPAC’s doesn’t change if it’s running on a cluster or not.

      Regards
      Pieter

  3. Pingback: SQL Azure Database Deployment - MS-Tech learning with Prasant - Site Home - MSDN Blogs

  4. Thanks a lot Pieter. This was extremely helpful. Would be great to see how we can upgrade the database using DACPAC!!

  5. Please can you suggest are DACPAC(s) really good in handling Database deployments on 500 GB databases and above.

    What really happens if there are Schema changes needs to be done on 10 million rows of data in DACPAC, will it be slow than traditional Alter commands.

    Please let me know as we want to use DACPAC for larger databases, if yes which version should we use.

    • Hi,

      In fact, when you look at the content of a DACPAC, you will notice that SQL scripts are executed.
      So if you should ALTER a table with a DACPAC or just with the alter statement, there will be no difference.
      So, be carefull when performing schema changes on really large tables.

      Regards
      Pieter

  6. Hi – I am trying to deploy a DACPAC to a Always On High Availability SQL Server Environment. there are three sql servers. Also, there is a Listener. I am also using Release Management Studio 2013, integrated into our TFS 2013.

    I have never deployed a DACPAC to this environment, and was wondering –

    a) should I be deploying the DACPAC to all three SQL Servers?
    — OR —
    b) should I be deploying to the Listener?

    If I should deploy to the Listener, then How do I configure that in the Release Management Studio?

    For some reason I am getting a “failed” with Exit code 1. Any ideas?

    • Hi Juan,

      You have to deploy it to the Listener, which will in fact redirect your connection to the primary replica of your AlwaysOn Availability Group.
      The changes will also be transferred to the secondary replicas.
      The listener enables a client to connect to an availability replica without knowing the name of the physical instance of SQL Server to which the client is connecting.
      So normally you can use this listener name in the Release Management Studio like another SQL Server Instance.
      But to be honest, I don’t have any experience with Release Management Studio…

      Regards
      Pieter

  7. Thanks, and to update this post. I finally got this to work. The issue I encountered is that the service account used in Release Manager must be DB Owner, and should have SA permissions, if you’re updating logins and roles with the DACPAC. After correcting the permissionns, everything worked as expected. Thank you for your response. Great Blog!

  8. Hi Pieter,

    I am quite new to this DAPAC system. By following your this article, now i could be able to create a DACPAC with some Create Table scripts and one post deployment script for inserting initial values to some of the tables. Now this DACPAC has been deployed to a different server.

    Now I have some defects to fix and as a part of the fix, I have some alter scripts to change constraints, add columns to table and this table is also referenced with Foreign Key. So in this case how to make the DACPAC, so that this can be deployed to the same server and the Test Data entered by Testers while testing should not be deleted. Thanks in advance.

    • Hi,

      When you deploy a new DACPAC, only the schema changes will be done. Nothing will change to the data that has been inserted in the tables, unless you remove the tables in the DACPAC of course.

      Regards
      Pieter

Leave a Reply

Your email address will not be published. Required fields are marked *