Categories: ReplicationSQL Server AdministrationTransactional Replication

Adding Article to Transactional Replication Publisher without Snapshot Re-initialisation

Table of Contents

Toggle

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.

Vamshi B

Next Transaction log file VLF management »

Previous « SQL Server Transparent Data Encryption

View Comments

luisa says:

May 20, 2020 at 12:56 pm

Hey there, You've done an excellent job. I'll certainly digg it and personally recommend to my friends.
I'm confident they'll be benefited from this website.
laurence says:

September 25, 2020 at 11:47 am

Thanks for one's marvelous posting! I genuinely enjoyed reading it, you are a great author.I
will be sure to bookmark your blog and may come back
later on. I want to encourage you continue your great posts,
have a nice holiday weekend!
Adam says:

November 13, 2020 at 3:36 pm

Since the admin of this site is working, no question very rapidly it will be famous, due to its quality contents.
Top Best Review says:

June 5, 2021 at 5:06 pm

Hi I am so glad I found your blog, I really found you by mistake, while I was looking on Yahoo for something else, Anyways I am here now and would just like to say kudos for a remarkable post and a all round entertaining blog (I also love the theme/design), I don't have time to go through it all at the minute but I have bookmarked it and also added your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the excellent job.
healt says:

November 1, 2021 at 7:17 am

Excellent site. Plenty of helpful information here. I am sending it to
a few friends ans additionally sharing in delicious.
And certainly, thanks for your sweat!
Vamshi B says:

October 15, 2023 at 3:00 pm

For Stock market related articles for educational purposes visit our site
https://quick-hack.com/