3

I have one MS SQL DB (very big) and MySQL. I must move data from MS SQL DB to MySQL by doing SQL. It mean that source data & destination data arenot same structure. When source data is updated, the updating is reflected into destionation. Please give me an advice which is the suitable way?

  1. Using SSIS
  2. Using store procedure & write my own script
  3. Other way.

Any advice is highly appreciated.

Improve this question
3
  • That depends how different the structure is. But as a general hint: have a look at linked servers in sql-server. You may be able to read the data from sql-server and insert it into your MySQL DB using one script (per table or similar depending on your structure) Commented Sep 18, 2013 at 8:27
  • I have had some luck with Microsoft SQL Server Migration Assistant for MySQL - can use it to bring schemas and data in sync. Commented Sep 18, 2013 at 8:57
  • You can refer this existing link where How to migrate from MSSQL to MySQL has been mentioned stackoverflow.com/questions/3917081/… Commented Sep 18, 2013 at 9:58

2 Answers 2

Reset to default
1

Cleanest way will be using stored procedures since it is not a complete immigration. By doing this you will have everything under control. MSSQL has a nice feature linked servers, a bit slow but worths it in most of cases. You can find instructions here: http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx

An example:

UPDATE your_mysql_database.dbo.your_mysql_table SET col1=...
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I am trying as your suggestion. Is it run whenever there is updated at source DB ?
Linked server only makes other servers available to call. It is like calling another mssql server on the same server. To make it run whenever your actual database gets updated, you can add a trigger to execute your stored procedure.
0

To migrate from MSSQL to MYSQL, Please use MYSQL Migration Toolkit

To download and use the MYSQL Migration Toolkit, Please use the following link http://dev.mysql.com/downloads/gui-tools/5.0.html

The MySQL Workbench Migration Wizard is designed to save DBA and developer time by providing visual, point and click ease of use around all phases of configuring and managing a complex migration process:

     Database migrations - enables migrations from Microsoft SQL Server, PostgreSQL, Sybase ASE, Sybase SQL Anywhere, SQLite, and more.
     Migration project management - allows migrations to be configured, copied, edited, executed and scheduled.
     Source and Target selection - allows users to define specific data sources and to analyze source data in advance of the migration.
     Object migration - allows users to select objects to migrate, assign source to target mappings where needed, edit migration scripts and create the target schema.
     Data migration - allows users to map source and target data and data types, set up data transfer and assign post data transfer events where needed.
     Version Upgrades - using migration users can easily move databases off older MySQL versions to the latest.
Improve this answer

4 Comments

I think this is more ETL than straight migration.
@DrewR: it will handle more complex structures also.
Extract, transform, load (the data)
@DrewR Thank you for your sharing. You are right. It is more ETL than straight migration

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.