Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by tasos petri
tasos petri
888 Points

Sync erp with opencart

I have an Erp software which uses mssql 2012 database. I also use opencart for my ecommerce solution. As you already know opencart uses mysql database.

I want to bidirectional sync the erp with opencart.

Erp will send categories, products, product options, stock to opencart And opencart will send orders to the erp.

I know the schema on both systems so I know which tables must be updated.

The question is What is the best method to sync two different databases? Especially when these two databases are different (mssql-mysql) and the schemas are completely different and also mssql is local but mysql can be accessed only remotely from the web server.

I searched and found the below options -Connect the mysql database as external to the sql management studio and run queries from one database to the other through sql agent. -Use of SSIS? -PHP? But I have little knowledge. -C sharp application? -Third party software like symmetricsds?

Please tell me your opinion. Thank you

1 Answer

Bruno Areias
Bruno Areias
5,181 Points
Bruno Areias
Bruno Areias
5,181 Points

I think your question is complicated to answer.

In my experience, sincronizing DBs through 3rd party or costum build software in the long run is never a good idea, eventually one or both DB will be out of sync or even inconsistent, so in my opnion, doing DBs sync in the way you plan I would try my best to leave that as a last resort option.

In your case, i would recomend you to leave the OpenCart and the ERP as is, don't change or force them to do things they aren't suposed to do, if you are planing to use tha DBs for a front end of some kind or merge the funcionality of both in one SW try to implement or check if they already have some kind of REST interface and use the REST of both to run your integration on the top of.

I don't mean to discurage you to do the sync, just keep it simple and stupid, if you have a major upgrade for the ERP or the OpenCart your sync has a great chance to break, while programming on top of the REST services, you will be able to upgrade the base systems more freelly as the applications will be 100% independent.

tasos petri
tasos petri
888 Points

You are very right, if the two systems are heavily modified, someday it will happen and there may be serious problems.

The erp has web services that create xml (get categories, get products...) but opencart does not have out of the box. I think there are some modules for opencart to do this. I will try to implement this with this method as you suggest.

Thanks for the answer!