0

I have configured a SQL transaction replication to create two subscribers and one publisher, the question is that after any deployment and changes that made on the SQL "alter procedures" on the publisher server, the replication log several errors and i solved the issue by re establish the snapshot.

is there any think that i should do during the deployment "run the updated script on the publisher" ? should i stop the application to not log any transaction during the deployment, or take the DB offline. any ideas please .

  • please tag this with your DBMS (and version) — you had it tagged 'sql', did you mean 'sql-server'? – Jack Douglas Jul 22 '16 at 6:25
2

An alter procedure would not normally be replicated and so would not cause any problems. More than likely you're doing DDL operations and changing the tables and this is breaking the replication.

Michael Keleher has suggested your replication may not be set up to replicate schema changes:

Open the properties of the publication, go to the Subscription Options section and change the Replicate schema changes property to true. Then all DDL changes applied to articles in the publication will be applied to the subscriber without the need to re-initialize.

If that works for you then great.

In my experience replication can cause a lot of normally benign deployments to fail, and so I prefer that replication be removed as part of a deployment and reapplied afterwards.

For a "lighter" touch, in your specific example the end of your deployment you can mark the subscribers as needing a snapshot (using sp_reinitpullsubscription) then trigger the snapshot agent (with sp_start_job).

It's roughly documented in BOL https://msdn.microsoft.com/en-AU/library/ms152466.aspx?f=255&MSPPError=-2147217396 though the instructions differ slightly depending on whether you're doing push or pull subscriptions.

However you probably need to dig a little more into what's actually going on to cause the problem because doing this kind of thing will make every single deployment go slowly for little reason.

  • Many thanks Cody Konior, 1- how can i mark the subscribers as needing a snapshot then trigger the snapshot agent automatically. 2- Is there another solution to avoid using the transaction replication and to be able have two servers with the same data. Thanks in advance. – PLO Jul 22 '16 at 3:20
  • Added to the answer. But no there's no simple alternative. – Cody Konior Jul 22 '16 at 12:53
  • I have usually unreplicated and re-replicated systems for deployments. One of the reasons was that I would only have to deploy most of the scripts to just one system, then replicate and the snapshot would pass my changes over to the subscriber. This can take some time, depending on the number of objects you are replicating, but you should never have issues with inconsistencies between the publisher and subscriber. – Jason B. Jul 22 '16 at 13:16

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.