Adding and dropping articles in a replication environment initialized from backup


  • We have a production environment in which replication is setup with a remote distributor and are having three pull subscribers. We add subscription using initialize with backup, we do not do snapshots to initialize the subscriber. And we have pull subscription in place. We are trying to find out a way to add articles to the same publisher without causing any outage in the environment.

    Our immediate_sync is set to ‘0’ and  the synchronization method is 3(concurrent).

    We had tried the following scenario.

    stop log reader agent, create the table on both publisher and subscriber manually(quiesced the table), Add the table as article to publication, and start the log reader agent. But this process caused issue with missing data on the subscriber and the replication broke.

    Please suggest on the best way to perform adding article to replication initialized from backup

    Monday, October 10, 2011 4:28 AM


All replies

  • Hi Vishnu, the way that I have done this in the past is to script up the subscriptions and then drop and recreate with the new article present.  This should alleviate the issue, and should not take too long to apply.
    Monday, October 10, 2011 4:33 AM
  • Hi Henners, thanks for a quick answer.

    Our database is around 200 GB. And we can not have any downtime. Reconfiguring subscriptions take around 2 hours which is not an option in my case. Is there a way to add the article with out having any downtime. i.e., without reconfiguring the subscriptions?

    Monday, October 10, 2011 5:00 AM
  • You need to put it into its own new publication which is not part of the publication/subscription you initialized from backup.
    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?
    Monday, October 10, 2011 10:20 AM
  • Hi Hilary,

    Thank you for the reply

    I have discussed regarding this with my team about creating a new publication for the newly added articles, and we are having the following questions.

    1. If we configure a new publication for the newly added article what is the replication scenario we need to use and can we use the already existing subscription db?(obvly. we can not use initialize with backup, coz this will break the already existing replication scenario)

    2. should we configure the subscription to a different database

    3. Is there any other way to add the articles to already existing publication

    Tuesday, October 11, 2011 2:24 AM
  • 1) You will need to use a no-sync or an ordinary snapshot as your base.

    2) This might work for you - but your subscription database will no longer be the same as your publication database, ie now you have 2 databases. This may complicate programing, recovery and availability.

    3) Not that I can think of.

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?
    • Marked as answer by Stephanie Lv Monday, October 17, 2011 12:29 PM
    Tuesday, October 11, 2011 10:06 AM