SQL Server 2008: Configuring Peer-to-Peer Replication

One of the cool features added in SQL Server 2005 for scaling-out a workload was peer-to-peer replication. The major drawback was that to change an existing peer-to-peer topology, the entire topology had to be quiesced. In SQL Servr 2008, the Configure Peer-To-Peer Topology Wizard in Management Studio has undergone a major face-lift and a peer-to-peer topology can be altered ONLINE – very cool!

To get to the wizard, you still need to go through the clunkiness of enabling peer-to-peer subscriptions in the Subscription Options pane of the Publication Properties of a new publication (by right-clicking the publication under the Replication->Local Publications folder in Object Explorer):

Once that’s set to True, you can right-click the publication and you’ll see a Configure Peer-To-Peer Topology option which will bring up the new wizard. Here’s what you’ll see with only a single node configured – I’ve hovered the mouse over the node to get the tool-tip to show up:

By right-clicking anywhere on the design surface you get a menu, from which you can select to Add a New Peer Node. Of course the node you select has to already have been setup for replication otherwise you’ll get an error. As long as replication is already setup, you’ll get the Add a New Peer Node wizard:

You need to set the Peer Originator ID to a number that isn’t already being used by another node in the topology – it would be nice if the wizard would default to an ID it knows isn’t being used instead of 1.  You then decide whether to have peer-to-peer connections with all the other nodes in the topology automatically setup. If you don’t check that option, the node will appear on the topology viewer, but with no connections, like below (again I’ve brought up the tool-tip so you can see it’s a different node than the first one – in this case a different instance inside a VPC):

If you don’t check the option, you can create connections manually by simply right-clicking either node and selecting Add a New Peer Connection. You’ll see a rubber-banding arrow that you pull to the node you want to connect to. I tested the automatic method of creating the connections and it works nicely too. Here’s a three node topology in the viewer:

The rest of the wizard is as before – setting the Log Reader Agent and Distributor Agent security settings and how to initialize the new peer. At any point you can go back into the wizard and add or remove connections or peers. I’m pretty impressed!

2 thoughts on “SQL Server 2008: Configuring Peer-to-Peer Replication

  1. Interesting ! Thank you, can PEER-TO-PEER be configured on different SQL Server versions, e.g. from 2008 R2 to 2016.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.