chocolatechip avatar image
chocolatechip 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?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chocolatechip avatar image chocolatechip commented ·
Thank you for your response. I disabled VerifyDeployment in Database.sqldeployment and I was able to deploy my database project with replication on.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
That sounds like a "home-built" deployment system. At least it is fixed now.
0 Likes 0 ·

1 Answer

WilliamD avatar image
WilliamD answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.