Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2005 Peer to Peer Replication


By:   |   Last Updated: 2006-08-07   |   Comments   |   Related Tips: More > Replication

Problem
SQL Server 2005 has a lot of new features and sometimes these new additions are overlooked as a new way of configuring and managing your SQL Server environment.  One of these new features is Peer to Peer transactional replication.  In the past, people have used transactional replication for load balancing, to keep a read only version for reporting purposes or possibly for a failover solution.  With Peer to Peer replication all of these tasks are made much simpler, because all nodes act as both a publisher and subscriber.

Solution
With Peer to Peer replication the idea is that any subscriber is also a publisher, so data can move both ways such as bi-directional replication.  With Peer to Peer replication there is a mechanism that knows when a transaction has been replicated, so the transaction does not continue to be replicated over and over again.  The one area that still needs to be handled is if the same data is modified on multiple servers and therefore some type of conflict resolution may need to take place. 

Below is a diagram that shows how Peer to Peer replication works.  This diagram shows two different configurations: on the left side both notes A and B are used for reading and writing and on the right side only node B is used for reading and writing where node A is used only for reading data.  The application server, which could be a web server, determines which node is used for which component. 

Peer-to-peer replication, two nodes

(source SQL Server 2005 Books Online)

This model could be further extended to have a node C, D, etc... and the application server could determine which server is utilized for reading and/or writing.  In most cases database servers are more read intensive then write intensive, so having more read only nodes would allow you to get additional I/O throughput on your database servers. If you also needed to have more nodes handling writes, you could build this into your application servers to determine which writes are done to what servers.

With Peer to Peer replication when data updates take place the data is then replicated to all other Peers, so the offloading of read activities could be directed to any one of the nodes.  From a failover solution, since all nodes act as both publishers and subscribers you can easily point your application server to any of the nodes to act as your primary database.

Next Steps

  • Take a look at Peer to Peer replication, you can find more info about it here
  • Determine whether this could work for your environment and how this could be utilized
  • As with regular transactional replication, all the same rules apply so there are no shortcuts to getting this up and running and maintaining it.  Take your time to see if this makes sense and carefully plan how this will be implemented and maintained.


Last Updated: 2006-08-07


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools