asked
Replication Issue with Deployment
Background: We have a web application in C# with a SQL Server 2008 database back-end. We deploy the application and database projects at the same time in our weekly deployments. Our SQL Server database is replicated (transactional replication) to another server for our SSRS reports. Problem: We get the error below when deploying the database project with replication on. I tried disabling the SQL Agent jobs for replication, but I got the same error. “DatabaseName.dbschema : Deploy error TSD00565: [dbo].[TableName] is replicated and cannot be modified. [09:16:00]: DatabaseName.dbschema : Deploy error TSD01234: Plan verification encountered errors; deployment cannot continue.” As a work around we manually drop replication before deploying the database project then we recreate replication after the deployment. The problem with this is that it takes an hour to sync the data to the replicated database and our users can’t have reports down for an hour. How can we deploy without dropping replication? Is there other ways to get around this problem?
Thank you for your response. I disabled VerifyDeployment in Database.sqldeployment and I was able to deploy my database project with replication on.
I suspect that the deployment is doing something which replication doesn't like. Normally the first error happens when you try to make a change like adding a column to a table, but one of two things is happening: 1. You are adding the column into the middle of the table columns. 2. Your alter table script is generated poorly Either of these will cause a DROP TABLE to occur at some point in the alteration process. The first instance will create a new table with the new column in the right place, bulk copy the data from the old table, drop the old table and rename the new table. Because the table is being replicated, it cannot be dropped and the command fails. The second instance is possibly an implicit drop table, create table command, which again will fail if the table is replicated. If this is the case, you can just issue the alter table add column command and this will work. If your publication is setup to replicate DDL this change will also get replicated to your reporting server without any extra intervention on your side.
