1

I'm in a multi-tenant environment and some tenant databases have custom columns in the common tables that all tenants share. The SQL Server Data Tools project only contains the schema for the common columns in the tables. So when I try to publish the dacpac to apply schema changes it'll try to drop the custom column since the SSDT project doesn't contain those custom columns.

For example:

Common Account table columns:

  • AccountID
  • FirstName
  • LastName

Tenant Account table columns:

  • AccountID
  • FirstName
  • LastName
  • CustomField1
  • CustomField2

Is there any way to prevent SSDT from trying to drop any columns while leaving the flag to block on potential data loss on? I'll be using sqlpackage.exe on the build pipeline as well.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
roverred
  • 1,841
  • 5
  • 29
  • 46
  • 2
    I provided an answer to a similar question at https://stackoverflow.com/questions/65944279/is-it-possible-to-leverage-a-sql-server-database-project-to-manage-a-set-of-data/65945133#65945133. Does that approach work for your use case? – Ben Thul Jul 01 '22 at 01:26
  • I've always made sure when publishing the option to "Drop objects in target but not in source" is unchecked. That usually leaves those columns/objects intact. You can try gen'ing a script from the Publish and/or a report to see what SSDT is planning to do. – Peter Schott Jul 01 '22 at 20:37
  • @BenThul that's a very clever way of doing it. There are hundreds of DB so this approach may not be feasible, but definitely something to consider. – roverred Jul 08 '22 at 17:10
  • @PeterSchott I have that option set to false and it is still trying to drop the column. I don't believe columns are considered objects so this option won't affect columns. – roverred Jul 08 '22 at 17:22
  • Is the divergence deliberate or accrued technical debt? I don't know your use case, but having hundreds of variations of a code base running in production would be untenable to me. – Ben Thul Jul 08 '22 at 20:25
  • There is an answer here using a DeploymentContributor: https://dba.stackexchange.com/a/141977/173206. The links are broken, but you can still find the code here: https://github.com/GoEddie/DeploymentContributorFilterer – Chad Baldwin Aug 26 '22 at 21:59

0 Answers0