Best way to synchronise data: MySQL -> SQL Server

Question

I have a fairly straight way to copy selective data using SQL like so:

# Courses
DROP TABLE db_node.courses;
CREATE TABLE db_node.courses LIKE db_prod.course_sis;
INSERT INTO db_node.courses SELECT
    *
FROM
    db_prod.course_sis
WHERE
    db_prod.course_sis.enabled = 1
AND db_prod.course_sis.hidden <> 1;

This is easy when I am on the same server with the same db, however I want to run this SQL to get put the final data on the SQL Server.

This isn't just a once off thing, it would need to be handled every hour or so. I am unable to change db's, one will always be MySQL and SQL Server because the data is used in a sharepoint app.

Thanks

Is this similar to setting up MySQL as a connection from the SQL Server (Linked Server?), then letting SQL Server running the SQL? — stabbie
– stabbie, Commented Aug 19, 2013 at 10:13
Not exactly. It is an ETL tool, that lets you transfer data between heterogeneous platforms — Raj
– Raj, Commented Aug 19, 2013 at 10:17

valentin · Accepted Answer · 2013-08-19 10:16:58Z

1

There are several third-party tools to make this transition easier. Check out SSMA here and here. I'd also look at SQL Studio scheduled tasks to automate the process.

answered Aug 19, 2013 at 10:16

valentin

6672 gold badges12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stabbie Over a year ago

Perhaps I can filter it before hand and then it just needs to replicate the whole database over. I'll check into the docs, thanks.

Collectives™ on Stack Overflow

Best way to synchronise data: MySQL -> SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related