My employer is changing hosting providers. Our current hosting provider offers MSSQL, which we use for the database used on our website. The host we are changing to is insisting that we switch to MySQL because they only offer an older version of MSSQL and
are concerned about compatibility issues.
I have absolutely no experience working with MySQL and am a novice with MSSQL as well. What is the best way for me to port a database from one to the other?
I have downloaded MySQL and Workbench... I've also downloaded the .Net connector as a zip file but am not sure where to extract its contents.
Personally, I would build a script file that will create all of your objects (tables, views, stored procedures, etc.) and then populates the tables with the data inside the script itself. Then, on an experimental basis, attempt to run the script in MySQL.
What will likely happen is that you will get a series of errors due to the differences in SQL syntax between MS SQL Server and MySQL.
An alternative would be to try to export your MS SQL Server database to MySQL using the Export feature. This, too, may be a trial and error process. Also, when the export has been completed, you will still need to add other objects such as views and stored
procedures.
Christopher Reed, MCT, MCSD, MCPD, Microsoft Specialist, MTA, MCTS
"The oxen are slow, but the earth is patient."
If you do not use any ORM (like Nhibernate ) it will be very difficult to pass from MSSQL to MySql. It will be easier from a version of MSSQL to another version of MSSQL.
Do you have tests ? If not , I suggest start with defining tests for important features.Then you could define your application like here(self advertising):
Member
316 Points
566 Posts
Porting MSSQL Database To MySQL
Jun 23, 2010 10:47 PM|drocco|LINK
My employer is changing hosting providers. Our current hosting provider offers MSSQL, which we use for the database used on our website. The host we are changing to is insisting that we switch to MySQL because they only offer an older version of MSSQL and are concerned about compatibility issues.
I have absolutely no experience working with MySQL and am a novice with MSSQL as well. What is the best way for me to port a database from one to the other?
I have downloaded MySQL and Workbench... I've also downloaded the .Net connector as a zip file but am not sure where to extract its contents.
Any advice would be greatly appreciated. Thanks!
Star
13130 Points
4049 Posts
Re: Porting MSSQL Database To MySQL
Jun 24, 2010 12:18 AM|Careed|LINK
Personally, I would build a script file that will create all of your objects (tables, views, stored procedures, etc.) and then populates the tables with the data inside the script itself. Then, on an experimental basis, attempt to run the script in MySQL. What will likely happen is that you will get a series of errors due to the differences in SQL syntax between MS SQL Server and MySQL.
An alternative would be to try to export your MS SQL Server database to MySQL using the Export feature. This, too, may be a trial and error process. Also, when the export has been completed, you will still need to add other objects such as views and stored procedures.
"The oxen are slow, but the earth is patient."
Member
350 Points
87 Posts
Re: Porting MSSQL Database To MySQL
Jun 24, 2010 12:23 AM|rahul.kansara|LINK
Hi,
Check this : http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html
Rahul
Please mark this reply as answer if it helps
All-Star
120146 Points
27989 Posts
Moderator
MVP
Re: Porting MSSQL Database To MySQL
Jun 24, 2010 12:23 AM|ignatandrei|LINK
If you do not use any ORM (like Nhibernate ) it will be very difficult to pass from MSSQL to MySql. It will be easier from a version of MSSQL to another version of MSSQL.
Do you have tests ? If not , I suggest start with defining tests for important features.Then you could define your application like here(self advertising):
http://msprogrammer.serviciipeweb.ro/2010/03/29/asp-net-mvc-orm-and-viewmodels/
Note that
First Layer , Data Access Layer
could be in EF, NHibernate, plain old SQL - and can be switched between MSSQL and MySQL without changing the application