Update MySQL database from SQL Server in different domain

Question

I am SQL Server developer and the current assignment is little different than what I have done in past. I found Stack Overflow very promising for my problem. I am working on the SQL Server 2005 database for the internal application for my client and the client also got the public facing web application with MySQL database. I do not have any details about this web application, but I got the assignment to update the MySQL database (on public domain) from the SQL Server database (internal domain) on daily basis as auto process. How can I achieve this through the SQL Server?

Answer 1

You might want to try Pentaho Data integrator.

http://wiki.pentaho.com/display/EAI/Latest+Pentaho+Data+Integration+%28aka+Kettle%29+Documentation

The product would allow you to speak to both data technologies. (MSSQL+MySQL) You will find the product similar to DTS. You may be able to construct your solution will little to no code.

Answer 2

SSIS will do this just fine. The hard part is determining how you want to transform the data from one structure to the other (I assume they are not exactly alike in terms of table design.)

But basically you create a dataflow task, connect to the SQL Server for the source data and use a query to define what data you are going to copy, then you do any transformations needed to make the data fit into the MySQL structure and connect to a MySQL destination.

Repeat this process for mulitple data sets you want to send to differnt places.

Once the SSIS pacakge is done, set up configurations so that you can run the package on the production server (you will want to test development to development of course!) then schedule the package to run at an appropriate time.

Depending on how different the two databases are and how much data you need to move, this can be a relatively simple process or very complicated.