Sychronizing two databases

Question

Hi,

I have a database in MySQL and another database that runs on MS SQL. The MySQL is the backend database for my website running on Joomla. I have an ERP running my store. This ERP is made by a 3rd party in .Net

A table called the orders gets updated whenever a user places an order in my website. The order details must get flushed to my orders table in my ERP. The table structure in the two databases are totally different so I will do the mapping myself.

My questions are:

1. How frequently should I transfer the data from my MySQL database to MS SQL?
2. Someone suggested that I could write a web service that would periodically pump data to my table in the ERP. So I started thinking about Nusoap webservices. Is this the right way or is there a better way to do it ??
3. I will also have to retrieve inventory-related information from my ERP to my MySQL database.

Answer 1

1. How up to date do you need the ms sql database. That is going to be the deciding factor
2. I don't see any huge advantage to this being a web service.
3. This isn't a question.

Answer 2

1: Depends on how often your data is changing, and how often you need to sync up (i.e., depends on your business).

2 & 3: A web service to transfer data could work just fine. But unless you're trying to come up with a general solution, this sounds like a lot more trouble than it's worth.

If I were doing this, I would export the data from Sql Server to a file, then import that file into mysql (mysql my_db < file.sql).

Getting data OUT of sql server in this format isn't so easy (there's no equivalent to mysqldump on Sql Server). But check out this question for some ideas.

If the data itself is compatible between systems (if the columns are equivalent data types), you can overcome the table structure differences by just creating a query in SQL Server which exports the data in the correct order.

In fact, you may be able to create a query who's output is the file.sql for import into mysql. For example, a query such as:

SELECT CONCAT(
    'INSERT INTO MYTABLE VALUES (', 
    myColumn, 
    ',', 
    myOtherColumn,
    ');'
) AS SQL_STATEMENT

Produces output something like:

INSERT INTO MYTABLE VALUES (myColumnValue1, myOtherColumnValue1);    
INSERT INTO MYTABLE VALUES (myColumnValue2, myOtherColumnValue2);    
....

I've exported data from sql server that way on at least one occasion.

Answer 3

Deciding how often you transfer the order across is a business decision not a technical one. But it is hard to see what competitive advantage you might gain from not processing your customers' orders as soon as possible, so it ought to be a no brainer.

Without knowing a lot more about your infrastructure and architecture we cannot give you definitive advice about approach. I would expect a decently written ERP package to include interfaces for importing and exporting information. Alas such expectations are often confounded. If you do need to write your own interface, avoid web services. Unless you have a very peculiar set-up all WS will mean is that it will take longer to satisfy your customers. I think we have already agreed that is not a good idea.

Considerations for a Syncronization API:

1. You need to track which new orders have not been transferred to the ERP database. A flag is clumsy, a queue is perhaps more elegant.
2. Have a job/daemon polling continuously to identify orders which need to be transferred and transfer them in near-real time.
3. Have a plan for handling the unavailability of the ERP database.
4. Construct the mapping in a modular fashion so you do not have to rewrite the entire thing just because of a change to the structure of one of your tables.
5. The inventory data will probably have to be pulled from the MySQL database, as it seems unlikely that the third party will allow you to put code into their database. But it's worth reading the contract.

Answer 4

Okay based on the replies I got I will rephrase my question giving more details.

I have an eCommerce portal running on Joomla and Virtue mart (never mind what they are !!) The backend database here is MySQL. I have an erp written in .net by my friend and the Db used there is MSSQL Now I am going to host my eCommerce portal.

Following are actions that will take place and questions related to the actions

Action 1: At the start of the day my friend updates inventory of various products on and erp table question: I want the updated inventory from the erp (MS SQL) to get reflected on my website database (MySQL) automatically. How do I do it ?

Action 2: People come to my site and place orders.These orders are stored in an order table in my website(MYSQL). Question 2: I want these update orders related data from my website (MySQL) to be updated on a corresponding table in my erp (MS SQL)

More over the db structures of the tables in my erp and my website are completely different