Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

have a bit of conundrum a the minute.

ANSI sql solutions please.

We have two databases located at two different geographic locations and will be using the web for all traffic between the two locations. We have a few tables common between the two databases that need to be sync'd periodically (i.e. at the end of the day) With such syncs, we are worried about many things like security, congested traffic (could be thousands of records),data integrity.

The end game is to store files in each record (up to 10mb) so the sync needs to be efficient. (Not a priority at the moment)

There may be records that need to be sync'd both directions (i.e a column on one updated, then a column on two updated, the one record needs these two changes)

What software/hardware options are there out there ?

Is there another way without using triggers (want to get away from this).

Thanks, been a while since I dealt with these technologies

share|improve this question
is there a reason why you have 2 databases instead of 1. What you gonna do if there's a conflict on a record? – Marc Oct 9 '12 at 19:51
2  
You've tagged this for both Oracle and SQL Server. Does that imply that one of the two databases in question is Oracle and the other is SQL Server? Bidirectional replication is made much more challenging if you are trying to work with a heterogeneous environment. Is there some way to identify changes (say, for example, a timestamp column in every row of every table)? Does your request for ANSI SQL solutions imply that you want to roll your own replication process rather than using one of the many replication tools out there? – Justin Cave Oct 9 '12 at 21:01
Yes we would have some sync yn column probably. Would having some sort of junction interface apply here ? – TheMayne Oct 9 '12 at 21:45
Per Justin's comment, this question is unanswerable without knowing more about the environment and requirements. – Jon Seigel Oct 10 '12 at 17:19
Ok so the main db will only ever pull and push the data to the isolated db. The isolated db with a minimum amount of common tables will just interact with users. The traffic only flows in one direction (from the main db). SQL Server may be able to handle this task alone but it requires different solutions for the various db types. Looking for a common solution – TheMayne Oct 11 '12 at 22:30

migrated from stackoverflow.com Oct 10 '12 at 9:20

2 Answers

up vote 4 down vote

This task is much more complicated than it seems at first. I suggest you do not try to homebrew your own solution, use the replication facilities of your DBMS or use a third party solution, You can also try an opensource replication system SymmetricDS It talks between instances over http, can do payload compression, smart enough to resolve simple conflicts etc. And yes it uses triggers, you do not really have many alternatives in that regard.

share|improve this answer
The safest thing seems to be only sync overnight when theres no possibility of data written on both ends. It doesn't seem the most adept solution – TheMayne Oct 9 '12 at 21:42
Depending on the count and the size of data, replication may take long enough time to fail that assumption. – alikox Oct 9 '12 at 22:45
Really ? Maybe the tables need to be polled periodically instead – TheMayne Oct 11 '12 at 22:32
Polling main data tables will be inefficient (you need to know which data is changed). You can keep the changes in a separate table and periodically poll it. – alikox Oct 12 '12 at 13:39
up vote 3 down vote

ANSI is the standard for the language, not for the task you need. The simplest way is to use your RDBMS provides (You mentioned rather different ones). In case of Orace - GoldenGate/Streams or materialized views. If you don't like dbms-based solution mess about Talend http://www.talend.com/ - it could help you presumably. But the main problem remains - data conflict resolution...

share|improve this answer
The reason I said ANSI is because we support a variety of DB's. All our products are developed in ANSI sql as in the past we've had huge compatability issues when upgrading our infrastructure/software. We have our own homebrew solution but it's dated and we havn't been involved in the field in some time. We were hoping there were magic new solutions ! :) – TheMayne Oct 9 '12 at 21:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.