In my previous two blogs – Capture Important Parameters of the SQL Server Replication and Add new articles to existing Transactional Replication without initializing old articles, I mentioned about one of the very critical parameters to consider is the immediate_sync in the Transactional Replication. Let’s understand both the options of the parameter. When you say;

immediate_sync= true

• It means if you run the Snapshot Agent, the Snapshot files will be created for all the articles for the Publication. Even if you add only one article, you have all article created for that snapshot execution.

• If you have publications which got many articles with huge row count, It could significantly increase the distribution database size because all the transactions will be saved (CACHED) into the database for the retention period.

immediate_sync= false

• If you add a new article to a publication and you execute the Snapshot Agent, only the new article snapshot file is generated.

• If you add a new subscriber to the existing publication, the Snapshot files only get created for the new subscription.

• It cleans up the saved (CACHED) transactions as soon as it gets replicated to the subscriber database.

Enabling\Disabling immediate_sync using Replication Wizard (GUI)

During a new Publication setup, you get an option “Create a snapshot immediately and keep the snapshot available to initialized subscriptions” on the “Snapshot Agent” tab.

• If you check the option, it means you have enabled the immediate_sync. In other words, the option is set to TRUE (1). 

• If you don’t check this option, it means you have not enabled the immediate_sync. In other words, the option is set to False (0). By default, it is unchecked.

Once you created a new Publication using the wizard (GUI) and you want to change the immediate_sync configuration later on using the GUI, it doesn’t provide an option to change it. Therefore, you need to use T-SQL to change the status. You can follow the below script to change the immediate_sync status

----Check the Publication immediate_sync status
USE <Publisher DatabaseName>
GO
EXEC  sp_helppublication 
GO
----Change the Publication immediate_sync status
USE <Publisher DatabaseName>
GO
EXEC  sp_changepublication  
@publication = '', ------ Key the publication name
@property = 'immediate_sync', 
@value = 'false'; -----True or False
GO 
----After chaning the Publication immediate_sync status, let's verify it
USE <Publisher DatabaseName>
GO
EXEC  sp_helppublication 
GO

Hope, you find this blog useful and helpful to understand the meaning of the immediate_sync status.

Keep Learning!

The post What Immediate_sync means in Transactional Replication – Part1 appeared first on .