sync online order portal with local DB

Question

So we're building a locally(at customers site) hosted, custom point of sale system, nothing too much out of the ordinary. Its all .NET, C# and SQL Server.

One of the requirements is that the system allows orders to be taken over the internet as well. For various reasons(connection reliability/speed, backups, stability, maintainance, etc) the web portal will be hosted off-site, with some standard web hosting company.

The product details are in the local DB, and thus we need to somehow sync the external website DB with this data. It has to be secure obviously, and not too bandwidth heavy.

We came up with a solution that minimises exposure of the local DB to the outside world:

• the web app cannot access anything from the local app.
• the only way data can be moved is by a push sent from the local app to the web app.
• the only thing the web app can do is send a notification to the the local db that a new order has been taken. The local app will then connect to the external app and pull the order details.

So we were thinking of a design like this, using web services, and just defining basic actions on the web app like addProduct(id, blah blah blah)

But then we have to write routines to keep stuff in sync, like if a product is deleted from the local app, but the external app is temporarily unavailible, etc, etc...

Is this a resonable approach, or is there a better way?

Answer 1

Not really an answer to your question directly ... I'd try to get a VPN tunnel up between the two sites, and if you can't, try and use an SSH tunnel?

Aside from the security being just good, a lot more options open up once the VPN is there in my experience, as things that would previously have been far too insecure to consider are work thinking about.

Answer 2

This is a case where SOA seems applicable to me. I would look into exposing certain functionality from the POS application using WCF. This has at least the following things going for it:

• you can reuse the functionality from the POS system instead of duplicating parts of it at the website
• Using for instance a MSMQ binding you can function in disconnected situations
• WCF has excellent security mechanisms, transactional support etc...

this way you would end up adding allot of value to the POS system instead of doing a one off coupling between the POS and the website.

To learn more about WCF I can recommend Programming WCF services by Juval Lowy

Answer 3

@olle This looks promising. One issue is that the connection b/w online portal and local app is not reliable. However, we still want to be able to take orders when the link is down. So, I think we are going to use WCF in the webapp to read data from the server, and cache it in local tables. The web app will request data through an intermidiary layer, which serves content out of the cached tables if the WCF call doesnt return in a short time. If it does return successfully, the cache will be erased and replaced with the results. Sound like a plan?