-2

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.

Green Dragon
  • 3
  • 1
  • 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
  • The problem is my experience lies with MySQL and even then it's at a junior level. Thank you for the advice TomTom. Any chance there is a technical keyword for this particular thing? I'd like to know exactly what to ask for and I'd appreciate if I can be pointed in the right direction. – Green Dragon Jan 20 '13 at 21:23

1 Answers1

0

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

Austin Brougher
  • 116
  • 1