Shulou(Shulou.com)06/01 Report--

The property immediate_sync of Publication controls the creation of the Snapshot file. If the property immediate_sync is set to true, then snapshot file is created every time snapshot agent run. If set to false,snapshot file, it is created only when the subscription is created.

After creating the Snapshot file, if you update the article, you will generate a pending transaction, and the transaction will be written to the distribution db.

If immediate_sync is set to true, then replication will keep pending transaction and will not be deleted because transaction is replcated. The advantage is that snapshot file and pending transaction can be reused by multiple subscription, and the disadvantage is that it will lead to distribution db growth. The maximum retention time of pending transaction is determined by the Retention property of Publication, and the default value is 336hours, that is, 14days.

If the immediate_sync property is set to false, replication removes pending transaction and snapshot file after pending transaction is pushed to subscriptions.

Quote "SQL Server" Distribution clean up: distribution "Job Failing Large MSRepl_commands":

-- Check to see if "immediate sync" is set to 1.Mutual-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'

The growth of distribution size will lead to distribution latency. It is recommended to set the immediate_sync property to false. Each time you create a new subscriber database, use the new snapshot.

If you want to set the immediate_sync of Publication to false, the prerequisite allow_anonymous must be 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.

Use the following script to update publication in bulk

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:

1. Important parameters of 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.

Second, quote document: 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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.