Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SQL Server Peer to Peer Replication

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.

New Peer to Peer Publication

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

Peer to Peer Publication Wizard

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

Select Publisher Database

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

Publication Type - Peer to Peer

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

Peer to Peer Articles

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.

SQL Log Reader Agent Security

Click Next to continue.

Publication Agent Security

Check the Create the Publication Box and Click Next.

Publication Final Wizard Actions

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

Complete the Publication Wizard

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

Complete Creating Publication

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.

Configure Peer to Peer Topology

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

Peer to Peer Topology Wizard

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

Topology Wizard Select Publisher

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

Add New Peer Node to Topology

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

New Peer Mode using Pull Subscription

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

View Peers in Topology for Replication

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

Peer Log Reader Agent Security Settings

Click Next to Continue.

Log Reader Agent Security Confirmation Page

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.

Distribution Agent Security Settings

Click Next to continue.

Distribution Agent Security Window

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

New Peer Initialization Window

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

Complete the Peer to Peer Topology Wizard

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

Building the Peer to Peer Topology

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

View of the Publications and Subscriptions for Peer to Peer Replication

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

View of database table in both Publisher and Subscriber

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.

Update to a Peer in the Topology