Convert a SQL Server Database to a MySQL One

Introduction

Hi. I recently had to develop a small website as part of my studies. That site had to deal with a database, a Microsoft SQL Server’s one. Problem: I wanted to work on this project while I was on holidays, but I knew I wouldn’t have an Internet access during this pediod. So, SSH wasn’t an option. The database contains text, but also pictures and music extracts. The project had to run on my school’s server, the MySQL database was only used to work more efficiently (i.e I did not try to optimize it). The database has 20 tables and about 50 000 rows.

I decided to copy the database on MySQL to work on my computer with all the advantages of it (faster, all warnings activated in PHP)

Step 1: extract the database using SQL Server Management Studio

The database I was copying had 20 tables. I tried to extract all of them, but it failed. So I extracted them one by one. The extraction of tables with images went all right (around 60 Mo / file for about 600 images in each) but the table with music in it always failed. I didn’t know if I could copy all the columns except one of them with the software, so I decided to use a more brutal approach: I wrote a PHP page that generated MySQL Insert Statements, without the music.

There is plenty of documentation to generate those files. I’ve just realized that I could have used CSV to export data :(.

Now, you should have lots of files, each on them starts with the structure of the table, then contains Insert statements, and then information about foreign keys and indexes.

Step 2: create tables with PHPMyAdmin

20 tables, even with integrity constraints, is small enough to create them by hand. I didn’t find any script to translate a syntax to another.

1- Create a new user in PHPMyAdmin. Name it as your database and ask PHPMyAdmin to create a database on which the user has full rights. This way, you won’t write your root password in your code, and if you have a security issue, the only database which can be impacted is the one you’re creating right now. I have to use Local as Host; otherwise I can’t use the console.

2- Create tables I wanted to work as quickly as possible, so I didn’t optimize the database. A few things you have to take care of:

• If you create a primary key as Unsigned Int, then all the foreign keys referencing this key must both be unsigned and have the same size (unsigned int 11 for example).
• If you use the InnoDB Engine and are willing to add a foreign key constraint, first you have to add an index on the foreign key.
• I chose to use varchar to replace nchar. There may be there is a better solution.
• You can fill a blob with small files. Use longblob if you don’t care about optimization. Have a look at the documentation.

Step 3: convert SQL Server’s syntax to MySQL

Again, I did it by hand. I worked on Linux, and I used Geany to apply Search & Replace. I think you can do the same using Notepad++ (or SublimeText, I’m just trying this tool which seems to be wonderful) on Windows; I don’t know if it can handle large files (60Mo).

SQL Server export scripts are encoded in UTF-16-LE (Little Endian). The first thing you can do is changing the encoding to UTF-8. This will reduce by half the size of the files (and, I guess, speed up the process of search and replace)

What you have to delete in every file:

• The beginning and the end of it. You only have to keep Insert statements.
• All GO instructions. Use standard replace with GO\r\n. On Geany, I had to check the box “use escape characters”.
• all [dbo]. that prefixes tables name. I did a “search and replace” on [dbo].. Be careful, do NOT check the regular expression box.
• All the brackets around columns name. Because I hadn’t brackets in the data I was manipulating, I removed all of them.
• Add a semi-colon at the end of every line. I replaced )\r\n by );\r\n

Geany has a nice option which applies “search and replace” on all the files opened.

Step 4: add data in the database.

You can use PHPMyAdmin to do so, but it cannot handle large files (~15 Mo) therefore we will use the console. With images in the database I had 2 files bigger than 30 Mo. You have to change MySQL’s configuration to allow the importation of files bigger than 16 Mo.

Open my.cnf file (http://dev.mysql.com/doc/refman/5.0/en/option-files.html). Find this line, or add it: max_allowed_packet=64M Or course, if you have a larger file, allow a larger value. Restart your MySQL server.

Open a MySQL console in the folder of your scripts, in order to avoid filepath issues. run the command: mysql --show-warnings -u NEWUSER -p. MySQL will ask your NEWUSER password, the one you created in step 2. Then, run use MYNEWDATABASE;, where MYNEWDATABASE is the one you created in step 2.

If you have integrity constraints, you can either disable them, run all your INSERTs, and enable them, or let them enabled, in which case you have to add your tables in the right order. The former solution is fast, the latter is slower but it allows you to see warnings when mysql displays them. To disable integrity constraints, run SET FOREIGN_KEY_CHECKS=0; in the console. Set it to 1 to enable them after the job is done.

Now, run the command source MYTABLE.sql; for each table you have. If MySQL displays warnings, try to read them (try, because it is hard to stop the execution of the script as the rows are multiplicating on the screen).

Job’s done :)

You can use PHPMyAdmin to export the database and give it to all your friends \o/. They will just have to go through step 2-1 and step 4.