How to sync databases?

Hi all,

I want to have a main online database and a local database (to be used when you are offline).

Is there a build in way to sync databases, or a standard way of doing this?

Or does everyone write their own solution?

Thanks

Markus

I think this was discussed a couple of times. There is no standard way to do this and it’s seldom a good idea.

I’d look at the DB technology itself to see if it supports replication rather than a Xojo based solution. In MS SQL I’d look at using Merge Replication to do the above

MS SQL Merge replication

[quote=58525:@Markus Winter]…
Or does everyone write their own solution?
…[/quote]Yes, because it highly depends on business rules.
For instance, the same record has changed, but the one on the server is older than the same changed record on the client: which one dominates? This has to be decided by management, not by the programmer.

[quote=58530:@Oliver Osswald]Yes, because it highly depends on business rules.
For instance, the same record has changed, but the one on the server is older than the same changed record on the client: which one dominates? This has to be decided by management, not by the programmer.[/quote]

Very complex as Oliver says 2 clients change a record offline, one syncs and 2 minutes later the other syncs, the first sync is overwritten. Depends what you want to do and who “wins” in a sync conflict.

Syncing and dealing with conflicts depends highly on business rules AND on your data model. If your data model is quite simple (all data in rows and fields) you will fail in syncing between multiple users. But if you’re using your fields as 1:n relations to another table, where each modification or version is stored together with a timestamp and UserID you will succeed. In case of conflicts you have kinda log, where you can go forth and back in time, watching who modified when and you can offer this feature to your users.

Absolutely on the business logic front.

When I do this I use conflict resolvers to handle conflicts. In other words I put the business logic in the database to handle any conflicts. Conflict resolution

I know I’m labouring the point but IMHO replication is absolutely the way forward with this sort of problem. If your DB technology handles replication then the best place to handle syncing is in the database.

Part of my talk at XDC is about finding out what the database can for you and not just treating it as a DataStore. Maybe I should adjust the presentation to put a little replication example in there.