Problem
I have transactional replication configured in production. From the publication properties I can see that the immediate sync option is set to true. What is the significance of this setting if this is set to either true or false?
Solution
This tip, Limit snapshot size when adding new article to SQL Server replication, describes how to script out replication when it is initially configured through the wizard. While configuring replication using the SSMS wizard, if we check the option to “Create Snapshot immediately and keep the snapshot available to initialize subscriptions”, the immediate_sync option gets automatically enabled. So what does this mean and how does this impact replication.
Before we get started, this tip assumes transactional replication is already configured on your server. Let’s check the significance of both options, when immediate_sync is set to true and when it is set to false.
When immediate_sync is TRUE
To check the setting for immediate_sync we can run sp_helppublication on your publication database.
Here, you can see the immediate_sync option set to 1 (true).
So let’s see the impact when this is true, perform the steps below.
Step 1
Insert some values into the replicated articles in the publication database and check to see if the same data is reflected in the subscription database.
Step 2
From the job activity monitor, go to the job: “Distribution clean up: distribution”. Go to job step and click on edit to view the details of the step.
Below is the same code which we will run on the distribution database.
use distribution
go
EXEC dbo.sp_MSdistribution_cleanup
@min_distretention = 0,
@max_distretention = 72
Step 3
After executing the above stored procedure in the distribution database, you would see this message…
You would see that the data is not purged from the distribution database. This happens even though the data is already replicated entirely from the publisher to the subscriber. This is because the maximum distribution retention period of 72 hours is considered and the data in the MSrepl_commands and MSrepl_transactions replication tables in the distribution database would be maintained until this time period is crossed.
Step 4
Query the MSrepl_commands and MSrepl_transactions tables in the distribution database and you would see the data is not yet deleted even though the distribution clean up job has run.
Step 5
As a next step create a new subscription to the publication database. You would notice that a new snapshot is not created. Instead the existing snapshot is used. You could confirm this by checking the snapshot folder. To find the snapshot folder in SSMS go to Replication > Local Publications > right click on your publication > Properties > Snapshot tab > Location of Snapshot files. Also the data that is available in MSrepl_commands and MSrepl_transactions replication tables in the distribution database is used to bring the new subscription database in sync with the publication database. This is evident as a new snapshot is not generated.
When immediate_sync is FALSE
To test this we can change the value of immediate_sync option to false by running this on your publication database.
–Run on your publication database
EXEC sp_changepublication
@publication = ‘REP_P’, –Enter your publication_name
@property = ‘allow_anonymous’ ,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘REP_P’, –Enter your publication name
@property = ‘immediate_sync’ ,
@value = ‘false’
GO
The reason you need to set allow_anonymous to false is because if you only try to disable the immediate_sync option you would get this error message as shown below:
After running the above step do the following.
Step 1
Insert some values into the replicated articles in the publication database and check to see if the same data is reflected in the subscription database.
Step 2
From the job activity monitor, go to the job: “Distribution clean up: distribution”. Go to job step and click on edit to view the details of the step.
Step 3
After executing the stored procedure from Step 2 above in the distribution database, you would see this message…
This is because data is already replicated from the publisher to the subscriber. You could also query the data in the MSrepl_commands and MSrepl_transactions replication tables in the distribution database to confirm.
Step 4
Navigate to the snapshot folder and you would notice that it is empty. Unlike the case when immediate_sync was set to true the snapshot folder was maintained for the entire course of the maximum retention period.
Step 5
Create a new subscription to the publication database. On checking the subscription database you would notice that data from the publication database is not yet available as no snapshot is present. This information can be seen in the Replication Monitor as shown below.
Step 6
Reinitialize the subscription so that a new snapshot gets applied on to the subscription database. Refer to this tip for more details: Options to reinitialize subscriptions in SQL Server replication
Summary
From the testing performed, you can see that the immediate_sync option set to TRUE is useful especially if you have the requirement of adding new subscriptions during the course of the retention period. You can also see that the data in the distribution database replication tables gets maintained even though the data is already replicated from the publisher to the subscriber. This might be problematic especially if you are looking for performance optimization in a highly active replication environment. Consider this option only after careful analysis based on your business requirements. Also, a point to remember is that this option might get inadvertently enabled, especially if you are using the GUI for initial replication configuration as described in the beginning of the tip.
Next Steps
- Get familiar with the replication concepts
- Try testing this tip in your own replication setup where transactional replication is configured
- Refer to other related replication tips
