Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

run synchronisation in the background

Posted on 2013-01-15
11
Medium Priority
?
194 Views
Last Modified: 2013-02-05
hey guys,

here's the situation of my database. it handles 2 things - allows users to input their work time, and allows users to check codes.

for users to input their work time it's quite simple. it's inputting something from nothing so they enter all the inputs from their head / the time they start work till the time they end.

for users to check the codes, they first need to "download the codes" from the main replica --> or rather the right term is to synchronise the data containing the codes, and then later on after they have checked the codes they need ot synchronise the databack to the main replica on the network drive.

synchronisation takes time and synchronising the DB on opening and on closing makes the user feel that it's very "slow" --> when i open the DB i have to wait for 3 minutes. synchronising on closing is not that bad, but on opening is something i need to avoid.

so my DB is split into FE and BE and the replicas is only the BE.

when the user keys in their work time, it goes from the FE to the BE on their local machine. then when the user wants to check the codes (right after entering their work time) they need to sync the BE from the network replica.

is there any way that i can allow users to enter their work time whilst i sync the BE too? so this gives them a feeling of programme responsiveness. something like a doevents function, but the thing here is that it's using both the same BE.

is it possible for me to have a main replica BE and 2 child replica BEs which it syncs to? the 1st child BE will just contain the work hours and the 2nd BE will contain all the codes to check.

what do you think is the best way for me to do it? thanks guy!!

i'm thinking maybe the only way is to have a sync button - such that user opens the DB, it's fast and zippy, and after they enter their work time, they hit the sync button and then they expect it to "stall" for 3 minutes. so it's handling the users expectations. but i would prefer to be able to do it whilst the user is entering their work time. thanks guys!!!
0
Comment
Question by:developingprogrammer
  • 6
  • 5
11 Comments
 
LVL 58
ID: 38782499
Couple of things to make sure of first:

1. By synching, you mean you are using replication services?  Or is this something you've developed on your own?

2. Are all the users on the same LAN?  If so, why replication at all?

Jim.
0
 

Author Comment

by:developingprogrammer
ID: 38796418
hi jim,

thanks for your response = )

1) i'm using the replication services

2) i'm replicating cause the network is way, way, WAYYYYY too slow!!!! it took me 6 minutes to even open the database!!! and it's split already!!!! haha, but the replication is so much faster man = )
0
 
LVL 58
ID: 38805363
Sorry I haven't gotten back to you as of yet; been a bit over-run work wise.

I'm not sure I understand exactly what your shooting for, but I can say in general that you don't want to be synching while data is being added.

 And most certainly you do not want to sync anything other then data (back ends).   Syncing a FE with objects other then tables never worked well and almost always lead to corruption.

Jim.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:developingprogrammer
ID: 38812914
hrmm yup i understand. i'm thinking that perhaps if i want to give the user the experience of an asynchronous sync a la AJAX style, then what would the architecture be like? 2 BE databases, one containing the work hours and the other one containing the codes that i need to check, then when they open the database it syncs the codes whilst user adds to the work time and when they're checking the codes then it syncs the work hours? but for the reports i need both the codes and the work hours so that would mean that i need to ensure that both the databases are synced to the same latest data before i can pull the reports. do you concur with such an architecture? doubt that i will actually build it but just for moot in the future if we need to solve problems like that. think the punchline would be - "how to give users the feeling of asynchronous syncing in the background whilst they use the FE in the foreground".

thanks for your time and advice jim, looking forward to your response and learn from you! = )
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 38818681
Yes, I would say the split BE is your best option.  They only thing that would concern me is if there is need of the codes while time is being entered and vice versa.

 And the best approach I think is the one you outlined; let them enter their times first and then at some point synch just know that it will stall.

 Other thing I'm wondering about is how your replicating; direct or indirect?   Indirect might give you better performance.

 Indirect is where a file of the changes is generated and these are then placed in a drop box.  At some point, the other end picks up the file and does the synching.

 With this, your users don't have to wait for anything.  

 This is intended for situations where a lot of remote data entry is being done, but users don't really need to use the data right off, which kind of sounds like what your doing.

 The other thing to toss on the table is forgetting about replication entirely.  Not sure what bandwidth you have to deal with or if remote users are really off-line, but using SQL Server for the back end is pretty workable even over a WAN.  Would require a lot of re-write, but it's something to think about.

 Along the same vein, stick with JET, but hold all the data in local tables.  Then write the data to the BE in bulk.  Of course again, without any idea of the size datasets were talking about, that might not be a hot idea.

Jim.
0
 

Author Comment

by:developingprogrammer
ID: 38853726
Hi Jim, thanks so much for your ideas, contribution and help. i really appreciate it.

a few things caught my attention - 1st is your idea of an indirect replication

Other thing I'm wondering about is how your replicating; direct or indirect?   Indirect might give you better performance.

 Indirect is where a file of the changes is generated and these are then placed in a drop box.  At some point, the other end picks up the file and does the synching.

1) i read on the net another lengthy post saying that replication was not build for indirect syncing cause it would cause conflicts that are irresolvable. here is the post i've put up in another EE question - if you don't mind could you help me take a read of it too (and it might be quite valueable to you too cause i think the write has a great amount of experience and expertise in replication too)

the other idea is:

Along the same vein, stick with JET, but hold all the data in local tables.  Then write the data to the BE in bulk.  Of course again, without any idea of the size datasets were talking about, that might not be a hot idea.


2) this sounds like a manually implemented synchronisation to me - or rather what access synchronisation was supposed to do in the first place if it didn't get corrupted so much. are you talking about a manual synchronisation and who would you implement your idea here? i'm like soooo sooo late for my project and quite desperate to just get the IT part working. the business logic has been mind boggling enough already and now access keeps getting corrupted with super slow network connection which isn't helping me = ((((
0
 
LVL 58
ID: 38855900
<<1) i read on the net another lengthy post saying that replication was not build for indirect syncing cause it would cause conflicts that are irresolvable>>

  Well that is possible, but that is true in normal replication as well.  You always need to handle the conflicts.  But the up side is that replication always succeds because it is being performed local rather then over the net.

  Of course it does introduce a timing issue (time between files being generated and then processed), which you would not have by doing direct, but most found indirect more robust and almost always perfered it over direct when going over any type of WAN.

<<2) this sounds like a manually implemented synchronisation to me - or rather what access synchronisation was supposed to do in the first place if it didn't get corrupted so much.>>

  Yes, that's basically it.   Keep in mind too that replication has been removed from Access at this point, so by using the built-in replication, you are painting yourself into a corner.

Jim.
0
 

Author Comment

by:developingprogrammer
ID: 38856334
thanks jim for your response! = )

i just want to share one thing i did to make the whole replica sync architecture more stable (and less chances of getting corrupted). as this is a multiuser environment, multiple users may try and sync at the same time. so before the sync line of code is hit, i check for a .ldb. if there is one, that means someone else is syncing and i'll tell the user to wait. alternatively i can put in a recursive code to sleep for 30 sec and then sync again with a doevents method of exiting this whole "redial" attempt at syncing = )

thanks for all your help jim, you've really cleared up my head!!
0
 

Author Closing Comment

by:developingprogrammer
ID: 38856361
guys i just want to point of something i thought of today, my small little contribution to a big network of super experts! = )

i think it's worth expanding our thoughts to think of multiple FE and multiple BEs overlapping each other, instead of thinking of a 1-1 FE-BE architecture. that could be quite interesting having multiple FEs and BEs and actually more optimised.

managers could have their own FEs and staff their own FEs whilst querying the same BE. the BE can be split as well (as advised by jim here) so that we can have a "background synchronisation" effect. so many interesting variations to play with it around here = )

thanks guys and thanks all the experts for coaching me and the rest of us amateurs here!!
0
 
LVL 58
ID: 38856911
Best of luck with the project!

Jim.
0
 

Author Comment

by:developingprogrammer
ID: 38857563
Thanks Jim!! = ))
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question