Coding Thread, Adding resilience to an application with local data storage fallback in Coding and Web Development; As I have mentioned before, I have designed and implemented a custom cashless catering system for our school. I have ...
27th May 2010, 01:52 PM #1
Adding resilience to an application with local data storage fallback
As I have mentioned before, I have designed and implemented a custom cashless catering system for our school. I have no reached the stage where I would like to add that little bit of resilience to the system by allowing them to work without having a connection to the main server - by having a local data store which can be used to store data in, and retrieve data from.
So, my question is this - how would you go about doing this?
My idea is to install an SQL server express instance on each till device, with a copy of the data structure of the main database for things like the item buttons, and for user data (such as fingerprint data, and account balances). These parts would be periodically checked and updated from the main database when it was available.
The storage of local transactions would be done by altering the way each transaction is given an ID. I would alter this to allow each till to have a completely independant ID numbering system, and then the main database would store these as 'local-ID' for example.
Then, transactions could be stored on the local machine and when the connection comes back up, the software would iterate through them, storing the data on the main server and emptying the locally stored data tables.
Now, this seems reasonably complex, so I am wondering if anyone has a simpler idea?
28th May 2010, 03:33 AM #2
If you are using SQL 2008 standard or above then most of this is built in, there are two syncing methods specificly designed not remote working on non-persistant applications:
Occasionally Connected Applications (Local Database Caching)
SQL Server Compact Edition supports Remote Data Access (RDA), Merge in SQL Server Programming
Introduction to Sync Framework Database Synchronization
The method works well and I know someone who developed a stock managment app using hardened Windows CE devices and a core SQL DB that works well. They just sync up whenever there is a network connection and keep running on local data throughout the day.
Last edited by SYNACK; 28th May 2010 at 03:35 AM.
28th May 2010, 09:12 AM #3
Thanks. I'd already stumbled upon the SQL Server CE stuff, and find it interesting. I shall have a read of that lot and see how it works
28th May 2010, 10:05 AM #4
Ok, makes sense. I need to make some major structural changes to the database though now. Out with integer primary keys and in with GUIDs. Out with varchars and in with nvarchars (SQL CE doesn't support varchar). I'm sure there'll be others too.
28th May 2010, 11:50 AM #5
Are you sure you want to do this it could cause you a world of pain if the cached data does not merge with the original.
28th May 2010, 11:52 AM #6
Very sure, yes. Why would the data not merge?
Originally Posted by ricki
11th June 2010, 10:19 AM #7
- Rep Power
You may also have a problem depending on how your system is setup with charging students from the main online database
Say your live database goes down and all the tills fall back to a locally stored database copy. it means that each till will then be functioning by itself, so if a student had (theoretically) £2.50 on the main live database and you had 4 tills. the student could in fact over spend by £7.50 by using different tills at purchase. unless you had some sort of P2P check between the tills themselves?
Last Post: 9th March 2010, 10:56 AM
By Rydra in forum Windows
Last Post: 12th February 2010, 03:42 PM
By cjohnsonuk in forum Windows
Last Post: 30th September 2008, 10:29 AM
By Chuckster in forum Windows
Last Post: 30th July 2008, 09:54 PM
Last Post: 7th February 2007, 12:28 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)