2

I am deploying a DACPAC via SqlPackage.exe to database servers that have a large volume of transaction replication in SQL Server. The DACPAC is built as the output of a SQL Server Database Project. When I attempt to deploy the DACPAC to the database with replication enabled the SqlPackage execution returns errors such as, Error SQL72035: [dbo].[SomeObject] is replicated and cannot be modified.

I found the parameter DoNotAlterReplicatedObjects which does not alter objects with replication turned on and would silence those errors, which isn't what I want to do. Instead, I want to alter all objects regardless of replication as part of the deployment.

The only option that I can think of to deploy the DACPAC to these replicated databases is to:

  1. remove the replication through a script before deploying,
  2. deploy the DACPAC via SqlPackage,
  3. reconstruct the replication via scripts after deploying.

Unfortunately, the database is so heavily replicated that the step #3 above would take over 7 hours to complete. So this is not a practical solution.

Is there a better way to use SQL Server Database Projects and DACPACs to deploy to databases with a lot of replication?

Any assistance would be appreciated. Thank you in advance for your advice.

Michael Erpenbeck
  • 553
  • 4
  • 11
  • 1
    The obvious answer here is to not use a replicated SQL database. I can’t imagine relying on an automatic update script for data that is so critical that it’s replicated like this. It seems you have a conflict of desires. – theMayer Jul 22 '21 at 22:58
  • 1
    The DACPAC deploy is trying to make PRD look like DEV from where it was built. Do you not have replication enabled in your DEV environment? – AlwaysLearning Jul 22 '21 at 23:30
  • We have replication enabled in lower environments as well and we see the same errors occur in lower environments. – Michael Erpenbeck Jul 22 '21 at 23:35
  • Regarding not using replicated SQL databases, I appreciate the out of the box thinking, but I unfortunately, am not able to influence the replication strategy at this time. – Michael Erpenbeck Jul 22 '21 at 23:44
  • 1
    There are some promising answers here but I'm unable to work out if they actually solved it. https://dba.stackexchange.com/questions/65568/replication-blocks-code-deployment – Nick.Mc Jul 23 '21 at 01:41
  • 1
    One thing is to be really careful about what you're changing - not rearranging column order, not renaming a lot of objects, etc. If you're making pretty straightforward changes, you shouldn't have a lot of rebuild operations. Adding/removing columns and tables shouldn't cause a lot of issues in most cases. You can also inspect the scripts generated to see what's going to happen prior to attempting to deploy. – Peter Schott Jul 23 '21 at 15:43

1 Answers1

3

We solved the issue by doing the following. Hopefully this will work for others as well. The high level idea is that you need to disable "Do not ALTER replicated objects" and enable "Ignore column order".

There's a couple of ways to do this.

  1. If you are using the SqlPackage tool in your deployment pipeline, then use the DoNotAlterReplicatedObjects and IgnoreColumnOrder properties see this link. So /p:DoNotAlterReplicatedObjects=False /p:IgnoreColumnOrder=True

  2. If you are using C# or PowerShell Dac classes, then use the DacDeployOptions.DoNotAlterReplicatedObjects and DacDeployOptions.IgnoreColumnOrder properties.

  3. You can directly modify the "Advanced Publish Setting" in Visual Studio IDE. You uncheck the Do not ALTER replicated objects checkbox and enable the Ignore column order checkbox. See this StackOverflow answer for how an example for the Ignore checkbox.

Our theory on why this works is that the alter table can only append a column to the end of a table so the only way to add a column to a specific position is to drop and recreate the table. The ignore tells the publisher to append the column to the end regardless of where I positioned the column in the script.

So the place this could be a problem is if you do an insert without specifying a column list because you expect the columns to be in a specific order and they're not.

Another potential side-effect that you could run in to is the table created by the DACPAC could have a different column order than the table altered by the DACPAC. We have been using this solution for a few months without issues, but the above are things to be aware of.

I hope that this helps.

Michael Erpenbeck
  • 553
  • 4
  • 11