How to add new articles to Transactional Replication without Generating Snapshot of All Articles

Run the below commands on the Publication database

USE Distribution
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT DISTINCT srv.srvname publication_server
 ,a.publisher_db
 ,p.publication publication_name
 ,a.article
 ,a.destination_object
 ,ss.srvname subscription_server
 ,s.subscriber_db
 ,da.name AS distribution_agent_job_name
FROM MSArticles a
INNER JOIN MSpublications p ON a.publication_id = p.publication_id
INNER JOIN MSsubscriptions s ON p.publication_id = s.publication_id
INNER JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
INNER JOIN master..sysservers srv ON srv.srvid = p.publisher_id
INNER JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
 AND da.subscriber_id = s.subscriber_id
ORDER BY 1 ,2 ,3

If the columns “immediate_sync” and “allow_anonymous” are having a value of 1(Enabled) for both of them, the Snapshot of all the articles will be generated.

As we do not want this behavior, we will change these values to 0(Disabled) for both the “immediate_sync” and “allow_anonymous” options.

We can disable these options by running below commands on Publication database.

Run the below commands on the Publication database


use <PublicationDB>
go

EXEC SP_CHANGEPUBLICATION @PUBLICATION = 'YOUR PUBLICATION NAME'

,@PROPERTY = 'ALLOW_ANONYMOUS' ,@VALUE = 'FALSE'

GO

EXEC SP_CHANGEPUBLICATION @PUBLICATION = 'YOUR PUBLICATION NAME'

,@PROPERTY = 'IMMEDIATE_SYNC' ,@VALUE = 'FALSE'

GO




Add article through GUI



use <PublicationDB>
go
EXEC SP_CHANGEPUBLICATION @PUBLICATION = 'YOUR PUBLICATION NAME'

,@PROPERTY = 'IMMEDIATE_SYNC' ,@VALUE = 'TRUE'

GO
EXEC SP_CHANGEPUBLICATION @PUBLICATION = 'YOUR PUBLICATION NAME'

,@PROPERTY = 'ALLOW_ANONYMOUS' ,@VALUE = TRUE