Please critique my proposed method of replication

[fearoffours]

Hello there. I've been administering a split Access 2003 database for some time now. The Back end currently resides on a central network drive, with the front end being updated and pushed out to users via a batchfile.

I now see the need to replicate the BE so that users on slow connections (which exist within the company, as we have multiple branches in rural areas with limited network bandwidth) can work on it acceptably.

After reading up on replication, mainly at the jet replication wiki, I've come up with the following plan of action to implement replication. However, I'm concerned that my method is flawed, and I'd appreciate input before I implement it.

1. User opens database by running shortcut pointing to batchfile on networked drive X:\network_db
2. The batch file checks for a folder C:\database and creates it if it does not exist
3. The batch file then copies the latest version of database_FE.mde from the network to the local folder
4. It then checks for a file database_BE_replica.mdb in the local folder. If this does not exist, it copies the (never opened file) database_BE_replica_hub.mdb from the network and copies it to the local folder, renaming appropriately.
5. The batchfile then opens the local database_FE.mde file
6. A startup form (without a titlebar close button) which serves as a main menu for the application, opens. A procedure in the Form_Open event runs that deletes existing links to linked tables (as the existing links are to the networked copy of the BE!). This procedure then creates links to the tables in the local copy of database_BE_replica.mdb.
7. In the same always open startup form, a procedure exists in the Form_Close event. This procedure synchronises the local BE with the networked one. This ensures the synchronisation (potentially a long process over a slow connection) happens at a time which should least inconvenience the user.

My concerns are as follows:

• What steps can I take to ensure the hub replica is never opened?
• Is there a better way to ensure the front end tables are linked to the local copy of the BE, rather than dropping and recreating links every start up?
• Is my call to synchronise on the closing of a startup form good enough? I believe there is no way to close the database without the Form_Close event happening.

And yes, I'll happily hear out any other suggestions! The database is low use, rarely more than one concurrent user. The database is used for usually no more than an hour or two at a time, rarely more than once a day, often as little 3 times as once a week.

 

[dfenton]

Over a slow WAN you don't want to do direct synchronizations. You really need indirect.

Let me suggest that you avoid replication entirely and instead get the app hosted on a Windows Terminal Server. Then when A2010 is out, you can port to a Sharepoint-based synchronizing app and not have any of the complicated issues that come with using Jet replication.

Secondly, you want to be careful creating your replicas by copying, because this means all of them with have the same replica priority. This may be desirable, but you don't want the hub to have the same priority, and you really should have a networked production replica that is separate from your replication hub. Now, with such low usage, you may be able to get by without that, but it also has the advantage of being a de facto replica farm, which gives you some fallover protection.

If you use indirect replication, and have a separate production replica and replication hub, then you won't put the replication hub in a shared folder, in any case -- it will be synched entirely via the synchronizers.

If you use a single replica for both purposes, then you can put it in a hidden share. That's accomplished by naming the share with a $ at the end, i.e., \\Server\HubReplica$. This share will not be enumerated in Network Places, but will still be accessible if called via the full name.

Why anybody would try to open the hub is another question. Since when do end users care about back ends?

 

[fearoffours]

Many thanks for your reply.

Over a slow WAN you don't want to do direct synchronizations. You really need indirect.

Why is this? Is the direct sync more likely to cause data corruption over the slow connection? Direct synchronisation seems easy to implement. Indirect seems difficult, esp in Access 2003, without ReplMan.

This may be desirable, but you don't want the hub to have the same priority, and you really should have a networked production replica that is separate from your replication hub.

I've read this before and I don't quite understand the difference. Would you be able to elaborate? What would the purpose of the production replica be? Is it essentially a networked replica that is regularly synched but never directly edited?

 

[dfenton]

Direct replication opens the remote replica across the wire, i.e., pulls 100% of its data across the network connection to the RAM of the computer on which you are initiating the direct synch. This is fine in a LAN situation, as LANs are fast enough and reliable enough that you'd seldom have a problem. But the slightest hiccup in the network connection during any operations that write to the remote replica are going to corrupt it. This is usually the kind of corruption that Jet almost always can repair with non-replicated databases, but for replicas, it almost always results in loss of replicability. That means that the remote replica will be permanently unable to ever synch with the replica set again. It's dead. It's done. The data in it will have to be recovered through some other method and folded into the replica set.

Do you really want to take that risk?

As to implementing indirect replication, ReplMan is not needed at all. Indeed, ReplMan is not useful for much of anything except for that pretty diagram of the replica set. I give instructions for how to implement indirect replication without ReplMan on the Jet Replication Wiki (FAQ #9).

And I still think hosting the app on Windows Terminal Server is by far preferable to setting up indirect replication (and would completely eliminate any need for replication at all, unless there are occasionally disconnected laptops involved).

As to the question of production replica vs. hub replica there are a number of reasons for having two different replicas with different functions:

1. redundancy -- gives you two replicas on your server with the same data and if one gets corrupted for whatever reason, you've got a replica with identical data from which you can create a new replica to replace the bad one.

2. synch/editing conflicts -- certain kinds of editing operations can cause errors if they are happening when a synch is processed, and in the case of memo fields, can corrupt the memo field. The latter can be avoided by editing memo fields unbound. In reality the risk here can never be avoided because even with a replication hub that is separate from the production replica, there still have to be scheduled synchs between the two, but the scheduled synchs are controlled, while the synchs from remote users are not.

3. control of conflicts -- a replica that serves no purpose but to be the point of synchronization can have a low replica priority so any accidental edits to it will never produce a conflict (they'll always lose because the replica priority is lower than any of the edited replicas).

In your question, though, it's clear you are confusing the function of the two. The replication hub is never edited by anyone -- it does nothing except function as the partner replica in any synch from a remote user. The production replica is the one that users connected to the LAN would share and edit throughout the day. Scheduled synchs between the two are required to keep the data in the hub replica fresh so that remote users get current data and the LAN users get current data from the remote users.

The interval for the scheduled synch is an important choice and will depend on the app's tolerance for latency. If I thought I needed the shortest interval (every 15 minutes), I'd be thinking that my application had exceeded the capabilities of Jet Replication and start looking for some other solution to the problem. A couple times a day or once every hour during the business day would seem to me to be perfectly reasonable scheduled synch intervals.

All that said, in the case of the low-use app you described, there's probably no need for the two functional replicas, as it doesn't sound like there's anybody editing on the LAN throughout the day. That doesn't mean you shouldn't implement a replica farm for backup, though. But doing that is simpler than setting up the hub and production replicas (which between the two constitutes the smallest possible de facto replica farm) -- you simply manage all the replicas in your replica farm and set up a scheduled synch between them and you're done.

 

[fearoffours]

Many thanks again, I appreciate the time you took to explain that thoroughly. I'm looking at the way windows terminal services work, I can see why you are recommending it for a low use app like mine.

I still stand by what I say about Indirect replication being tricky to set up!

 

[dfenton]

Indirect replication is, indeed, tricky to set up. But it's the only way to synchronize safely across non-LAN connections.