How to create "concurrent" databases between distant places

[mc_bluebeard]

Hi!

My company is creating a database application for internal use and the database needs to be accessed and changed from China and the U.S. It would be great if there were two machines w/ the database: one here (U.S.) and on there and if they were kept "concurrent."

The problem is that, changes would be made to either copy...etc.

Is this possible? Advice appreciated....Thx!
_________________
I do not belong on this mission and I want to return to earth.

[sonicbhoc]

I don't exactly get what you're trying to do, but maybe rsync could help?

[Fanatic]

[MrUlterior]

Offhand, these're the solutions I would investigate:

Solution 1 (3 MySQL instances)

The principle is you have 2 slaves nodes, these serve readonly queries (ie SELECTS) to your application & the master node which receives INSERT, UPDATE etc statements. Then the master replicates to the slaves. One physical server could contain both master and a slave node on different ports/sockets.
(see http://dev.mysql.com/doc/refman/5.0/en/replication.html)

Solution 2 (Distributed application)

Your application never directly interacts with a database. Instead it interacts with a web service (or similar) which serves back data in XML. These "controllers" intercommunicate via XMLRPC or similar to ensure records are locked for writing on both servers, whilst the server recieving the request first writes changes locally then forwards the request to the remote server; which then unlocks the record(s) in question. This would of course need to be combined with a FIFO queue on both controllers to handle requests for locks and/or updates, ....

Solution 3 (MSSQL Merged replication)
2 SQL Servers with Merge replication. Note that this tends to have serious issues with lossy connections (I manage 15 pairs of merged MSSQL server, so I speak from experience here ... )

IMHO solution 2 is best as it will scale better, cost less, perform as well as you build it and not leave you locked into an RDBMS vendor. Solution 1 & 3, typically arise because of bad design and/or not catering for future needs.
_________________

Misanthropy 2.0 - enough hate to go around

[Fanatic]

[mc_bluebeard]

way complex! option #1 seems best to me re: simplicity.


does something like Oracle support mirrorered DB's sans all the complexity?

thx!
_________________
I do not belong on this mission and I want to return to earth.

[MrUlterior]