I need help in regards to replicating data from a third party point-of-sale system with a SQL Server backend to a MySQL DB.
Specifically, can anyone suggest a best way to mirror data from SQL Server on our MySQL instance?
- Select a dataset from the distant SQL Server and write to a text file using PHP.
- Import the text file using LOAD INFILE using PHP.
- Some data sets must be chunked into several iterations.
- It is slow and cumbersome.
- Requires polling their server every x minutes for changes.
- Difficult to detect changes as most tables do not have an 'updated time' on the row to indicate a change. We therefore have to sometimes assume we will need the whole table.
- Tables are up to 11 million records (and growing).
- Bandwidth is being used inefficiently.
- Whenever the schema changes (short notice) on the SQL Server, we have to react to the changes and change every query.
- Our third party SQL Server host will not allow any changes to the system on their end.
- Our side (MySQL) is more flexible to system changes.
- We cannot change to a SQL Server platform as we are a LAMP shop.
- We are very capable IT developers, but this issue seems to warrant additional advice.
- Management is against an organic point-of-sale.