two-way database replication (sync)

[crleblanc]

Hi there,

I've got a pretty simple problem. We're hosting a SugarCRM install in New Zealand. Its working great locally, no problems to speak of. However, we have partners in Houston, Texas who need to access the same Sugar site. Latency appears to be a real problem, and they are having problems connecting to the server reliably. New Zealand is a special case, since its connection to the rest of the world is through a tiny fibre optic cable and bandwidth/latency can be bad at times.

I was thinking of setting up another server in the US, and doing a two-way MySQL replication. This should eliminate the latency problem for them. There will probably be few if any record conflicts, but I can't be 100% sure. If there were, we could safely assume that the NZ server is 'right'. Does anyone have any experience with this? Its not critical that the US server update the NZ one immediately, it would be fine if it sync-ed after a network outage for example.

Is there any other strategy I can try to help this problem? I've tried increasing the timeout in Sugar, but this doesn't seem to help. I was also thinking about running Sugar in a VMware session in New Zealand, and using the 'cnnect to remote host' feature in VMware to run the same session in the US. In theory, this should be able to update the MySQL db without any conflicts but I haven't tried it and I'm not sure how reliable this would be with the poor connection between NZ and the US.

I see the enterprise version has a remote sync capability. Does anyone have any experience with this? Could this solve our problem? We're running the open source version, and would prefer to stick with it. Is there any way to buy this thing itself instead of the full enterprise package?

Thanks for any input,
Chris

[mycrmspacegunnar]

Hi,

Sorry but what you want to do will not work this way.
Two way master replication is not save thing to do in MySQL.
For details please have a look at the MySQL manuel:
http://dev.mysql.com/doc/refman/5.0/...ation-faq.html

In short if you run two database master that hold the same tables
and your clients insert in both servers then the database will crash when
you insert new values similtanously that for exmaple use auto increment values.
This running of two master will only work if your application will fully handle
all possible conflicts but SUgar does not do this.

A much saver thing, what you could do to reduce latency:
Is running one database as slave and having your application read from it
But akll writes should go to only one server.
But unfortunately Sugar does not support such a setup as it out of the box.
But it would be possible change Sugar to support this.


Cheers
Gunnar

[kpit]

SugarCRM does not use autoincrement in this case and guids are generated locally and the odds of hitting the same guid is highly unlikely. a 2 way master replication will work in SugarCRM's case. But you still will have the latency issue to deal with for the long distance. A better solution might be using a solution like Jitterbit (http://www.jitterbit.com/). Jitterbit is designed to provide 2 way syncronization and conflict resolution. you could use this as a realtime sync or scheduled syncronization. It talks SOAP and MySQL to do sync. It also talks to any database format that java can in addition to flatfile, CSV and many other formats. Jitterbit has a specific jitterpak for SugarCRM that will start you out. You will still have to customize the pak to do what you want but It will jump you ahead to get you started.

[crleblanc]

Excellent, Jitterbit sounds like the solution we're after. Thanks for the info.

-Chris

[mycrmspacegunnar]

SugarCRM does not use autoincrement in this case and guids are generated locally and the odds of hitting the same guid is highly unlikely. a 2 way master replication will work in SugarCRM's case.

Hi Max,

This is not 100% true.
You are right that because the hashes that Sugar uses for most ids are very long (36 chars)
the odds of hitting the same id is very small - but there is no guarantee this will will never happen.

And more critical, Sugar does use autoincrement value. For example the CASE_NUMBER is type autoincrement.

So running a two way master replication on Sugar is a game.
A MySQL master-master database replication can fail at any time.


Cheers
Gunnar