I am going to build a distributed solution based on the following requirements:
1. I have one production Java web service site (Spring+MS SQL server). 2. I have to build a new site in other region. 3. The two site DB data must be synchronous by real time.
My current consideration is to use MS SQL server replication feature. I guess replication in real time production env is big challenge. Do you have other good solutions? e.g. distributed data transaction?
Be careful with MS SQLserver replication. When we looked at MS SQLserver 2000 the only real time replication offered would roll back transactions on both servers if both database could not be updated. Because of this we didn't adopt SQLserver replication because the VPN between the two divisions involved goes down sometimes and we could not risk the sites having to stop shipping goods if that happened. Newer SQLserver may offer a better model.
There are 3rd party utilities, like DataMirror, that sit between two servers and do real time replication by reading the transaction logs and posting the transactions to the target server. DataMirror has a nice feature that it knows when replication has been interrupted and queues the updates. When the connection is re-established it will update the target and then go back into real time mode.