Peer-to-Peer Transactional Replication in SQL Server 2005

How to Configure Peer-to-Peer Replication

Now that we have seen how peer-to-peer transactional replication works, let's look at the configuration options.

First, we need to create a publication. In SQL Server 2005, we'll use the New Publication Wizard.

Figure 3: New Publication Wizard.

As you can see (above), there are four publication types. To configure peer-to-peer transactional replication, we need to select Transactional publication.

After the publication is created, we need to change its properties. In the Publication Properties window (see below), set Allow peer-to-peer subscriptions to True. Please note that once we set this property to "true" we cannot revert to "false" until replication for the publication is dropped.

Figure 4: Publication Properties.

Now we'll go back, right click on the publication, and run the peer-to-peer transactional replication wizard (see below).

With this wizard, we can add more SQL Servers or SQL Server instances to the peer network. You will not be able to add main publication databases or the previously selected database on which we ran the wizard.

Figure 5: Configure Peer-To-Peer Topology Wizard: selecting peers.

After configuring the peer network, we need to specify how we have initialized the databases for the other servers in the peer-to-peer network.

Figure 6: Configure Peer-To-Peer Topology Wizard: initializing the new peers.

The obvious way to initialize the new peer databases is to restore the backups (making sure they are not changed before setting up peer-to-peer replication). Otherwise, we can specify the backup file used. The SQL Server engine will synchronize the databases by comparing the publication database to the backup.