CODEWITHSUNDEEP
sql-serverdatabase-migrationsql-server-data-toolsdacpac
Michael Erpenbeck
Michael Erpenbeck

Reputation: 603

How to deploy DACPACs to transaction replicated databases

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.

Upvotes: 3

Views: 3626

Answers (1)

Michael Erpenbeck
Michael Erpenbeck

Reputation: 603

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.

Upvotes: 6

Related Questions