how to add an article (table) to peer-to-peer replication in sql server2012

Question

I have configured peer-to-peer replication between 2 nodes. I have followed this instruction step-by-step: http://www.databasejournal.com/features/mssql/article.php/3814591/Peer-to-Peer-Replication-in-SQL-Server-2008-150-Configure-a-two-node-topology.htm

then I had a problem with adding new articles(new tables) to the replication. I have followed this instruction: http://msdn.microsoft.com/en-us/library/ms146867.aspx

it works, but it is not practical for an environment with many table changes. does anyone know a better solution? I want a fast and automatic way to add articles.

UPDATE: I have added a table to publication, just with sp_addarticle. I did not stop and start log reader agent. It seems to work. Is it going to cause problems later?

score 1 · Accepted Answer · answered Sep 07 '14 at 20:14

This is covered in Administer a Peer-to-Peer Topology (Replication Transact-SQL Programming):

To add an article to an existing configuration

1.  Quiesce the system.

2.  Execute the CREATE TABLE statement to add the new table at each node in the topology.

3.  Bulk copy the data for the new table manually at all nodes by using the bcp utility.

4.  Execute sp_addarticle to create the new article at each node in the topology. For more information, see Define an Article.

After sp_addarticle is executed, replication automatically adds the article to the subscriptions in the topology.

5.  Restart the Distribution Agents at each node in the topology.

thanks about your answer. I didn't quiesce the system or restart the distribution agent, but it worked. do you think I might get in trouble for that? — Paria Molayem, Sep 08 '14 at 05:26
If it worked and data is replicating as expected, I think you are fine. — Brandon Williams, Sep 08 '14 at 18:00

how to add an article (table) to peer-to-peer replication in sql server2012

1 Answers1