Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















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

MSSQLTips author Murali Krishnan By:   |   Read Comments (8)   |   Related Tips: More > 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


Last Update: 9/29/2011


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, October 03, 2011 - 5:18:37 AM - AnonymousSQLUser Read The Tip

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 Read The Tip

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 Read The Tip

Murali;

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

thanks

 


Thursday, January 26, 2012 - 7:59:28 PM - Murali Read The Tip

 

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

 

 


Friday, January 27, 2012 - 10:38:47 AM - Luis A Vaquerano Read The Tip

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

 


Friday, January 27, 2012 - 3:27:51 PM - Murali Read The Tip

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

 


Tuesday, November 06, 2012 - 6:53:51 AM - Anil.T Read The Tip

Yes, this content is helpful to a dba.

Thanks for providing it,

Anil.T

 


Tuesday, August 20, 2013 - 10:11:17 PM - ed galvez Read The Tip

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.

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.