Migrate 10 Gb database from MSSQL to MYSQL

Question

Post reply

Migrate 10 Gb database from MSSQL to MYSQL

Saran

Right there with Babe

Points: 760
More actions
November 2, 2022 at 9:40 am

#4107681

Hi All,
Does anyone have experience in moving /migrating database from mssql to mysql. I have tired in test server, 10gb took more than 4 hours by using mysql workbench tool (Still running with unresponsive state). I am thinking to export each table to CSV and import it.
Any suggestion pls.
November 4, 2022 at 11:36 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Question: what kind of connectivity do you have between the two instances?

😎

Migrating the data via CSV export/import is one of the most labour-intensive methods.

Saran Right there with Babe Points: 760 More actions · Answer 2

Saran

Right there with Babe

Points: 760

November 2, 2022 at 12:18 pm

#4107722

Hi,

I have created ODBC connection.

Saran Right there with Babe Points: 760 More actions · Answer 3

Eirikur Eiriksson wrote:

Question: what kind of connectivity do you have between the two instances? 😎 Migrating the data via CSV export/import is one of the most labour-intensive methods.

If you have any other suggestion for connectivity and data transfer, I can try that as well. Only issue we cannot go with cost to buy any tool.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Questions:

What is the operating system of the MySQL server?
Can you connect in both directions, just thinking if you have both push and pull options?
Have you created the schemas and objects in the MySQL database?
Are there constraints in the database (unique, foreign key etc.)?

😎

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Can you make a linked server to MYSQL ? Then use that to push data ?

Saran Right there with Babe Points: 760 More actions · Answer 6

homebrew01 wrote:

Can you make a linked server to MYSQL ? Then use that to push data ?

I have linked server from MS to MYSQL, How to push the data? Cannot do insert into select * from mssql.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 7

if workbench is that slow it may have to do with your server specs - or with your network.

can you tell us the specs of both servers - as well as the route taken between source , workbench and destination.

e.g. is it local network, is it cloud, mix of both.

and is workbench running in windows or linux

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

Further on Frederico's post, can you supply the answers to my questions and his, please? Without the information, it is going to be really hard to help you!

😎

A third-party tool for the job is around $200, an elbow grease method might take you a few days 😉

Saran Right there with Babe Points: 760 More actions · Answer 9

frederico_fonseca wrote:

if workbench is that slow it may have to do with your server specs - or with your network.
can you tell us the specs of both servers - as well as the route taken between source , workbench and destination.
e.g. is it local network, is it cloud, mix of both.
and is workbench running in windows or linux

Hi,

I am doing testing, both SQL and MYSQL on same server. I tis a VM running on Vcenter.

Specs - 4 vCPU, 8 GB of RAM.

I can migrate small database ex: 500 MB. I have one 7 GB database, tired to exclude top 5 big tables as well.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 10

and you running workbench on same server?

if so then in all likelyhood your server is maxed - such a small spec for 2 db servers is bad - and if you running user side programs on it then even worst.

but speak with your VM admins to see how performance on that server while you are doing the migration.

if you are running on your own pc then you still need to tell us what your network is (between your PC and the serve)

one other thing you can try is to use the file based migration option on the workbench - for such a small db it should work ok.

Saran Right there with Babe Points: 760 More actions · Answer 11

Hi,

I do not think it is VM or resource issue. I have same setup in my local laptop as well, 8 CPU with 16 GB RAM on SSD. DB size is 1 GB, same happening in workbench. It says not responding and I left for 2+ hours and killed after that.

Is there any other best way to move data from MSSQL to MYSQL.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 12

frederico_fonseca

SSCoach

Points: 16155

November 6, 2022 at 2:47 pm

#4109290

DID you try the script file option on workbench?

frederico_fonseca SSCoach Points: 16155 More actions · Answer 13

so... decided to experiment it myself.

Workbench data migration is a pile of crap.. no wonder you are having issues.

your best option is to use the option "create a batch file to copy the data" or the "create a shell script to use native server dump".

in either case the scripts maybe incorrect (the second one is for sure) and depending on the datatypes on your source tables you may need to change the queries within the scripts (if you have anything defined as SYSNAME you need to convert them as SYSNAME is NVARCHAR but workbench treats them as VARCHAR so you may loose data)

if using the first option you may need to change the server "max_allowed_packet" - required for some big column sizes

if using the second option you need to ensure that the startup variable/config --secure-file-priv is set to the folder you put your files in to be imported.

good thing is that either of these 2 options are fast enough