CODEWITHSUNDEEP
asp.netsql-servervisual-studiovisual-studio-2013sql-server-data-tools
Chris
Chris

Reputation: 1475

Publishing Databases using DacPac in Visual Studio 2013

I need some clarification on when to register a Database as a Data Tier Application (DAC). I've looked at all the guides but am stuck on a few points.

First time round, this works. It registers the database and succeeds.

However, on subsequent publishes is fails as it says the DB has drifted noting two users which have not changed.

Am I following the correct process? i.e. setting the Publish script to re-register each time?

What is the best practice for making changes? By changing the relevant .sql files in the Database Project and then building? The guides talk a lot about being able to version the DB using the DacPac but its not clear how. Should I rename each DacPac and commit it to TFS?

My next step is to publish the Database as part of the overall ASP.Net Solution. When I try to do that (it works fine when the DB publish is not included), it comes up with the following error

Web deployment task failed. (The SQL provider cannot run with dacpac option because of a missing dependency. Please make sure that DacFx is installed.  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_DACFX_NEEDED_FOR_SQL_PROVIDER.)

However, I have all the required elements installed on the publishing machine. Do they need to be on the SQL Server or IIS VMs?

Any guidance would be much appreciated.

Upvotes: 1

Views: 1358

Answers (1)

jagberg
jagberg

Reputation: 53

If you want to deploy your changes to a database using a dacpac you would need to register the database as a DAC. This basically creates a snapshot of the database at that point in time. You do this before making a change to create the initial snapshot and then after a deployment.

The reason you do this is to detect drift. Lets say you do a deployment and someone makes a change directly in that database, for instance changing the logic of a stored procedure, you would want to know about that change before making a subsequent deployment. If you deploy your dacpac and ignore this change it will revert their change to whats in the dacpac model. This is where drift occurs. You can generate an xml report on what has drifted through the sdk.

You can enable a setting to disable deployment if drift occurs so that you can retrofit those changes in the database directly in your source code. You would then need to re-register the database as a DAC to create a new snapshot.

Am I following the correct process? i.e. setting the Publish script to re-register each time? Yes

What is the best practice for making changes? By changing the relevant .sql files in the Database Project and then building? Yes

The guides talk a lot about being able to version the DB using the DacPac but its not clear how. Should I rename each DacPac and commit it to TFS? You can set a version within the databse. Have a look at the properties of the database project. You shouldnt rename the dacpac.

About the ASP.Net publish, I would need a bit more detail around the project structure and environment setup.

Upvotes: 1

Related Questions