April 23, 2008 at 3:37 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 a problem?
April 23, 2008 at 6:21 am
What you are describing is what merge replication was designed for. It does, however, present some interesting issues. If you have the ability to modify schema, you may be able to use it. One of the drawbacks is that it uses (and requires) a GUID and a TIMESTAMP column on each replicated table to ensure uniqueness and indicate changes. If you have the option, this is probably the way to go.
One of the big advantages to using merge replication will be the tools you get with it. You are going to have to manage replication conflicts - what happens when someone makes a different change to the same record at each end?
If you cannot make this kind of a schema change, transactional replication may actually work for you. It is designed more for 1-way communication (a publisher to a subscriber), but you can set up updatable subscribers that basically runs a batch on a regular basis to send changes back to the publisher. This is a bit of work to set up, but you would get a lot of automated tools in your favor again. You will get some issues with constraints and keys using transactional replication and that may be a lot to manage.
April 23, 2008 at 10:12 am
Thanks Michael for your response. Will look into it and give you a feedback.
April 23, 2008 at 11:16 am
I forgot to add, peer-to-peer transactional replication may work for you as well if you are using the Enterprise Edition of SQL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply