Re: Replication: Alternatives?

"Denis Bisson via AccessMonster.com" <u14064@uwe> wrote in
5895a7c09eb37@uwe:">news:5895a7c09eb37@uwe:

> I am ?just? starting to familiarize myself with ?Replication?,
> and wish to validate some concerns.
>
> A client wishes to have a ?Main? database on a LAN that will be
> updated by 4 different users, each with a laptop, and each with a
> copy of the database (or at least the relevant fields). The
> database has yet to be designed, however, I anticipate the number
> of tables (and relationships) to be anywhere between 6-10.
>
> The hitch however, is that for security reasons, the laptops
> cannot be connected to the LAN.

If they can *never* be connected to the LAN, replication is
probably
not a proper option, unless they can be connected over the Internet
or a VPN.

> Based on a post on
> http://www.trigeminal.com/usenet/usenet009.asp?1033 which states:
> ?PURPOSE OF REPLICATION: To have two or more copies of a Jet
> database that are always considered one synchronization away from
> being identical. Multiple people can make changes in multiple
> databases but in the end everyone will reach "convergence" and
> will be the same. The support is for connected and occasionally
> connected users... but not FOR disconnected users. Microsoft Jet
> replication does not support the scenario where you do not have a
> true direct, indirect, or internet connection. Period.?

> I get the impression that replication is not really an option in
> this case.

If they can't connect via LAN and not via Internet or VPN, then,
no,
replication is not an option.

> If this is the case, what other alternatives do I have?

You have a very complex programming problem ahead of you.

How in the world are you expecting the users to share data?

It sounds to me like some point-haired boss is putting ridiculous
restrictions on the network here.

I would never accept a job like this, as it sounds like massive
incompetence is involved.

Of course, I don't know the details, so perhaps there is
justification for the severe restrictions. But I can't figure out
what those would be.

Programming synchronization of multiple databases, all of them with
the ability to update records, is a hugely complex problem.

Now, it would be made sugstantially easier if each user has their
own set of records and never edit other users records. But there
are
virtually no applications I can think of where this flows through
all the way, since those records have to be related to some kind of
parent records. However, that may or may not be a problem if the
creation of the parent records is centralized. In that kind of
scenario, the records at the top level of your relational schema
are
created at the Master database, and all the laptop databases are
slaves of that (no ability to create or edit records in those
top-level tables). And if each user has their own sets of child
records, then all edits/adds will be specific to each user, so the
central database would then be a slave to all the individual laptop
databases.

Synching in a master/slave relationship is the easiest to
implement,
since you never have any conflicts, since the records in any single
table are never created/edited except in one database.

Now, there are complexities in creating unique PKs across your
group
of databases, but the easiest way to solve that problem is to use
compound PKs, with the second column being an indicator of the
source database (i.e., the MDB file in which the record was first
created).

But if there needs to be editing of any records in more than one
location, you have the difficult problem of merging conflicts. One
way to circumvent this is to convert to a transaction-based data
storage model. A simple (though stupid) example: in regard to
inventory, instead of changing the quantity field for the Product
record, you'd have a table, each record of which would have a value
that adds or subtracts from the inventory, and you'd find the
current inventory onhand by summing that table. That is, of course,
the only way that any robust inventory system is ever designed, but
you can extend that design to other types of tables where you
wouldn't normally do it that way.

Another approach would be to do all edits via SQL and record the
SQL
statements. This would be a transaction log type of approach. But
it
has problems in that you'd have to serialize the running of the SQL
strings, and you have no guarantee that you are getting them in the
right order from all the sources, so it leads to any number of
complexities in trying to process SQL statements from multiple
sources. The replication GenerationID concept is a good one, and
that could be adapted to a homegrown replicatoon solution, but then
you have to deal with the issue that was common in Jet 3.5 of
"least-decisive user" winning in some conflict situations (i.e.,
the
user who made the most changes to a single record will have the
highest generation number, even if their data is not the most
current).

But all of this could be done with replication if there were some
single point of connection between the LAN and outside the LAN.
Perhaps some machine could serve as a gateway between the LAN and
the laptops. Or one machine could be occasionally authorized to
connect to the LAN, and while connected, it could synch with the
master database, and then be disconnected from the LAN. Then the
laptops could synch with that machine after it's disconnected to
the
LAN.

The ideal topology for replication is a star topology, but it's
also
possible to do it in a ring or in a multiple-star topology, which
is
what I've just described. The machine that can be connected either
to the LAN or to the laptops would be the hub for synchronizations
with the laptops, and the master on the LAN would be the hub for
data on the LAN (and probably not synched with any replicas on that
LAN, but potentially at the center of its own star on the LAN). So,
it would be something like this (which will make sense only in a
monospaced font):


LAN Hub<------------------>Laptop Hub
| | | | | |
/ | \ / | \
/ | \ / | \
/ | \ / | \
Repl1 Repl2 Repl3 Laptop1 Laptop2 Laptop3

The key point is that there has to be one poimt of connection
between the two hubs. If that is impossible, then this could never
work.

But it would definitely lessen the security implications of
connecting the laptops.

In a certain sense, tye laptop hub machine would be in a DMZ, but
still authorized to connect to the LAN (perhaps when it was
disconnected from the larger world).

This is the only scenario I can think of where the laptops can't be
directly connected to the LAN where replication would work.

If it's not possible, then your task is extremely hard to manually
code your own synchronization. If you're lucky, your schema and
business rulee will greatly limit the potential for edits of single
records in more than one location. If you're not, well, then you've
got a lot of work ahead of you, and, perhaps, an impossible task.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
.

Relevant Pages

  • Re: Replication ...on wireless LAN?
    ... you were ranting and raving about how 'Jet Replication is 100% flawless' ... and now you say that if a wirless connection is dropped then it CORRUPTS ... synch opens the remote database across the wire. ...
    (microsoft.public.access.replication)
  • Re: new to replication: how to goe about it
    ... Connection by LAN is one method, perhaps to be used by salesmen who ... For the disconnected office, you must provide a connection using a ... you should not consider using replication. ... You have to connect to the LAN to do this? ...
    (microsoft.public.access.replication)
  • Re: Almost always connected scenario
    ... You can synchronize as often as you like with merge replication. ... You don't want to use any direct-to-database connection scheme, ... Data is always saved in the local database, ... synchronization taking place every 5-10 minutes. ...
    (microsoft.public.dotnet.framework.compactframework)
  • RE: "internet synchroniser exited" error - any clues??
    ... is how we will be deploying the app if I can ever get it ... We are using Internet replication because ... The user just needs to make a dial-up connection ... into the corporate LAN and click "update". ...
    (microsoft.public.access.replication)
  • Re: Front End Bahaviour -server off
    ... should be looking at database replication. ... they reconnect to the LAN. ... Replication and splitting are compatible -- ... What should I expect from the FE when the server is off line? ...
    (microsoft.public.access.tablesdbdesign)