We help IT Professionals succeed at work.

Replicate/Sync two Databases on separate servers

Hornwood
Hornwood asked
on
We are running a custom written webapp which relies heavily on it's SQL Database. Due to poor performance across continents, we need to have a 'live copy' of the database running at the other location (as well as a replicated Webapp), but the data between them needs to be syncronised behind the scenes so the users dont see any performance degredation.

Would we need SQL 2008 Enterprise edition to facilitate the sync?
Is the sync automatic once it's been setup?

We are really not sure how to begin.
Comment
Watch Question

Volunteer Chief Information Officer
Commented:
Unfortunately the answer is not as simple as the question.

First things first,  Live across continents is difficult at best.  That interprets live data pushes as each transaction occurs.  This is a massive bandwith latency issue.

You might want to consider a Transaction replication capability that comes with SQL SERVER.
This will give you an automatic replication.

Here's something to watch to give you an idea: http://www.youtube.com/watch?v=BPU3cefvioY

Another thought is to forego live replication to a daily replication...you can use any number of ETL tools for this.

Author

Commented:
We have Timezones on our side, Africa and Australia are never usually working at the same time, and when they are, it's usually different data.
I see there are 3 kinds of replication available. Merge seems like the option that I'm after. Would you have any links or tutorials on setting that up?