CODEWITHSUNDEEP
databasevisual-studio-2013sql-server-data-tools
Vivekh
Vivekh

Reputation: 4259

What happens when we publish the database project through visual studio

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.

Upvotes: 3

Views: 4232

Answers (3)

Steven Green
Steven Green

Reputation: 3517

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.

Upvotes: 5

Dai
Dai

Reputation: 155543

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).

Upvotes: 3

Flowy
Flowy

Reputation: 11

I'm not too familiar with it but played around with some database uploads myself. From what I gathered the dacpac has settings that can be used and uploaded. I found these instructions: •To create a database project based on a dacpac, create a new SQL Server Database Project in Visual Studio. Then right-click on the project in Solution Explorer and choose "Import -> Data-tier Application (*.dacpac)" and select your dacpac. That will convert the contents of the dacpac into scripts in the project, and if you choose "Import database settings" the database options will be set based on the settings in the dacpac.

Dacpac is A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects - like tables, views, and instance objects, including logins – associated with a user’s database. A DAC is 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. from https://msdn.microsoft.com/en-us/library/ee210546.aspx

hope this helps...

Upvotes: 0

Related Questions