Replication – Adding New Article without Generating Snapshot – By Nideesh

Below article on replication was written by my colleague Nideesh K Vasudevan. I would like to thank him for his easy to understand input on this topic.

There were lot of requirements in replication to add new article with out
running snapshot. Some article explained ‘how to include only the newly
added article in the snapshot with out generating snapshot of all the
article”.

All my play only with different publication properties gave me failed
output but a test to also play with subscription property worked!!!!!!!!!!

3 properties that we need to consider are 1) Immediate_Sync
2)allow_Anonymous in the publication properties and important one is 3)
@Sync_Type in subscription properties.

Immediate_Sync and allow_anonymous publication properties

1) The above 2 values do not determine if a snapshot is needed for a new
article, but it determines if snapshot need to be generated for all the
article or only for new article.
2) If both the above options are enabled/true and when we generate a
snapshot after adding new articles, the snapshot will be generated for all
the articles (new article+old article).
3) If both the above options are disable/false and when we generate a
snapshot after adding new articles, the snapshot will be generated only for
newly created article. (only for new article)

@Sync_type – Subscription property

1) The above option determines if a snapshot needs to be generated for
newly added article.
2) If the value of @Sync_type is set to ‘none’ or ‘Replication support
only’ then its enough we just create table structure on both publisher and
subscriber and enable the new table in the publication. When the new data
comes in, it will get replicated automatically.
No snapshot is needed in this case and the application is working with the
above option (@Sync_type=’Replication support only’). Hence adding new
article by creating table structure on publication and subscription got
replicated with this option enabled.
3) If the @sync_type option is set to automatic then for sure we need a
snapshot for newly added article. – Immediate_sync option determines if
snapshot need to be generated for all article or only new article.

http://technet.microsoft.com/en-us/library/ms181702(v=sql.105).aspx – Use
this link to see about @sync_type option.