Solved

SQL Server; storing data in offline mode.

Posted on 2016-08-17
  • Visual Basic.NET
  • MS SQL Server 2005
  • MS SQL Server 2008
  • .NET Programming
  • ASP.NET
  • +1
10
57 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 34

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
 
LVL 34

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of utilizing SQL Server views 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 Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now