[Last Call] Learn how to a build a cloud-first strategyRegister Now


merge replication - add new article

Posted on 2006-05-15
Medium Priority
Last Modified: 2012-05-05
Hi experts

I have here a merge replication to 5 location over the world. I have one distributor and 5 subscribers.
So, now I want to add a new Article to all 5 Subscribers ( a new table for example).

How is the easiest way to do this because the hole database has a size of about 2GB of data? Is it possible to add this article without doing a complete Snapshot over the complete database?
Does a Snapshot delete all records at the subscribers and copy it again from the publisher?

Thank you
Question by:gumball_ch
LVL 28

Assisted Solution

imran_fast earned 375 total points
ID: 16682516

Stop all the users from using the database at the subscriber.
Make sure all the subscriber are synch with the publisher.
drop the subscription
modify the publisher to add the new article.

Generate the script for this new table (after adding in the publication) also script the triggers created on this table by merge replication and the stored procedure (for insert, update and delete) you will find all of this on the publisher.

create this table (only table without triggers) on the subscirber and move the data using dts and then create the triggers and sps on the subscriber after that push the publication to the subscriber and select the option (data adn schema already exists)


Accepted Solution

lokeshgm7 earned 375 total points
ID: 16732047
You can add a article to the existing publication by using the following stored Proc.

"sp_addmergearticle" (please refer SQL Books online for more info)

This will rerun the snapshot but the only difference would be that the subscribers wont be reinitialized. (It wont drop and recreate the articles at the subscriber for all the other artciles which are already published). It will only update the newly added artilce at the subscriber.

You might be interested in looking at the option
"[ @force_invalidate_snapshot = ] force_invalidate_snapshot " when you use that stored proc.


Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question