Peer to Peer Replication For 2 or more SQL Databases

This article reviews the steps to implement a Peer to Peer replication between 2 servers to ensure that any Inserts, Updates, and Deletes in tables as well as any Data Definitions to tables, etc., are synchronized between all Peers. Check to make sure the Distributor is set up on all Peer machines and that you make a Back up of the database to replicate to the Peers. You may either restore the Backup prior to setting up the Peers or when Configuring the Peer Topology later in the process after setting up the Publication.

Publication of Peer to Peer database

Right click on the Publication Database server in the Replication folder and select New Publication.

The New Publication Wizard Window will appear. Click Next to continue.

Select the Publication Database to replicate via Peer to Peer Replication. Click Next.

Select the Publication Type of Peer to Peer Publication, click next.

Select the tables to replicate to the other Peers and click Next.

Select the Log Reader Agent Security for the Publisher. This may be the SQL Server Agent service account or a Windows Domain account if the service account does not have the correct permissions.

Click Next to continue.

Check the Create the Publication Box and Click Next.

Enter a Publication Name in the Text Box and click the Finish Button.

SQL Server will create the new Northwind DB publication and click Close.

Create Peer to Peer Toplogy for Subscriber Database(s)

Right click on the newly created Peer to Peer Publication and click on Configure Peer-To-Peer Topology.

The window for the Configure Peer-to-Peer Topology Wizard will appear, click Next to Continue.

The Publication will appear in the Window. Click Next to continue.

Right click in the Configure Topology screen and select Add a New Peer Node.

Select the database to configure as a Peer. This article is using a Pull Subscription - select the Use Pull subscription option and click OK.

The Peer Nodes and Connections will appear in the window. Click Next to Continue.

Log Reader Agent security for the Peers: use a Windows Domain account or the SQL Server Agent service account.

Click Next to Continue.

Distribution Agent security for the Peers: use a Windows Domain account or the SQL Server Agent service account. This article uses the SQL Server Agent service account. Click Next.

Click Next to continue.

In this instance, the Peer database was created from a backup of the original publication database on the peer Server.

Click Finish to complete the setup of the Peer to Peer Topology.

SQL Server will create the Publication and subscriptions for the Peer to Peer Topology. Click Close to Finish.

In SQL Server Management Studio, the Peer Publication and Peer subscription for each database will appear.

A quick query on the Employees table in both databases show that they are presently matching.

An Update statement to the Northwnd_P2P database employees table changes the name of employeeID 9 from Anne to Anni.  A select query shows that the change was transmitted immediately to the peer database to reflect the updated name.