locked
Adding Article to Replication RRS feed

  • Question

  • Hi, 

    When I add new article to publication and run the snapshot agent.

    Will the snapshot agent generate snapshot for all article or only for newly added replication 

    Wednesday, February 24, 2016 3:19 AM

Answers

  • Adding article will generate full snapshot. However you can tune following properties to generate only incremental snapshot:

    --To be run on your publication database
    
    EXEC sp_changepublication
    @publication = 'Pub_Name', --Enter your publication_name
    @property = 'allow_anonymous' ,
    @value = 'false'
    GO
    EXEC sp_changepublication
    @publication = 'Pub_Name', --Enter your publication name
    @property = 'immediate_sync' , 
    @value = 'false' 
    GO
    

     

    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Wednesday, February 24, 2016 3:45 AM
  • Hi  ImranKazi,

    According to my knowledge, there is no method to change the value of 'immediate_sync' property and 'allow_anonymous' property from GUI. Before you add new article to publication, you can check immediate_sync and allow_anonymous on the publication by running:

    exec sp_helppublication ‘<publication_name>’

    If the columns ‘immediate_sync’ and ‘allow_anonymous’ are having a value of 1(Enabled) for both of them, the Snapshot of all the articles will be generated after you add new article. Thus, to avoid this behavior, change these values to 0(Disabled) for both the ‘immediate_sync’ and ‘allow_anonymous’ options as other post before you add new article to publication.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Wednesday, February 24, 2016 6:17 AM

All replies

  • Adding article will generate full snapshot. However you can tune following properties to generate only incremental snapshot:

    --To be run on your publication database
    
    EXEC sp_changepublication
    @publication = 'Pub_Name', --Enter your publication_name
    @property = 'allow_anonymous' ,
    @value = 'false'
    GO
    EXEC sp_changepublication
    @publication = 'Pub_Name', --Enter your publication name
    @property = 'immediate_sync' , 
    @value = 'false' 
    GO
    

     

    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Wednesday, February 24, 2016 3:45 AM
  • Adding article will generate full snapshot. However you can tune following properties to generate only incremental snapshot:

    --To be run on your publication database
    
    EXEC sp_changepublication
    @publication = 'Pub_Name', --Enter your publication_name
    @property = 'allow_anonymous' ,
    @value = 'false'
    GO
    EXEC sp_changepublication
    @publication = 'Pub_Name', --Enter your publication name
    @property = 'immediate_sync' , 
    @value = 'false' 
    GO

     

    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

      What if I am doing with GUI
    • Edited by ImranKazi Wednesday, February 24, 2016 4:39 AM
    Wednesday, February 24, 2016 4:39 AM
  • Hi  ImranKazi,

    According to my knowledge, there is no method to change the value of 'immediate_sync' property and 'allow_anonymous' property from GUI. Before you add new article to publication, you can check immediate_sync and allow_anonymous on the publication by running:

    exec sp_helppublication ‘<publication_name>’

    If the columns ‘immediate_sync’ and ‘allow_anonymous’ are having a value of 1(Enabled) for both of them, the Snapshot of all the articles will be generated after you add new article. Thus, to avoid this behavior, change these values to 0(Disabled) for both the ‘immediate_sync’ and ‘allow_anonymous’ options as other post before you add new article to publication.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Wednesday, February 24, 2016 6:17 AM
  • I do not think we can use SSMS UI to change these values to False and therefore this script comes handy. What is more important is that we can control the behavior associated with Snapshot while adding the article. This script comes handy there.  



    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Wednesday, February 24, 2016 6:59 AM