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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a bit of a conundrum at the moment.

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), and 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.


migration rejected from Jul 2 '13 at 22:35

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as too broad by Nick Chammas, Mark Storey-Smith, Jon Seigel, RolandoMySQLDBA, swasheck Jul 2 '13 at 22:35

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the 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
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