1

We have SQL Server databases in every retail store, and one database in main office. We want to collect data from all stores and load the main DB (for BI), but we also want to sync data from main DB to retail DBs, when new product is awailable for selling. The solution has to provide reliable and scalable mechanism for data synchronization, using transactions, tracking the data that is not synced. I read about SSIS and tools, I was wondering if SSIS is good choice for this task.

vpetrovic
  • 121
  • 4
  • I don't have much hands-on experience with database maintenance and setup beyond simple installation, but this strikes me as a very fragile setup and one likely to create many many headaches in your future if something should go wrong. You might consider switching to Big Data for your database services, though depending on how the program is written, that may be somewhat straightforward or completely impossible to do. – Neil Jun 13 '17 at 07:23

1 Answers1

1

We want to collect data from all stores and load the main DB (for BI),

SSIS (or other ETL software) is often used for loading a "Data Warehouse" for business intelligence purposes. This sounds a lot like what you're trying to do. SSIS can have all sorts of transformations that will help you get the data in a good shape for conducting BI on it efficiently.

we also want to sync data from main DB to retail DBs, when new product is awailable for selling

This you can also accomplish with SSIS. Since this sounds more mission critical, it's important that you have good monitoring and tracking in place. You should know if a retail store wasn't updated for some reason and you may have to run consistency checks to make sure the product table for each retail store is complete.

A better architecture would involve the retail store software hitting a web service at your main office to see which products are available. This would guarantee that no products are sold that aren't supposed to be sold, and it would immediately reflect new products. But I understand this kind of architecture may not be possible because internet connections may not be reliable at the retail store.

Samuel
  • 9,137
  • 1
  • 25
  • 42
  • Thanks for clarifying some things. I am exploring SSIS for all these purposes. Internet connection would not be a problem, all stores are in VPN. – vpetrovic Jun 14 '17 at 05:25