Peer to Peer Transactional Replication On SQL Server

It uses the technology of Transactional Replication. As a difference, every subscriber is also publisher. You can read my article “Transactional Replication On SQL Server“.

Let’s try to understand the logic by installing step by step.

We will do a very similar installation with the Transactional Replication installation. One of the most important differences in installation is that it should be a distributer in every subscriber.

In the article “Transactional Replication On SQL Server“, we have configured the Distributor on the first server. We don’t need to do the same thing again. You can do this by following the steps in the article “Transactional Replication On SQL Server“.

On the second server, let’s configure the distributor as follows. If you did not configure the distributor on the first server, you can do this on both servers, as described below.

First, in the AdventureWorks2014 database, let’s create a table that we will replicate with the help of the following script.

Click on Configure Distribution as follows.

On the next screen, select Do not show this page again and  click Next.

On the next screen, we create the Distributor by selecting the following option. This way the distribution database will be created. We’re moving forward.

On the next screen It wants a share to hold snapshot files. I explained how to create a shared folder in the article named “How To Define a Share and Map This Share To Windows“.

On the shared folder, you must authorize the SQL Server service account and the SQL Server Agent service account that is used by two instances, and the user you are installing.

I created a folder named Replication on Sunucu1 and gave the necessary privileges and I wrote the share address as Snapshot folder. We’re moving forward by clicking Next.

On the next screen, we write the path information for the data and log files of the distribution database as follows and we click next.

On the next screen we specify Publisher that this distribution database will use. We will install Publisher and distributor on the same instance, so we select the same instance and click Next.

On the next screen, click Next and Finish to complete the distributor configuration when Configure Distribution is selected.

After the installation is complete, you should see the distribution database in the system databases as follows.

After our Distributor configuration is complete, we click New Publication on the same server (because we’ve created Publisher and Distributor on the same server) by right-clicking Local Publications.

We select the database that we will replicate as follows.

Select Peer-to-Peer Publication as follows and click Next.

Since we are going to transfer only the PeerTpPeer table, we select the following and click next.

The next screen aks the user accounts that the Log Reader Agent will use. Click Security Settings to set related users.

In my own test environment, two sql server instances and two sql server agent instances use the same windows sql server service account and have the necessary privileges. I have previously authorized this user on the snapshot share. So I’m going to set it as follows.

The minimum rights of the user you set for the Log Reader Agent:

• Must be db_owner in the Distribution database.

• Must be db_owner in publication database.

Microsoft recommends that you use a Windows Account for this account.

Then we are proceeding by Clicking Next while “Create the publication” is selected.

On the next screen, we give Publication the name of PeerToPeerPublication, and we click finish to complete the publication installation.

Unlike Transactional Replication, we did not configure Snapshot Agent and did not filter.

Filtering is not supported in Peer To Peer Transactional Replication.

We transfer the database with backup and restore method to the seconds server.

You must delete the tables that you would not replicate after the Restore from the 2nd server.

We installed Publication. Then we performed the restore operation.

Sometimes you may want to delete the database in subscriber and reconfigure replication. In such a case, the error occurs as follows.

Cannot drop the database X because it is being used for replication.

If you get a new backup from the first server and restore it by overwrite, the problem will be solved.

Right-click on the publication as follows and select “Configure Peer-To-Peer Topology”.

We select the first instance as follows.

Click Add Node as below.

We make the following selections on the below screen. We chose Push Subcriptions. You can find details about Pull and Push Subscriptions in our article “SQL Server Replication Types“. We’re moving forward.

The next screen asks the Log Reader Agent’s user accounts in the second instance.

The second instance is normally subscriber, but in Peer-To-Peer Replication every subscriber is also a publisher, so we’re filling this information.

The minimum rights of the user account we enter here should be as follows. In the following screen, we click on “……”

• Must be db_owner in the Distribution database.

• Must be db_owner in publication database.

We use the same windows account on both instances and we have given the necessary privileges to this account before. So we are making a choice as follows.

After clicking OK, a screen will appear as below. To use the same security setting for all replicated nodes, we select “Use the first peer’s security settings for all others peers”

On the next screen, we need to make the necessary security settings for the subscriptions on both instances. By clicking on “….” on the right side as described above, we select “Run under the SQL Server agent service account ..”.

After making the necessary selections, a screen should appear as below.

On the next screen, I select the part that starts with “I restore a backup of the original publication database”, and I find the database’s backup by clicking Browse.

Installation has completed.

When you add a record to the table on the first server, you will be able to see it on the second server.

When you update this record on the second server, you will notice that the changes are reflected in the table on the first server.

If the first server and the second server try to update the same row at the same time, there will be a conflict.

You can see the conflicts as follows.

You can use two-phase commit to avoid conflict, but the performance will not be very good.

There are several ways to solve Conflicts. The distribution agent will stop when Conflict occurs.

You can restore the backup of your database to the node conflicted node.

Or,

You can re-enable Distrubition Agent again. In this way Distribution Agent may be attempted to implement changes to the conflicted node.

Merge replication is more successful in detecting and correcting Conflicts. You may want to read my article “Merge Replication On SQL Server”.