I encounter an interesting issue on Transaction Replication where I was unable to add a newly added table into some subscriber. My replication setup consists of 1 Publisher and 7 subscribers.

I followed the below step to add a new article in my existing Publication which is working fine in my Development environment which is almost similar to Production.

1. To avoid complete snapshot of the Publisher database I set allow_anonymous and Immediate_sync to False by default its set to TrueEXEC sp_changepublication @publication = ‘PubDB’,
   @property = ‘allow_anonymous’,
   @value = ‘false’
   GO
   EXEC sp_changepublication @publication = ‘PubDB’,
   @property = ‘immediate_sync’,
   @value = ‘false’
   GO
2. Added new table into exiting publication properties
3. Start Snapshot Agent
4. Snapshot Agent created a Snapshot for one article and the scripts can be seen in repl data folder
5. Started Distribution Agent (Ignore if its already running) to deliver this snapshot on the subscriber and it should look like this as shown below

Now here I noticed that on 3 of the sites out of 7 Distribution Agent did not apply snapshot on the subscriber and after some time I noticed that the folder gets deleted for this snapshot containing schema, index ,BCP file etc.

This is because my distribution agent cleanup job was configured to run after every 5 min to control the size of distribution database and shared drive containing snapshots.

EXEC dbo.sp_MSdistribution_cleanup  @min_distretention = 0, @max_distretention = 72

Initially I focused on one of the properties immediate sync = TRUE, which I believe could be responsible for deleting the snapshot before applying it on the subscriber.

But after looking into my distribution agent cleanup job properties and after referring to Article my assumption start getting wrong here the difference is that in my case its set to @min_distretention = 0 so do not wait and delete the transaction once it delivered to subscriber case

If “immediate sync” = TRUE, do not keep transaction EVEN IF they have been replicated to the subscriber but delete any transaction older the 72 hours.  Metadata that is younger than 72 hours, but older than 4 hours will be kept. This is conservative.

If “immediate sync” = FALSE, then keep transaction for at least 4 hours but if the transaction has already been replicated (distributed) to the subscriber go ahead and delete EVEN IF not older then 72 hours. Metadata that is younger than 72 hours, but older than 4 hours may be deleted quite aggressively.

Later on I focused on Replication Script and noticed that there is a small change in the Subscription properties of these 3 subscribers as shown below but the impact is big.

@sync_type = N’replication support only’

This property allows you to manually synchronize your data and schema on your publisher and your subscriber, but then the support objects needed to make replication work are pushed automatically to the subscriber. The benefit is that you can still take advantage of the replication tools to setup replication, but you also have more control over getting your data and schema in sync.  This is great if you already have a database in place and you only want to setup replication. Instead of having to drop and recreate the tables, this option will just push out the objects needed for replication and not affect your existing schema or data.

But you will not be able to add new article in publication once you configure subscriber with this option and this is by design “Replication Support Only” 

Work Around:-

There is no direct way to fix this as this property and this property is not available in GUI and can not be modified, so you have to drop and recreate the subscription with sync_type = N’automatic’. But in a mission critical application this is not possible so one of the approach is to manually apply the script on subscriber. If you have the script copied from the repl data folder then execute that script on all the subscriber which are having this problem.

Important:- While you are manually adding this article ensure that no transaction are made into this table else you will encounter errors like “Object Note Found” or “Row Not Found”.

Resolution:-

• Create a new database with same name on another SQL instance and make changes in web.config to point to this database, meanwhile you can reconfigure your subscriber using re-initialization method with sync_type = N’automatic’
• If your application is accessing subscriber database using Synonyms from other database then create a database with same name (using restoration)on same SQL instance and point synonym to this database and meanwhile you can reconfigure your subscriber using re-initialization method with sync_type = N’automatic’

When this option was enabled on my subscription and why?

Well there is no direct way to identify when this configuration was done so you have to rely on distribution agent creation job date or check for subscriber database restoration date. This option might give you some idea if  in past the replication was synchronized with this option to avoid any down time.