Adding new article to Existing Publication (Transactional Replication)

Adding new article to Existing Publication (Transactional Replication)

First of all run the Exec sp_helppublication in publication database and checked the following fields

1. Immediate_sync
2. Allow_anonymous

Both the fields were set to ON as they showed a value 1 which is enabled. If the Immediate_sync is enabled, every time you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone. Usually, the Immediate_sync publication property is set to true if we allowed anonymous subscriptions while creating the publication through the Create Publication wizard. To prevent the complete snapshot, run the script below.

Step 1:- Disable the two fields in publication database

 

EXEC sp_changepublication
@publication = ‘Publication name’,
@property = N'allow_anonymous',
@value = 'false'
GO

EXEC sp_changepublication
@publication = Publication name’,
@property = N'immediate_sync',
@value = 'false'
GO

Step 2:- Add new article in publication database

    exec sp_addarticle
    @publication = N'Publication name’,
    @article = N'Table Name',
    @source_object = N'Table Name',
        @destination_table = N'Table Name'
         GO

exec sp_addsubscription

          @publication = N'Publication name’,
          @subscriber = N'Subscriber Server Name',
          @destination_db = N'Destination Database Name',
          @subscription_type = N'Subcription Type' [Pull\Push]

Step 3:- Now start the snapshot agent in publisher, it worked perfectly. You can see that only the particular table added was replicated. So from now on to apply the snapshots of the entire articles you need to reinitialize the subscriptions since the Immediate_sync is set to off.