Replication: Adding articles to existing publication:

Following are the steps for adding articles to existing Transactional Replication without initializing old entries:

Adding articles to existing publication:

Allow_anonymous:

--First, change the allow_anonymous property of the publication to FALSE

EXEC sp_changepublication @publication = N'PubMplus_Transactions2' @property = N'allow_anonymous'

@value = 'False' 

GO

Immediate_Sync:

 --Next, disable Change immediate_sync

EXEC sp_changepublication @publication = N'PubMplus_Transactions2' @property = N'immediate_sync'

@value = 'False' 

GO

Following are articles with their row count that needs to added to existing replication.

Because of row count is greater than 10 lacs and if all articles are added at one time, then log space and blocking are main issues.

So we are adding one by one article and run snapshot agent after completion, add another article.

1. Invalidate the snapshot

EXEC sp_addarticle @publication = N'Adventureworks2016-Pub', @article =N'Cities', @source_object =N'Cities', @force_invalidate_snapshot=1

2. Refresh subscriptions 

EXEC sp_refreshsubscriptions @publication = N'Adventureworks2016-Pub'

Now, start Snapshot Agent using Replication monitor.

Check rows on the subscriber

Monitor the drive space and make sure free space is available.

Adding articles to existing publication:

Allow_anonymous:

--First, change the allow_anonymous property of the publication to FALSE

EXEC sp_changepublication @publication = N'PubMplus_Transactions2' @property = N'allow_anonymous'

@value = 'False' 

GO

Immediate_Sync:

 --Next, disable Change immediate_sync

EXEC sp_changepublication @publication = N'PubMplus_Transactions2' @property = N'immediate_sync'

@value = 'False' 

GO

Following are articles with their row count that needs to added to existing replication.

Because of row count is greater than 10lacs and if all articles are added at one time, then log space and blocking are main issues.

So we are adding one by one article and run snapshot agent after completion, add another article.

1. Invalidate the snapshot

EXEC sp_addarticle @publication = N'Adventureworks2016-Pub', @article =N'Cities', @source_object =N'Cities', @force_invalidate_snapshot=1

2. Refresh subscriptions 

EXEC sp_refreshsubscriptions @publication = N'Adventureworks2016-Pub'

Now, start Snapshot Agent using Replication monitor.

Check rows on the subscriber

Monitor the drive space and make sure free space is available.