Sql-server – Effective way to migrate from SQL Server to MySQL

migrationMySQLsql server

I have a 20GB Microsoft SQL Server database that needs to be migrated to MySQL.

Right now I only have the schema for the source db. I tried the migration wizard on the schema in MySQL Workbench and it worked out fine (with some minor varchar issues that was easily solved)

As I am waiting for the data I am wondering if MySQL Workbench is the way to go here as the source is 20GB.

Can the Workbench handle it? Is it effective?
Should I investigate other tools than the Workbench?

Any advice and pointers is appreciated.

Best Answer

If you're looking for an easy-to-use, open-sourced tool, checkout etlalchemy.

You can carry out your SQL Server to MySQL database migration with 4 lines of Python code:

To Install:

pip install etlalchemy

(On El Capitan you may have to run pip install --ignore-installed etlalchemy)

To Run:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

mssql_db_source = ETLAlchemySource("mssql+pyodbc://username:password@DSN_NAME")

mysql_db_target = ETLAlchemyTarget("mysql://username:password@hostname/db_name", 
              drop_database=True)

mysql_db_target.addSource(mssql_db_source)
mysql_db_target.migrate()

If you want to learn more about etlalchemy, check out this article.

Related Solutions

Sql-server – SQL Server to MySQL migration – how can I remove UCS-2 surrogate pairs in SQL Server

You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example:

echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd
0000000: 3307

Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733. If you have \0xDCB3 in the SQL Server it means it was not translated into UCS-2LE before import. You should not have surrogates in the NVARCHAR fields, UCS-2 is "surrogate agnostic". See UCS-2 vs. UTF-16 (not quite Kramer vs. Kramer).

I'm not an expert in the MySQL tool set so I can't say what step is missing that was supposed to do the iconv.

Update

to locate the records with surrogates you must turn to the binary representation, since any character function will threat the surrogates as 'special':. Luckily the string manipulation functions work on binary too with the expected semantics. Eg. CHARINDEX:

insert into test(a) values  (N'a');
insert into test(a) values  (NCHAR(0xdc83));
insert into test(a) values  (N'b');
go

select * from test where charindex(0x83dc, cast(a as varbinary(8000))) > 0;

Sql-server – Migrating from SQL Server to MySQL using MySQL Workbench tool

Well, since you have data currently stored in SQL Server, and it's already in an NVARCHAR column, then either it's an NVARCHAR <= 4000 (in which case you can't lose any data, and should just change all instances of NVARCHAR(8000) to NVARCHAR(4000)), or it's an NVARCHAR(MAX) column, in which case you change all instances of NVARCHAR(8000) to NVARCHAR(MAX). Or just leave out those CASTs - do you really need them?

As an aside, you should probably change as NTEXT to as NVARCHAR(MAX) as well.

Best Answer

Related Solutions

Sql-server – SQL Server to MySQL migration – how can I remove UCS-2 surrogate pairs in SQL Server

Sql-server – Migrating from SQL Server to MySQL using MySQL Workbench tool

Related Question