SQL Server peer-to-peer replication

In my last post I wrote about scaling SQL Server 2005. One of the most important technologies we have used to scale SQL Server 2005 is peer-to-peer replication.


This type of replication is a special transaction replication type. This type of replication is available in SQL Server Enterprise Edition since the 2005 version. The peer-to-peer replication is responsible for replicating transactions across the configured peers. Transactions marked for replication are transmitted and executed at other SQL Server databases. The peer-to-peer replication makes sure that database A and B stays in sync without circular references. This concept is great to start building a NLB for load balancing. At msdn you can find a good article about setting up a peer-to-peer replication topology.

Before setting up a peer-to-peer replication topology you should realize you are entering the world of SQL Server replication. This means you should be familiar with articles, publishers, subscriber, subscription and SQL Agent for example. Replication is a grown up, but also complex technology of SQL Server 2005. Get to know the basics before starting with peer-to-peer replication.

One of the misunderstandings has to do with the basics of the peer-to-peer replication: transactional replication. Only, and only transactions are replicated (in the right order). When you for example bulk insert a set of data without logging in the transaction log the data is not automatically replicated to the other peers. This means a manual action to get the other peers in sync. The order of the transactions is another concept to take notice of. When transaction A fails to be replicated, for whatever reason all other transactions (later in time) in the log will not be replicated. These transactions “wait” until the problem with transaction A is solved. When you start with peer-to-peer topology, get to know the transactional replication basics.

Once you decided to use a peer-to-peer replication topology, you must start thinking about synchronizing the peers. The replication topology has a snapshot option to get a new peer in sync with the rest. However when the database consists of a fair amount of data I advice to use the backup/ restore technology or simple attach the database. Make sure the schemas between the peers are identical!

Two important design issues are about the number of peers en conflict resolution. Keep the number of peers low. When you see the subscriptions and publication for a four node technology, you can imagine that this can become complex very soon. Microsoft recommends to the keep the number of peers below 12. I personally think 12 is a very high number. An alternative you can find in the second figure. However, this means when node B or C fails the transactions of A don’t reach node D and vice versa. The replication topology in the second figure is much easier to maintain and configure.

An other design issue has to do with conflict detection and resolving conflicts. Most of the cases a conflict situation comes up when somebody updates some data manually. In a normal situation, this data will be replicated as well. However, don’t be surprise when somebody at your organization blows up the whole peer-to-peer replication without even realizing it. The replication monitor can help you to detect problems, but there are no replication tools to help you resolving the problems. You are at your own…in some future post I might talk about resolving problems in a peer-to-peer replication topology.