2

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.

|improve this question
3

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.

UPDATE :

USING GUI :

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
0

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:

http://searchsqlserver.techtarget.com/tip/Pros-and-cons-of-the-DACPAC-with-SQL-Server-2008-R2

|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.