Best way to sync data from one database to another

Question

Let's say I have my web shop and database have multiple tables like Products, Product prices and similar. I'm selling my software solution to my affiliate partner. He gets its database, it window application for entering data and I want all he's products are available on my website also. So, I have my products and sell them on my website. Partner has his website and his products, but I also want that all his products are also available in my database. My initial idea was to build some kind of app that periodically connects to Partner's database and insert/update data to my tables. But recently I think about some service buses. Is service bus way to go ? So situation would be like this. Partner inserts product (bus sends message that products was inserted, partner updates prices then bus sends message that some row in prices row was changed. I would then have some console app running on server that listens to message and updates to my database. What do you think would be scalable solution? Example is fictional and in real life I have lot of tables.

Answer 1

Firstly you need to ask yourself some questions:

1. do you need to sync the data in realtime, quasi-realtime or in sort of backup mode (like once per day).
2. when moving the data are you manipulating it or just fully replicating to another data base (maybe of some time).
3. what is the amount of data per, say minute? Is it 1000 records or 100000 records? Are there may relations in your db that need to be replicated on other end? Or maybe the other end is schemaless so you don't care?
4. What is the technology on both sides? For example, MS SQL sever has built-in replication functionalities, at least 3 options as I recall.
5. Is there any problem with connection between 1st and 2nd storage? Are they in same network, same organization maybe? Or maybe they are in 2 different networks VPN connected.
6. How will you mark records that have been replicated?
7. How will you discover that records have been updated and need to be replcated again (triggers? - but can add them to each table)?

Each answer can be decisive. Even in 2017 we can still find synchronization by FTP files, because of non-technical reasons.

Console application? Only if run periodically from windows scheduler, never use it as permanent service - this is what linux deamons / windows services are for. Service bus - sounds cool, as always, but again, if you need to replicate db wth lot of relationships, it might turn out, you're pulling half database for each transferred object. Hope this helps you choose a solution, Łukasz

Answer 2

Are you handling the database for your partner? If not, i don't think they giving you access to their database is a good idea (security concerns). Another thing: you probably want your products separated from those of your partner(s).

In any case, service bus is part of your solution, just as a message handler. So what you need is this:

• Something to send new and updated products from your partner's side to yours (that can very well be a job sending messages to a service bus).
• Something to listen to the message queue and grab the products and insert them into your database in the form you like.

Scalability depends on your platform. But even for small, on-premise solutions, this seems very small and able to work with reasonable amounts of data (nothing amazon sized).