 |
|
SQL Server 2005 Peer to Peer Replication
|
By: Greg Robidoux | Read Comments | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More |
|
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.
(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 Update: 8/7/2006
| Share: |  |
Share | Tweet |
|
 |
 |
Connect with MSSQLTips.com |
Comments and Feedback:
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |