Add article on existing publication without reinitialize in SQL Server

We can add an article or table on existing publication in SQL Server without dropping the publication and we can synchronize the particular article on existing subscription. This is common and frequent requirement in DBA task list to add an article or table on existing publication in SQL Server and also frequently asked questions in DBA interviews.

How to add article or table on existing publication without reinitialize the subscription agent in SQL Server

Open new query window in SQL Server Management Studio and follow the below steps.

Step 1: Ensure the publication property allow_anonymous and immediate_sync value should be zero where you have to add the new article.

SELECT immediate_sync, allow_anonymous FROM syspublications WHERE Name = 'Publication Name'

Step 2: If else, you have to update the property values using below mentioned query.

--Changing allow anonymous value as false
EXEC sp_changepublication
@publication = 'Replication Name', -- Publication Name
@property = 'allow_anonymous', -- Property Name
@value = 'false' -- Property Value

--Changing immediate sync value as false
EXEC sp_changepublication
@publication = 'Pub_ReplicationDB', -- Publication Name
@property = 'immediate_sync', -- Property Name
@value = 'false' -- Property Value

Replication Property Definitions:

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

If true, every time you add a new article it will cause the entire snapshot to be applied not the one for the particular article alone.

If false, the synchronization files are created only if there are new subscriptions. Subscribers cannot receive the synchronization files until the Snapshot Agents are started and completed.

Allow_anonymous: To modify the immediate_sync propery value we need to set false on allow_anonymous value.

Once you have updated these property and ensure the values are updated or not using below mentioned query.

SELECT immediate_sync, allow_anonymous FROM syspublications WHERE Name = 'Publication Name'

Step 3: Now, we can able to add the new article on existing publication using below mentioned query.

--Adding an article on existing Publication
EXEC sp_addarticle
@publication = 'Pub_ReplicationDB', -- Publication Name
@article = 'RepTable3', -- Article Name
@source_object = 'RepTable3', --Table Name
@force_invalidate_snapshot = 1; --Force to stop validating the snapshot

--Adding an article on existing Subscription
EXEC sp_addsubscription
@publication = 'Pub_ReplicationDB', -- Publication Name
@article = 'RepTable3', -- Article Name
@subscriber = 'MDS02', -- Subscriber Instance name
@subscription_type = 'push', -- Type of Subscription push/pull
@destination_db = 'SubscriptionDB', -- Subcription Database
@reserved = 'internal' -- It is for internal purpose only to specify the new article instead of all.

Step 4: After added new article, we need to execute the snapshot agent using below mentioned query.

EXEC sp_startpublication_snapshot
@publication = 'Pub_ReplicationDB' -- Publication Name

Step 5: Verify the article applied on subscriber using Agent history in Replication Monitor.

We can see the action message in agent history like as above and the table changes applied successfully on subscription database.

Also see below articles

Top 30 SQL Server DBA Interview Questions and Answers
Implement Best Practice and Performance tuning tips in SSIS and ETL Development
Top 15 Power BI interview Questions with detailed Answers