How do you port a SqlServer database to MySQL?

Question

I have a SqlServer db that I would like to port to MySQL. What's the best way to to this. Things that need to be ported are: - Tables (and data) - FileStream --> MySQL equivalent? - Stored Procedures - Functions

Answer 1

This is not for the faint of heart. Here is an article that explains what you are in for:

http://searchenterpriselinux.techtarget.com/news/column/0,294698,sid39_gci1187176,00.html

Answer 2

A guide/article with some useful tips is available on the official MySQL dev site.

Answer 3

Data types are relatively similar.

There is no equivalent to FileStream in MySQL - the files must either be stored as BLOBs, or on the file system while the path is stored in the database.

Migrating away from TSQL means:

• There's no WITH clause in MySQL - it will have to converted into a derived table/inline view
• There's no TOP syntax - these have to be converted to use LIMIT
• There's no ranking/analytic functionality in MySQL - can't use ROW_NUMBER, RANK, DENSE_RANK or NTILE. See this article for alternatives.
• MySQL views have notoriously limited functionality:
  • The SELECT statement cannot contain a subquery in the FROM clause.
  • The SELECT statement cannot refer to system or user variables.
  • Within a stored program, the definition cannot refer to program parameters or local variables.
  • The SELECT statement cannot refer to prepared statement parameters.
  • Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.
  • The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.
  • Any tables named in the view definition must exist at definition time.
  • You cannot associate a trigger with a view.
  • As of MySQL 5.0.52, aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).
• Dynamic SQL will have to be converted to use MySQL's Prepared Statement syntax