Home  |  Articles  |  Scripts  |  Consultancy  |  FAQs  |  Links  |  SSIS


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


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_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. 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
  • Insert of unpartitioned Identity PK on Node1 and Node2  -----  PK conflict error.

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


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.