merge replication - add new article

Posted on 2006-05-15
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


    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)

    LVL 3

    Accepted Solution

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now