SQL Server 2005 has a lot of features and sometimes these additions are overlooked as a new way of configuring and managing your SQL Server environment.

One of these 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.
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.

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.

Thanks for reading this article,

Next steps :

1. Share this with your colleagues because Sharingis Learning
2. Comment below if you need any assistance

Powered by CodeReview – Let’s make it Better!