Introduction
Peer-to-Peer (P2P) Replication was introduced in SQL Server 2005 in order to improve availability and performance. In SQL Server 2008 the P2P Replication feature has matured further with the addition of more features. This articles describers the reasons to use P2P, how to configure P2P and some common issues.
Why use P2P Replication
In typical replication senario, there is a publisher and one or more subscribers as in figure 1.
Figure 1 : Typical Replication Cofiguration
You can see that in the above senario, if the subscriber is down, other subscribers and publishers will still work. After the presence of the failed subscriber, it will syncronise with the publisher and will be back into normal operations. However, what if there is a failure in publisher. Well, then that is the failure of the entire system. This means that above configuration will lead to single point of failure.
To address the above issues in typical replication, P2P replication is the option that you can use as shown in figure 2 below:
Figure 2: P2P Replication
In P2P replication, each Database acts as a Publisher and a Subscriber. Because of this in this replication topology we won’t call them publisher and subscriber. Instead we call them Nodes as they all are at same level. A transaction originating at one node will be replicated to all other nodes, but not replicated back to originator.
Usage or Benefits of P2P Replication
- All the database queries are spread across multiple nodes. This enables performance to remain consistent as reads increase. Because of this it is recommended to have the same configurations for all the databases so that users will not be able to identify which node they are connected to.
- If one of the nodes in the system fails, an application layer can redirect the writes for that node to another node. This maintains availability.
- If a node requires maintenance or the whole system requires an upgrade, each node can be taken offline and added back to the system without affecting the availability of the application.
Figure 3, shows the implementation of P2P Replication in three different cities London, Los Angeles and Taipei.
Figure 3: P2P Implementation Source: MSDN