Role of the Immediate Sync Option for SQL Server Replication

By:   |   Updated: 2012-06-04   |   Comments (6)   |   Related: > Replication


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.

Checking publication options

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.

Distribution clean up job

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

Removed 0 replicated transactions consisting of 0 statements in 0 seconds (0 rows/sec).

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:

Disabling only immediate_sync option

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

Removed 5 replicated transactions consisting of 5 statements in 0 seconds (0 rows/sec).

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.

Replication monitor

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-06-04

Comments For This Article




Tuesday, January 13, 2015 - 5:31:38 AM - Manu Back To Top (35911)

Good Tip. As always....

Well done, learnt quite a good things in your posts

Monday, June 23, 2014 - 9:08:32 PM - Joe Williams Back To Top (32362)

I'm a DBA needing some advise on two subjects.

1. Could anyone tell me how to synchronize replicated data???

2. How do you take a snapshot of the schemas???

Thanks in advance

Joe Williams

Wednesday, November 13, 2013 - 1:57:49 PM - Salman Back To Top (27487)

Hi Moin,

I am trying to add new article in an existing publication. So my question is how can I achieve this without creating a new snapshot or without reintializing.

Thanks

Thursday, October 24, 2013 - 3:52:31 AM - Azim Back To Top (27256) 
Dear Moinudheen,
Thanks for sharing this nice article,
After doing all the changes you have identified, I am still un-successful in removing rows from distribution DB.
Any further suggestion would greatly resolve issue at my end
Thanks in advance.

Monday, March 4, 2013 - 12:39:02 PM - David Back To Top (22552)

I have a large MS Access database which is used by about 40 different users. Right now we have the frontend linked to a backend database on each users computer and it is replicated with a master backend up on a server and each user synchronizes there data usually on a daliy basis.

I have just learned that the company wants to migrate the database to an sql server and thay also intend to eventually upgrade from Acess 2003 to Acees 2010. Can I still keep their backend database on the conputer and syunchronize it up to the databse in the sql server through replication  which makes it run much faster when ther backend is on their computer or do I have to directly link the frontend to the SQL database  which might slow things down directly processing the data from the sql server.

Also would I have alot of problems converting this access 2003  database to the newer access 2010 format and migrating to a SQL server at the same time. We also have a lot of security set up in the fronted with different user groups assigning different permission to certain objects in the frontend and also tables in the backend.

 

Monday, June 11, 2012 - 1:15:20 AM - Mobile Sync Server Back To Top (17919)

It is very informative and very helpful on my research regarding seo techniques. Thanks for sharing this post.

 

Regards

Andrew Struss

For more information visit our website http://www.go-db.com/














get free sql tips
agree to terms