Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

Current process:

  • 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.

Drawbacks:

  • 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.

Additional Circumstances:

  • 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.
share|improve this question
2  
What is your data latency threshold? – swasheck Jun 19 '12 at 20:22
I'm not sure and perhaps I'm not providing and intelligent response; but the response times are fairly slow and it takes 10-90 seconds per update. Some updates timeout altogether and have to be retried. – Kevin Giszewski Jun 19 '12 at 20:28
1  
Sorry for my lack of clarity. How immediate do changes in the MSSQL instance need to be reflected in your local MySQL install. – swasheck Jun 19 '12 at 20:37
We grade the data update frequency based on systems that use it and by table. So some tables replicate every 5 (minutes), 15, 60, daily and weekly. We did that so we're not clogging the bandwidth with relatively static data. Not ideal. Ideal would be update the changes only as they happen. – Kevin Giszewski Jun 19 '12 at 20:43
1  
Scheduled SSIS package(s) – Eric Higgins Jun 19 '12 at 22:35
show 1 more comment

migrated from stackoverflow.com Jun 19 '12 at 22:30

1 Answer

up vote 2 down vote accepted

Does the data actually need to be replicated in the same model?

What will you be doing on the MySQL side with the data?

I would strongly consider putting either another database or instance which you control on the SQL Server side to be able to take snapshots on that side so you pull less data down (comparing the snapshot to live data should be quick) and also look at what you want to do with the data on the MySQL side, since it is possible that you actually want to transform the data during the extraction to better facilitate the load.

I think you will find that having a sandbox database or instance which you do control collocated with the vendor database will give you a tremendous amount of power, while still allowing the vendor to operate without interference.

This can also help in reducing the bandwidth (unneeded columns, denormalizing usually increases data size, but not always) and reducing the load times or the complexity of the load process. A different model on the MySQL side which corresponds to the queries you will actually be running can also improve performance of reporting or even the load.

Having views against the source data can also help mitigate against changes - although you might not be able to use DDL triggers and schemabinding to stop changes from breaking dependencies, you could possibly catch schema changes when the extraction fails instead of after the extraction has neglected to bring down some new columns.

share|improve this answer
It doesn't have to be replicated as-is (schema-wise) but the consumers of the information could easy want a piece of data that we chose not to replicate. Our current approach is replicate as-is to avoid having to piece meal together extra columns as needed (possibly flawed reasoning). – Kevin Giszewski Jun 20 '12 at 16:33
The end usage of this data feeds 'bolt-on' applications and custom metrics. The obvious solution is to have the original system incorporate what we need, but the POS is a 'one-size-fits-many' application and the devs aren't very warm to add-ons. There is no SDK or API to the POS. – Kevin Giszewski Jun 20 '12 at 16:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.