Mobilink together with SQLRemote

[Baron]

For a some special situation we have to use Mobilink & SQLRemote together in a multi tyer installation:

DB1+DB2 are intended to synchronise the tables (T1, T2, T3) using mobilink (DB1 is cons. and uses mlsrv17, DB2 is remote and uses dbmlsync(version10) ). DB2+DB3 are replicating the tables (T1, T4, T5) using SQLRemote (DB2 is cons. and DB3 is remote, both DB2&DB3 use DBRemote(Version10) )

DB1 is SQL Anywhere 17, DB2&DB3 are SQL Anywhere 10.

I tested the installation and there was no any problem for mobilink to synchronise two databases with different versions, and also it was no problem for the middle database (DB2) to use DBRemote & dbmlsync at the same time.

My only problem is that the tables T2,T3 are replicating from DB2 to DB3 unintendedly!

My question is how can I prevent this to happen?

The first solution for me was to build triggers for tables T2,T3 on DB3 and let the trigger ignore all changes coming from remote users, but this seem to be a big work having that my list of unintended tables is much longer as T2, T3!

Any ideas or experiences?

[VolkerBarth]

My only problem is that the tables T2,T3 are replicating from DB2 to DB3 unintendedly!

Well, so they must be part of a SQL Remote publication, see system tables SYSPUBLICATION and SYSARTICLE, and that ought to be changed...

In other words: SQL Remote does only replicate those tables that it is told do so.

[Baron]

Yes, but as long as I want that T2&T3 synchronise with DB1, then it should be present in SYSARTICLES (which will cause it to replicate unintendendly to DB3).

In other words, DBREMOTE and DBMLSYNC use the same table SYSARTICLES to know which tables to replicate/synchronise.

The problem is that I cant define publication name in DBREMOTE.exe, whereas in dbmlsync I can do (thanks the -n option).

[Breck_Carter]

In case you were wondering: many organizations use MobiLink and SQL Remote side-by-side without problem... your three-tier mixture is fairly common, with good reason (the top level DB can't use SQL Remote because it's not SQL Anywhere, for example).

Also... you should not have to study (or even understand) the SYS tables to get it to work... to debug a horrendous runtime problem, perhaps, but not during initial development.

[VolkerBarth · Accepted solution]

Yes, generally the MobiLink client and SQL Remote do both use publications and articles. However, in your case they need to use separate publications for both (so T1 will belong to 2 publications, whereas T2 and T3 only to one and T4 and T5 to a different one), and you will need to specify which publication(s) should be used by MobiLink resp. SQL Remote.

For SQL Remote, you decide which publications are used by specifying the according subscriptions. If you do not create a subscription for a certain publication, SQL Remote will ignore that.

[Baron · Accepted solution]

OK, thank you, this is what I was looking for!!

[regdomaratzki · Accepted solution]

Volker is of course, correct. In your middle tier (which is an ML Client and a SQL Remote consolidated), you will have two publications :

create publication pub_ml ( table t1, table t2, table t3 );
create publication pub_sr ( table t1, table t4, table t5 );

You'll create a synchronization subscription on pub_ml :

create synchronization user ml1;
create synchronization subscription to pub_ml for ml1;

You'll create SQL Remote subscriptions on pub_sr :

grant remote to rem1 type file address 'rem1';
create subscription to pub_sr for rem1;
start subscription to pub_sr for rem1;

I didn't check syntax on any of the above commands, so let's call it psuedo-code in case I've made a mistake.

[Baron · Accepted solution]

Thanks for the explanation. I was just taking the wrong direction of thinking.

But again, having the ability to specify publication name in dbremote.exe (something like -n) would add very great option to SQLRemote. For example, if we need to specify different channels of replication (fast lane, slow lane...), then this is only possible with DBMLSYNC.exe but not with DBREMOTE.exe

[VolkerBarth · Accepted solution]

having the ability to specify publication name in dbremote.exe (something like -n) would add very great option to SQLRemote

Hm, personally I have never had that need (neither with ML), and I prefer the simple "replicate everything and go"-approach which is inherent to that tool IMHO.

But to understand your requirements w.r.t to "different channels": What exactly would you try to achieve with that? Would you like to be able to replicate data for some tables "as fast as possible" and for other tables "on demand" for the same remote(s), or would you try to replicate with some remotes with a higher priority than with other remotes?

[Baron · Accepted solution]

What I need is to have priority among tables. So your first assumption!

Some tables are changing very fast on remotes, and we need to replicate them as fast as possible (fast lane), another tables are not changing so often (slow lane).

This separation is in ML possible, but in SQLRemote NOT!

[Baron · Accepted solution]

In our situation the transactions of fast lane are far less than the transactions of slow lane.

The transactions of slow lane are actually too big, and this will prevent us to "replicate everything" as fast as possible.

[Breck_Carter · Accepted solution]

> let's call it psuedo-code

ROFL! ...that takes me back to mainframe days