Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

What is the most efficient way to sync/mirror certain data from a large remote database with a local version?

The scenario is, there is a central remote database on a hosted server, which will share data with several smaller databases available. The problem is, each database will only require a part, and not all the data that is available in the main database.

For example the database server of company A should not sync and get the data that belongs to company B.

How can we restrict the syncing process so that the smaller databases request and sync only the required/relative data, and not the entire table(s) and/or central database, in order to reduce bandwidth usage and not compromise security.

share|improve this question

closed as not a real question by Chopper3, HopelessN00b, EEAA, Dave M, mdpc Jan 20 '13 at 23:41

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
What DB? throw us a bone here! –  Chopper3 Jan 20 '13 at 18:27
    
sql server: RTFM, "Replication" is part of it since - hm - v7. MySQL: don't use it. Mix: No easy way. –  TomTom Jan 20 '13 at 18:28
    
You mean what kind? Right now the main DB is an MSSQL DB, but the smaller databases can be either MSSQL or MySQL. Preferably MySQL if it won't complicate this implementation. But the main DB might be migrated to MySQL if need be, but that is a last resort. –  Green Dragon Jan 20 '13 at 18:29
    
@TomTom I was trying to ask if someone has had any experience with this before so I can check what is the best approach. Even tho there are some obvious solutions, I'm checking for the best one as well as people's opinion and advice. –  Green Dragon Jan 20 '13 at 18:45
    
@GreenDragon Myql complicates it. SQL Server has a good implementation. SqlServer only. You will need a consultant for that - this stuff is tricky by design, especially if you have multiple slaves and distributed updates. Any professional dba should be able to deal with this. –  TomTom Jan 20 '13 at 19:02

1 Answer 1

up vote 0 down vote accepted

I work for a company that implements this scenario all the time.

SymmetricDS supports a root SQL Server and MySQL clients.

How will you determine which rows need to synchronize with which clients? You will probably need to create a "column match router". This will let you filter which records are synchronized with each client by the value found in a specific column. Take a look at the following guide for the different methods to select what and where data gets synchronized.

http://www.symmetricds.org/doc/3.2/html-single/user-guide.html#configuration-column-match-router

You can download the open source version for free or pay for the pro version including a gui, support, and implementation.

OSS: SymmetricDS.org

Pro: JumpMind.com

share|improve this answer

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