Conflict Detection
Conflict detection was not available in P2P replication in SQL Server 2005. This was the main reason for DBAs to not use P2P Replication. All peer nodes must be running in SQL Server 2008 to have Conflict detection enabled.
Conflict detection is managed by each row having a hidden column listing the originating peer node ID. The Distribution Agent on each node will detect conflicts by comparing the hidden column. The highest value of the originating peer node ID publisher wins in a conflict.
Warnings
When a conflict occurs in peer-to-peer replication, the peer-to-peer conflict detection alert is raised. I recommend that you configure this alert so that you are notified when a conflict occurs.
1. Right click the Publication and select Launch Replication Monitor option.
2. In the replication monitor, navigate to any P2P publisher.
3. Select the Warning tab and select the Conflict Alerts… button. You will be taken to Figure 11. This first alert is peer-to-peer conflict detection alert. Select the Configure button:
Figure 11: Configure Replication Alerts
4. In the response tab, you can define the operators and the means of the notification.
Limitations
Peer-to-Peer replication can be implemented only in Enterprise and Developer editions of SQL Server. As you are aware, you cannot use the Developer edition in a production environment. This means that to implement P2P, there is a cost element which you needs to pay attention to.
As every node acts as a publisher and subscriber, there will be large number of subscriptions. Table 1 shows the number of subscription based on nodes. This table indicates that nodes with more than ten nodes are not feasible:
Table 1: Nodes and Subscriptions
Figure 12 shows the number of subscriptions vs nodes. In this you can see there is an exponential relationship between nodes and subscriptions. A large number of nodes means that there is a large number of subscriptions which can lead to performance issues. Therefore it is recommended to have less than ten nodes in your P2P replication configuration.
Figure 12: Nodes Vs Subscription
You are not allowed to do row filtering or column filtering in P2P replication. This means that each and every database needs all the rows and all columns. Simply, you need to replicate all your data. However, you have the option of not including some database objects.
A table can be used in only one P2P topology. However it unlikely that one table will be included in two or more P2P topologies.
If you want to revert your P2P replication back to merge replication or any other replication topology, you cannot directly revert this back. Instead, you need to stop the P2P replication and re-configure the replication.
Conclusion
P2P Replication was first introduced in SQL Server 2005 but with SQL Server 2008 there are additional enhancements. Apart from the UI you now have to configure the P2P replication topology, you also have a new method to detect conflicts amongst the P2P nodes.
]]>