Making SQL express database available offline.

jwh

Well-known member
Joined
Aug 18, 2006
Messages
155
Programming Experience
3-5
Good afternoon.

I am currently developing an epos system, and have been thrown a curve ball.

The Story so far:


Each Epos terminal connects to the server over a standard Network.
The server is running SQL 2005 Express edition.

As each transaction is put through the epos systems, the transaction details are recorded to the database on the server.

Nothing unusual so far.

However, 9 time out of ten, the server will also be a touch-screen epos, just like the others.

What happens if the server (or 'Master Till') goes down?

In the current scenario, that means all of the epos systems will also go down, due to them not being able to talk to the server.

I need all remaining systems to keep running.

as I understand it, this would have been an ideal scenario for SQL Replication, however that is not possible with Sql 2005 Express edition.

This is a low cost high volume piece of software, so bundling sql server Workgroup ed. is out of the question.


Any Ideas?
 
hm, could you simply stick the server on a separate computer (ie. one that will never be an epos)? there is still the problem of if the server goes down everything goes down... so maybe you could modify the program so that if it cant access the server, it simply writes the transactions to a local source, until the server is back up. and then from the server computer, make an extra feature that searches all its clients on the network for local transactions and updates them to the main server.

what do you think?

regards
adam
 
Hm, interesting.


That just might work, however I need to check with my bosses as to whether the remaning epos systems will still need to talk to each other, for example:

In normal operation, when a user starts a transaction, but then signs on to another till, the original till logs off, and then the new till logs on with the same data available on the current transaction.

It does this by the following.

  1. The new till sets a flag on the users account (sql record on server) that it is requesting a Log Off
  2. The Old Till reads the flag, clears it, and then saves the current transaction to the database, and then sets another flag to say that logoff has completed.
  3. The New till finds the second flag, and logs the user on.
All of this happens in under half a second, and as you can see, the process relies entirely on the central data source.

The only other way I thought of last night, would be to give each client epos system the name of a second server, that if the first fails, they try to contact.
The epos that has been set as a backup server will check the main databse every second (more often if possible, but dont want to clog the network) for changes, and copy them.
In the event of a failure, the second server would take on all database roles, periodically checking if the original server was back online.
if the original server comes back on, then all transactions are passed from second server to the master.

Any comments or suggestions are very welcome!
 
Oh, and the idea of sticking a stand-alone srver in there is out of the question.

These guys run bars and if they could have it all for free, they would. They will not pay extra because of a shortcoming of our product, they will go to a competitor that already does it.
 
In the event of a failure, the second server would take on all database roles, periodically checking if the original server was back online.
if the original server comes back on, then all transactions are passed from second server to the master.

ok, i see your problem. but what if the second server fails as well? this is unlikely but its best to plan for the worst.

you could do a couple of things, one thing could be to turn every client into a sort of mini-server that could run itself (ie. write all transactions locally), and at the end of the business day, the main server simply grabs all transactions and updates them to the main database. this eliminates the absolute necessity of having the main server functional ALL of the time. if you wanted to, you could even update the transactions once a WEEK (in the even of a catastrophic failure of the master server), i think it would make the entire system much more flexible. and to update the transactions, you could simply grab the new transactions in a dataset and update the dataset to the master table (i dont know if this is inefficient, but its easier than grabbing it row by row)

you could also create a hierarchy of temporary backup servers, so if the master fails, and then the second server fails, it know where to go to next to temporarily write the transactions, like a chain-of-command sort of thing.

hope it helps mate

regards
adam
 
If I were doing this -with this level of required availability I would try to use a more loosely coupled solution.

I would have some type of secure local data storage for the epos system, with a second process (lazily) pushing the local data to the main database.

A one candidate for this type of local storage is the new embedded SQL Server engine:

http://www.microsoft.com/sql/ctp_sqlserver2005everywhereedition.mspx

although I realise it is still only a ctp and so may not be considered mature enough for production environments (MS might not even licence it for that - I didn't check -sry:( )

Another option would be to look at message queuing.

Nothing comes for free - if the users are not going to pay for replication they will have to pay for it in increaced application complexity.

Hope this helps and good luck
 
Back
Top