Some time back, I was discussing multi-master database replication requirements which have data redundancy and read performance improvements. As per our need, we have multiple locations and the databases should be synchronized continuously and users can connect to any location if needed to do any kind of operation (Insert/Update/Delete) irrespective of the location. The expected functionality can be achieved with peer-to-peer (P2P) transactional replication. In this tip we look at an overview of P2P replication.
The peer-to-peer replication was introduced in SQL Server 2005. It can help scale out an application and implement high availability. When we configure P2P between the servers (called a node), all the inserts, updates and deletes are propagated to the other nodes when the command is executed. Thus all the nodes are updated and the databases stay synchronized in near real-time.
Peer-to-Peer Transactional Replication
P2P replication is built on transactional replication. It maintains transactional replication between servers and allows publishers and subscribers to send data to each other. Thus participating servers are updated near real-time based on the scheduled frequency.
Since we know the functionality of transactional replication and the role of the publisher, distributor and subscriber, let me explain peer-to-peer in terms of transactional architecture. In the peer-to-peer transactional replication, each node acts as publisher and subscriber to one another. As specified in the below image, Server1 and Server2 are both a Publisher and Subscriber. When data are inserted/Updated/Deleted in any of the servers (node) the other one is updated through its publisher and subscriber.
Read performance can be improved significantly through distributing load among the servers since the peer-to-peer replication topology helps to have the same data set in multiple locations.
Conflict detection is available with SQL Server 2008, so most of the issues can be resolved with minimal effort.
All the participating servers in the P2P setup can be used for all types of activities since the servers have the same data. If one server is down, the other nodes continue to serve the user requests. So a single point of failure issue can be overcome.
The peer-to-peer setup can be made as a high availability solution.
There are administration enhancements with SQL server 2008 like nodes can be added or removed without disturbing the existing setup.
Schema changes can be done while database is online in SQL Server 2008.
Replication Monitor options are enhanced and Replication Agent related issues are addressed with more specific error messages in SQL Server 2008.
The peer-to-peer replication option is available only in the Enterprise edition.
In the p2p setup, the write operation needs to be maintained at only one node otherwise it may lead to data conflicts.
Performance may be an issue when there more participating servers.
Row and Column filter options are not available.
Participating databases must have identical schema and data.
Identity column usage may need manual intervention.
P2P and Ring Topology
The peer-to-peer transactional replication setup can form a ring topology. The below image (from MSDN) shows three different database locations with peer-to-peer transactional replication. The locations are replicating data respectively from Location1 <- -> Location 2, Location2 <- -> Location3 and Location3 <- -> Location1. If any one of the locations is down, the other locations can still stay synchronized, because each node acts as a publisher and a subscriber.
With this topology, a database implementation can be planned across multiple locations and the applications can share this setup for read functionality improvements. Also if a location is down, the application can redirect to one of the other locations, so application database availability can be managed.
Note: In addition, peer-to-peer transactional replication can be a substitute for an "updatable subscription for transactional replication" since updatable subscriptions are a deprecated feature in SQL Server 2008. For more info refer to this link.
There seems to be conflicting detail in this article, firstly the point is made that "users can connect to any location if needed to do any kind of operation (Insert/Update/Delete) irrespective of the location." This suggests that users can connect to any of the servers at any location and perform writes, however the article later states that a con of this setup is "the write operation needs to be maintained at only one node otherwise it may lead to data conflicts." What is meant by this as it seems to conflict with the original statement? Please can you clarify whether multiple users are able to connect at will to any server and perform write operations in peer-to-peer transactional replication, or whether only one node should be used to perform write operations for safety?
Monday, October 03, 2011 - 11:12:41 AM - Murali Krishnan
User can connect to any node and perform any operation provided there should be workaround to ensure that write operations for a particular row are performed at only one node. The more details about the data conflicts please refer http://technet.microsoft.com/en-us/library/bb934199.aspx
Thursday, January 26, 2012 - 1:37:00 PM - Luis A Vaquerano
Thanks for such quick response, what I really want to achieved here is better performance. Currently I’m running a single Database of about 45 Gb with about 390 process running at all times (from the Current Activity on enterprise manager consul). There are roughly 180 user login into the server any given moment.