The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Multi-store sync question


I have a developed an win application that until now only worked in a network, locally. MSDE, .NET, the works.

Now the evolution of the software passes by having it work on various stores, dispersed geographically.

I guess the way to go is to syncronize data by the Internet, maybe using webservices. Putting the master database on a web server is no good since the users of the app are very keen of their client's contact info.

I need some help from you guys in coming with the best solution for synchronizing data between stores, even several times in a day. Data is mainly text but also photos (jpeg).

Sorry my bad english.
John Connor Send private email
Tuesday, December 20, 2005
Aaah, I see you're already preparing the fight against the machines. Very well, me and my army of rebels will try to help.

What's the database engine?
On what platform is it running?

Are these stores all independent, or are they all networked

If I understand correctly, the stores need to share a subset of a bigger central database, correct?
Parisian developer available for cute geek girls
Tuesday, December 20, 2005
Hi thanks for you quick response.

If you read my post, you'll notice I use MSDE for database engine, and that the app is windows platform exclusively.

The stores are dispersed geographically with only internet access (1 to 4 mbps) and they need to syncronize their data. For example in store 1 a new client is created, that client should be copied to stores 2, 3, etc. at the next syncronization.

All new or changed data on store 2 and 3 should also be copied back to store 1.

Hope this clarifies.
John Connor Send private email
Tuesday, December 20, 2005
I don't think there's a way to do this with MSDE.  IIRC synchronization features are only in full SQL.  But why do they have to be peers?  Why can't you make it more master/slave?  You say "Putting the master database on a web server is no good since the users of the app are very keen of their client's contact info", but you're going to have to ship the data around anyway.  Just secure the pipe with SSL or IPSEC or something.
bmm6o Send private email
Tuesday, December 20, 2005

Doesn't have to be peers, I was thinking more of a  master/slave relationship.

For the clients, me saying "yeah your database will be online" it's different of saying "your data will be syncronized through the internet", even when we as developers know it's the same darn thing security-wise :) (can i say darn here?)

I've read an article about HOW to sync, that says:
1. Send info from slaves to master
2. Reconcile data in master
3. Send new data in master to slaves

Can this sync be accomplished using a webservice?
John Connor Send private email
Tuesday, December 20, 2005
"Can this sync be accomplished using a webservice?" is like asking "can I print this document with a serial cable":  Yes, you can use a web service if you like, but it's only going to be part of the solution.
bmm6o Send private email
Tuesday, December 20, 2005
Let me reformulate my questions:

Have any of you guys developed or worked with stores spread out, having to syncronize data to all of them through the internet?

If so, how did you implement that synchronization, why did you opt for that design, what are the pitfalls of that design, and today how would you implmement it?
John Connor Send private email
Wednesday, December 21, 2005
ok, MSDE = MS SQL Server Desktop, right? Sorry.

You should consider having a central database running a full SQL Server; then you would have a replication service. The stores could (I think) continue running MSDE. Each store would synchronize only to the central database.

You have to pay a license but it is easier than writing your own synchronization engine. If you write your own, even with webservices, it will be much more complex than what you described in 3 lines. You'll have to handle the usual sync issues such as:
- how do you resolve conflicts (double-modify on a record)
- how do you handle network failure during sync without compromising data integrity, and how do you pick up where you left off
- etc

With its replication services, MS has already done the work for you.

Re. network and security:
1) even though the central db would of course need to be reachable remotely, a username and password would still be required to access the data.
2) you can always set up secure connections, a VPN, etc
The MS documentation on these subjects is pretty clear, you could extract a couple of security options and present them to your customer and let him choose...
Parisian developer available for cute geek girls
Wednesday, December 21, 2005
Thanks Parisian developer available for cute geek girls (see if I can get one too),

I think full SQL Server is also out, because mine is a commercial app that is sold through the web and the end users don't know jack about installing SQL Servers and the like.
John Connor Send private email
Wednesday, December 21, 2005
You're selling to users too unsophisticated to install MSSQL, but at the same time they're going to install your software at several locations and configure their firewalls to allow replication (of one sort or another)?
bmm6o Send private email
Wednesday, December 21, 2005
The program goes in a self-installable CD, only two clicks necessary to install the program.
Configuring firewall is as simple as to allow the program to access the web, every firewall allows it.
John Connor Send private email
Wednesday, December 21, 2005
I'm done something very similar at the moment.
Its a huge pain, I couldn't do a Master Slave replication as both the stores and the central database needed to be able to update the database.  The whole thing was designed into my database from the start.
I used I web service model to regulary send information to and from the database and the stores. 
There are a few things you need to keep in mind:
Your stores need to be able to keep working without an Internet connection.
You can never delete something from the database as you never  know if a store is going to try and update it later.
Plan for loss of data on the way.  You will need a way for the servers to work out if it hasn't received some data, even if the store/server thinks it has sent it to you.
It is not the kind of thing I would like to try and add on to an existing database, unless it was orginal planned for.
John Joske
Wednesday, December 21, 2005
John thank you for your advice, it is exactly what I have in mind.

Can you please elaborate more on what you did or what lessons you learned from that experience?
John Connor Send private email
Wednesday, December 21, 2005
There are lots of lessons I have learned a few pointers:

It is hard to guarentee refernce integrety as it is always possible the update to get to the server before the orginal data.

Try and keep the store and central database structures the same.  This way you can have a generic alogramith that maps the database structures, if they are different you will need you will end of with way to many lines of code to just snychronise the database.

Before you start map out where the data can be updated if you can have it updated in only one place it makes your life easier.

Primary keys will need normally be two columns, one for the store and a unqiue key for that store.

The connection will go down, as much as possible the store must be able to keep operating and seamisly pick up once the connection is alive again.

When you do updates to the software each store will need to be able to update itselve as well, or you will need the server to be able to talk with multiple versions of the store.

Never delete anything from the database, you never now when some else might decide to update that record.

Work out your policy for multiple updates of the same record, what happens if two stores decide to update the same record at the same time.
John Joske
Wednesday, December 21, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz