How to: Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio)
This topic describes how to configure and maintain a peer-to-peer replication topology by using the Configure Peer-to-Peer Topology Wizard. First, the topic shows how to create a three node topology with the wizard, and then describes how to add and remove nodes, and connections between nodes. In SQL Server 2008 and later versions of SQL Server, nodes can be added and connected while activity is occurring in the system. If a topology includes SQL Server 2005 nodes, you can connect a new node to one existing node, but you must quiesce the system to connect to more than one node. 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. This process is described in detail in the last section of this topic.
Creating a Peer-to-Peer Replication Topology
The following steps provide an overview of how to create a topology that has three nodes: Server A, Server B, and Server C. Each step is described in more detail in this section.
Make sure that SQL Server Agent is running on each node and that the Log Reader Agent and Distribution Agent are running after the topology is configured. For information about SQL Server Agent, see Starting SQL Server Agent; for information about how to start and stop agents, see How to: Start and Stop a Replication Agent (SQL Server Management Studio).
Configure distribution at Server A, Server B, and Server C.
You can use a local or remote Distributor. If you use a remote Distributor, we recommend that you do not use the same remote Distributor for all nodes, because this is potentially a single point of failure. For more information about the Distributor, see Replication Publishing Model Overview.
Create a publication at Server A by using the New Publication Wizard.
Enable the publication for peer-to-peer replication by using the Publication Properties - <Publication> dialog box.
Initialize the schema and data at Server B and Server C manually or by restoring the database at Server A to Server B and Server C. The schema at all nodes must be identical.
Add Server B and Server C to the topology by using the Configure Peer-to-Peer Topology Wizard. Adding the servers: creates a publication at each server; and creates subscriptions to the publications that are on the other servers in the topology.
If there are any identity columns in published tables, you must reseed the identity ranges for the tables at Server B and Server C.
To configure distribution for each node
In SQL Server Management Studio, connect to Server A. This configuration process must be repeated for Server B and Server C.
Expand the server node, right-click the Replication folder, and then click Configure Distribution.
On the Distributor page of the Configure Distribution Wizard, select a Distributor.
To use a local Distributor, select '<ServerName>' will act as its own Distributor; SQL Server will create a distribution database and log. To use a remote Distributor, select Use the following server as the Distributor, and then select a server. The server must already be configured as a Distributor, and the Publisher must be enabled to use the Distributor. For more information, see How to: Enable a Remote Publisher at a Distributor (SQL Server Management Studio).
If you select a remote Distributor, you must enter a password on the Administrative Password page for connections made from the Server A to the Distributor. This password must match the password specified when Server A was enabled as a Publisher at the remote Distributor.
On the Snapshot Folder page, specify a snapshot folder.
The snapshot folder is just a directory that you have designated as a share. Agents that read from and write to this folder must have sufficient permissions to access it. This directory stores objects that are required by replication at the first node. This directory is not used by peer-to-peer replication at the other nodes, but it is still required to configure a Distributor. For more information about how to secure the folder appropriately, see Securing the Snapshot Folder.
On the Distribution Database page, specify a name for the distribution database.
The distribution database stores transactions, metadata, and history data.
On the Publishers page, optionally enable other Publishers to use Server A as a remote Distributor.
If other Publishers are enabled to use Server A as a remote Distributor, you must enter a password on the Distributor Password page for connections made from the Publisher to the Distributor.
On the Wizard Actions page, optionally script configuration settings. For more information, see Scripting Replication.
To create a publication at the first node
In SQL Server Management Studio, connect to Server A, and then expand the server node.
Expand the Replication folder, and then right-click the Local Publications folder.
Click New Publication.
On the Publication Database page of the New Publication Wizard, select the database you want to publish.
On the Publication Type page, select Transactional publication.
On the Articles page, select the database objects you want to publish.
Click Article Properties to view and modify article properties. The following properties should not be changed:
Destination object name
Destination object owner
The <ACTION> delivery format options (where ACTION is INSERT, UPDATE, or DELETE), cannot be set to <ACTION> statement or <ACTION> statement without column list.
Filters cannot be defined on the Filter Table Rows page, because filtering peer-to-peer publications is not supported.
On the Snapshot Agent page, clear Create a snapshot immediately.
On the Agent Security page, specify credentials for the Snapshot Agent and Log Reader Agent.
For more information about the permissions that are required by each agent, see Replication Agent Security Model and Replication Security Best Practices.
On the Wizard Actions page, optionally script the publication. For more information, see Scripting Replication.
On the Complete the Wizard page, specify a name for the publication. Publication names must be the same across the topology, because the Configure Peer-to-Peer Topology Wizard uses this name when it creates a publication at each node.
To enable the publication for peer-to-peer replication
Expand the Replication folder on Server A, and then expand the Local Publications folder.
Right-click the publication you created, and then click Properties.
In the Publication Properties - <Publication> dialog box, on the Subscription Options page, select a value of True for the property Allow peer-to-peer subscriptions.
Click OK.
To initialize the schema and data at each node
Initialize Server B and Server C in one of the following ways:
Manually copy the schema and data from Server A to Server B and Server C by using Integration Services (SSIS), scripts, or another method. Make sure that the schema and data at Server B and Server C are identical to those at Server A before you configure those servers as peers.
The initial data must be present on each node before the topology is configured. Use SQL Server backup and restore functionality to initialize data for the publication at each node in the topology. The backup should come from the first node that is configured; in this case, that is Server A. The backup must be taken after the publication at Server A is created and enabled for peer-to-peer replication. This topic assumes that no activity occurs on any of the new nodes before all nodes are added; therefore you can use the same backup to initialize each node.
If activity does occur on any of the new nodes before all nodes are added, you must take a new backup after each node has been added and has synchronized at least once with Server A. This ensures that the backup from Server A contains metadata about all other nodes. For example, if you add Server B and Server C with activity on these nodes: initialize Server B with a backup from Server A; configure and synchronize Server B; take a new backup from Server A; initialize Server C with the new backup; and configure and synchronize Server C.
For more information about how to back up and restore databases, see Backing Up and Restoring Databases in SQL Server.
Important
When you are restoring the database, do not specify the KEEP_REPLICATION option (for Transact-SQL) or the Preserve the replication settings option (for SQL Server Management Studio). Replication will configure the database appropriately when you run the Configure Peer-to-Peer Topology Wizard.
A backup contains the whole database; therefore, each peer database contains a complete copy of the publication database when it is initialized. The backup might contain tables that are not specified as articles for the publication. It is the responsibility of the administrator or application to remove any unwanted objects or data after the backup has been restored. In subsequent synchronizations, data changes are only replicated if they apply to tables that are specified as articles.
To configure the topology
Right-click the publication that you created on Server A, and then click Configure Peer-to-Peer Topology. You might have to refresh the publication node to see the Configure Peer-to-Peer Topology option.
On the Publication page of the Configure Peer-to-Peer Topology Wizard, the publication that you created on Server A will be selected.
On the Configure Topology page, add Server B, and then repeat this process for Server C:
Right-click the design surface of the page, and then click Add a New Peer Node.
In the Connect to Server dialog box, connect to Server B.
In the Add New Peer Node dialog box, select the publication database and select Connect to ALL displayed nodes. If conflict detection is enabled (the default), specify a unique value for each node in the Peer ID box. The value must never have been used in the topology. For a list of IDs that have already been used, query the Mspeer_originatorid_history system table. For more information about conflict detection, see Conflict Detection in Peer-to-Peer Replication.
Click OK. Server B now appears on the design surface with a connection to Server A. When Server C is added, each node will be connected to the other two nodes.
On the Log Reader Agent Security page, specify credentials for the Log Reader Agent at each node.
For more information about the permissions that are required by the Log Reader Agent, see Replication Agent Security Model and Replication Security Best Practices.
On the Distribution Agent Security page, specify credentials for the Distribution Agents that service the subscriptions for each node.
The Configure Peer-to-Peer Topology Wizard creates push subscriptions (the Distribution Agent runs at the Distributor), with an independent agent for each subscription. In a three-node topology, each node would have a subscription for the other two nodes, so two Distribution Agents would run at each Distributor.
For more information about the permissions that are required by the Distribution Agent, see Replication Agent Security Model and Replication Security Best Practices.
On the New Peer Initialization page, select I created the peer database manually, or I restored a backup of the original publication database which has not been changed since the backup was taken.
Complete the wizard. On the Building the Peer-to-Peer Topology page, a warning might be issued about the Log Reader or publication already existing for Server A. This is informational and requires no action.
If there are any identity columns in published tables, after the restore the identity range you assigned for tables on Server A would also be used in tables on Server B and Server C. You must use DBCC CHECKIDENT to reseed the tables on Server B and Server C to ensure that a different range is used for each.
For more information about how to manage identity ranges, see the "Assigning ranges for manual identity range management" section in Replicating Identity Columns.
Adding a Node That Is Running SQL Server 2008 or a Later Version to an Existing Topology
The process for adding a node that is running SQL Server 2008 or a later version to an existing topology is the same as including the node in the initial configuration. To add a node to the existing topology, follow these steps:
Configure distribution at Server D.
Initialize the schema and data at Server D by restoring the database at Server A to Server D.
Add Server D to the topology by using the Configure Peer-to-Peer Topology Wizard.
To configure distribution for the new node
- See the procedure "To configure distribution for each node," earlier in this topic.
To initialize the new node
- On Server D, restore the latest backup of the publication database from Server A. A backup from Server B or Server C can also be used; however, that server and database must be selected on the Publication page of the Configure Peer-to-Peer Topology Wizard when Server D is added.
To add a node to the topology
Right-click the publication that you created on Server A, and then click Configure Peer-to-Peer Topology.
On the Publication page of the Configure Peer-to-Peer Topology Wizard, the publication you created on Server A will be selected.
On the Configure Topology page, you will see Server A, Server B, and Server C on the design surface.
Right-click the design surface, and then click Add a New Peer Node.
In the Connect to Server dialog box, connect to Server D.
In the Add New Peer Node dialog box, select the publication database.
If you want to create connections between all four nodes, select Connect to ALL displayed nodes. If you want to add connections manually between some of the nodes, make sure that the check box is cleared.
Click OK.
If you selected Connect to ALL displayed nodes, each node will be connected to the other three nodes. If you cleared this check box, add connections manually: Right-click a node, click Add a New Peer Connection, and then drag the connection arrow to another node. Repeat this process for each connection that you want to establish between Server D and the other nodes.
Finish the Configure Peer-to-Peer Topology Wizard as described in the procedure "To configure the topology," earlier in this topic.
Removing a Node from a Topology
To remove a node from the topology
On the Configure Topology page of the Configure Peer-to-Peer Topology Wizard, right-click a node, and then click Delete Peer Node.
Finish the Configure Peer-to-Peer Topology Wizard as described in the procedure "To configure the topology," earlier in this topic.
Removing a Connection Between Two Nodes
To remove a connection between nodes in the topology
On the Configure Topology page of the Configure Peer-to-Peer Topology Wizard, right-click a node or the design surface, and then click Remove Peer Connection.
Finish the Configure Peer-to-Peer Topology Wizard as described in the procedure "To configure the topology," earlier in this topic.
Hiding a Node in a Topology
If a topology has many nodes, it can be useful to hide one or more nodes. Be aware that you can add connections only between nodes that are visible on the design surface.
To hide a node in the topology
On the Configure Topology page of the Configure Peer-to-Peer Topology Wizard, right-click a node, and then click Select Node(s) to View.
In the Add/Remove Peers dialog box, select nodes to add to or remove from the design surface.
Click OK.
Adding a SQL Server 2005 Node to an Existing Topology
As noted in the introduction to this topic, the main difference between adding a SQL Server 2005 node and adding a node that is running a later version of SQL Server, is that SQL Server 2005 requires you to quiesce the system in order to connect the new node to all existing nodes. The following series of procedures show how to add a SQL Server 2005 node to the existing topology in stages:
Stage one involves partially connecting Server D to the topology by creating subscriptions between Server A and Server D. This allows changes to continue to occur on Server A, Server B, and Server C. Changes at Server D can begin as soon as the subscriptions have been created between Server A and Server D. Changes from Server B and Server C are replicated to Server D through Server A.
Stage two involves completely connecting Server D to the topology by creating subscriptions between Server B and Server D, and between Server C and Server D. For this stage, you must quiesce the system.
Stage two is not required, but it provides more fault tolerance than having only a single connection between Server A and Server D.
Stage One Procedures
To configure distribution for the new node
- See the procedure "To configure distribution for each node," earlier in this topic.
To initialize the new node
- On Server D, restore the latest backup of the publication database from Server A. A backup from Server B or Server C can also be used, but that server and database must be selected on the Publication page of the Configure Peer-to-Peer Topology Wizard when Server D is added.
To add the new node to the topology (creating subscriptions between Server A and Server D)
Right-click the publication that you created on Server A, and then click Configure Peer-to-Peer Topology.
On the Publication page of the Configure Peer-to-Peer Topology Wizard, the publication that you created on Server A will be selected.
On the Configure Topology page, you will see Server A, Server B, and Server C on the design surface.
Right-click the design surface, and then click Add a New Peer Node.
In the Connect to Server dialog box, connect to Server D.
In the Add New Peer Node dialog box, select the publication database.
Click OK.
Add a connection between Server A and Server D: Right-click Server A, click Add a New Peer Connection, and then drag the connection arrow to Server D.
On the Log Reader Agent Security page, specify credentials for the Log Reader Agent at Server D.
On the Distribution Agent Security page, specify credentials for the Distribution Agents at Server A and Server D.
On the New Peer Initialization page, select I restored a backup of the original publication database, and the publication database was changed after the backup was taken.
If you select this option, any changes that are not included in the backup from Server A will be delivered to the restored database at Server D. When you enabled the publication for peer-to-peer replication, the allow_initialize_from_backup publication property was set to True. Replication immediately started to track changes in the publication database at Server A so that other peers could be brought up-to-date after a restore.
Click the Browse button to navigate to the backup that is used, and replication will read the log sequence number (LSN) from the backup. All changes in the publication database at Server A that have a higher LSN will be delivered to Server D.
Complete the wizard.
For any published tables that include identity ranges, the identity range that you assigned for each table on Server A would also be used on Server D. If you will not quiesce the system and fully connect all nodes, assign the appropriate range for each table on Server D by using the following procedure. If you will fully connect all nodes, you can assign ranges after the quiesce is complete.
Execute sp_requestpeerresponse on Server D and retrieve the output parameter @request_id.
By default, the Distribution Agent is set to run continuously; therefore, tokens should be sent to all nodes automatically. If the Distribution Agent is not running in continuous mode, run the agent. For more information, see Replication Agent Executables Concepts or How to: Start and Stop a Replication Agent (SQL Server Management Studio).
Execute sp_helppeerresponses, providing the @request_id value retrieved in step b. Wait until all nodes indicate they have received the peer request.
Use DBCC CHECKIDENT to reseed each table on Server D to ensure that an appropriate range is used.
Stage Two Procedures
To quiesce the topology
Stop all activity on all published tables in the peer-to-peer topology.
Execute sp_requestpeerresponse at database on Server A, Server B, Server C, and Server D, and retrieve the output parameter @request_id.
By default, the Distribution Agent is set to run continuously; therefore, tokens should be sent to all nodes automatically. If the Distribution Agent is not running in continuous mode, run the agent. For more information, see Replication Agent Executables Concepts or How to: Start and Stop a Replication Agent (SQL Server Management Studio).
Execute sp_helppeerresponses, providing the @request_id value retrieved in step 2. Wait until all nodes indicate they have received the peer request.
Assign new identity ranges if necessary. You can now completely connect the topology by adding the remaining subscriptions.
To create additional subscriptions for the new node
Right-click the publication that you created on Server A, and then click Configure Peer-to-Peer Topology.
On the Publication page of the Configure Peer-to-Peer Topology Wizard, the publication that you created on Server A will be selected.
On the Configure Topology page, you will see Server A, Server B, and Server C, and Server D on the design surface.
Right-click Server B, click Add a New Peer Connection, and then drag the connection arrow to Server D.
Right-click Server C, click Add a New Peer Connection, and then drag the connection arrow to Server D.
On the Log Reader Agent Security page, specify credentials for the Log Reader Agent at Server B, Server C, and Server D.
On the Distribution Agent Security page, specify credentials for the Distribution Agents at Server A, Server B, Server C, and Server D.
On the New Peer Initialization page, select I created the peer database manually, or I restored a backup of the original publication database which has not been changed since the backup was taken. All nodes already have the data; specifying this option ensures that the proper subscription relationships are established between each node.
Complete the wizard and then restart activity in the system.