Adding resilience to an application with local data storage fallback

[localzuk]

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?

[SYNACK]

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.

[localzuk]

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

[localzuk]

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.

[ricki]

Hi

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.

Richard

[localzuk]

Hi

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.

Richard

Very sure, yes. Why would the data not merge?

[koryo]

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?

Regards

Koryo