Solved

SQL Server; storing data in offline mode.

Posted on 2016-08-17
10
77 Views
Last Modified: 2016-08-22
I have a non-WEB based Windows app that our technicians use in the field on PC tablets.  It saves entered data to an Access database.  When the tech is done working for the day, there is a sync feature that syncs/sends the Access data to a SQL database in our home office.  There are multiple issues with this syncing process that cause us grief.  Duplicate data, loss of data, etc.  

What would be nice is to have a WEB-Based app on the technician tablets, that can talk directly to the SQL server database. The problem  is that due to location, the physical environment, etc., the tablets often cannot get a WIFI connection/signal.  

What would really be nice: The technician can enter their data on the tablet and if there is no WIFI connection, the data is saved somehow - (cached, stored, ???  etc) without using a local Access database, using some SQL or other robust mechanism, until such time that there is a WIFI connection and then write the data to the home office SQL database.

Hope this make sense. Anyone have any ideas?
0
Comment
Question by:HLRosenberger
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 41760043
Changing to using a Web app isn't going to solve the problem.  The problem is sync'ing.  

Your sync'ing process needs to be fixed.  It needs to recognize duplicate sync request and either reject the second and subsequent requests or delete the data from the first sync and try again.  That will also help you recognize loss of data.

Without more details it is hard to be much more specific.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41760090
Welcome to my box.
It's not ours alone by a long shot.
I've been trapped in it for quite some time.
So have myriad others.
There are no easy answers.

Syncing offline data is always a nightmare.
Who added what?
Who changed what?
Who did so first?
Who's additions/amendments should have priority?
How should the losers in that algorithm be notified that their changes haven't been propogated?

All very difficult and evil stuff to manage.
And I haven't managed.

What would really be nice: The technician can enter their data on the tablet and if there is no WIFI connection, the data is saved somehow - (cached, stored, ???  etc) without using a local Access database, using some SQL or other robust mechanism, until such time that there is a WIFI connection and then write the data to the home office SQL database.

It is not that Access isn't robust and some other technology miraculously will be, it's that the whole process is incredibly pain-staking to plan, code, test, refine and rollout.

Even when you disallow offline edits of existing data, the process doesn't get a lot simpler.
You have to build mechanisms that detect what was added by the offline user(s)
You then have to build mechanisms that add that data, one record at a time, one table at a time, that maintain referential intgerity and the integrity of the offline data.
You have to ensure that when more than one user is syncing up data that this doesn't break the process.
And then you have to build mechanisms to refresh all the data in preparation for going offline.
And all these mechanisms have to work flawlessly, without fail.

That's a very, very high bar for any non-dedicated professional development firm to hurdle.
What would really be nice: is if wishes were horses, but they aren't.

Connected apps like Access can be very powerful.
Disconnected apps like  internet apps can deal with the disconnectedness right at the time they commit the data.
But a truly disconnected multi-user app that punches up batches of data -- well that takes a professional development team, and even they drop back and punt a lot of the complexity to the user when the syncing get gnarly.

Sorry that I don't have a better vista to share with you inside the box.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41761179
Nick67 - Thanks so much for your feedback.  I'm taking over pre-existing code that saves to Access in the field, and then does a sync process to SQL server.

Probably what would also help is to limit what data actually gets modified in the field, yes?  Keep it simple as possible.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 35

Expert Comment

by:PatHartman
ID: 41761198
One way to control things is to send the tech data about the assigned tasks via email.  You can attach a spreadsheet that he can import.  That minimizes what he has to type and therefore reduces errors.   Some data about the job will be entered into the database and then collected during a sync.  Rarely, basic data will need to be changed.  This should be done using a separate table to clarify what exactly is being changed.  That way the sync doesn't have to guess.  It is clear that the tech is specifically changing the client's email address or whatever.

The import process should ask the tech if he wants to clear the old data.  This allows him to start fresh each day  with only the current tasks.  But the app should still work even if he doesn't explicitly clear old tasks.  It should track what time a sync was done and ensure that old data isn't resent.

As Nick said, it's complicated.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 250 total points
ID: 41761337
Probably what would also help is to limit what data actually gets modified in the field, yes?  Keep it simple as possible.

Absolutely.

Two items with two choices -- 4 possibilities
Three items with two choices -- 8 possibilities
Four items with two choices -- 16 possibilities

Two items, three choices -- 9 possibilities
Three items with three choices -- 27 possibilities
Four items with three choices -- 81 possibilities

To get any kind of scenario that's codeable and workable you have to be absolutely ruthless in minimizing the possible outcomes.  If you do not permit edits to pre-existing data when offline, the problem gets simpler as all that has to be dealt with is records added when offline.  The next step to simplify is if the offline user HAS to be given a unique datum  generated by the main system online, and the main system correlates that generated datum with the user.

Then your syncing system, when it goes to do its magic, can ask the user for that unique generated datum, find the primary key value in the user's offline (but presently accessible) Access database, and begin appending each offline record in each offline table that has that PK value to the main data store (and error checking that the record does not already exist as it goes)  After the sync is complete, the system then rubs out the entire offline data store and replaces it with a copy of the master data store.

Workable, but still extremely complex, and it has a two step process involved.  The user HAS to be given that pregenerated unique datum, and the sync system has to use it.  Getting it fully automated so that user screwups don't factor in gets harder yet.
0
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 250 total points
ID: 41762046
I've done three intermittently connected (IC) apps that would fall under the category of "What would really be nice". To address your question, I used SQL Server, MySQL and XML for offline data storage in those projects.

Nick67 has graciously offered quite a bit to consider when engineering an IC app. Naturally, I disagree, with all due respect, in the number of developers required. You don't have a complex task, you have a thousand simple tasks.

On that note, SQL Server 2016 includes temporal tables and row-level security, both of which could be extremely handy for IC apps.

Good luck!
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 41762657
@funwithdotnet
Does my description of what is involved jive with your experience in executing such a task (or successfully integrating a thousand simple tasks.)
How did you go about defining the problem?
What approaches worked?
What approaches proved to be unworkable?
What pitfalls did you encounter?
What kind of roadmap would you suggest?
0
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 250 total points
ID: 41763193
Nick67, your descriptions of some of the issues involved were spot-on.

There's a number of loaded questions there!  As I'm sure you know, each project, and especially the syncing involved, is unique.

One thing that has to be determined is a method of concurrency. No method is perfect, so choices need to be made.

I believe in all cases a SOAP web service was used to act as "middleware" to manage transmissions and process the flow. Handy in the regard that it accepts .NET objects.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41765455
Thanks for all your help!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

825 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