How to: Configure Peer-to-Peer Transactional Replication (Replication Transact-SQL Programming)
This topic describes how to configure and maintain a peer-to-peer replication topology by using stored procedures. The topic first shows how to create a three node topology, and then describes how to add and connect a new node. Separate procedures are shown for adding a node that is running SQL Server 2005 and adding a node that is running SQL Server 2008 or a later version of SQL Server. This topic does not include information about the parameters that are used to detect conflicts in peer-to-peer replication. For information about conflict detection, see How to: Configure Conflict Detection for Peer-to-Peer Transactional Replication (Replication Transact-SQL Programming).
Configuring the topology is very similar to configuring a series of standard transactional publications and subscriptions. The main point to note is how the nodes are initialized and the values that are specified for the @sync_type parameter of sp_addsubscription:
The first node in a topology contains the original publication database; therefore it does not have to be initialized with schema and data. Therefore, you specify a value of replication support only for subscriptions that replicate data from other nodes to the first node. This option ensures that objects required by replication are copied to the first node.
After the first node is configured, nodes are typically initialized from a backup of the first node. Therefore, you specify a value of initialize with backup or replication support only for subscriptions that replicate data from the first node to the other nodes. A value of replication support only can be used if no activity is occurring in the system, but a value of initialize with backup is required if the system is active. Specifying initialize with backup ensures that all relevant transactions are replicated to a new node.
After a node has been initialized, subscriptions that replicate data from other nodes do not have to initialize the node again. Therefore, you specify a value of replication support only or initialize from lsn. A value of replication support only can be used if no activity is occurring in the system, but a value of initialize from lsn is required if the system is active. Specifying initialize from lsn ensures that all relevant transactions are replicated to a new node.
SQL Server 2005 does not support initialize from lsn. 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 making sure that each node has received all changes from all other nodes. This process is described in detail in the last section of this topic.
Procedure
If you are configuring a topology for databases that are active, use the following procedure to add the first and second nodes (Node A and Node B). Then, use the subsequent procedure for Node C and any additional nodes. The subsequent procedure uses a @sync_type value of initialize from lsn. This option enables you to configure a topology while the system is active without missing any transactions.
To set up peer-to-peer replication among three nodes
Configure each node as a Publisher, and associate it with 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, see How to: Configure Publishing and Distribution (Replication Transact-SQL Programming).
At Node A, execute sp_addpublication. Specify a value of true for @enabled_for_p2p, a value of active for @status and a value of true for @allow_initialize_from_backup. To add articles to the publication, execute sp_addarticle.
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 Node A. The backup must be taken after the publication at Node 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 Node A. This ensures that the backup from Node A contains metadata about all other nodes. For example, if you add Node B and Node C with activity on these nodes: initialize Node B with a backup from Node A; configure and synchronize Node B; take a new backup from Node A; initialize Node C with the new backup; and configure and synchronize Node 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.
At Node A, execute sp_addsubscription. Specify the name of the publication created at Node A as @publication, the name of Node B as @subscriber, the name of the target database at Node B as @destination_db, a value of initialize with backup for @sync_type, and appropriate values for the @backupdevicetype and @backupdevicename parameters.
At Node A, again execute sp_addsubscription. This time, specify the name of the publication as @publication, the name of Node C as @subscriber, the name of the target database at Node C as @destination_db, a value of initialize with backup for @sync_type, and appropriate values for the @backupdevicetype and @backupdevicename parameters.
At Node B, execute sp_addpublication. Specify the name of the publication for @publication, a value of true for @enabled_for_p2p, a value of active for @status and a value of true for @allow_initialize_from_backup. To add articles to the publication, execute sp_addarticle.
At Node B, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node A as @subscriber, the name of the target database at Node A as @destination_db, and a value of replication support only for @sync_type.
At Node B, again execute sp_addsubscription. This time, specify the name of the publication as @publication, the name of Node C as @subscriber, the name of the target database at Node C as @destination_db, and a value of replication support only for @sync_type.
At Node C, execute sp_addpublication. Specify the name of the publication for @publication, a value of true for @enabled_for_p2p, a value of active for @status and a value of true for @allow_initialize_from_backup. To add articles to the publication, execute sp_addarticle.
At Node C, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node A as @subscriber, the name of the target database at Node A as @destination_db, and a value of replication support only for @sync_type.
At Node C, again execute sp_addsubscription. This time, specify the name of the publication as @publication, the name of Node B as @subscriber, the name of the target database at Node B as @destination_db, and a value of replication support only for @sync_type.
If there are any identity columns in published tables, after the restore operation, the identity range that you assigned for tables on Node A would also be used in tables on Node B and Node C. You must use DBCC CHECKIDENT to reseed the tables on Node B and Node 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 of Replicating Identity Columns.
Use the following procedure to add one or more nodes to a topology that is composed of nodes that are running SQL Server 2008 or later versions of SQL Server.
To add a node running SQL Server 2008 or later version to the topology
Configure Node D as a Publisher, and associate it with a local or remote Distributor.
Restore a backup from Node A to Node D.
At Node D, execute sp_addpublication. Specify the name of the publication for @publication, a value of true for @enabled_for_p2p, a value of active for @status and a value of true for @allow_initialize_from_backup. To add articles to the publication, execute sp_addarticle.
At Node D, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node A as @subscriber, the name of the target database at Node A as @destination_db, and a value of replication support only for @sync_type.
At Node A, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node D as @subscriber, the name of the target database at Node D as @destination_db, a value of initialize with backup for @sync_type, and appropriate values for the @backupdevicetype and @backupdevicename parameters.
Node D has potentially received transactions from Node B and Node C through Node A. These transactions are considered in the next step.
At Node D, query the MSpeer_lsns table. Use the originator and originator_lsn columns to determine the log sequence number (LSN) of the most recent transaction that Node D has received from Node B.
At Node D, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node B as @subscriber, the name of the target database at Node B as @destination_db, and a value of replication support only for @sync_type.
At Node B, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node D as @subscriber, the name of the target database at Node D as @destination_db, a value of initialize from lsn for @sync_type, and the LSN retrieved for Node B for @subscriptionlsn.
At Node D, query the MSpeer_lsns table. Use the originator and originator_lsn columns to determine the LSN of the most recent transaction that Node D has received from Node C.
At Node D, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node C as @subscriber, the name of the target database at Node C as @destination_db, and a value of replication support only for @sync_type.
At Node C, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node D as @subscriber, the name of the target database at Node D as @destination_db, a value of initialize from lsn for @sync_type, and the LSN retrieved for Node C for @subscriptionlsn.
If there are any identity columns in published tables, after the restore operation, the identity range that you assigned for tables on Node A would also be used in tables on Node D. You must use DBCC CHECKIDENT to reseed the tables on Node D 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 of Replicating Identity Columns.
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 that you to quiesce the system to connect the new node to all existing nodes. The following procedure demonstrates how to add a SQL Server 2005 node to the existing topology in stages:
Stage one is covered by steps 1 through 5. This stage involves partially connecting Node D to the topology by creating subscriptions between Node A and Node D. This allows for changes to continue to occur on Node A, Node B, and Node C. Changes at Node D can begin as soon as the subscriptions have been created between Node A and Node D. Changes from Node B and Node C are replicated to Node D through Node A.
Stage two is covered by steps 6 through 9. This stage involves completely connecting Node D to the topology by creating subscriptions between Node B and Node D, and between Node C and Node D. For this stage, you must quiesce the system.
Stage two is not required, but it provides more fault tolerance than having only one connection between Node A and Node D.
To add a SQL Server 2005 node to the topology
Configure the Node D as a Publisher, and associate it with a local or remote Distributor.
Restore a backup from Node A to Node D.
At Node D, execute sp_addpublication. Specify the name of the publication for @publication, a value of true for @enabled_for_p2p, a value of active for @status and a value of true for @allow_initialize_from_backup. To add articles to the publication, execute sp_addarticle .
At Node D, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node A as @subscriber, the name of the target database at Node A as @destination_db, and a value of replication support only for @sync_type.
At Node A, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node D as @subscriber, the name of the target database at Node D as @destination_db, a value of initialize with backup for @sync_type, and appropriate values for the @backupdevicetype and @backupdevicename parameters.
Quiesce the topology by following these steps:
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 at Node D if you have to. You can now completely connect the topology by adding the remaining subscriptions.
At Node D, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node B as @subscriber, the name of the target database at Node B as @destination_db, and a value of replication support only for @sync_type.
At Node D, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node C as @subscriber, the name of the target database at Node C as @destination_db, and a value of replication support only for @sync_type.
At Node B, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node D as @subscriber, the name of the target database at Node D as @destination_db, and a value of replication support only for @sync_type.
At Node C, execute sp_addsubscription. Specify the name of the publication as @publication, the name of Node D as @subscriber, the name of the target database at Node D as @destination_db, and a value of replication support only for @sync_type.