Friday, September 4, 2009

Porting a web site from MS SQL to MySQL

My son is starting his senior year in High School, and he's doing an internship at Build-It-Yourself, a site for helping kids ages 8-13 design and build their own toys and robots. A section of the site, Build-It-Blocks, has a library of reusable, functional building blocks, construction tricks and presentation tips. In talking with my son, I learned that there's a desire to port Build-It-Blocks from MS SQL Server to MySQL. I offered to help out since I have some experience with MySQL from my work at Tokutek.

Several thousand person hours went into creating the existing site, so one of the goals is to re-use as much of the existing code and content as possible. Other goals include running on Windows, and minimizing the amount of effort required to complete the port.

To get started, I reviewed the existing implementation, and it consists of:
  • Just over 100 .asp files
  • 116 stored procedures
  • 1 database with less than 100 tables
I did some research, and based on this article I think it will be fairly easy to port to MySQL using the MyODBC connector. Here's an outline of the porting plan:
  1. Install MySQL and MyODBC
  2. Export the MS SQL Database to MySQL
  3. Modify the .asp code to connect to MySQL and use MySQL stored procedures
  4. Port the stored procedures from MS SQL to MySQL
I'll post more details as we work on the port the MySQL - should be fun!

4 comments:

Roland Bouman said...

Hi Tom!

If I may give you some advice, I would spend some serious time reviewing the stored procedure code before you start porting. You may run into MySQL missing particular features, and its best if you know what kinds of things you run into, and whether that still justifies porting MS SQL SP's to MySQL SP's (as opposed to porting MS SQL SP's to code outside the database).

Now, if I would find that substantial effort was required to rebuild SP functionality in code, I would spend some time thinking whether I'd want to do that in .asp.

I know, one should avoid turning over every stone, and if an easy port of only the database is possible, I would go for that.

Tom Hotchkiss said...

Roland,

Thanks so much for the valuable advice - I'll review the stored procedures and consider the options - should be interesting.

Tom

Sheeri K. Cabral said...

Tom -- looks like a plan. Have you checked out http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html ? It not only points out some tools, but shows some of the concerns (Especially Roland's above...)

Darren Cassar said...

Hi Tom,
It's great to see your enthusiasm, but given I'm currently migrating Sybase to MySQL (although it's for a few thousand stored procs and a few databases with an average of 400tables each), I wanted to bring some things to your attention before you start the migration.

Be careful for:
custom data types (mysql doesn't support them)
timestamp datatypes if they are using any kinds of milliseconds (mysql storing of milliseconds can be done using decimal instead of timestamp but you'd have to hack a bit with that), generation of milliseconds is on the other hand something you must do using a UDF (user defined function).

There are of course other issues but it'll be a fun project i'm sure :)

If you only have few objects to migrate, you might want to have a look at sqlways which would facilitate your conversion. If you have less than 100 tables you could migrate those for free :).

tc
Darren

Post a Comment