Basic Replication Configuration

This example sets up replication in one direction between two SQL Server databases. The Capture Process reads the changes of the Source Database and the Apply Process applies them to the Target Database. The commands are typed in repcli. The same configuration can be made in the Web UI (see Setup First Replication) or with the REST API.

What you need

• Repstance is running and repcli can reach it (see Fully Interactive Mode). The Source Database is testDB on db-src.example.com and the Target Database is testDB on db-tgt.example.com. The login repuser with the password Passw0rd123 has the privileges that are described in Database Privileges.
• The tables of the schema dbo exist in both databases with the same columns and the same data. If the Target is empty, copy the data first, see Initial Data Migration, or restore it from a backup of the Source, see Database Sync from Backup.

Start repcli

Connect to the Repstance server (see Getting Started with Repstance) and run repcli. It starts in Fully Interactive Mode and shows its prompt, the address of the Repstance server and repcli>:

The commands below are typed after the prompt, which is written repcli> in the examples. Press the Tab key to see the possible commands and parameters.

Prepare the Source Database

The Capture Process needs a few objects in the Source Database. The command prepare database creates them (see Prepare Source Database). database=source says that the objects of a Source are created, dbtype=mssql is the type of the database and the other parameters are the connection:

To check a database without changing it, use validate (see Validate Database).

Create the Capture Process

The Capture Process reads the changes of the Source Database and writes them to trail files. id=1 is the number of the process: it is used to run, stop, change or remove it. name=rep1 is the name that the Apply Processes use to read from this Capture Process. dmlinclude=dbo.% and ddlinclude=dbo.% select the tables whose data changes (DML) and whose structure changes (DDL) are replicated: all the tables of the schema dbo, the % symbol matches any number of characters (see Capture Objects Specification and Prepare Capture Process):

Prepare the Target Database

The Apply Process keeps its position in the Target Database, so the Target is prepared too (see Prepare Target Database):

Create the Apply Process

The Apply Process reads the trail files of the Capture Process and applies the changes to the Target Database. capturename=rep1 connects it to the Capture Process rep1 (see Prepare Apply Process):

Start the processes

Start the Capture Process first and then the Apply Process. The commands return at once and the processes start in the background (see Run any Capture or Apply Processes):

To start a process when it is created, and every time the Repstance server starts, add autostart=1 to the prepare command of the process.

Check the replication

The command status shows the processes (see Status of any Capture or Apply Processes):

RUN (In Process), Waiting for Transactions means that the process is running and has nothing to process at the moment. The Apply Process shows the last change of the Source Database that it knows, the last change that it has applied and the lag between them. The lines below Total since count what the process has done since it was started.

Change a row in a table of the Source Database and run status again: the numbers of the transactions grow. The command report shows the numbers for every table (see Report Process). DATA COMPARISON compares the data of the two databases. It matches the rows by the primary key or the unique index of a table, and it skips a table that has none, unless the key columns are named (see Requirements and Limits). The replication itself does not need a key.

Next steps

• Initial Data Migration copies the data that exists in the Source before the replication starts.
• Multi-Target Replication sends the changes of one Source to two Targets.
• Bi-Direction Replication replicates in both directions.
• Objects and Data Transformation renames objects and changes the data on the way.