Re: Homegrown synchronization

The code for importing of an update file is working and, as you noted
earlier, it is very fast (as expected). There are still some
housekeeping issues I need to consider and I've started on them, but
I'll save the details for another post.

**Let me add a bit more about how I'm currently handling the deletion
of records issue.**

1) When the app opens on the remote PC it makes a "snapshot" copy of
the local backend file that the user is connected to.

2) When the app is closed, the code that EXPORTS changes to the temp
updateDB (the DB where updated records are stored and which will be
copied to the server) looks for records in the local backend file that
the user is connected to that are not contained in the "snapshot"
copy. If it finds any records these are written to the appropriate
table in the temp updateDB. There is a flag in each table in the temp
updateDB that indicates to code that IMPORTS data from the temp
updateDB whether that data is to be added/edited (flag = false) or
deleted (flag = true). For deletions this flag is set to true.

3) There are several issues with this method:

a) A copy of the backend file has to be created. It is true that the
copy is made from a local PC file to another local PC file, so the
copying process is fairly fast.

b) In order to determine if there are record that were deleted, the
code has to check each table. I don't see how a filter can be applied
in the current setup. That means that the process for over 100 tables
is relatively long.


*************How do I apply updates to the server
backend*****************************

This is the main last piece that I need to deal with. If I understand
Jet indirect synchronization correctly, the synchronizer on the server
is running in the background looking for updates in the server dropbox.
When it finds one, it will process it.

Well in my homegrown synchronization scheme, I'll use the same method,
namely send the update file created by a remote user upon closing my
app to the server ImportDropBox.

But the real question is how do those updates in the ImportDropBox for
the server get applied. There is no program running in the background
on the server like the Jet Synchronizer. Window's scheduler is not
available to us to launch an Access program that could maybe handle
this (although I'm not completely sure how). In one form or another,
this has to somehow be user initiated.

My current plan is:

1) Only users that are connected to the server via a LAN can initiate
the updating of the server backend (no WAN users). If I keep the code
for this in my app, this is easy to do since I have a user setup screen
which administrators use to set permissions to view data. This would
just be one more permission.

2) Should I use a hidden form in my app with a timer that monitors if
new update files have appeared in the server ImportDropBox. Then the
user could be alerted to apply the update or this could be done
automatically. I could get away with this because in general we don't
update data very often. The code would of course check if any of the
update files in the dropbox had already been imported (each import is
logged to a table).

A better alternative might be to launch another program at the same
time myapp is launched. This second program would be the one
monitoring the server ImportDropBox and would automatically apply
updates. But right now, I'm not sure if a timer on a form within in
application that does not have the focus, works within Access. I need
to research this.

But either method requires that someone is logged in. Even if I use a
separate app to accomplish the server synchronization, it is always
going to require that someone is logged into their computer at a
minimum.

But let's keep things in perspective and look at how we're doing it now
(BTW: the app is still in the deployment stage so we are still working
out coding and logistic bugs and adding customization; but I need to
figure this "synchronization" issue out and deploy it soon). Updates
are sent to a central person (typically by spread*** or e-mail) and
that person has to manually update the server backend. Then after the
server backend is updated an e-mail is sent out and remote users have
to download the updated backend from the server.

So from that perspective, this is a large improvement. Further, since
there are multiple users using the app that are also connected to the
LAN where the server resides, each one would be given permissions to
update the server backend so there would be some redundancy. There
will still be latency, but it will be much shorter than it is now. Of
course, Jet indirect would be better yet (but IT won't yet allow it).
OTOH, one of my other concerns is that MS will abandon Jet replication
as they currently seem to be on that path (I don't know how well it is
supported in A 2007).


Some thoughts, suggestion, before I embark on this coding effort.

Thanks.

rdemyan@xxxxxxxxxxx wrote:
Your suggestion about applying the filter put the spunk back in me :)

I'm making good progress. I have the code setup to create and fill the
temp updateDB and it seems to be working well. Took me quite some time
to make sure that all of my tables were set up correctly (no autonumber
PKs, all have TIME_STAMP field) and to make sure that all my code
always set the TIME_STAMP when a change is made to the data tables.

The 'checking for delete' process is what is taking the most execution
time. With the TIME_STAMP filter for checking for additions and
updates, this part of the code takes as little as 2 seconds to execute
for 107 tables.

But the delete process can't use this technique because the record is
missing. I'm creating a "snapshot" backend when the app opens (i.e.
just filecopy of the local backend). Then when the app closes, the
delete process looks for non-matching records in the snapshot backend
versus the backend that the app is linked to. This takes about 50
seconds at a minimum. However, when I then deleted 130,000 rows from a
table it only took an extra 7 seconds.

What's taking so long is that I'm using generic code. The tables are
looped through and then the index is looped through to get the PK.
This dynamic creation of the SQL is what appears to be taking the
lion's share of the time. However, it beats the alternative of coding
for 107 tables and specifying each field name explicitly. I don't even
know if that would save any execution time and I'm not inclined to find
out.

It would seem that the only way to trim this execution time down would
be to write the record(s) to the temp updateDB table when the record(s)
are actually deleted. This might actually be a possibility, since with
virtually all table deletes, I use an action query like strSQLDELETE =
"DELETE *......". Of course, I'm sure that there are probably a few
exceptions, but still I'd be able to find the vast majority very
quickly and then simply copy and paste the code and add a database
qualifier to the external table. I'm going to look at this since then
I wouldn't have to check for deleted records upon creating the temp
updateDB which would save at least 50 seconds of time.

David W. Fenton wrote:
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/

.

Loading