Wednesday, 24 October 2007

Dan at work - Dan vs MySQL

Todays challenge was to port the back-end code of our company Intranet from SQL Server 2000 to MySQL. This is due to our company Intranet sitting on our web development server (not the best place for it really).

In theory this shouldn't be too difficult a job as the SQL queries that we run to generate the site aren't really that complicated (no nested procedures or anything like that), the only parts that I was a bit concerned about were moving the data (especially binary objects held in database tables) from SQL Server to MySQL.

I didn't want to install MySQL on the same server as SQL Server 2000, then I remembered that I'd already set up a MySQL database on a test server for use with Merak Email server, so that can hold an extra couple of tables till I can sort out a more permanent home for it.

MySQL comes with a migration tool which takes care of moving the tables and data from the old server to the new one. It will also make relocating the database later on an absolute breeze. There were a couple of gotchas - namely codepage support on some of the databases - the data wouldn't move over until I set the target database to "international" mode.

A couple of the ASP pages needed a little tweak here or there - the original SQL Server 2000 table name was "northeast.dbo" (this was to match a remote version of the database used for our "live" client pages) - the MySQL version is "northeast_dbo" so some of the queries needed a little nudge here and there, as well as MySQL not supporting things like CAST or TOP. All told it took a little over five hours to port our company Who's Who database, Newsletters databases (three of them), Editorial Style Guide and Administration pages (including fixing up the ASP pages).

Tomorrow I've got to create a version of our Intranet site for one of our sister sites, and I get the feeling that will be followed by one for another site too. So it looks like things could be a little busy for the next couple of days (at the very least).

No comments: