Video Transcript: Setting up Bi-Directional Replication

David_Fergen · Sep 2, 2020 9:06:23 AM

Description:

Video:

Transcript:

This session will explore Bi-Directional Replication tasks with Qlik Replicate.

We will review different replication topologies,

Concentrating on the bi-directional feature, including the setting “Loopback prevention.”

How bi-directional tasks are initiated, the limitations

And you will see these demonstrated.

In general, there are different types of Replications.

Uni-directional is the classic type of replication, from A to B.

Bi-Directional is the focus for this session, from A to B and from B to A.

Multi-directional means that there are several end points. With each end point able to replicate to other end points.

Many to One, means that there are several sources, that are replicating to a single hub or target.

Broadcast replication means that there is a single source, and every change made on that source is replicated to several targets.

And the Cascading replication option means replication is from A to B and then from B to several additional targets.

Now to focus on the Bi-Directional feature.

First there are two main steps.

There must be an initial synchronization between the two end points.

This is done with a Full Load only task from A to B.

And then, if there are tables that only exist on B,

to include those tables, they must also be replicated from B to A with a Full Load task.

So the initial synchronization is complete when both end points have the same data.

Once that first step is complete, the Bi-directional tasks must be defined.

One that captures the changes from A and replicates them to B;

And one that captures the changes from B and replicates them to end point A.

This is how the task settings look. The Replication Profile must be set as Bi-Directional.

The will only be a CDC task, so full load will be disabled.

Once the bi-directional task is defined, this chart will be displayed.

In this example, it shows that it’s replicating from BD-MySQL1 to BD-MySQL 4.

The light grey line shows that it’s bi-directional.

So there should also be second task that does the opposite, replicating from the target of this task to the sources of this task.

These tasks should be set for all tables that you want to be updated on both points, meaning having the same data at both sites.

It’s important that the starting point is a synchronized one.

Next, the Loopback Prevention must be defined.

This setting will prevent changes from being replicated in an endless loop.

A schema will be defined that sets an update table with the last changes.

This is what the Loopback settings look like.

This settings tab is enabled with bi-directional tasks.

Schemas need to be set at the source and the target. This will hold a special table recording recent changes of updates.

It’s important that these settings are set on both bi-directional tasks to be opposite of each other.

So these settings on the second bi-directional task will have loopback 2 as the source and loopback 1 as the target.

Another important setting is that bi-directional tasks must be set to Transactional Apply mode. As opposed to Batch Apply mode.

So once some Full Load tasks have run to accomplish the initial synchronization of the end points – Wait until they are completed,

and the bi-directional tasks have been defined and set correctly, so we’re ready to start updating both end points with changes.

So changes from A will be replicated to B and changes from B will be replicated to A.

And the loopback prevention schemas have been defined.

It’s important to understand why bi-directional tasks must be set in transactional mode.

Changes need to be recorded with the latest time stamp, and in the correct order. Running in Transactional mode does not affect performance.

One limitation with bi-directional tasks, is that there is no support for conflict resolution.

This means that if there IS a conflict, the bi-directional replication tasks will need to be stopped. Then Full Load tasks need to be run on both sides, creating a new synchronization starting point, and then the bi-directional tasks can be re-initiated.

There are other limitations regarding the use of transformation and filters.

Full Load tasks are not the same as bi-directional tasks; and CDC is supported only in transactional mode.

Now for a demo

First, the Full Load task should be defined.

Create a new task – uni-directional. Set to Full Load.

Set the end points – from A or “BD-my SQL 1” to B – or “BD-mySQL4.”

Now, choosing the tables. BD tables 1 and 2.

And save.

Now let’s run the Full Load Only task.

And now create the two bi-directional tasks.

The first one, set to Bidirectional, and Apply Changes. Ok.

There you can see the diagram with the second light grey line.

Set the end points, A to B. from Bd-mySQL 1 to Bd-mySQL 4.

Save.

Now on task Settings, on the bidirectional tab, specify the loopback prevention schema.

And under Change Processing Tuning, change it to transactional mode.

Select the tables, bd-table 1 and bd_table 2.

Save.

Now to create the second bi-directional task.

Again, bidirectional, Apply.

Now set the end points for the second bidirectional task to be the opposite of the first.

Now this is important. There are two databases here.

Each one needs to be defined as a source AND as a target.

So this is the same database as bd-mySQL 4, but when defined as a source, it has a different name,

Which is bd-mySQL 3.

The same name cannot be used for both Source and Target.

Set the tables.

Now that the Full load task has run and the two bidirectional tasks are defined, start the bi-directional tasks.

Here you can see that the task will not run until the loopback prevention is set. And here it should be the opposite of the first.

Now to record changes.

For example, BD-MySQL 1 is on server ShaiP-rep2.

So I’ll go to that server where the data is currently.

And a change is made.

Now back to replicate.

You can see here that there was one insert picked up by the first bi-directional task and replicated. But that will not be picked up here on the second task, because of loop prevention.

So, loop prevention is working fine.

To take a closer look at the table. And you can see that the change was recorded here.

If another change is entered, you can see that the time was updated to the newest change.

If I select the correct table, you can see the changes that I inserted here.

Going back to the bi-directional tasks, two changes have been recorded.

I’ve just showed that I can replicate from both sites, as long as there are not conflicts, it should be fine.

And that loopback prevention has prevented them from being picked up by the second task.

A common problem with bi-directional tasks is loop back prevention.

Make a change on A, it goes to B;

and I don’t want to pick that change back up again and move it back to A.

The whole idea here, is that for every transaction that Replicate does, it’s going to add an update to the loopback prevention table.

So when Replicate reads a transaction, if that table is in the transaction, the transaction is thrown away. That’s needs to be clear. That’s why bi-directional tasks need to be in transaction apply mode, because you want full transactions on the target, and why batch apply mode is not supported with this type of task.