Homegrown synchronization
- From: rdemyan@xxxxxxxxxxx
- Date: 23 Dec 2006 08:53:15 -0800
I'm concerned about the lack of support by Microsoft for database
replication and indirect synchronization. They don't even supply
replication manager any more. In our tough IT environment, they don't
allow unapproved third-party software (like TSI Synchronizer). Further
IT has not approved the Jet Synchronizer for use and it currently
cannot be installed. Even if they do eventually allow it, they may at
some point in the future take it away. Also since the Replication
Manager is now no longer provided with Office Professional 2003, I
seriously doubt they will allow its installation even if they can get a
copy. Although Jet direct synchronization is a possibility, a number
of users are on WANs so corruption is possible (many would say
inevitable).
The institution I work with is at the forefront of network security.
Since my app is only used by a relatively small number of people and is
not mission critical, they could care less about our requirements. Who
knows what they will do in the future. So, I'm thinking about creating
my own indirect synchronization code. Although it will be major work,
at least I will own it and nobody will be able to take it away. My
thoughts are to do it differently than Jet and to not have any features
regarding synchronization of table design or synchronization
scheduling.
So:
1) Scrap Jet Replication and Synchronization. This is not a problem
since we've only been testing it for use. It was never implemented in
the existing production environment. Start with an UNREPLICATED
back-end file. Each workstation (including laptops) will have its own
"local" backend. In addition, there will be a backend on a common
server that all workstations can connect to.
2) All back-end tables currently have a time-stamp field which is
modified if data in the record is changed.
3) Create a "model" database that contains all of the tables from the
back end but is EMPTY of data. This "model" database will be copied to
each user's workstation and be part of my application's files. When a
user updates data on their local backend, code in my app will create a
temporary database from this "model": a temporary updateDB (temp
updateDB). The temp updateDB will contain all the tables from the
backend, but will be initially empty of data. The code then will write
to the temp updateDB only the records that were added/modified/deleted.
The tables in the temp updateDB, however, will include an extra field
used by "import" code to decide what to do with the records in a table
in the temp updateDB. I think I can keep this as a Yes/No field. If
'Yes' then when data from the temp updateDB is imported into a backend,
the record is deleted. If 'No' then the record is an update to an
existing record or addition of a new record.
How does my application know what data to write to the temp updateDB?
Good question. My thinking right now is that I can just use the
TimeStamp field in each table in the backend. For additions and
updates, copy only records where the TimeStamp has today's date in it
(or some such algorithm). Any deletions would probably need to
immediately be written to the temp updateDB (since that record is
removed from the backend tables). This is an area that needs more
thought in order to keep it simple, but I think is definately doable.
4) My app will look to add records to the temp updateDB when the app
closes (deletions would presumably already have been added). Once the
temp updateDB is filled with the updated/modified/deleted data and the
Yes/No flag set for each record, then the app will copy the temp
updateDB from the local workstation to a common server location that
all workstations can see.
So, the idea is that the temp updateDB created by the app on local
workstations should in the vast majority of cases be relatively small
and easily/quickly copied to the common server folder. It will be
named with the user's name and a date (down to the second). BTW: The
reason for using a model DB is that it should be quicker to simply
create an instance of a model database, rather than creating an entire
temporary DB from code (besides I've got a lot of other coding to do!).
I already do it this way for my normal temporary database that my app
uses. In two years, this techniques has always worked as intended and
never failed.
5) When my app starts up, it will look for temp updateDBs in a folder
on the server. If it finds any that have not already been processed
(based on prior logs in a frontend table), it will copy them from the
server to the workstation. Then code in the app will transfer
"update/add" data from each table in the temp updateDB(s), copied over
from the server, to the equivalent local backend table. If the flag
for the record in the table in the temp updateDB is set to 'Yes' that
record will be deleted from the local backend. As noted, my app will
contain a front-end table that will log the filename of each temp
updateDB that was imported as well as the import date.
So, I think this can work for updating workstations. It's an indirect
synchronization scheme because the temp updateDB and the local back-end
file are only opened locally for updating of the local back-end file.
Scheduling is not needed. When the app opens it will look for temp
updateDB(s) to IMPORT and when it closes it will look to EXPORT any
table changes made by that user into a temp updateDB that will be
copied to the server. Again synchronizing table design changes is not
critical. There are other ways to handle this. If we change the table
design, then we will do it to the most current copy we can find and run
code to take all temp updateDB files on the server and import them.
Most of these updates will likely fail due to key violations. My app
launcher already looks for updates to my app located on the server and
will download them to the workstation. It is a simple matter to also
force downloading of fresh copies of the backend from the server.
Further, in this scenario, even if the supposedly updated backend on
the server does not truly have all current data, a new copy of my app
will have an empty frontend table that logged previous imports of temp
updateDBs. So it will try again to import temp updateDBs once it is on
the workstation. Most if not all will fail due to key violations, but
it should ensure that the new backend file is updated.
HOWEVER, I still need to have a current copy of the backend on the
server. My app launcher allows users to download a copy of the backend
from the server (in case it ever gets corrupted,or if we've changed the
table designs, or if they haven't logged on in 30 to 60 days).
But I'm unclear on how to keep the copy of the backend on the server
up-to-date. I guess I will need to have one or more of the
workstations handle this. My app already has a user setup form that
defines the group a user belongs to as well as what permissions they
have to see data. Only administrators can add/delete users and set
permissions. I could add a checkbox to that user setup form that sets
whether their copy of my app can update the server backend. Only users
that are within the LAN of the SERVER would be allowed to do this; no
users connecting to the server via a WAN would be allowed to do this.
When these users close my app, the code would not only update their
local copy but also the server copy. An alternative would be to create
a program that would schedule these updates. I already have a small
program for scheduled backup and compaction of the server backend so I
could add updating of the server backend tables to this program. It
has a timer (IT won't allow us to use Windows Scheduler). This might
actually be the best solution. Users on the LAN where the server is
could just launch this small program, make sure the timer is set and
then leave their machines on over night.
Also "housekeeping" is an issue. At what point do we start getting rid
of these temp updateDB files that are accumulating not only in the
server folder but also in the workstation folders. Since the frontend
will have a table that logs every temp updateDB filename and a date of
import, any entries where the date of import is over 60 days old, for
example, could be deleted from not only the table but also the folder.
These are just my initial thoughts and I would appreciate comments,
suggestions, etc.
Thanks.
.
- Follow-Ups:
- Re: Homegrown synchronization
- From: cemopolat
- Re: Homegrown synchronization
- From: David W. Fenton
- Re: Homegrown synchronization
- Prev by Date: Re: Indirect synchronization setup with no synchronizers on servers
- Next by Date: Re: Is there a way to tell if two replicas need to be synced without starting the sync process
- Previous by thread: Indirect synchronization setup with no synchronizers on servers
- Next by thread: Re: Homegrown synchronization
- Index(es):
Relevant Pages
|
