MySQL and VB.NET - Migrate

[anna7]

Hi,

how i can migrate a sql server database to mysql? I have an app (developed with VB 2003) that it works with a big DB SQL server. But the user wants to migrate to MySQL.

thanks in advance.

[gep13]

Hey,

What exactly are you trying to migrate, i.e. is it just data, or do you have stored procedures etc that you need to convert as well?

If you have stored procedures then you are basically going to have to completely rewrite them as there are major differences between the syntax that is used.

Have a look at this article here for an overview of the migration route:

http://dev.mysql.com/tech-resources/...microsoft.html

Also, have a look at SQLYog here:

http://www.webyog.com/en/

The enterprise edition has a migration tool that you can use for porting data. There is a trial version of it which you can use to try it out.

Hope that helps!!

Gary

[anna7]

thanks. I will look these webs.

I need to migrate all.

Which is most faster (or good): mysql o SQL server Standard?

regards

[gep13]

Hey,

That is not an easy question to answer. Both MySQL and SQL have both the pro and cons. If you are being instructed by your client to use MySQL then it looks like the answer in your case is a no brainer.

I personally use MySQL mainly because it is free, and so far I have not run into any problems with it, and haven't met anything that I can't do with it, but I am not running the database hard, it is a very simple database that I have.

Gary

[jmcilhinney]

As far as your application is concerned, you'll have to change all your SqlClient types to OleDb or use a MySQL-specific ADO.NET provider. All the patterns will be exactly the same but all the types will change, e.g. OleDbConnection or MySqlConnection instead of SqlConnection.

All the rest doesn't really have anything to do with VB.NET and belongs in the Database Development forum.

[gep13]

Ah yeah, good point, I wasn't thinking about the application, I was just looking at the Database side of things.

If you haven't already downloaded it, I would recommend that you use the MySQL ADO.Net Provider, you can find a link to it in my signature. You will have to take care with things like named parameters in your stored procedures, if you are using them, SQL uses @ and MySQL does in some places, but not all, so care will have to be taken to make sure that this works.

Gary

[jmcilhinney]

Gary, do you know whether version 5.0 and later of that MySQL connector support .NET 1.x? I'm guessing not and that version 1.0 would have to be used, although I don't know that for a fact.

[gep13]

Ah, another snag, never spotted the OP's requirement for 02/03.

The newest version of the MySQL ADO.Net Provider is 5.2.5 and it only supports .Net Framework 2.0 and onwards.

As a result, the OP will have to use the earlier version of the Connector (as you mentioned) specifically for 1.1 which you can find here:

http://dev.mysql.com/downloads/connector/net/1.0.html

However, it doesn't support all the new features of ADO.Net 2.0, so I guess whether it will be suitable will depend on what exactly the OP is doing.

Gary

[jmcilhinney]

The newest version of the MySQL ADO.Net Provider is 5.2.5

Keep up. v6 is out.

[gep13]

Ha Ha, yeah you are right, I have that installed, but it's still relatively new, and not as mature as the 5.x series, hence why I didn't recommend it straight away

[anna7]

thanks gep13. :-)

As far as your application is concerned, you'll have to change all your SqlClient types to OleDb or use a MySQL-specific ADO.NET provider. All the patterns will be exactly the same but all the types will change, e.g. OleDbConnection or MySqlConnection instead of SqlConnection.

All the rest doesn't really have anything to do with VB.NET and belongs in the Database Development forum.

yes, but all is related and i dont want to open 2 posts with the same thread.

[jmcilhinney]

Driving a car and fixing a car are related but that doesn't mean you learn the two in the same place. This forum is for VB.NET questions. How to connect to a MySQL database from VB.NET is a VB.NET question. How to migrate data and logic from SQL Server to MySQL has absolutely nothing to do with the VB.NET. They are two separate issues and belong in two separate threads... and two separate forums. Don't be afraid to create multiple threads when they are for multiple topics. Putting the same topic in multiple threads and multiple topics in the same thread are both great ways to make the forum more confusing.