What Immediate_sync means in Transactional Replication
José Moreira Neto
Microsoft SQL Server Escalation Services
sp_addpublication parameter: [ @immediate_sync=] 'immediate_synchronization'
Immediate_sync has a default of FALSE.
independent_agent must be true for immediate_synchronization to be true.
Immediate_sync feature instructs Replication to maintain Snapshot BCP files and distributed transactions in the Distribution database should a new subscriber be created (or reinit) within the Retention period. By default, this information is purged as soon as existing subscribers receive the transactions. By keeping the Snapshot and transactions for hours or days, new subscribers can 1) Use old snapshot files, and 2) apply all pending changes since the snapshot. New Subscribers (or reinit) would not need to have a “fresh” snapshot generated.
IF TRUE (keep commands CACHED in Distribution database)
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. Subscribers would then receive any “pending” transactions from the Distribution database.
If the publication has the property @immediate_sync set to “true” after the Snapshot Agent is created, transactions on the distribution database are maintained only to support the existing subscribers. In other words, at the time of the Snapshot generation completes all subscriptions to be initialized must be known. If a new subscriber is added, or marked for re-initialization after the Snapshot is Generated another snapshot is required.
IF FALSE (clean up CACHED commands as soon as Replicated)
If false, the synchronization files are created only if there are new subscriptions. Subscribers cannot receive the synchronization files until the Snapshot Agents are started and completed.
With immediate_sync was set to “false”, if you then mark ALL subscriptions for re-initialization, they can all apply the same Snapshot.
If true, The Distribution Cleanup Agent will not purge the Distribution database or the Snapshot folder until the Publication Retention Period. This may result in unexpected distribution database growth.
When set false, and reinit a subscriber, a new snapshot is required, even if a snapshot was recently run. For example:
- Create Publication with Immediate_sync = True
- Create Snapshot 1
- Create Sub A
- Create Sub B
- Replicate Transactions
- Weeks later Reinit Sub A.
- Create New Snapshot 2 for Sub A Reinit as Snapshot 1 has expired (outside retention period)
- Set Immediate_sync = False
- Reinit Sub B
As soon as Immediate_sync = False, the previous Snapshot files can no longer be used to reinit subscribers. Sub B will require a new snapshot to be generated.
edited and posted by Chris Skorlinski