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:
(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.
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.
Paul Ibison, Copyright © 2013