Peer-to-Peer Replication

Peer-to-Peer is a type of transactional replication. Unlike the traditional replication types, it provides a headless topology, where there is no single publisher and one or more subscribers. In peer-to-peer replication, every participant is both a publisher and a subscriber. It is suited for cases where user applications need to read or modify data at any of the databases that participate in the setup. It provides an interesting alternative for load-balancing and high-availability scenarios. Note that this feature is only available in the Enterprise Edition of SQL Server 2005. Oracle calls this kind of replication "multimaster," whereas DB2 calls it "update anywhere."

There are a few things to keep in mind when evaluating and setting up peer-to-peer replication:

• It is designed for a small number of participating databases. A good rule-of-thumb number is less than 10. If you use more than that, you could encounter performance issues.

• Unlike merge replication, peer-to-peer replication does not handle conflict resolution.

• Peer-to-peer also does not support data filtering. That would defeat the purpose because everybody is an equal partner here for high availability and load balancing.

• Applications can scale out read operation across multiple databases. Databases are always online. Participating nodes can be added or removed for maintenance.

• As mentioned, peer-to-peer replication is only available in Enterprise Edition. However, for your testing purposes, it is available in Developer Edition of SQL Server 2005.

From an implementation standpoint, the process is very similar to the replication types discussed earlier. However, there are a few things that you need to pay attention to:

1. Before you start, the easiest approach is to start with one database, back it up, and restore it on all other participating databases. This way, you will start from a clean and consistent slate.

2. All replications need a distributor, so it needs to be ready before you start setting up peer-to-peer replication.

3. After the distributor is set, you need to create a publication just like a regular transactional replication publication. However, do not create the initial snapshot or schedule the Snapshot Agent at the Snapshot Agent screen. In addition, you need to make sure that the publication name is the same across all participating servers. Otherwise, peer-to-peer setup will fail.

4. After the publication is created, you need to modify a property before you have access to peer-to-peer replication setup. Expand your Object Explorer within Management Studio so the newly created publication is highlighted. Right-click it and pick Properties, as shown in Figure 16-10.

   Please note that there is no option for you to configure peer-to-peer in this context menu, as you can see from the preceding screenshot. That will change once you change the Subscription Options, which you will do next. After the property window opens, pick the Subscription Options page, shown in Figure 16-11.

   Set the "Allow peer-to-peer subscriptions" to True before setup can continue.

5. After that is done, you can proceed with the rest of the setup by right-clicking the publication and selecting "Configure Peer-to-Peer Topology…" as you can see in Figure 16-12. Once again, you can only see this after the Subscription Options is modified. This feature is only available in SQL Server 2005 Enterprise Edition.

6. Follow the rest of the steps. As mentioned earlier, most of the rest are pretty similar to typical replication setup. One difference is that this process will ask you to add Peers, instead of adding subscribers as you did with the other types of replication.

Figure 16-10

Figure 16-11

Figure 16-12