Adding an Article Without a Full Snapshot

  • Comments posted to this topic are about the item Adding an Article Without a Full Snapshot

  • Thanks Andy, that article you linked to is a great read. The context and discussion that Paul put around this was really helpful.

  • So after you have modified the two properties, allow_anonymous and immediate_sync, you do not change them back to what they were before? Does this have any potential impact to your subscription architecture? (which I think so)

  • It changes how long commands are retained in distribution, basically they would get removed after 4 hours.

  • I was facing the same challenge. I am pasting abbreviated version of the code I use. It does seem to avoid generating full snapshot.

    --1. Declare some variables

    DECLARE @found_out INT,

    @publication_name SYSNAME = 'your_publication_name',

    @article_nameSYSNAME = 'your_table_name',

    --2. This part should only run if there is already existing article and we need to change it

    BEGIN

    -- Check if subscription exists and, if so, drop it first

    EXEC sp_helpsubscription @publication = @publication_name, @article = @article_name, @found = @found_out OUTPUT

    IF @found_out = 1

    BEGIN

    EXEC sp_dropsubscription @publication = @publication_name, @article = @article_name, @subscriber= 'all'

    END

    EXEC sp_droparticle @publication = @publication_name, @article = @article_name, @force_invalidate_snapshot = 1

    END

    --3. Add article back (or create it for the first time)

    EXEC sp_addarticle @publication = @publication_name,

    @article = @article_name,

    --- list any other desired options (this proc has a ton of parameters) ----

    @force_invalidate_snapshot = 1

    --4. Add subscription for our newly added article

    EXEC sp_addsubscription

    @publication = @publication_name,

    @subscriber = 'Subscriber_Server_Name',

    @destination_db = 'Subscriber_DB_Name',

    @subscription_type = N'Push',

    @article = @article_name,

    @sync_type = 'automatic',

    @update_mode = 'read only',

    @subscriber_type = 0,

    @reserved = 'Internal'

    --5. Kick-off snapshot

    EXEC sp_startpublication_snapshot @publication = @publication_name

  • Nice 🙂 - thank you for this! I worked with the sprocs for a while, but didn't seem to get it right. I will look forward to using your method.

  • Typically I would create another publication rather than add a new article to an existing publication.

  • The GUI will run the SPROC sp_refreshsubscriptions after adding articles. This SPROC updates the subscription metadata to include the newly added table.

    To run this manually, it's a simple matter of including the following in your script:

    EXEC sp_refreshsubscriptions @publication = 'PublicationName'

    Update PublicationName as appropriate for your publication.

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

  • Hi Matt, thank you for this. I had a feeling that there was something missing when I was trying to use the sprocs... but I couldn't quite figure it out. I will add in your suggestion later this week when I run another test - it could be a real life saver in some of our sites!

  • Hi Paul, we actually thought about doing the same - just adding a new publication. It would have been a nice simple way to go. But in the end, the publish-subscribe configuration is quite specific in the environment we were in and adding a new publication wouldn't have fit the requirements.

    Not all bad though, prompted us to really look at how we went about this.

  • Matt Slocum (1/6/2015)


    The GUI will run the SPROC sp_refreshsubscriptions after adding articles. This SPROC updates the subscription metadata to include the newly added table.

    To run this manually, it's a simple matter of including the following in your script:

    EXEC sp_refreshsubscriptions @publication = 'PublicationName'

    Exactly that's how I add a new article to an existing publication without changing any publication property..

  • Its been a while, but I would avoid using the GUI completely when adding anything to replication. SSMS overly cautious with replication.

    Briefly, my way which I believe to be the most simple (and safe) way of doing this is to run these two sprocs then run the snapshot agent manually.

    exec sp_addarticle --(once per new article)

    exec sp_addsubscription @article='all' --(once per subscriber)

    /*run snapshot now*/

  • MysteryJimbo (1/7/2015)


    Its been a while, but I would avoid using the GUI completely when adding anything to replication. SSMS overly cautious with replication.

    Briefly, my way which I believe to be the most simple (and safe) way of doing this is to run these two sprocs then run the snapshot agent manually.

    exec sp_addarticle --(once per new article)

    exec sp_addsubscription @article='all' --(once per subscriber)

    /*run snapshot now*/

    Actually, the right way (at least what I have been using for long time) should be:

    exec sp_addarticle --(once per new article)

    exec sp_refreshsubscriptions @publication='<your publication name>';

    exec sp_startpublication_snapshot @publication='<your publication name>';--this starts the snapshot for the new articles

    This applies to transactional replication with push subscription. I guess I can write a blog to detail this (together with pull subscription scenario)

Viewing 14 posts - 1 through 13 (of 13 total)

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