Overview of Peer-to-Peer Transactional Replication in SQL Server 2008

By:   |   Updated: 2011-09-29   |   Comments (10)   |   Related: > Replication


Problem

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.

Solution
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.

peer to peer replcation

Pros:

  • 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.

Cons:

  • 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.

sql server p2p replication

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Murali Krishnan Murali Krishnan is a Lead Consultant with vast experience in Database/BI Design, Development and Administration.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-09-29

Comments For This Article




Sunday, January 18, 2015 - 9:18:26 AM - Murali Back To Top (35979)

Federico,

You can refer : http://msdn.microsoft.com/en-us/library/ms143432(v=sql.110).aspx#Replication

I believe the limit is with transactional articles.

Tuesday, January 13, 2015 - 8:50:34 AM - federico Back To Top (35917)

For peer to peer replication

Which are the limits articles count, column count by table, snapshot articles count ?

 

Tuesday, August 20, 2013 - 10:11:17 PM - ed galvez Back To Top (26404)

Question:

What if a node loses connection temporarily? When it reconnects it gets synchronised again?

What if while disconnected from the other nodes, a user performed some changes to the local data?

Would those changes propagate to the other nodes when it reconnects?

 

Thank you.

 

Tuesday, November 6, 2012 - 6:53:51 AM - Anil.T Back To Top (20224)

Yes, this content is helpful to a dba.

Thanks for providing it,

Anil.T

 

Friday, January 27, 2012 - 3:27:51 PM - Murali Back To Top (15812)

Fine and the suggestion would be upgrade if your requirements need P2P.

 

Friday, January 27, 2012 - 10:38:47 AM - Luis A Vaquerano Back To Top (15806)

Murali; 

 

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.

Do you think this is the way to go?

Sincerely

 

Thursday, January 26, 2012 - 7:59:28 PM - Murali Back To Top (15793)

 

Peer- to - Peer replication was introduced in SQL Server 2005.

But you can achieve the same with bidirectional transactional replication  for the requirements. For more info, 

http://support.microsoft.com/default.aspx?scid=kb;en-us;820675

 

 

Thursday, January 26, 2012 - 1:37:00 PM - Luis A Vaquerano Back To Top (15790)

Murali;

Can a P2P be implemented on a 2000 Enterprise SQL environment?

thanks

 

Monday, October 3, 2011 - 11:12:41 AM - Murali Krishnan Back To Top (14764)

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                                             

Monday, October 3, 2011 - 5:18:37 AM - AnonymousSQLUser Back To Top (14762)

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?














get free sql tips
agree to terms