Behavior observed due to @immediate_sync parameter of Transaction Replication when adding new article to an existing publication

  • Article

* sp_addPublication procedure for creating a new transactional replication has a parameter @immediate_sync. BOL has description about it as:
<BOL>
@immediate_sync=] 'immediate_synchronization'
Specifies if the synchronization files for the publication are created each time the Snapshot Agent runs. immediate_synchronization is nvarchar(5), with a default of FALSE. If true, the synchronization files are created or re-created each time the Snapshot Agent runs. Subscribers are able to get the synchronization files immediately if the Snapshot Agent has completed before the subscription is created. New subscriptions get the newest synchronization files generated by the most recent execution of the Snapshot Agent. independent_agent must be true for immediate_synchronization to be true. If false, the synchronization files are created only if there are new subscriptions. You must call sp_addsubscription for each subscription when you incrementally add a new article to an existing publication. Subscribers cannot receive the synchronization files after the subscription until the Snapshot Agents are started and completed.
</BOL>

* We observed that the value of this @immediate_sync parameter makes a difference to the snapshot produced when a new article is added to an existing publication.
When the @immediate_sync parameter is set to 0 i.e. when the immediate_sync column of distribution..MSpublications table is set to "0" zero and you add a new table (article) to the existing publication, you will find that when you run the snapshot agent, snapshot of only that new article will be produced in the \REPLDATA folder. snapshot will be produced in concurrent mode. When you run distribution agent, it will apply the snapshot of only the newly article to the subscriber.

* When the @immediate_sync parameter is set to 1 i.e. when the immediate_sync column of distribution..MSpublications table is set to "1" one and you add a new table (article) to the existing publication, you will find that when you run the snapshot agent, concurrent snapshot of ALL the articles in the publication will be prepared. When you run distribution agent however, it will apply the snapshot of only the newly article to the subscriber.

* If @immediate_sync parameter is set to 0 and you add a new article to publication, distribution agent will not automatically produce the snapshot of the new article. You need to manually run the snapshot agent which will produce the snapshot of that article only. After this, when you run the distribution agent, it will now apply the snapshot of that article properly and proceed with synchronization.

* The behavior of producing the snapshot of only the newly added article may be very much desired in most cases because:
 > It means that we will not be require huge space for snapshot files
 > snapshot process will not lock (although the concurrent snapshot locks the tables for minimal time) the other existing tables
 > less processing by the distribution since the snapshot size is small.

* Please note that I have observed and repro'ed it on my sql server 2005 box but I have not tested all possible scenarios and additional testing may be required. Test it out in your environment before you take a decision to set this value for your purpose.