SQLTeam.com | Weblogs | Forums

Automate Dropping and Adding replication articles during Releases


We do releases frequently on a databases that we use replication on. When we make changes to replicated articles we need to stop Replication, Remove the Objects from the Articles, Complete the release, Test then manually add the articles back and restart replication.

I would like to script this but I am having difficulty with the correct scripts. This is what I have been Playing with

DECLARE @publication AS sysname;
DECLARE @subscriber1 AS sysname;
DECLARE @subscriber2 AS sysname;
DECLARE @article AS sysname;

SET @publication = N'PubName';
SET @subscriber1 = N'SubscriberName1';
SET @subscriber2 = N'SubscriberName2';
SET @article = N'ArticleName';

USE [PublisherDB]
EXEC sp_dropsubscription 
  @publication = @publication, 
  @article = @article,
  @subscriber = @subscriber1;

EXEC sp_dropsubscription 
  @publication = @publication, 
  @article = @article,
  @subscriber = @subscriber2;

EXEC sp_droparticle
  @publication = @publication, 
  @article = @article,
  @force_invalidate_snapshot = 0;


Two thoughts:

  1. You don't really need to stop replication. You can just remove the article from the publication. The original and replicated objects will remain in place; any data changes moving forward, of course, won't be propagated.
  2. It is possible to replicate schema changes. If you bring up the Publication Properties and select the Subscription Options page, you'll see the "Replicate schema changes" option. We use this option so that we don't need to take these types of actions when a column size changes or other modifications are made.


Continuing the discussion from Automate Dropping and Adding replication articles during Releases:

I have it set up to replicated Schema changes, but if we do not turnoff\pause replication and remove the articles during the release process, the release process fails. This is what I have been doing that seems to work, I just want to automate it

  1. Stop Replication Jobs
  2. manually remove the articles being changed
  3. when release\code update is complete Add articles back
  4. Add new articles
  5. recreate the snap shipt
  6. restart the replication jobs

I forgot to mention this is SQL 2005 still