THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Dacpac braindump - What is a dacpac?

In this week’s earlier blog post First release of my own personal T-SQL code library on Github I talked of how one could use a dacpac to distribute a bunch of code to different servers. Upon reading the blog post Jonathan Allen (of SQL Saturday Exeter fame), with whom I’ve been discussing dacpacs with on-and-off recently, sent me this email:

Hi Jamie,

The DacPac thing I emailed about in December hasnt taken off yet but I have just downloaded your code library to take a look and I like the way the dacpac works. Should I be able to open that in VS or is the dacpac compiled/built in VS? The video you linked to didnt cover dapac at all so I am in the dark on how to create one/them.

If I can build a database and create a dacpac simply then this could be really useful.

Jonathan’s email made me realise that there is perhaps a lot of confusion about what dacpacs are, what they can be used for and how they can be used so I figured a braindump of what I know about them might be useful, that’s what you’re getting in this blog post.

 

What is a dacpac?

A dacpac is a file with a .dacpac extension.

image

In that single file are a collection of definitions of objects that one could find in a SQL Server database such as tables, stored procedures, views plus some instance level objects such as logins too (the complete list of supported objects for SQL Server 2012 can be found at DAC Support For SQL Server Objects and Versions). The fact that a dacpac is a file means you can do anything you could do with any other file, store it, email it, share it on a file server etc… and this means that they are a great way of distributing the definition of many objects (perhaps even an entire database) in a single file. Or, as Microsoft puts it, 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. That in itself is, I think, very powerful.

Ostensibly a dacpac is a binary file so you can’t just open it up in your favourite text editor and look at the contents of it. However, what many people do not know is that the format of a dacpac is simply the common ZIP compression format and hence we can add .zip to the end of a dacpac filename:

image

and open it up like you would any other .zip file to have a look inside. If you do so you will see this:

image

The contents of that zip file conform to something called the Open Packaging Convention (OPC). OPC is a standard defined by Microsoft for, well, for zipping up files basically. You have likely used files conforming to OPC before without knowing it, docx, .xlsx, .pptx are the most common ones that you might recognise if you use Microsoft Office and there are some more obscure ones such as .ispac (SSIS2012 developers should recognise that). (For a more complete list of OPC-compliant file types see the wikipedia page).

Notice in the screenshot above showing the innards of TSQLCodeLibrary.dacpac the biggest file is model.xml. This is the file that contains the definition of all our SQL Server objects. I won’t screenshot that here but I encourage you to get hold of a .dacpac file (here’s one) and have a poke around to see what’s in that model.xml file.

What are dacpacs for?

Dacpacs are used for deploying SQL Server objects to an instance of SQL Server. That’s it. If your job does not ever involve doing that then you probably don’t need to read any further.

Dacpac pre-requisites

A .docx file (i.e. A Microsoft Word document) isn’t much use to someone if they don’t have the software (i.e. Microsoft Word) to make use of it and so the analogy holds for dacpacs; in order to use them you need to have some software installed and that software is called the Data-tier Application Framework (or DAC Framework for short, or DacFx for even shorter).

Incidentally, you may be wondering what DAC stands for at this point. I think its “Data-Tier Application” in which case you may be thinking that the acronym DAC is a stupid one especially as DAC also stands for something else in SQL Server, I would agree!

DacFx is available to download for free however you’ll probably never need to do that as installation of DacFX occurs whenever you install SQL Server, SQL Server client tools or SQL Server Data Tools (SSDT). If DacFX is installed you should be able to see it in Programs and Features:

image

How does one deploy a dacpac?

In dacpac nomenclature the correct term for deploying a dacpac is publishing however the two generally get used interchangeably. There are two methods of publishing a dacpac which I’ll cover below.

Publish via SSMS

In SSMS’s Object Explorer right-click on the databases node and select “Deploy Data-tier Application…” (told you they used those terms interchangeably):

image

This launches a wizard that prompts you to choose a dacpac, fill in some particulars (e.g. database name) and then deploy it for you by calling out to DacFx. Unfortunately this wizard is not very good because it doesn’t (currently) support all features of dacpacs, namely if your dacpacs contain any sqlcmd variables (I won’t cover those here but they are commonly used within dacpacs) a value needs to be supplied for them; the wizard doesn’t prompt you for a value and hence the deployment fails.

This. Is. Stupid. Microsoft should be suitably lambasted for not providing this basic functionality. Anyway, due to this limitation you’re most likely to be using the other method which is…

Publish via command-line

One component distributed in DacFx is a command-line tool called sqlpackage.exe which will quickly become your best friend if you use dacpacs a lot. sqlpackage.exe can do a lot of things and those “things” are referred to as actions, one of those actions is publishing a dacpac. Here’s the syntax for publishing a dacpac using sqlpackage.exe:

"%ProgramFiles(x86)%\Microsoft SQL Server\110 \DAC\bin\SqlPackage.exe"
      /action:Publish
      /SourceFile:<path to your dacpac>
      /TargetServerName:<SQL instance you are deploying to>
      /TargetDatabaseName:<Name of either (a)the database to create or (b) the existing database to deploy into>

Publishing is idempotent

Notice from my comment above for TargetDatabaseName that you can deploy to an existing database. You might ask why you might want to publish into an existing database, after all, the objects you are publishing might already exist. This segues nicely into what I see as the biggest benefit of dacpacs and DacFx, the software interrogates the target database to determine whether or not the objects already exist or not and if they do not it will create them. If they do already exist it will determine whether the definition has changed or not and if it has, it will make those changes. DacFx will always protect your data so if it determines that an operation would cause data destruction (e.g. removing a column from a table) then it will (optionally) throw an error and fail. You never again need to write an ALTER statement or first check that an object exists in order to change an object definition, DacFx will do it for you. To put it another way, publishing using dacpacs and DacFx is idempotent.

How does one create a dacpac?

Of course in order to publish a dacpac you’re first going to have to create one and one of Jonathan’s questions above pertained to exactly this. There are two distinct ways to do create a dacpac.

Use an SSDT Project

SQL Server Data Tools (SSDT) projects are basically a project type within Visual Studio that provide a way of building DDL for SQL Server databases. I’m not going to cover SSDT projects in any detail here except to say that when such a project is built the output is a dacpac. Note that SSDT can also publish the dacpac for you however I didn’t mention that above as the publish operation is essentially another wrapper around the same DacFx functionality used by sqlpackage.exe.

Create from an existing database

One can right-click on a database in SSMS and click on “Extract Data-tier Application…” to create a dacpac containing the definition of all objects in that database:

image

Wrap-up

Should you be using dacpacs? I can’t answer that question for you but hopefully what I’ve done is given you enough information so that you can answer it for yourself. Some people might like the way dacpacs encapsulate many objects into a single file and their idempotent deployment, others may prefer good old simple, handcrafted T-SQL scripts which don’t have any pre-requisites other than SQL Server itself. The choice is yours.

Further reading

UPDATE

David Atkinson from Redgate has been in touch to tell me about another dacpac feature that I didn’t know about. It is possible to right-click on a dacpac in Windows Explorer and choose to unpack it:

image

That essentially unzips it but what you also get is a file called Model.sql that will create all of the objects in the dacpac:

image

Very useful indeed! David tells me that Redgate use this functionality to enable comparison of a dacpac using their SQL Compare tool as you can read about at Using a DACPAC as a data source.

Published Saturday, January 18, 2014 11:31 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

dan holmes said:

January 21, 2014 9:27 AM
 

Rafael Salas said:

Nice summary Jamie.

January 25, 2014 8:21 PM
 

Adam McArdle said:

Once again you've saved my sanity. This summary was exactly what I was looking for, thanks a million.

April 30, 2014 11:29 AM
 

Dan said:

Two thumbs up. Thank you for the short, but in-depth definition and use cases. Very helpful!

July 22, 2014 1:29 PM
 

Jamie Thomson said:

An issue that regularly seems to rear its head on my travels is that of headless build servers for SSDT.

August 8, 2014 6:22 AM
 

JaY said:

Very Informative Article Jamie. I am trying to Extract DAC app for a db I am failing since 2 days I am following every word of your article !!! Is there any can you help me or let me know where I am going wrong ??

Thanks a lot in advance

August 18, 2014 2:18 PM
 

Peter Schott said:

@Jay, what problems are you having with extracting the DAC file? Have you tried using the SQLPackage command line to do that? I've had issues trying to use the GUI with some DBs that have cross-DB code or other things that don't necessarily translate as easily into a project.

August 18, 2014 2:29 PM
 

JaY said:

Hi Jamie, Yeah as you suggested I tried creating using SQLPackage command line !! Thanks a lot man !!!

August 18, 2014 3:33 PM
 

jamiet said:

Jay,

Wasn't me, it was Peter. He beat me to the punch :)

August 18, 2014 3:37 PM
 

Peter Schott said:

Been there before, been hit by the cross-DB stuff, learned to use the command line to at least get started and add DB references. Once those were set up, pulling in changes became a lot easier. Without that, the various dependencies almost always crashed the import/create project process. :)   (And I learned a lot from Jamie over his many, many posts on these topics - great stuff if you're using the MS SQL/DB Project option.)

August 18, 2014 3:45 PM
 

JaY said:

OMG!!! Sorry Peter My bad , Thanks a lot for your help . You made my day :-D

August 18, 2014 4:15 PM
 

JaY said:

Hi Jamie,

I do have one more concern. Lets say I created a Database Project using Visual Studio and I gave reference for Databases in project using DAC packages. After couple weeks may be some table structure or DB architecture will get change!! Then how these changes gonna affect that Database Project I created !! It will fail right ??

August 20, 2014 7:28 PM
 

jamiet said:

Hello Jay,

What do you mean by "it will fail"? Do you mean your build, your deployment, or something else? Can you be more specific?

if you're making a change to a system from which you previously extracted a dacpac and you are referncing that extracted dacpac, then you're going to have to extract that dacpac again.

hope that helps

JT

August 21, 2014 6:56 AM
 

Peter Schott said:

Jay, you can also make your changes to the other DB Project, build it, and use the generated dacpac to replace the existing one. It depends on how many databases you have in projects, of course. And yes, if you modify the referenced database in some fashion that will affect your projects, you'll need to re-extract. Ideally you've made that extraction into some process that can be re-run easily - powershell, batch file, etc. :)

August 21, 2014 7:43 AM
 

Vamsi said:

Thank you for the detail explanation. I googled a bit trying to put together all the individual pieces about this topic that I gathered from different sources. Happy that I stumbled onto your blog post  that has the exact information I have been looking for. Thank you.

September 3, 2014 5:07 PM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 1, 2014 2:37 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement