How to make DACPAC update only one schema?

Question

I have a VS2013 solution with *.sqlproj project that contains objects specific to this solution. The issue is that this is database common also for other projects.

My problem is to automatically deploy changes within my schema to database without affecting other objects. By default DACPAC updates whole database that is not desired in my case.

I tried to write deployment contributor http://msdn.microsoft.com/en-us/library/dn268597(v=vs.103).aspx but it seems there is no way to have it within solution folder, as it have to be placed in Program Files subfolder of SQL server.

I use Bamboo to create deployment packages and the application is hosted on Microsoft Azure with Azure SQL database.

Is there any way I can deploy DB changes only within my schema using DACPAC or other automatic means?

Expansionism

How do you end up deploying the dacpac? Do you have any control over it or is it run from a remote location? I ask because one additional place you can put your extension DLL is in the same directory as the Dac DLLs (Microsoft.Data.Tools.Schema.Sql.dll). Therefore if you could include those and your extension and use SqlPackage for the dacpac creation that might work? Note that the installation is to the SQL Server directory on your client machine, so if you can run from there then the fact that you're deploying to Azure shouldn't make any difference. – Majordomo

For now dacpac is executed by deployment team. System wide modyfication on their machines or on build server may not be possible, so I'm looking for a way to reference the contributor DLL within my solution folder or some other way to ignore other schemas in DACPAC. – Expansionism

Answer 1

Your two options are as follows:

1. Copy SqlPackage.exe and the other DAC DLLs to a folder inside your solution, or one controlled by your deployment team. Also copy your contributor DLL to that same folder. Then make sure that when deploying you use SqlPackage.exe from that location. Since any DLL in the same folder as Microsoft.Data.Tools.Schema.Sql.dll will be checked for extensions you can use this method to get your contributor included during deployment, without the need to install to a system-wide location.

2. Filter out objects related to other schemas from your dacpac, and then deploy with DropObjectsNotInSource = false. This is less ideal since it won't drop objects that you delete, but the benefit is that you can do it at build time / before passing to the deployment team.

Note that this basic topic is covered in the API tutorial I wrote, with some samples of that in this samples project. It sounds like you have option #1 written already (the tutorial has a simplified version that only blocks additions, not alters/drops), but you can see a comparison between them. Also the samples show how to publish using our APIs (this maps directly to using SqlPackage.exe) and how to easily test and validate contributor behavior.

Majordomo

Thank you for your response. Can you please tell me how to set DropObjectsNotInSource on build time? I tried to set <DropObjectsNotInSource>False</DropObjectsNotInSource> in first Property Group, but it seems ignored and objects are still dropped – Expansionism

I just checked the targets file (C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v12.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets) and there are 2 different variables, 1 for F5 deploy and one for publish. For F5 deploy your <DropObjectsNotInSource> should be correct. For Publish it should be <Publish_DropObjectsNotInSource>. Note that you can also set this in a publish profile file if you prefer. Also if you want more information about what happens during publish, run the task from the command line with the /v:diag flag to get a verbose output. – Majordomo

Answer 2

You can build a deployment using the DacpacMerge library, this will merge the model from the database with the model for a single schema, into a new DacPac. This new generated Dacpac can be deployed without affecting the other objects, as they are the same as the database current definition.

Autecology