This topic covers creating a three-node peer-to-peer transactional replication topology and then adding a node to the existing topology. The steps below provide an overview; each step is described in more detail in this topic:
The following series of procedures demonstrates how to create a peer-to-peer topology with three nodes.
In Microsoft 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 simply 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 required by replication at the first node; it is not used by peer-to-peer replication at the other nodes, but it is still required to configure a Distributor. For more information on securing 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.
Connect to Server A in SQL Server Management Studio, 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:
Filters cannot be defined on the Filter Table Rows page; 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 on the permissions 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, so the Configure Peer-to-Peer Topology Wizard uses this name when it creates a publication at each node.
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.
Peers can be initialized in one of the following ways:
Right-click the publication you created on Server A, and then click Configure Peer-to-Peer Topology. It might be necessary 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 you created on Server A will be selected.
On the Peers page, add Server B, and then repeat this process for Server C:
On the Log Reader Agent Security page, specify credentials for the Log Reader Agent at each node.
For more information about the permissions 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 on the permissions 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 is issued about the Log Reader 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 re-seed the tables on Server B and Server C to ensure that a different range is used for each.
For more information about managing identity ranges, see the "Assigning ranges for manual identity range management" section of Replicating Identity Columns.
The following series of "how to" procedures demonstrates how to add a new node to the existing topology in stages:
Refer to the procedure in the previous section.
Restore on Server D a recent 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.
Right-click the publication you created on Server A, and then click Configure Peer-to-Peer Topology.
On the Peers page, add 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 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 other peers could be brought up-to-date after a restore.
Click the Browse button to navigate to the backup 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 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 using the following procedure. If you will fully connect all nodes, you can assign ranges after the quiesce is complete:
Stop all activity on all published tables in the peer-to-peer topology.
Execute sp_requestpeerresponse (Transact-SQL) 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, so 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 Programming Replication Agent Executables or How to: Start and Stop a Replication Agent (SQL Server Management Studio).
Execute sp_helppeerresponses (Transact-SQL), 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.
On the Peers page, add Server B, and then repeat this process for Server C and 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.
