Introduction
We have a few MySQL based systems where I work and although those systems
function well there are times (at the risk of offending zealots) when there is
a need to move their data into a MS SQL Server environment.
Fortunately this is not an onerous task but there are a few gotchas.
Firstly, I must stress that although I like MySQL and familiarity does little to
diminish my liking, I am not an expert. The following article outlines the
steps that I went through to get a DTS transfer between the two DBMS' up and
running.
MySQL tools, documentation and manuals
I have been using the MySQL Control Centre, which is now obsolete but still available from
http://dev.mysql.com/downloads/other/mysqlcc.html. I simply have not got around to downloading
MySQL Administrator and
If you fancy a browser based tool then it is worth looking at
PHPMyAdmin.
The searchable user manual for MySQL is at
http://dev.mysql.com/doc/mysql/en/index.html
The documentation part of the MySQL web site can
be found at http://dev.mysql.com/doc. The documentation is
comprehensive and well worth a look.
MySQL user authentication
You are going to need a suitable login for MySQL in order to perform the data
transfer.
The MySQL equivalent of the SQL Server SA login is Root and the documentation
stresses that this login should not be used.
A MySQL login consists of two parts
- A user name
- A host name, which can include wild cards.
Let us suppose that I have a username "Steve" and I want " Steve
" to be able to login from any computer. I would simply put the host name
as %.
The login would be Steve@%
I could restrict "Steve" to a particular domain so if I specify a host
of sqlservercentral.com then the login would be Steve@sqlservercentral.com
If Steve had a .com, .net, or sadly .org domain then I could specify a host of
sqlservercentral.%
The login would become Steve@sqlservercentral.%
I can even restrict "Steve" to an ip address and subnet mask.
Installing ODBC Drivers
The next stage is to download and install the MySQL ODBC driver on you MS SQL
Server box.The driver can be downloaded
from http://dev.mysql.com/get/Downloads/MyODBC3/MyODBC-3.51.10-x86-win-32bit.exe/from/pick.
This is a standard EXE and when you run it you will be taken through a
straightforward installation wizard. This consists of agreeing to the licensing
agreement then clicking NEXT through the following informational screens, and
finally clicking FINISH.
Once the installation is complete you will see that the MySQL ODBC driver is now
available within the SQL Server DTS.
Setting up an ODBC Connection
The MySQL driver has a number of settings that are quite a bit different from
the ones used for connecting to standard Microsoft applications.
The main dialogue box when setting up the ODBC connection is at least comparable
to what you will be used to.
Note the help text that appears next in the right hand panel for any given
option.
It is also possible to select the port for the connection is you so wish.
The final tab is very important for migrating data. There are additional data
types in MySQL and how they are translated into their MS SQL equivalents will
be affected by this dialogue box.
If you hover over a particular check box on this dialogue you will get some
"helpful" tooltip text. As you can see from the screenshot above.
The flags that I have found important are as follows:
| Flags Tab | Flag | Comment |
|---|---|---|
| 1 | Don't optimise column width | Check this box. If you don't then the ODBC driver works out the longest entry in the field and tries to set script a CREATE TABLE command accordingly. The problem is that if you have an empty column DTS will script a VARCHAR(-1) which causes an error. |
| 1 | Change Bigint columns to int | You need to investigate your data before you check this box. If you have a value that exceeds the storage capacity of a SQL Server Int then you should not check this box. One point to bear in mind is that MySQL has the concept of SIGNED and UNSIGNED integers. |
| 2 | Don't cache result (forward only cursors) | For import only connections it is a good idea to check this box as you gain a performance boost, particularly on large tables. |
| 3 | Force Use Of Forward Only Cursors) | Once again, import only connections it is a good idea to check this box as you gain a performance boost, particularly on large tables. |
In my test environment I found that certain combinations of the other check boxes resulted in a
Windows popup box appearing warning that "memory could not be read". I did not pursue this further as
my main aim was to get the import up and running rather than to produce a definitive guide on migration.
Gotchas
As you have seen so far the mechanicals are very straight forward. The things to
watch out for are the differences between MySQL and MSSQL data types.
In general the ODBC drive handles most things quite well but you need to bear in
mind that you might not get the conversion that you were expecting.
A MySQL UNSIGNED INTEGER field will come across to SQL Server as DECIMAL(10,0).
A SIGNED TINYINT becomes a SMALLINT
Another difference is in the way that MySQL handles dates and times.
| Data type | MySQL | SQL Server |
|---|---|---|
| DATETIME | Holds date and time down to the second | Holds date and time down to the 3.3 milliseconds |
| SMALLDATETIME | No direct MYSQL equivalent | Holds date and time accurate to the minute. |
| DATE | Holds YY-MM-DD | No direct SQL equivalent. These get converted to SMALLDATETIME |
| TIME | Holds a time difference in hours, minutes and second. Although useful for storing a 24 hour time it can actually hold +/- 838:59:59 | No direct SQL equivalent. These get converted to SMALLDATETIME |
| TIMESTAMP | Appears to be a synonym for the MySQL DATETIME field | Is a RowVersion datatype rather than a date/time data type. The conversion from MySQL goes to a SQL Server DATETIME field. |
| YEAR | Holds values between 1901 and 2155 | No direct SQL equivalent but note that the MySQL datatype can hold 255 possible values. This gets converted to DECIMAL(4,0) |
TEXT/IMAGE datatypes
MySQL has TEXT and BLOBs but also subdivides these types so we have the following
- TINYBLOB - holds up to 256 bytes
- BLOB - holds up to 65Kb
- MEDIUMBLOB - holds up to 16Mb
- LONGBLOB - holds up to 2Gb
Provided you have checked the "Don't optimise column width" box in the ODBC Driver
these will come across as the SQL TEXT and IMAGE types. If you do check the box then the
consequences are that some columns may be converted to VARCHAR.
Finally MySQL has a couple of datatypes that were completely new to me.
ENUM
This is a field set to a collection of strings that can be referenced by the
index of the string, a bit like, well a VB collection actually.
This field will be converted into a CHAR with a length matching the longest value within
the enum.
SET
This is a collection of strings in the same way as ENUM but in this case the
index is a bit pattern. Asking for an index of 1 will produce the 1st value, 2
will produce the 2nd, 3 will produce the 1st and 2nd.
There can be a maximum of 64 values in a set.
This field will be converted into a CHAR with a length matching the total combined length
of the longest value within the set.
Conclusion
For the most part migrating MySQL into SQL Server has been very straight forward. The only
fields that caused any real headaches were the ENUM and SET types. The numeric value of the fields
can be determined in a SQL Query by doing
SELECT YourEnumField+0
FROM YourTable
If your MySQL database uses these field types then this will have to be rewritten for SQL Server.
The speed of the upload from MySQL to SQL Server was generally very fast. Given that MySQL's main strength
is its output speed this shouldn't be too surprising.
Perhaps the final point to make is that MySQL is an open source database and things move fast in the open
source world. It is worth checking the dev.mysql.com
for updated drivers, MySQL tools etc at regular intervals.
