0

I have 2 databases: MySQL and MsSQL. Inside of MySQL I have a lot of data, but I need to migrate this data to MsSQL. I tried to do this migration with

mysqldump -u root -p --compatible=mssql --database my_db > dump.sql

Then I faced an issue: Invalid mode to --compatible: mssql

How can I do the migration between MySQL and SQL server 2008?

UPDATE

As the result of the discussion I need to migrate just data from MySQL to the SQL server, because I already have db inside SQL server. Any ideas how to extract data and insert it to the SQL server?

Community
  • 1
  • 1
Artsom
  • 161
  • 3
  • 11
  • If you're migrating to SQL Server, why 2008? That only has 15 days left of (extended) support. i strongly suggest moving to a far more recent (and supported) version. – Thom A Jun 24 '19 at 11:32
  • @Larnu because I'm using this image https://hub.docker.com/_/microsoft-mssql-server?tab=description – Artsom Jun 24 '19 at 11:36
  • That image is for SQL Server 2017 or 2019 preview, not 2008. There is a **huge** difference between those versions... – Thom A Jun 24 '19 at 11:38
  • @Larnu is right, yoou should use the latest version of SQL Server (which is 2017 atm, 2019 will be released soon [see here](http://sqlserverbuilds.blogspot.com/) – Michael Tobisch Jun 24 '19 at 11:41
  • @Artsom which MySQL version are you using? MySQL 8 only supports [--compatible=ansi](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_compatible). Even [for previous versions](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_compatible), the generated scripts will be suboptimal to say the least. Different databases have different features that affect performance and design. Some features found in SQL Server 2008+ like `ROW_NUMBER()` and windowing functions were only added in MySQL 8. Previous versions used hacks to emulate ranking and windowing – Panagiotis Kanavos Jun 24 '19 at 11:45
  • @PanagiotisKanavos but ansi is not mssql, I tried to use --compatible=ansi as the result I server is getting stuck – Artsom Jun 24 '19 at 11:49
  • Considering the OP has stated they are using `docker` I have made the assumption they are not using SQL Server 2008 (which doesn't exist within docker, the first to was 2017) and are instead using SQL Server on Linux; which also substantially changes "the game". – Thom A Jun 24 '19 at 11:50
  • @Artsom that's what I said. If you target MySQL 8, you can't use any of the old options. Even if you could, the result would be a very inefficient databazse – Panagiotis Kanavos Jun 24 '19 at 11:51
  • @PanagiotisKanavos so, can I migrate just data between databases MySQL and MSSQL without schema? Probably you have some ideas on this point? – Artsom Jun 25 '19 at 08:03
  • @PanagiotisKanavos I want to do it, because I already have the database inside MSSQL with schema, whenever I need to migrate just data and don't want to do it manually – Artsom Jun 25 '19 at 08:58

0 Answers0