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.
Special Considerations
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.
Hi Chris,
thanks for an informative and useful article.
Given the distribution agent will not clean up transactions in the distribution database when immediate_sync is set to TRUE I assume you mean transactions for the "virtual" subscriptions you see when you look at distribution..MSdistibution_agents, the ones that have all rows entries in the UndelivCmdsInDistDB column and none in DelivCmdsInDistDB.
Also, if you have your Publication Retention Period specified by setting the "Subscriptions never expire but they can be deactivated until required" option, would this mean your distribution commands (in the UndelivCmdsInDistDB column) will NEVER be cleared up by the distribution cleanup job?
If thats the case, then setting the Publication Retention Period option back to "Subscriptions expire and may be dropped…." should ensure they get cleaned up after the defined number of hours retention.
And, should you be using backups to initilize large subscriptions instead of snapshots, it would be a better idea to turn immediate_sync to FALSE, to remove all command and transaction retention once delivered, as you aren't using snapshots anyway.
Can you confirm my ideas above?
thanks
Matthew
FYI, I noticed also that if immediate_sync is set to true the Distribution Cleanup job fails with Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
Could not remove directory '\Repldatauncpub20130619030836'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory.
Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent DistributionDB@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=onl scheduled for retry. Could not clean up the distribution transaction tables.
I read this line, "If true, The Distribution Cleanup Agent will not purge the Distribution database or the Snapshot folder until the Publication Retention Period." but it didn't sink into my brain until now that distribution cleanup would fail for good reason. I wish it would say, hey you have immediate_sync on, so not purging this publications snapshot…
I found for my error for purging snapshot folder after I turned immediate_sync off that it still didn't cleanup the folder. Since server was not in a domain, the SQL Service account didn't have rights in the folder, only the SQL Agent Service account. Once I fixed that, it could delete the contents. I could be that this was the error, not the immediate_sync setting being set to true.
I want to use Immediate sync so that I can add the subscription later, but I do not want the snapshot agent to start right away as soon as I create publication.
Since snapshot agent job and log reader agent jobs are created when publication is created, I disable the original schedule of snapshot agent job and create a new schedule for it to run at my convenient time. I do this before adding any articles to the publication. Is this right approach ?