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.

teddy
  • 197
  • 2
  • 15
  • 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) – LuigiEdlCarno Sep 18 '13 at 08:27
  • I have had some luck with [Microsoft SQL Server Migration Assistant for MySQL](http://www.microsoft.com/en-gb/download/details.aspx?id=28764) - can use it to bring schemas and data in sync. – Bridge Sep 18 '13 at 08:57
  • You can refer this existing link where `How to migrate from MSSQL to MySQL` has been mentioned http://stackoverflow.com/questions/3917081/how-to-export-ms-sql-database-to-mysql –  Sep 18 '13 at 09:58

2 Answers2

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=...
Kuzgun
  • 4,649
  • 4
  • 34
  • 48
  • I am trying as your suggestion. Is it run whenever there is updated at source DB ? – teddy Sep 18 '13 at 10:12
  • 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. – Kuzgun Sep 18 '13 at 10:41
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.
karthik
  • 17,453
  • 70
  • 78
  • 122