Converting MS SQL and/or MS Access to MySQL

For a current project I had to convert an MS SQL database to a MySQL database. I did not have access to the MS SQL server, but the previous database administrator provided me with a dump of the database creation statements and an MS Access file with the data.

Being unfamiliar with MS SQL, I assume he used BACKUP or BCP to create text files that contain the necessary MS SQL statements required to re-create the database structure (that is, scripts) then another tool such as MS DTS to export the data to MS Access.

The text files delivered were DB_Table_Creation.sql, DB_StoredProcedure_Creation.sql, and DB_Transaction_Creation.sql. Since the application will be rebuilt from scratch, I was only interested in DB_Table_Creation.sql.

The Access file delivered was xxx_tables_yyyymmtt.mdb.

To import the Access data I downloaded and installed the free MySQL GUI Tools, which include MySQL Migration Toolkit (as well as MySQL Administrator and MySQL Query Browser, which I will have to check out later): http://dev.mysql.com/downloads//gui-tools/5.0.html

To run MySQL Migration Toolkit you will need access to a functioning MySQL server.

While running MySQL Migration Toolkit I got the following error message:

"The connection to the target database could not be established (error: 0).
ReverseEngineeringMysqlJdbc.getVersion :Unknown initial character set index '48' received from server. Initial client character set can be forced via the 'characterEncoding' property."

After finding this error message described here: http://bugs.mysql.com/bug.php?id=25233
I realized that I had previously played with changing character sets and collations, so I followed the suggestion mentioned there and didn't have any further problems.

At the "Object Creation Options" step, I checked the option "Create Script File for Create Statements" to compare it with the table creation script from MS SQL. Note or change the path, so you can find it later (the default is "My Documents\Creates.sql").

The "Bulk Data Transfer" step may take a while depending on the amount of data, so don't assume the program has crashed. You can click "Advanced >>" for more information, but it appears to output an empty line for every row of data imported, so don't be alarmed.

When the transfer process was finished I compared the MS SQL script file provided by the previous database administrator with the one created by the Migration Toolkit to see if there were any huge discrepancies. The MS SQL file defined some fields with NOT NULL while the Migration Toolkit created them all with NULL; no big deal. The data types seem to have been converted correctly (at least with no data loss), considering their differences between MS SQL and MySQL. The VARCHAR fields with specific lengths were converted to LONGTEXT fields with no length given. This seems wasteful for VARCHAR fields defined with a length of 2 for example, but at least no data was lost. BIT fields were converted to SMALLINT(5): more waste but at least no loss. CHAR fields with a length of 10 were converted to VARCHAR(10). DECIMAL fields were also converted with their exact length. DATETIME fields became DATETIME fields. DEFAULT values were not defined in the target database; the new application must take this into account, or the fields must be altered after conversion. It appears IDENTITY(1,1) for example is meant to identify primary auto-increment keys. This definition is lost in the target database, as is all other key information (PRIMARY KEY, UNIQUE, etc.).

An alternate method of converting the database would have been to adapt the MS SQL script to MySQL as well as possible using copy/replace, and then run the script in MySQL. Then you would need to export the Access data into some sort of text file and load it into the new MySQL database with LOAD DATA INFILE. But that would require a much better understanding of the MS SQL CREATE TABLE syntax than I have and an installed version of MS Access, which I don't have.

See a more detailed version of this blog post on eHow:
http://www.ehow.com/how_5334020_convert-andor-ms-access-mysql.html

First Post

OK, I know there are about umpteen other blogs out there called "Robb's Blog", but I couldn't think of anything cleverer. I'd be happy to hear any suggestions.

I plan to use this blog for reports of my evaluations of various (IT) products and whatever else comes to my mind.

At the moment I am evaluating online (also call hosted or web-based or in-the-cloud) invoicing/billing systems and online bug-tracking (also called issue-, problem-, defect- and ticket-tracking) systems.

Soon after I twittered the following:

"dellfalconer is evaluating online billing/invoicing systems."

I got tweets back from:

curdbee
onlineinvoicing from invooice
Nagendr4mzoho from Zoho
harvest
jjriv from Intervals

This indicates a dedication to customer interaction that I find refreshing, so I will try to include the ones I hadn't heard of in my evaluation and have another look at the ones I had heard of.