How to: Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio)
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 initial topology includes Server A, Server B, and Server C. For the creation of the topology, it is assumed that activity is not occurring in any of the peer databases. To create the topology:
- Configure distribution at Server A, Server B, and Server C.
You can use a local or remote Distributor. If you use a remote Distributor, it is recommended 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 Configuring Distribution. - Create a publication at Server A using the New Publication Wizard.
- Enable the publication for peer-to-peer replication 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 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.
- Configure distribution at Server A, Server B, and Server C.
- After the topology is created, Server D is added. For the addition of the node to the existing topology, it is assumed that activity is occurring and that system downtime must be minimized. To add a node to an existing topology:
- 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 using the Configure Peer-to-Peer Topology Wizard. At this time, adding Server D: creates a publication at Server D; and creates subscriptions between Server A and Server D.
If there are any identity columns in published tables, you must reseed the identity ranges for the tables at Server D.
At this point, Server D can send changes to and receive changes from all servers, but all changes must flow through Server A, which is connected to Server B and Server C. If you want to add subscriptions between Server D and other nodes, go to step 4. Adding subscriptions is not required, but it provides more fault tolerance than having only a single connection between Server A and Server D. - Quiesce the topology, which involves stopping all changes and synchronizing all servers.
- Add subscriptions between Server D and other nodes using the Configure Peer-to-Peer Topology Wizard.
Creating a Peer-to-Peer Replication Topology
The following series of procedures demonstrates how to create a peer-to-peer topology with three nodes.
Note
Ensure that Microsoft 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 starting and stopping agents, see How to: Start and Stop a Replication Agent (SQL Server Management Studio).
To configure distribution for each node
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.
To create a publication at the first node
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:
- 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; 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.
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
Peers can be initialized in one of the following ways:
Manually. Copy the schema and data from Server A to Server B and Server C using Integration Services (SSIS), scripts, or another method. Ensure the schema and data at Server B and Server C are identical to those at Server A before configuring those servers as peers.
By restoring a backup of the publication database at Server A to Server B and Server C. For more information about backing up and restoring databases, see Backing Up and Restoring Databases in SQL Server.
Important
When 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 an entire database; therefore each peer database contains a complete copy of the publication database when it is initialized. The backup includes: tables not specified as articles for the publication and all data, even if row or column filters are specified on a table.
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 specified as articles.
To add nodes to the topology during initial configuration
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:
- Click Add SQL Server.
- Connect to Server B in the Connect to Server dialog box. Server B is now displayed in the Peer Server Instance column.
- Select the database on Server B from the Peer Database menu.
Note
Server A will also be displayed, but it is already configured, so do not select the checkbox for Server A.
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.
Adding a Node to an Existing Topology
The following series of "how to" procedures demonstrates how to add a new node to the existing topology in stages:
- Stage one, which is covered by the first three "how to" procedures, 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.
- Stage two, which is covered by the last two "how to" procedures, 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.
Note
Ensure 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 starting SQL Server Agent, see Starting SQL Server Agent; for information about starting replication agents, see How to: Start and Stop a Replication Agent (SQL Server Management Studio).
To configure distribution for the new node
- Refer to the procedure in the previous section.
To initialize the new node
- 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.
To add the new node to the topology (creating subscriptions between Server A and Server D)
Right-click the publication 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 Peers page, add Server D:
- Click Add SQL Server.
- Connect to Server D in the Connect to Server dialog box. Server D will now be displayed in the Peer Server Instance column.
- Select the database on Server D from the Peer Database menu.
Note
Server A will also be displayed, but it is already configured, so do not select the checkbox for Server A.
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:
- Execute sp_requestpeerresponse (Transact-SQL) on 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 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.
To quiesce the topology
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.
To create additional subscriptions for the new node
Right-click the publication 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 Peers page, add Server B, and then repeat this process for Server C and Server D:
- Click Add SQL Server.
- Connect to Server B in the Connect to Server dialog box. Server B will now be displayed in the Peer Server Instance column.
- Select the database on Server B from the Peer Database menu.
Note
Server A will also be displayed, but it is already configured, so do not select the checkbox for Server A.
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.
See Also
Concepts
Peer-to-Peer Transactional Replication
Replication Agents Overview