2

I have an app that is used by people in the US and in China and the database sits in the US. The people using it in China complain that the performance is poor, so I was thinking of using SQL Server Replication and put a database server in China, too.

However, when I mention this to other people, they get all, "are you crazy? you're gonna lose data! the data is gonna become corrupted".

Are there major concerns with SQL Server replication that should cause me to avoid this technology in mission critical apps?

Chris Burgess
  • 248
  • 1
  • 4
  • 11

2 Answers2

4

Replication like you're proposing is a very popular way to deal with high latencies. Unless you really screw up the replication setup, I can't see you losing any data.

Having said that, you have a few different options. You could do a full-on duplication, where each server tries to be as up-to-date about everything all the time, or you could setup your Chinese server to simply cache your main server. What you choose should ultimately depend on how your application is used, but in either case, SQL server won't be randomly losing data without attempting to recover and without letting you know.

I'd recommend reading up on it before you decide if it's a good idea for you.

I'm not sure who these "other people" are that think you're crazy, but I wouldn't take technical advice from them.

Ben S
  • 632
  • 3
  • 7
  • +1 - especially for that last paragraph. For full merge replication your database does have to be designed for it. If it isn't, you may have to re-structure some tables, especially if it's missing PK's and whatnot. – Mark Henderson Sep 28 '09 at 21:48
  • Should we be assuming that the replicated server is not used for writes? In my experience there is a delay before the subscribed server gets the data (even with "continuous" synchronization) - so if you have writes to the replicated server you could (potentially) get some transactions based on out-of-date data. I should point out that my experience is limited to transactional push-only replication. – Nathan Nov 04 '09 at 20:44
3

I've configured replication from the US to China before and it works fine. Just keep in mind that the performance won't be all that great because of the Chinese firewall.

As for loosing data because of corruption that happens vary rarely, and usually when people are doing things like replicating between SQL 7 and SQL 2008. If replication wasn't a solid solution Microsoft wouldn't still have it included in the product.

mrdenny
  • 27,174
  • 4
  • 41
  • 69