Setting up Peer-to-Peer Transactional Replication in SQL Server 2005

Introduction

Despite the diagrams and some of the explanations
that exist on the web, this is not an 'everyone can change everything on every
box' topology, or a disaster-recovery topology which works straight out of the
box! It is really a non-hierarchical setup, which we have never had before in SQL
Server i.e. each node is equivalent, each node is simultaneously both a publisher
and subscriber, and no single node is in charge. When complete it can indeed be
used to scale out work between servers, and for disaster recovery, but there are
several caveats to be aware of when creating this setup which we'll look at
below. This article isn't an A-Z of peer-to-peer systems and we concentrate
mainly on the easiest way to set things up - manually and via a wizard - and
therefore get it going successfully.

The Manual Setup

The creation of the publication is identical to a
normal transactional publication, and can be done through the GUI. Notice that
there is no mention of peer-to-peer in the replication types:

The peer-to-peer option was removed post Release
1 of Yukon as a replication type but appears later on as a separate right-click
menu option. So, we'll look at setting it up largely
manually, which leads to a better understanding of what the wizard was actually
trying to do. The
first part involves creating the transactional publication as per usual.
Once completed, we must enable the publication for peer-to-peer so on the
Publication properties, Subscription Options section we select: "Allow peer-to-peer
subscriptions". There is a warning that explains that other publication
properties will be changed to allow for peer-to-peer to be functional. The main
property which is changed behind the scenes is "Allow
Initialization From Backup files" which you'll notice if you open up the
same properties dialog box afterwards.

Next we backup the database on the Node1 and
restore it on Node2. Note that you can't create a Peer-To-Peer topology on the
same instance - it requires identical publication names and database names on each
node and
duplicates of these object names are not permitted on a single SQL Server instance. Setting up
peer-to-peer on a single instance wouldn't really make any logical sense anyway,
from the point of view of disaster recovery or scale-out. For testing purposes if you only have 1 box and
want to set things up, you'll just have to use 2 instances.

To create the subscription on Node 1 to Node2, use the subscription wizard as
per usual, but DON'T execute it - just create the script. All you need to
do is edit the sp_subscriptionscript arguments to
ensure that:@sync_type = 'replication support only'.Note
that you must ensure that no data changes have been made at the first publication database since the backup was taken.
Then run the
script with this new parameter, which prevents the need for a full
initialization.

Now that we have Node1 to Node2 replication set up, we create the inverse
publication and subscription: a publication on Node2 going to Node1. Before
starting, ensure that Node2 already has a distributor set up. The publication
creation could
be done by scripting out the original setup and editing the text file or just
going through the original process. For publications involving a lot of
articles, I'd do it just using scripts. If you're using Developer Edition of SQL
Server for one of the nodes, you'll need to
enable remote
connections at this stage.

Now we have the complete setup. Well, not quite. We have to consider 3 things:

(1) Identities. If we use identity columns, one node will need its values
reseeding on each identity table, or the increments could be used to ensure that
there is no overlap. Otherwise there'll be primary key conflicts when
synchronizing if rows are added on each Node.

(2) Conflicts. There is no conflict resolution offered, or anticipated.
This is a big distinction compared to merge replication. So, how do we avoid
conflicts? Basically, it is assumed that the data is partitioned. The identity
ranges mentioned above would take care of this if used as surrogate PKs and for other
tables we might need a location identifier in the PK to partition the data. What
sort of data issues could occur if the data is not partitioned?

Update at Node1 and Update at Node 2 ----- lost
update

Update at Node1 and delete at Node2 ----- could be
lost update or an error as it depends on the order of
synchronization

(3) Redundant Data. On the subscriber the
full database backup was restored. This backup contains all the required
articles AND all other objects - tables, procedures, functions etc. At this
point it is worthwhile cleaning out any unnecessary and redundant objects. They
only increase the backup time and size, and are potentially confusing to the DBA
who supports the system.

Using the Peer-to-Peer Subscription Wizard

Once we have selected to "Allow peer-to-peer
subscriptions" on the original publication, a new option of
'Configure Peer-To-Peer topology...' will arise in the menus:

For the most part the wizard is a
simple step through that can be understood in terms of the content above.
This wizard is quite clever and not only creates the subscription at Node2, but
it also creates the publication at Node2 and the corresponding (inverse)
subscription at Node1. There is also an interesting alternative option presented when it comes to
defining the backup on the subscriber:

You can select the first option if you
have an identical database on the subscriber. This may be because you created the schema for published objects
manually or perhaps you restored a backup, and no data changes have been made at the first publication database since the backup was taken.
In scripting terms, this option corresponds to a value of 'replication support only' for the subscription property
@sync_type and is the same setting as we used manually.

The second option is used when you restored a backup and data changes have been made at the first publication database since the backup was taken. Replication must now deliver changes from the first publication database that were not included in the backup. This option corresponds to a value of
'initialize with backup' for the subscription property @sync_type.

You might wonder why there is a
browse button on this wizard. After all, the backup file has already been
restored so why would it be required once again? This browsing is enabled for the second option only and is
used so the backup can be located and the log sequence number (LSN) read from
it. This LSN is compared to the records in the MSRepl_Commands table so that any
subsequent records can be delivered.

Conclusions

Setting up peer-to-peer transactional replication
is relatively straightforward. We can use a combination of SSMS and scripts to
create the setup or the wizard provided, and afterwards need to perform a few minor changes to have a
working system.