Database Sync from Backup

This example brings the Target Database to the state of the Source Database by restoring a backup of the Source, and then keeps the two databases in sync. It is the alternative to the Initial Load (see Initial Data Migration) for the first synchronization of the data: the Initial Load copies the tables with Repstance, here the backup and restore tools of the database move the data. The Source stays in use during the whole procedure: nobody has to stop working in it. The steps are the same whether you set up a replication or migrate a database. The Source and the Target are SQL Server databases. 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.

How it works

From the moment that it runs, the Capture Process writes every change of the Source Database to trail files. A full backup of the Source contains every change that was committed before the backup ended. A Target that is restored from that backup lacks only the changes that were committed after it, and they are in the trail files. So the Apply Process has to start with the first change after the backup, not with the first change in the trail files. The command validate tells the position of the backup in the Source, and the command reset moves the Apply Process there.

The picture shows the steps in time. The dotted line is the end of the backup, the backup LSN: the restored Target has all the changes up to this line, and the trail files have all the changes after it, because the Capture Process was started first.

Initial Load or backup and restore?

Both ways give the Target the data of the Source while the Source is in use, and both end with the Apply Process, which applies the changes that were made in the meantime. They differ in what they copy and in who moves the data.

Use the Initial Load when:

• the Source and the Target are different database systems, for example SQL Server and Oracle: a backup cannot be restored into another system;
• only some tables, or some rows of a table, have to be copied;
• you want to stay inside Repstance: it creates the tables, loads them and keeps them in sync, and there is no backup and no restore to organize;
• a table has to be loaded again later.

Use a backup when:

• the Source and the Target are SQL Server databases;
• the database is big, and you want the backup and the restore of the database to move the data, not a copy of the rows through Repstance;
• the Target has to be a copy of the whole database, with the indexes, views, procedures and triggers of the Source.

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. The Target Database is testDB on db-tgt.example.com, a SQL Server to which you can restore a backup of the Source. The login repuser with the password Passw0rd123 has the privileges that are described in Database Privileges; to list the backups it must also be able to read msdb.
• Free space on the Repstance server. The Capture Process writes trail files from step 1, and nothing deletes a trail file before an Apply Process has applied it (see Housekeeping). The space has to last until the Apply Process runs in step 8.

1. Start the Capture Process

Prepare the Source Database and create the Capture Process as in Basic Replication Configuration, and run it. Do not create the Apply Process yet:

Check with status (see Status of any Capture or Apply Processes) that the Capture Process is running and waiting for transactions:

2. Back up the Source Database

Make a full backup of the Source Database when the Capture Process is running. The Source stays in use during the backup. The backup has to be a full backup: validate lists the full backups, not the differential or the log backups. For example:

Any backup tool can be used, as long as it records its backups in msdb.

3. Find the LSN of the backup

Run validate for the Source Database (see Validate Database). For a SQL Server Source the answer has a line RESET LSN for each full backup of the database that is recorded in msdb and that the CDC data of the Source still reaches (see SQL Server as Source). The line has the LSN of the last change that the backup contains, the file of the backup as msdb records it and the date when the backup ended:

Find your backup by the file and the date, and note its LSN: 0x00000040000007AC0007 in the example. If there is no line for your backup, the backup is not a full backup, or the login cannot read msdb, or the backup was made before CDC was enabled for the Source (that is before step 1), or the CDC data that goes back to the backup has been cleaned already. Run validate soon after the backup (see the parameter housekeeping in SQL Server as Source).

4. Restore the Target Database

Restore the backup to the Target server. Restore it with recovery (the default), so that the database is online and can be changed. For example:

Add MOVE clauses if the files of the database go to other folders on the Target server. After the restore the Target Database holds the data of the Source as of the end of the backup.

5. Prepare the Target and create the Apply Process

The Apply Process keeps its position in the Target Database, so the Target is prepared too (see Prepare Target Database). The restored database has the schema repi of the Source already: prepare creates the objects that are missing.

Create the Apply Process, but do not run it. A new Apply Process starts with the first change in the trail files, and the changes that were made before the end of the backup are in the restored database already:

6. Reset the Apply Process

Make the Apply Process start after the backup (see Reset Process). The LSN is the one of step 3, and skip=1 says that the process starts after the transaction with this LSN, which the backup contains already. The Apply Process can start only from an LSN that is in the trail files: check with status process=capture that Last Captured DB Change is this LSN or a later one.

The command returns at once. RESET (Completed) in the status of the Apply Process means that its position is set and that it waits to be run:

If the status is RESET (Failed) with the message LSN ... has not been found. Available LSN/LSNs: ..., the LSN is not in the trail files. When the message names two LSNs, reset again with the first one and skip=1: the Apply Process then starts with the first change after the backup. When it names one LSN that is later than the LSN of the backup, the trail files do not reach back to the backup, because the Capture Process was started after it: make a new backup. When it names one LSN that is earlier, the Capture Process has not yet captured the changes up to the backup: wait for it and reset again.

7. Disable the triggers and the jobs of the Target

The restored database brings the triggers of its tables with it, and the Target server can run SQL Server Agent jobs that change data. The Apply Process changes the Target as an application does: every trigger fires for the changes that it applies, and the jobs work on top of changes that the Source has made already. Disable them before you run the Apply Process. For example:

A trigger that was created with the option NOT FOR REPLICATION does not fire when the Apply Process runs with repuser=1, and ddldisabletriggers=1 disables the triggers of a table after a replicated DDL statement on it (see SQL Server Apply Parameters).

8. Run the Apply Process

Run the Apply Process (see Run any Capture or Apply Processes):

It applies the changes that were made since the backup. The line Lag of status is the difference between the last change of the Source that the process knows and the last one that it has applied. It falls while the backlog is applied, and the databases are in sync when it is 00:00:00.000. DATA COMPARISON compares the data of the two databases.

Switch to the Target

To migrate the database, switch the application to the Target when the lag is 00:00:00.000: stop the application that writes to the Source, wait until status shows that the Apply Process has no lag, stop the Apply Process and the Capture Process (see Stop any Capture or Apply Processes), enable the triggers and the jobs of the Target and start the application with the Target. The Source was in use until the moment of the switch.

Next steps

• Initial Data Migration is the other way to the first synchronization of the data: the Initial Load copies the tables, and no backup is needed.
• Reset Process describes the command that sets the position of a process.
• Backup Repstance Files tells which Repstance files to back up.