Post reply

Transactional replication: how to replicate new objects without re-creating replication?

  • July 23, 2014 at 9:36 am

    #296733

    We have a transactional replication for a database with size 220 GB. It was created with backup/restore method.

    Sometimes developers make new DDL changes. For ALTER (object), replication perfectly reproduces them on subscriber. However it does not work for CREATE (object).

    I tried to add article by EXEC sp_addarticle, it really created an article for a new table, I can check for it in sysarticles, but that was all, it's not reproduced on the subscriber. What else should I do to bring it to subscriber?

    Dropping and recreating replication is not an option, backup/restore takes more than 1 hour, users won't accept it.

    Any suggestions?

    Thanks

  • July 23, 2014 at 10:55 am

    #1731849

    I believe that adding a new article requires a new snapshot of the publication.

    http://msdn.microsoft.com/en-us/library/ms152493.aspx

    Alternatively, you can create a new publication for the new object. There's nothing wrong with this, though it can be administratively a hassle.

    I did see a note on StackExchange that said you can add an object to a publication and ignore the reinitialization (new shapshot) if you don't have data. Not sure if that works for you, but I suspect it's possible.

    Here's a better view of the process: http://saveadba.blogspot.com/2011/08/adding-new-article-without-generating.html

  • July 23, 2014 at 11:00 am

    #1731853

    You can add a new article and get it sync'd without a full snapshot.

    Here is an article on how to do it.

    http://ansqldba.blogspot.com/2012/02/adding-new-article-to-existing.html

    The key settings are

    allow_anonymous

    immediate_sync

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 23, 2014 at 11:06 am

    #1731856

    A few people have mentioned that a new article should only snapshot that article. Looking for references.

  • July 23, 2014 at 11:10 am

    #1731858

    That link I posted goes through the settings to change to make that happen. The default behavior is a snapshot of the entire publication.

    Changes to those settings snaps only the newly added articles.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 23, 2014 at 12:38 pm

    #1731911

    Thanks Jason, that's good article. I was able to add a table to replication even though my subscription was initially synchronized from a backup, not snapshot.

  • July 23, 2014 at 1:22 pm

    #1731925

    Good to hear.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply