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 ie 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 is actually trying to do. So, the first part we do uses the GUI and results in the usual transactional publication. Once completed, we must enable the publication for peer-to-peer so on the Publication properties, Subscription Options section we select: "Allow Initialization From Backup files" and "Allow peer-to-peer subscriptions".

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 server - it requires identical publication names on each publisher and duplicates are not allowed on a single SQL Server instance. Setting up peer-to-peer on a single server doesn't really make any logical sense anyway, but you might want to do it for testing purposes. So, if you only have 1 box and want to set things up, you'll just have to install 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_subscription script 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. This 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 on PKs and for other tables we might need a location identifier in the PK to partition the data.

(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 Wizard

Once we have selected "Allow Initialization From Backup files" and "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. However there is 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.

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. This 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.

May 2006