Publication的 immediate_sync 屬性

Publication的屬性 immediate_sync 控制 Snapshot 檔案的建立，如果屬性 immediate_sync設定為true，那麼snapshot file在snapshot agent 每次 run的時候都會建立。如果設定為false，snapshot file 隻在subscription建立時才會建立。

在建立Snapshot file之後，如果對article 進行update，會産生 pending transaction，這些transaction會被寫入到distribution db中。

如果immediate_sync設定為true，那麼replication 會保持 pending transaction，不會因為transaction被replcated而删除，優點是snapshot file 和 pending transaction 能被多個subscription重用，缺點是會導緻distribution db增長。pending transaction的maximum retention time是由Publication的Retention屬性确定的，Default值是336hours， 即14days。

如果immediate_sync屬性設定為false，那麼在将pending transaction推送到subscriptions之後，replication 将pending transaction 和 snapshot file删除。

引用《SQL Server "Distribution clean up: distribution" Job Failing Large MSRepl_commands》：

distribution size增長，會導緻distribution latency，推薦将immediate_sync屬性設定為false。在每次建立subscriber database時，使用新的snapshot。

如果要将Publication的 immediate_sync 設定為false，前提條件 allow_anonymous 必須是false。

allow_anonymous：Anonymous subscriptions can be created for the given publication, and immediate_sync must also be true.

使用以下腳本批量更新publication

Appendix：

一，sp_addpublication 重要參數

[ @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.

[ @independent_agent=] 'independent_agent'              

Specifies if there is a stand-alone Distribution Agent for this publication. independent_agent is nvarchar(5), with a default of FALSE. If true, there is a stand-alone Distribution Agent for this publication. If false, the publication uses a shared Distribution Agent, and each Publisher database/Subscriber database pair has a single, shared Agent.

[ @retention=] retention 

Is the retention period in hours for subscription activity. retention is int, with a default of 336 hours. If a subscription is not active within the retention period, it expires and is removed. The value can be greater than the maximum retention period of the distribution database used by the Publisher. If 0, well-known subscriptions to the publication will never expire and be removed by the Expired Subscription Cleanup Agent.

二，引用文檔：What Immediate_sync means in Transactional Replication

sp_addpublication parameter: [ @immediate_sync=] 'immediate_synchronization'

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 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.

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.

      本文轉自zsdnr  51CTO部落格，原文連結：http://blog.51cto.com/12942149/1928733，如需轉載請自行聯系原作者