MS SQL database to MySQL

[ViviTheMage]

I loaded 2005 MS SQL express onto my machine, loaded the .bak I received and tried the MySQL migration method, but for the life of me, it would not connect up to the database I had running on my system.

Is there a not so complicated method to convert from MS SQL to MySQL?

Anyone have experience in this area? I am willing to spend some money on this conversation if someone knows how to do it, and it works correctly.

 

[Markbnj]

Conversion of table structures is probably no big deal. There may be some minor differences in data types. I would install MySQL and then convert the tables one by one to make sure I got the types right. Where you'll run into larger issues is with stored procedures, user functions or triggers (if you have any).

But it sounds like you're having basic connectivity issues. Which drivers did you install?

 

[ViviTheMage]

Yeah...I am no good with MS SQL stuff, so I really do not know. I was hoping for strait forward, and that it is not!

I do not even know it installed drivers..it just plopped the database into 05 express, and I was able to connect with this free ms sql -> mysql tool but that errored out, it just seems the migration tool for MySQL requires a lot more information, IP, log in info, etc...when it's just on the local machine...I tried localhost, my PC name, the databases name, etc, etc.

Should I try 08 express? It's a 4.8mb file that a client gave me (I do not THINK there are triggers/functions), which is why I am willing to pay to have it converted it for him. (I have the .BAK file from the MS SQL database server that it was previously hosted on)

 

[Crusty]

We need more information about your setup.

What OS?
Where is the MSSQL server installed?
Where is the mysql server installed?

By default mysql will create a user named 'root' with no password that can be accessed from the localhost only. During the setup it should have asked to put in a password if you wanted to, but you could have still left it blank. Does the tool require the ODBC driver for mysql or does it communicate with the server using the native mysql protocol?

 

[ViviTheMage]

Originally posted by: Crusty
We need more information about your setup.

What OS?
Where is the MSSQL server installed?
Where is the mysql server installed?

By default mysql will create a user named 'root' with no password that can be accessed from the localhost only. During the setup it should have asked to put in a password if you wanted to, but you could have still left it blank. Does the tool require the ODBC driver for mysql or does it communicate with the server using the native mysql protocol?

windows xp pro sp3
ms sql is hosted on this machine, via 2005 express
I have a mysql server online, but all I am trying to do is convert the ms sql database via the migration tool so I can upload it to the web server.

 

[Crusty]

I'm betting the migration tool is wanting to connect directly to the database to do the import and it won't be able to dump a datafile. You could use a local install of mysql to do the migration and then use mysqldump to dump the whole database and upload it to your remote mysql server.

 

[ViviTheMage]

Originally posted by: Crusty
I'm betting the migration tool is wanting to connect directly to the database to do the import and it won't be able to dump a datafile. You could use a local install of mysql to do the migration and then use mysqldump to dump the whole database and upload it to your remote mysql server.

But if the migration tool cannot connect to the MS SQL database, I am hosed.

 

[GilletteCat]

Originally posted by: ViviTheMage

Originally posted by: Crusty
I'm betting the migration tool is wanting to connect directly to the database to do the import and it won't be able to dump a datafile. You could use a local install of mysql to do the migration and then use mysqldump to dump the whole database and upload it to your remote mysql server.

But if the migration tool cannot connect to the MS SQL database, I am hosed.

No, you are not. What Crusty is suggesting is to install MySQL locally, on the same pc, and use it to connect to the running instance of MS SQL and import the data. Then dump it and upload the dump file to the remote system.

 

[KLin]

What are you using for the servername in the migration tool? You should be using SERVERNAME\SQLEXPRESS since it's a named instance.

 

[Crusty]

Originally posted by: GilletteCat

Originally posted by: ViviTheMage

Originally posted by: Crusty
I'm betting the migration tool is wanting to connect directly to the database to do the import and it won't be able to dump a datafile. You could use a local install of mysql to do the migration and then use mysqldump to dump the whole database and upload it to your remote mysql server.

But if the migration tool cannot connect to the MS SQL database, I am hosed.

No, you are not. What Crusty is suggesting is to install MySQL locally, on the same pc, and use it to connect to the running instance of MS SQL and import the data. Then dump it and upload the dump file to the remote system.

Well at first I assumed he was having trouble connecting to the mysql instance, but it appears he's having problems connecting to the mssql instance?

I'm not too sure now though, there aren't enough details or even any error messages to try and figure out what's going on other then making a rather uneducated guess.

 

[ViviTheMage]

Originally posted by: GilletteCat

Originally posted by: ViviTheMage

Originally posted by: Crusty
I'm betting the migration tool is wanting to connect directly to the database to do the import and it won't be able to dump a datafile. You could use a local install of mysql to do the migration and then use mysqldump to dump the whole database and upload it to your remote mysql server.

But if the migration tool cannot connect to the MS SQL database, I am hosed.

No, you are not. What Crusty is suggesting is to install MySQL locally, on the same pc, and use it to connect to the running instance of MS SQL and import the data. Then dump it and upload the dump file to the remote system.

You can run SQL in a windows environment? Why would I need to if the MySQL migration tool will connect right up to the MS SQL database, or so it says it should.

Originally posted by: KLin
What are you using for the servername in the migration tool? You should be using SERVERNAME\SQLEXPRESS since it's a named instance.

That is exactly what I used.

Originally posted by: Crusty

Originally posted by: GilletteCat

Originally posted by: ViviTheMage

Originally posted by: Crusty
I'm betting the migration tool is wanting to connect directly to the database to do the import and it won't be able to dump a datafile. You could use a local install of mysql to do the migration and then use mysqldump to dump the whole database and upload it to your remote mysql server.

But if the migration tool cannot connect to the MS SQL database, I am hosed.

No, you are not. What Crusty is suggesting is to install MySQL locally, on the same pc, and use it to connect to the running instance of MS SQL and import the data. Then dump it and upload the dump file to the remote system.

Well at first I assumed he was having trouble connecting to the mysql instance, but it appears he's having problems connecting to the mssql instance?

I'm not too sure now though, there aren't enough details or even any error messages to try and figure out what's going on other then making a rather uneducated guess.

Sorry, I was just attempting something I read online, and it proved to be taking a lot more time then I have. Which is why I am kind of asking if anyone will do it for a couple dollars, or know of a tool for a couple dollars that can do it for me.

I am kind of doing this as a favor for a client, which is why I thought i'd ask here before I say I cannot do it.

 

[Crusty]

I still have no idea what you are trying to do. Are you running the tool remotely on your server with mysql installed and assuming it's going to connect to a local mssql server? Your lack of details is frustrating me!

 

[ViviTheMage]

Originally posted by: Crusty
I still have no idea what you are trying to do. Are you running the tool remotely on your server with mysql installed and assuming it's going to connect to a local mssql server? Your lack of details is frustrating me!

everything is on my local system (xp pro sp3), ms sql 2005 express, the mysql migration tool.

nothing is remote.

I loaded a .bak that is off of a clients old hosting company, and loaded it into the 05 express server I have locally. Ran the migration tool and the errors it basically gives me are, that it could not connect to the ms sql database.

I have tried many variations of ip/username's and no luck.

 

[Crusty]

Is it timing out? Is it telling you invalid username/password? What exact error message are you getting?

 

[ViviTheMage]

Originally posted by: Crusty
Is it timing out? Is it telling you invalid username/password? What exact error message are you getting?

a networking IO message is what I was getting. I uninstalled everything though...I really do not want to go down that route again .

Want to do it for me? I will pay you!

 

[KLin]

How big's the bak file? Send it to me and I'll extract the data for you.

 

[ViviTheMage]

Originally posted by: KLin
How big's the bak file? Send it to me and I'll extract the data for you.

4.3MB.

What's your email?

 

[KLin]

Originally posted by: ViviTheMage

Originally posted by: KLin
How big's the bak file? Send it to me and I'll extract the data for you.

4.3MB.

What's your email?

check your PM.

 

[KLin]

This mysql migration tool is pretty cool.

 

[GeekDrew]

Originally posted by: KLin
This mysql migration tool is pretty cool.

OK already, what tool is it?!?!?

 

[ViviTheMage]

Originally posted by: GeekDrew

Originally posted by: KLin
This mysql migration tool is pretty cool.

OK already, what tool is it?!?!?

MySQL Migration Tool.

http://dev.mysql.com/downloads/gui-tools/5.0.html