SQL Server 2008: Configuring Peer-to-Peer Replication

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!