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》：

-- Check to see if "immediate sync" is set to 1.--  This will cause transactions to be held in MSRepl_commands -- rather than deleted when distributed. EXEC sp_changepublication 
    @publication = '', -- put your publication name here 
    @property = 'immediate_sync', 
    @value = 'false'

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

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

EXEC sys.sp_changepublication 
    @publication = '', -- put your publication name here 
    @property = 'allow_anonymous', 
    @value = 'false'

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

使用以下腳本批量更新publication

select 
    name,
    immediate_sync,
    allow_anonymous,
    N'exec sys.sp_changepublication  @publication =  '''+name+''' 
    , @property =  ''immediate_sync'' 
    , @value =  ''false''' as Alter_immediate_sync,    case 
        when allow_anonymous=1 then 
            N'exec sys.sp_changepublication  @publication =  '''+name+''' 
            , @property =  ''allow_anonymous'' 
            , @value =  ''false''' 
        else '' 
    end as alter_allow_anonymousfrom dbo.syspublicationswhere immediate_sync=1

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.