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

By: Murali Krishnan | Read Comments (6) | Print Murali is a Lead Consultant with vast experience in Database/BI Design, Development and Administration. Related Tips: More

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.

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.

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

• Overview of Peer-to-Peer Transactional Replication – SQL Server 2008 from MSDN.
• Step by Step: Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio)
• Administer a Peer-to-Peer Topology: Replication Transact-SQL Programming from MSDN.
• SQL Server 2005 Peer to Peer Replication

Related Tips: More | Become a paid author

Last Update: 9/29/2011

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

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.

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

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Join the over million SQL Server Professionals who get their issues resolved daily. Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST