Re: Homegrown synchronization

rdemyan@xxxxxxxxxxx wrote in
news:1166989123.099373.61830@xxxxxxxxxxxxxxxxxxxxxxxxxxx:

Well, I've been playing around with the SQL for synchronizing
EXISTING records in two tables. Eventually, I'm sure I can get it
to work in a TableDef loop, but I can see now that this is going
to be really, really slow (looping through over 100 tables). And
I'm just talking about writing the updated and deleted records
between my app and a temp updateDB on the SAME workstation (no
network connection). The largest table (130,000 rows) takes 40
seconds alone. Add in 99 more tables (okay they're smaller), but
this could probably take at least 10 minutes. And it doesn't
really matter whether there have been any changes to the tables or
not. OTOH, Jet direct synchronization takes only a few seconds
when both databases are on the same machine.

Are you filtering your records based on the date stamps for updates?
You could do it one of two ways:

1. record the last synch date and filter on that.

2. process only the records where the date stamp doesn't match.

Processing the actual updates should be fast, as it's running a SQL
statement for each field.

I've done this on apps with 40 or so tables, and it was quite fast,
though the number of records was not as large as yours.

Are you sure you've code it right?

A speedier although time-consuming-to-implement method is: when my
code makes an add/edit/delete to a table, at that point write the
record to the temp updateDB database. There would essentially be
no effect on speed in this case. The hard part would be modifying
all of the forms where updates occur to also include writing to an
external database.

Why duplicate data? What would you do when there are multiple
updates to a single record?

I'm coming to the conclusion that there just aren't any good
alternatives given the existing IT environment that I have to work
in.

That's basically what I said on the front end.

Did you say earlier why Windows Terminal Server was out of the
question? That would give complete IT centralized control and
wouldn't require anything but Access and a little disk space for the
front ends for each user.

Maybe what I should do is stick with replication and direct
synchronization and implement as many processes as possible to
protect the backend replica on the server (the main backend).
Alert the user when a database has lost it's replication (so they
can create another replica for local use).

You're missing a key point. It's much more likely to be the server
replica that loses replicability than the user replica, since the
user will be initiating the synch across the wire to the server, and
it's never the local replica that gets corrupted.

Creating daily backups of the server replica
will probably be required.

Well, also, doing your remote synchs with a buffer replica that is
distinct from the actual main replica of record on the server would
help, too. That's basically the smallest possible replica farm (two
replicas).

If the server replica has a problem, then
it can be deleted, removed from the replica set and replaced by a
replica from the most recent "backup" replica (note: MakeReplica
will be used at all times, no file copying).

A replica farm would be preferable.

Hypothetical: Hub backend replica in a star configuration is on
the server at SiteA (server replica). User1 is at SiteB connected
to SiteA by a WAN. User1 tries to direct synchronize his remote
replica with the server replica but an error message comes back
saying that synchronization cannot be accomplished because the
server replica is corrupted or no longer replicated (I hope there
is such a message available).

I still don't understand why you've rejected the multiple star
topology, with hubs on each of the servers on each of the LANs, with
all users synching directly with the local server's hub replica
(perfectly safe on a local LAN), and then using indirect synch to
keep the server replicas synchronized with each other.

This would be a two-level star, with a central server at the hub of
a small star of servers, synching indirectly with the central
server, and then a secondary star with each server as the center,
with all synchs from the spokes to the center being direct.

Can User1 at SiteB safely initiate the replication of a new server
replica at SiteA from an existing "backup" replica also located at
SiteA. Or is this dangerous because of the WAN connection?

It's very dangerous, because the local CPU will be doing the
processing to create the new replica. It's more dangerous than any
other direct synch operation you've contemplated.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Relevant Pages