We have a database (SQL Server 2008r2) that is being transactionally replicated, namely tables, sprocs, views, indexed views and UDFs only, to another server (SQL Server 2014) as part of a cautious (read: slow) upgrade process. However, we still need to deploy new code or changes to it in the meanwhile.

Whenever we try to deploy new/changed code to it, using DACPAC under Visual Studio, we get a slew of the following errors:

Error SQL72035: [dbo].[SomeTable] is replicated and cannot be modified.

Is there anyway to avoid this? I was thinking if we could script the drop and create of the replication, we could put it in pre and post-deploy script, respectively, but I'm not sure if a) that is a good approach, and b) common practice.


We are facing the same problem - DACPAC does not support replication (as of now).

What we do is - if the deployment involves/affects replicated tables then

  • manually drop replication.

    • Script out drop and create replication using GUI or Powershell

    • Drop the replication

  • Run your DACPAC to upgrade your system

  • Create replication (scripted out previously) and kick off the snapshot of articles.



enter image description here

USING PowerShell :

| improve this answer | |
  • Is there a link you can provide me for this scripting out process? I think that is exactly what we are looking for in the current scenario, rather than looking at alternatives to transactional replication. – alphadogg May 21 '14 at 15:14
  • Is there any downside to drop and recreating replication? – alphadogg May 21 '14 at 16:43
  • The only downside is that during snapshot, the tables involved in replication will be locked. SO if the tables are huge, your users have to wait until the snapshot is completed. There are way to optimize that as well, but that is beyond the scope of your question. HTH ! – Kin Shah May 21 '14 at 17:01
  • Also, dont replicate the entire databases, just only tables that you need should be replicated. – Kin Shah May 21 '14 at 17:15
  • So, production code updates are in the middle of the night, when this database is seeing very low use. We should be good. – alphadogg May 22 '14 at 11:40

The issue is not that it can't deploy to replicated tables but that it blocks during the verification step when it identifies replicated tables on the target.


Out of the seven actions that SqlPackage.exe automates, Publish, DeployReport, and Script actually modify (or imitate modifying) the target server and thus support this validation flag.

When I first encountered this error I made the mistake of thinking that SqlPackage.exe could not deploy to targets with replication, so I modified the database deployment scripts in our CI/CD pipeline to use the Script action instead of Publish. The hope here was that it would generate T-SQL that I could then execute in a next step and avoid the referenced SqlPackage.exe error. When I still received the same error on the Script action, locating the answer simply became a matter of digging in to the possible command line options to override this validation.

| improve this answer | |

There is an article in the MSDN that discusses schema changes and replication. The link to the article Making Schema Changes on Publication Databases is: http://technet.microsoft.com/en-us/library/ms151870(v=sql.105).aspx

This article points out that you can make schema changes using T-SQL or SMO. However, other tools are not so friendly. For example, when an SSMS script is created it cannot replicate because of the strategy to create a new table and drop the old one. That would break replication, of course.

DACPAC, at least initially, would use a similar approach to deploy schema changes, which would not work with replication. See comments from Denny Cherry on SQL Server 2008 R2 at:


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.