How close can MobiLink get to "peer-to-peer" or "non-hierarchical" synchronization?

[Breck_Carter]

The version 12 help says the following (bolding is mine) in Hierarchical database configurations:

For databases in a hierarchical configuration, every database has a single parent database, except the consolidated database, which has no parent.

SQL Remote supports hierarchical configurations of databases; it does not support peer-to-peer synchronization or other non-hierarchical configurations. MobiLink is also normally used with a hierarchical configuration, but can also be used in other configurations.

How close can one get to arbitrary peer-to-peer synchronization with MobiLink? ...keeping in mind that only one instance of dbmlsync.exe can be running at one time: "SQL statement failed: (-782) Cannot register 'sybase.asa.dbmlsync' since another exclusive instance...

[Breck_Carter]

Yes, I'm the one who tagged it "even-a-cow-knows" because this is a question about fundamentals... I think "even-a-cow-knows" is a wonderful tag, so I will add it to my tag repertoire ( along with "debugging" and "performance", the only other two that I apparently use 🙂

However, "even-a-cow-knows" should never be interpreted (or used!) as an insult. This is not StackOverflow.

[Former Member]

Thanks for the explanation. I'm glad I don't have to remove this tag. Nobody wants to be the moo-tag repairman. };-)

[Former Member · Accepted solution]

We've considered extending MobiLink for peer-to-peer (and variants) synchronization over the years, but haven't been sufficiently motivated. It is also a tricky problem with many different approaches and possible contraints. It comes up once in a while in both internal and customer discussions. Technical proposals have been made but I am not aware of any working implementations.

Peer-to-peer is designed to avoid centralization, ie. the one true consolidated database. Fundamentally, though, MobiLink requires a consolidated database to store remote progress offsets on a per subscription basis. Once you start decentralizing, then all your nodes need to track state, but since they are not always in communication, they all have different states, and it can get complicated, or at least very inefficient.

Consider, for example, a system with every node having a consolidated database, and every other node having a subscription to that consolidated database. In this system, if node B downloads all of node A's changes, then node C synchronizes with node B, then node C will get all of node A's changes, but if node A synchronizes with node C, what is to keep it from getting all the original changes back again? That may not be a problem, per se, but it would definitely be inefficient. Preventing it means assigning and tracking A's creation/ownership of the rows. There are cases when even this approach can cause redundant transfer of rows.

To make a long story short, I do think peer-to-peer with MobiLink is possible. The simplest implementations would likely cause (a) lots of redundant row transfers, and (b) transaction logs to accumulate without truncation. That may be good enough for some. The complexity increases as you work harder to diminish or eliminate redundant row transfers -- assuming the latter is even possible.

[jeff_albion · Accepted solution]

I will expand Russ' answer slightly: I have worked with customers who have attempted to set this type of environment up. It's messy, and yes, they keep getting the 'multiple instance' problem - although I think a couple of the solutions have made it to production, in the "simple" case.

(Note that these solutions were attempted with SQL Anywhere, not UltraLite - UltraLite is NOT recommended to be in multi-consolidated environment, due to its row-state tracking mechanism.)

As Russ already highlighted, 'redundant rows' are a major problem. My customer's business process was something like upload to server 'A', approve item, send approved item back to the client, upload to server B for further processing. This required a lot of row-state tracking to ensure everything flowed smoothly. (Remember that downloaded rows from one server in a synchronization session are not automatically flagged for re-upload by dbmlsync, so...!).

The key is to ensure that the two dbmlsync sessions never see each other concurrently - that may be 'tricky' to do, but not impossible. Scheduling, triggers, and other logic should be able to help figure out whether a dbmlsync task is already running against the database.