Adding Article to Transactional Replication Publisher without Snapshot Re-initialisation

Add article to Transactional Replication without Snapshot Re-initialisation

To add a new article to a publisher efficiently without reinitializing whole replication but running the below scripts/procedure can be done without whole snapshot to be run again. This method only snapshots the new article added to the publisher.

The Steps needs to be followed in same order.

Step No Steps
1 EXEC sp_changepublication @publication = ‘ContosoPub’
@property = N’allow_anonymous’
@value = ‘false’
GO
Change the property of replication.
2 EXEC sp_changepublication @publication = ‘ContosoPub’
@property = N’immediate_sync’
@value = ‘false’
GO
Change the property of replication.
3 add article to publication either by script below or GUI
EXEC sp_addarticle @publication = ‘ContosoPub’
4 add article to subcriber either by script below or GUI
EXEC sp_addsubscription @publication = ‘ContosoPub’
5 run snapshot agent from the GUI by going into Agents Tab in Replication Monitor
6 Notice that you should see just the one article being copied over
7 set to publication options to true by following steps below
8 EXEC sp_changepublication @publication = ‘ContosoPub’
@property = N’allow_anonymous’
@value = ‘true’
GO
Change the property of replication.
9 EXEC sp_changepublication @publication = ‘ContosoPub’
@property = N’immediate_sync’
@value = ‘true’
GO
Change the property of replication.

Leave a Reply

Your email address will not be published. Required fields are marked *