Peer-to-Peer Replication in SQL Server 2008
Peer-to-Peer Replication in SQL Server 2008
By : Kasim Wirama, MCDBA
One of interesting enhancements made in SQL Server 2008 February CTP is in area of replication, peer to peer replication particularly. Now with SQL Server 2008 February CTP, a SQL Server 2008 new node could join to existing peer-to-peer existing topology without having to stop database activity on the topology, and also visual designer is introduced in this upcoming SQL Server version. With visual designer, I just only to visually design, implement and review peer-to-peer easily especially when I have more than 3 nodes to be implemented, which are more complex peer-to-peer topology.
Peer-to-peer replication is located under transactional replication. By default, transactional replication turns off peer-to-peer replication. After you have set up publication with transactional replication, you need to open the publication property, and set value to true for enable peer-to-peer replication. Once you set it to true, you couldn’t revert back to transactional replication (by setting the value from true to false), the only way is to re-create the publication. Not like with other type of replication, peer-to-peer replication doesn’t need snapshot publication, uniqueidentifier column and trigger created on underlying table of an article. It doesn’t support re-initialization and timestamp type column because timestamp column is generated automatically by SQL Server so it couldn’t replicate to its peer. Another restriction of peer-to-peer replication is that it doesn’t support horizontal and vertical filtering. Besides that, peer-to-peer replication is not designed to handle conflict resolution even though it is able to able to detect conflict by turning on conflict detection. When conflict happens, data would not replicate to its peer node, thus manual intervention is needed to manually resolve conflict resolution. If you would like to automate conflict resolution with possible sophisticated scenario, merge transactional replication is the best replication option rather than peer-to-peer replication.
The least performance impact is belonged to peer-to-peer replication, and for high availability, better fault tolerance, and read performance enhancement in OLTP scenario, it is one of the viable options besides log shipping, and database mirroring.