Peer-to-Peer Replication in SQL Server 2008

7. Initially, you will see only one node (in P2P there are publisher and subscribers as they all at same level, hence they are called Nodes). If you right click you will see a context menu with five options.

8. Take note of the third option which is Add a New Peer Node which allows us to add another node.  In this (Figure 7), you need to select a database from the list. Unlike in other replication options, you cannot create databases straightway. Instead, you need to create a Database from SQL Server Management Studio and restore the latest database version.

Figure 7: Add a New Peer Node

In this screen, Peer Originating ID is used for conflict detection.  This has to be a unique across the P2P topology.

9. Right click and select the Redraw Graph option. This will give you a good visual about your topology as shown in Figure 8.

Figure 8: After configuring P2P configuring

Unlike in the SQL Server 2005, you can now easily visualise the P2P Replication topology. You can delete and add new topologies very easily. Also, you don’t have to stop all the nodes before adding a new node.

10. The next two screen will allow you to enter the Log Reader Agent Security and Distribution Agent security settings.

11. The next step is to synchronise databases. The best option is to restore the databases and don’t do anything to the publications. (Option 1 in Figure 9) If not, after restoring keep the backup file with you as it needs at this point to synchronise. (Option 2 in Figure 9)


Figure 9: New Peer Initialization

12. After this screen you will be taken to the final screen and you will have a conformation screen.

Figure 10: Conformation of P2p creation

In this you can see that it has created two additional publications apart from the originally created publication. Also there will be six subscriptions created among three publications.


Leave a comment

Your email address will not be published.