April 23, 2008 at 3:34 am
Hello,
I was asked to come up with a solution to sychronizing two sql databases real time.Let me explain the problem the best way i can.
We are presently testing out our systems being developed in a test environment. We made a decision to have two identical databases. One at a remote office offshore and the other will be onshore. Update will be made simultaneously to these databases (Onshore and offshore). We are looking at synchronizing the contents of the two databases at the end of the day, to reflect the updates made to both databases. At the end of the day, the contents of the database onshore should be identical to the contents of the database offshore.I was looking at various options.
1) Creating a script using the tablediff option in sql server to check the recent updates made to both databases and capturing such updates as update statements in a script and running the script on the sql server databases. This is to be done daily for both databases.
2) Looking at recommending getting the Enterpise version of Sql server 2005. But was also considering the cost implication.
3) The third option i was looking at is getting the Redgate sql data compare to do this job.
If option three which is the Redgate sql compare tool is considered, How can it handle the items below:
a) Incremental change tracking
b) support large data sets
c) Can the tool be customized at the backend to enable automation.
d) How does it handle referential integrity to avoid constraint violations. Cause we have two databases that should be identical. rows are being added to tables in both databases simultaneously and at the end of the day, we expect the two databases to be identical.
e) What if there is a break in Communication between both databases?
What do you think is the best approach to such an issue?
I am kind of new to this. Any help at all will be very much
April 25, 2008 at 1:59 pm
1. If you are going to synchronize on a daily basis, then you are not updating both databases simultaneously.
2. I have no personal experience in DOING this, so everything I tell you is coming from reading BOL or other sources. There's no substitute for experience, but the literature does yield some insights.
3. It sounds like you are trying to operate under merge replication rules, but not requiring the 'simultaneous 24/7' that merge replication offers.
4. Something you've not mentioned is the sensitivity of the data that you are going to be shipping over long distances - presumably over non-dedicated, secured connections. That's another layer of complexity, if you need to address it.
5. What will be your criteria for resolving conflicts in updates - does the overseas office always yield to home office updates, or is it based on a pure time-stamp criterion? If time-stamp controls, be careful of time zone differences and how you will address those.
There will always be more questions, so keep them coming.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply