How to: Administer a Peer-to-Peer Topology (Replication Transact-SQL Programming)

Administering a peer-to-peer topology is similar to administering a typical transactional replication topology, but there are a number of areas with special considerations. The principal difference in administering a peer-to-peer topology is that some changes require the system to be quiesced. Quiescing a system involves stopping activity on published tables at all nodes and ensuring that each node has received all changes from all other nodes. For more information, see How to: Quiesce a Replication Topology (Replication Transact-SQL Programming).

To add a new node to an existing configuration

1. Quiesce the system.

2. Create a publication and subscriptions on the new node as you did with the existing nodes. For more information, see How to: Configure Peer-to-Peer Transactional Replication (Replication Transact-SQL Programming).

To add an article to an existing configuration

1. Quiesce the system.

2. Stop the Distribution Agent at each node in the topology. For more information, see Programming Replication Agent Executables or 如何：啟動和停止複寫代理程式 (SQL Server Management Studio).

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

4. Bulk-copy the data for the new table manually at all nodes using the bcp utility. For more information, see bcp 公用程式.

5. Execute sp_addarticle (Transact-SQL) to create the new article at each node in the topology. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

   附註：
   After sp_addarticle (Transact-SQL) is executed, replication automatically adds the article to the subscriptions in the topology.

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

To make schema changes to a publication database

1. Quiesce the system.

2. Execute the data definition language (DDL) statements to modify the schema of published tables. For more information about supported schema changes, see 對發行集資料庫進行結構描述變更.

3. Before resuming activity on published tables, quiesce the system again. This ensures that schema changes have been received by all nodes before any new data changes are replicated.

範例

This example demonstrates how to add a new table article to an existing peer-to-peer replication topology with 2 nodes.

-- Create the new table at both nodes.
CREATE TABLE AdventureWorks.dbo.ProductTest (column1 int, Column2 int);
CREATE TABLE AdventureWorksReplica.dbo.ProductTest (column1 int, Column2 int);
GO

REM Bulk insert data into both the publication and subscription databases.
REM The BCP format depends on the snapshot format (native or character).
REM Execute at the command prompt.

bcp AdventureWorks..ProductTest in NewTable.bcp T SMYPUBLISHER n/c
bcp AdventureWorksReplica..ProductTest in NewTable.bcp T SMYPUBLISHER n/c

--- Add the article to the publication.
DECLARE @publication AS sysname;
DECLARE @newtable AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @newtable = N'ProductTest';

USE AdventureWorks

EXEC sp_addarticle 
  @publication = @publication,
  @article = @newtable,
  @source_table = @newtable,
  @destination_table = @newtable,
  @force_invalidate_snapshot = 0;
GO

請參閱

其他資源

管理複寫
在 SQL Server 中備份和還原資料庫
點對點交易式複寫

說明及資訊

取得 SQL Server 2005 協助