Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Look at MYSQL

By David Poole,

MySQL To SQL Server

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 MySQL Query Browser.

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.

Example DTS connection box

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.

ODBC Connection basic login dialogue

It is also possible to select the port for the connection is you so wish.

ODBC Connection connection settings dialogue

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.

ODBC Flags dialogue

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 TabFlagComment
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.

Total article views: 11369 | Views in the last 30 days: 3
 
Related Articles
FORUM

Linked server using MySQL ODBC 5.1.8

Linked server using MySQL ODBC 5.1.8

ARTICLE

Linked server creation to MySQL Server

Here is a step by step procedure to create a linked server to MySQL from SQL Server.

FORUM

Migrate MYSQL into SQL Server 2008

MYSQL to SQL Server

FORUM

SQL Server ODBC Drivers - do they all support UNICODE correctly?

NVARCHAR fields from SQL Server appear as blank in MS Word mail merge. Is this an ODBC driver probl...

ARTICLE

Linked server to MySQL Server.

In this article, we are going to create a linked server to the MySQL Database. Once finished that t...

Tags
miscellaneous    
strategies    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones