Bi-Direction Replication

In a bi-direction replication the changes that are made in either database are sent to the other one. Each database is a Source and a Target at the same time, so each database has a Capture Process and an Apply Process. This example keeps the schema dbo of two SQL Server databases in sync. The steps are the ones of Basic Replication Configuration.

How a change does not come back

A change that an Apply Process writes to a database is a change of that database, and the Capture Process of the same database would send it back. Repstance prevents this. The Apply Process writes its position to the table repi.apply_ckpt of the Target Database in the same transaction as the changes. The Capture Process of a SQL Server database recognises these transactions and does not capture them. The parameter skiploopback of the Capture Process says which Apply Processes it ignores: all (the default), none or a list of Apply Process ids (see SQL Server Capture Parameters).

The Capture Process looks for the table repi.apply_ckpt when it starts. So prepare each database as a Source and as a Target before its Capture Process is started. If you prepare a database as a Target later, stop its Capture Process and run it again.

What you need

• The same as in Basic Replication Configuration: Repstance is running and the logins have the privileges that are described in Database Privileges. The first database is testDB on db-src.example.com and the second is testDB on db-tgt.example.com.
• The tables of the schema dbo exist in both databases with the same columns and the same data. If one database is empty, copy the data first, see Initial Data Migration.
• Repstance does not detect or resolve conflicts. If the same row is changed in both databases at about the same time, each Apply Process applies the change of the other database over its own, and the two databases can end up with different values. Plan the application so that a row is changed in one database only.

Prepare the databases

Prepare each database as a Source and as a Target:

Create the Capture Processes

Create a Capture Process for each database. This example replicates the data changes (dmlinclude) but not the DDL statements:

Create the Apply Processes

Each Apply Process reads the Capture Process of the other database: the Apply Process 1 writes to the first database and reads cap2, the Apply Process 2 writes to the second database and reads cap1. repuser=1 makes the Apply Process connect as a replication login. For the objects that were created with the option NOT FOR REPLICATION SQL Server then does not fire the triggers, does not run the cascade actions of the foreign keys and does not count the identity values (see SQL Server Apply Parameters):

Check the replication

The command status shows the two Capture Processes and the two Apply Processes (see Status of any Capture or Apply Processes). Change a row in one database: it appears in the other database once, and it does not come back. To check that the two databases are the same, compare them: DATA COMPARISON.

The values of skiploopback