What are DACPACs and how do I use them?

I had someone ask me about DACPACs recently.

DACPAC technology had fallen off of my radar after I had seen demos of the feature at a SQL Saturday many years ago.

In short, a DACPAC is a file that contains definitions for the objects of a database. This file can be used to create new databases or update old databases to a new version. For the most part, existing data in the updated database should be preserved. DACPAC technology is intended to replace the bundles of .SQL scripts and the giant "Hail, Mary" scripts that are often used to update databases.

At the time of that SQL Saturday demo, which was probably in the SQL Server 2008 timeframe, DACPAC technology was new and there were a lot of gotchas. IIRC, it was presented as a way to create databases in SQ:L Azure.

A few weeks ago, I noticed that SSDT was creating DACPAC files. I've long been a user of the database comparison tool provided by SSDT and other "Data Dude" descendants, but I didn't give the "freebie" DACPAC file that SSDT generated for me a second thought.

Since someone asked, I thought that I would spend some time researching this and write it up for the blog.

Things in the DACPAC universe seem to have improved substantially, and I found the following links useful:

• Jamiet does a braindump of his DACPAC knowledge on his blog.
• Pieter Vanhove relates his DACPAC experience and goes through various methods of applying DACPACs.
• Technet has a page on DACPAC.
• Bob Beauchemin provides some examples of using DACPAC functionality with Powershell. 
• The wiki over at StackOverflow has some quick points. 

According to the wiki, the DACPAC scheme still makes a copy of the target database and subsequently deletes the old one. That might be a showstopper for many large databases and was the main reason that I banished DACPAC to the back of my brain. However, I don't see many complaints on the web about this and that made me suspicious. In some very limited testing of a single-table database with four columns, I did not see any creation of mysterious databases. Perhaps the situation has changed with SQL Server 2012 and the wiki is out of date?

(While performing my limited tests, I noticed that a certain amount of downgrade-ability might be feasible. By simply applying the 1.0.0.0 DACPAC to my 1.0.0.1 database, I could remove a column. I'm not sure how I can exploit this, or if it would work on a non-trivial database, but this is the sort of think I like to keep in the back of my head as a possible future trick.)

In short, DACPAC seems to have matured into a viable deployment strategy. I intend to look for situations where I can use this technology to improve the speed and quality of deploying new database versions.