What happens when we publish the database project through visual studio

Question

I have been working on a project which has a database project in it and I used to publish that database when ever I made some changes to the scripts. Now that I noticed that when I publish the database project it builds first and creates a dacpac file and then it publishes after I selects the target database. I am interested in knowing what role does that dacpac file plays in publishing the sql database. Also I have found this thing when I was trying to read about pro's and con's about dacpac. Is it really works like that?

Link

The biggest problem with DACPACs has to do with the way a data-tier application is released to push version changes from the DAC into SQL Server. This is done by creating a new database with a temporary name, generating the new objects in the database, and then moving all the data from the existing database to the new one. After all the data has been transferred and the post-release scripts run, the existing database is dropped and the new database is given the correct name.

Answer 1

The dacpac file is the compiled build output of the database project. It's analogous to a .dll file built from a C# class library project. All of the information you defined in your database project about your database is stored in the dacpac file, along with information about the relationships between the objects.

When a dacpac file is published, the target database is compared to the dacpac and the tool will figure out what T-SQL to execute to make the target database match the dacpac's definition.

Regarding the article, note that the Data-Tier Application Framework that shipped with SQL Server 2008 R2 was largely rewritten/replaced for SQL Server 2012, so that article, while correct regarding that very old version of the Data-Tier Application Framework, is not correct regarding the tools available today.

Answer 2

The DACPAC file is a Zip file contains an XML representation of your database schema. It does not contain any table data (unless you provide pre-and-post deployment scripts). More information is available here: https://www.simple-talk.com/sql/database-delivery/microsoft-and-database-lifecycle-management-(dlm)-the-dacpac/

When a DACPAC is deployed, the receiving server compares the difference between the current schema and then updates your schema accordingly by generating a change script. However, be careful, as some changes can be very expensive (such as adding a new column in the middle of a table that already has millions of rows).

The article I linked to shows you how you can view the generated change script and see what happens. Repeated here is a snippet that does it:

"%ProgramFiles(x86)%\Microsoft SQL Server"\110\DAC\bin\sqlpackage.exe 
    /Action:Script
    /SourceFile:MyPathAndFileToTheDacPac
    /TargetConnectionString:"Server=MyTargetInstance;Database=MyTargetDatabase;Integrated Security=SSPI;"
    /OutPutPath:"MyPathAndFile.sql"

Using DACPACs and Database Projects (in SSDT, but do not use SQL Server Management Studio) is the preferred way of pushing database changes now as it is less error-prone than manually redesigning tables using the table designer (which will drop-recreate-and-repopulate tables if you do things like add non-terminal columns to existing tables).