Synchronisation Question

[PaulG111]

I have a split Access 2000 Db with a front end on each pc. Also on each pc I have a local, replicated copy of the back end. The master back end is on a file server with a fast(ish) connection to the networked PCs. Each morning when a user logs on at their workstation the front end creates a clean back up of the local replicated Db and then synchronises with the master Db on the file server. The same happens again when the user logs off at the end of the day in that the local replicated back end Db is backed up and then synch’ed with the master on the file server. Additionally the user has a synch button which can be used at any time during the on-line day to synch with the master on the file server in the event that updated store or contractor information needs to be pulled down. All in all this approach seems to work well with no replication errors, record update conflicts or back end Db corruption. However, (and there always seems to be a however doesn’t there?) it seems that Access 2000 allows only one replicated back end Db to synch with the Master Db on the file server at any one time. If 2 users log on at the same time only one will get a successful synchronisation message whilst the other will get a hung front end. Am I correct in thinking that this is an absolute restriction in that the master Db can only synch with one replicated copy at any one time? If this is so, is there some kind of error trapping or test I can carry out to prevent a front end from hanging if another front end is already synching it’s local back-end with the master on the fileserver? The approach I was thinking about would be to attempt some kind of connection or transaction with the master Db on the fileserver. If this connection/transaction timed out or failed I could trap the error and supply a message to the user to attempt a login/synch a little later when the master back end is free or maybe have a timed event to retry the synch again under the front end control. Is this feasible? Any ideas you have would be greatly appreciated. Any examples of suggested code would be even better! Thanks for your time and any help you can provide. Regards, PaulG

 

[votegop]

Paul:
Sorry if this is a dumb question, but why are you using replication in a split db format? The whole point of having the split db is so the backend can reside on the server with only the front end on the local machines. The backend can be backed up regularly when all users are logged off. The front ends don't need to be backed up.

Your system seems unnecessarily complicated, but I might be missing someting.

 

[PaulG111]

Hi votegop,

There are 2 reasons why I have a replicated "local" back end. The main one is a performance issue in that when users are hitting the master back end directly the whole thing runs too slow for their liking with the order creation process just taking too long. I've looked into why it runs too slow this way but cannot see any cause other than slow network traffic. When connecting to a local replicated copy of the back end the application runs lickety spit with a new order being created in fractions of a second. Another reason is that I also have 4 users who access the application remotely on PCs which are at the wrong end of a slow ISDN line. In order for them to be able to use the system they need a quick, accessible local copy of the database backend with only synchronisation updates passing via the ISDN. This seems to work ok with synch operations over the ISDN line taking only 2 - 5 minutes at the start and the end of each online day. Therefore, to address the local performance issue and to give the remote users viable access to the application I have a replicated copy of the back end on the C drive of each PC. This approach also gives me a consistent architecture on each PC. You are right in your comment that the front ends don’t need backed up and they aren’t. It is only the local replicated back ends which are backed up before each synchronisation takes place. .The local back up has come in handy on one occasion so far when a complete failure of the file server occurred and the relevant person had not been backing up the required files. It took 3 days for the server to be replaced, a lot of other data files were lost but my application was back up and running 10 minutes after the new server came online with no loss of any transactions. Hope this clarifies for you. Regards, PaulG

 

[votegop]

Paul:
Yep. I was missing something. Under the your circumstances, you've devised a pretty clever work-around. I think you are right on track trapping a connection error. Unfortunately I can't help with sample code since I've never done it. A guru should be along any minute with an answer...

Jay

 

[Silas2]

Sorry to reply to this so late, but you have just pointed me to this thread. I was thinking that a much nicer workaround to waiting for a time-out/hanging if two “slaves” try to synch with the master at once, would be just to set a flag field (db entry or anything on the server) when synching is starting, and unset it when the synching finished – nobody else would be allowed to synch with the flag set. The advantage being that goodness knows what problems might have been created by trying to synch two at once, as replication is such a complex activitiy.

 

[PaulG111]

Silas,

Thanks for that but I would have to set the flag on the Master Db so the other slaves could see it. However, I am not sure that one slave could read the flag on the master whilst another slave was synching with it. We would run into the same problem in that the master will probably not respond to slave A whilst synching with slave B.

Having said that I might just give it a try to see.

Thanks for your input.

Regards,

PaulG

"God is a DJ
Life is a dance floor
You get what you're given
It's all how you use it

 

[Frink]

Hallo,

Just looking at Replication and Synchronisation in the forum. Bit scary and lots of 'no replies'

I think the 'flag' Silas mentions is an empty file on the Server.
When a user starts the app it checks for this file. If it does not exist, create it, do your replication thang, then delete it.
If it already exists whan you start the app then close with a suitable message (or delay until it is deleted) as someone is already replicating (and you wouldn't want to walk in on that!)

- Frink

 

[PaulG111]

Frink,

Thanks for your thoughts on this question even though it is quite aged now!

I ended up implementing a similar approach but instead created a completely separate database instead of an empty file in order to track sync requests. Here is how it works.

Remote user A logs in to the slaveDb01 which wants to sync with the nasterDb. The first thing that the slaveDb01 does is to check for records in tblTrack on a separate sync control Db called SyncDb.

If it does not find any existing records it takes a DenyRead lock of tblTrack and creates a record as follows:-
SyncNo SlaveID
01 slaveDb01

The slave Db then immediately goes off and initiates sync with the Master Db. The DenyRead lock prevents other slave Dbs from reading or creating a record in this table until slaveDb01 has finished creating its own entry and has commenced the sync.

Once the sync is completed the slaveDb01 returns to tblTrack within SyncDb and deletes the record it previously created in order to show that synchronisation has completed.

If another user B tries to sync their slaveDb02 at the roughly the same time as user A then one of 3 things will happen:-

1 – slaveDb02 gets to tblTrack first, creates an entry for itself and goes off to sync with masterDb.
2 – slaveDb02 gets to tblTrack just after slaveDb01 and finds tblTrack already in DenyRead lock. Error trapping makes slaveDb02 wait/retry until the DenyRead lock is removed. Then the process below commences.
3 – slaveDb02 finds tblTrack unlocked but with the sync entry from slaveDb01 already there with a SyncNo value of “01”. It then creates an entry for itself with a SyncNo value of “02”. SlaveDb02 will then requery tblTrack at intervals until it finds that there is no entry which proceeds its own, It then goes off and commences sync as described above.

Advantages of this approach are:
- DenyRead lock prevents slaveDb01 and slaveDb02 both creating an entry in tblTrack with a SyncNo value of 01
- Having a set of sync request records in tblTrack means that users will be synchronised with the master in the order in which they logged into their slave Dbs

Hope I have managed to explain this clearly.

Regards,

PaulG111






"God is a DJ
Life is a dance floor
You get what you're given
It's all how you use it