Post reply

Adding a new article (table with Cluster and Non Cluster Indexes) in Replication

  • January 26, 2017 at 10:47 am

    #318185

    Dear Experts

    Wanted to quickly check that if I need to add a new table to a publisher in repl, do I need to mandatorily take a fresh new snapshot and run it? Or just adding the new table will sync the entire repl system automatically?

    Thanks.

  • January 26, 2017 at 11:39 am

    #1925143

    SQL-DBA-01 - Thursday, January 26, 2017 10:47 AM

    Dear Experts

    Wanted to quickly check that if I need to add a new table to a publisher in repl, do I need to mandatorily take a fresh new snapshot and run it? Or just adding the new table will sync the entire repl system automatically?

    You would add the table to a publication, create a new snapshot for the publication and then synchronize the subscription.
    This article  goes through all the steps:
    Add Articles to and Drop Articles from Existing Publications

    Sue

  • January 26, 2017 at 11:50 am

    #1925145

    Ok- so escape from generating a new snapshot. For large number of articles, that process is not effective.

    Thanks.

  • January 26, 2017 at 2:06 pm

    #1925198

    SQL-DBA-01 - Thursday, January 26, 2017 11:50 AM

    Ok- so escape from generating a new snapshot. For large number of articles, that process is not effective.

    You do need to create a new snapshot for the publication and yeah it can be a pain to go through all of that. But when you think about it, the definition of the publication changed and you can have things filtered differently than what's on the publishers side so it would need those steps in order for the subscriber to know the definition changed and work with that new publication. It made more sense to me when I thought about the possibility of filtering. That new definition is only on the publisher until you go through the steps. And there are times when dropping the publication and just setting up a new one, new subscriptions, etc takes less time.

    Sue

  • January 27, 2017 at 1:50 am

    #1925252

    Hi,
    I think, it is possible to add an article, without creating a new snappshot with all alrticle. If you lock for these parameters, it is possible, to create a  snapshot just for the new article, and publish this to the subsriber:

    Parameter to create snapshot just for new article


    EXEC sp_changepublication
    @publication = 'REP_P', --Enter your publication_name
    @property = 'allow_anonymous' ,
    @value = 'false'
    GO
    EXEC sp_changepublication
    @publication = 'REP_P', --Enter your publication name
    @property = 'immediate_sync' ,
    @value = 'false'
    GO

    gool luck

    Andreas

Viewing 5 posts - 1 through 4 (of 4 total)

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