Planning For Your Migration
It is very important to plan ahead when migrating a database application to MySQL, as you will want a solid strategy in place before you begin your conversion. In your planning, you will need to consider changes to your data such as modification of data types, as well as modification of the actual data that may be required. You will also want to look at the changes that will need to be made to your client application(s) including such things as cursor use, functions, stored procedures, and internal data types. You will also want to take a look at your current maintenance strategies and make any modifications necessary to continue maintenance under MySQL. Finally, you will want to look at the strengths and weaknesses of MySQL, SQL Server and/or Access and ensure that you will be using MySQL to its fullest.
Data Types
While SQL Server and MySQL have a fair amount of overlap as far as data types go, there are still some differences to be accounted for. Make sure to spend some time looking at the various data types you use in your tables and plan to migrate those tables to the MySQL data types that best match. Be careful when planning this: you want to match data types by capacity and not necessarily by name. For example: a MySQL VARCHAR can hold up to 255 characters, whereas a SQL Server VARCHAR can hold up to 4000 characters. In this case you would need to use a MySQL TEXT column type instead of VARCHAR.
Some data types do not have a direct correlation between SQL Server or Access and MySQL. One example would be the CURRENCY data type: MySQL does not (yet) have a CURRENCY data type, but creating a column with the definition DECIMAL(19,4) serves the same purpose. While MSSQL defaults to Unicode character types such as nCHAR and nVARCHAR, MySQL does not so tightly bind character sets to field types, instead allowing for one set of character types which can be bound to any number of character sets, including Unicode.
You can find the latest list of MySQL column types in the MySQL Reference Manual. You can use this table of mappings between Visual Basic datatypes and MySQL column types as a quick reference of the basic MySQL column types, their capacities, and their VB6 equivalents. MSDN also provides a list of SQL Server data types.
Data Modification
Sometimes you will need to modify the data itself when doing a data conversion. One example of this would be columns that hold date information. MySQL stores date information in a standard format of YYYY-MM-DD, while Microsoft databases are often in a MM-DD-YYYY format. It is very likely that your conversion tool will automatically take care of this, but if you are creating your own conversion tools you will need to keep this in mind. Both MSSQL and MySQL use single quote characters to wrap date information (i.e. '2000-12-13'), but Access uses hash marks to accomplish the same task (i.e. #23-11-2001#). If converting from Access to MySQL you will need to change your queries accordingly. Other data modifications might include schema changes to normalize your tables while performing data conversions. For more on database normalization, read the article “An Introduction to Database Normalization”.
Built-In Functions
Many of the built-in MySQL functions are the same as SQL Server built-in functions, though sometimes there are naming differences. One example is the MSSQL ISNULL() function. MySQL’s equivalent is the IFNULL() function, which uses the same syntax. Conversely, the ISNULL() function in Access uses a different syntax, and returns only a boolean instead of a substituted value. MySQL has more built-in functions than its Microsoft counterparts so there should be MySQL equivalents for any built-in functions your existing queries use.
Cursors
Typical Windows applications will use server-side dynamic or keyset cursors when accessing data through APIs such as ADO. The Connector/ODBC driver does not support keyset-driven cursors and server-side cursor support is very limited in any case. You will want to evaluate the cursor types and cursor locations used in your application to determine if changes need to be made. You may benefit from reading the article “CursorTypes, LockTypes, and CursorLocations”.
User Defined Functions
User Defined Functions (or UDFs) are not the same between SQL Server and MySQL. SQL Server functions are very similar to stored procedures, allowing you to encapsulate a series of queries into a callable function that can then be incorporated into a query. MySQL UDFs, on the other hand, are compiled C code that can be assigned to a function name and used in queries. One example would be using a C function that converts a color photo to black and white within a MySQL query to return images stored in color in BLOB columns as black and white images. Once your C code is compiled you can then incorporate it into the server and call it from a query.
MySQL does not currently offer an equivalent for the SQL Server style User Defined Functions, and the functionality of any UDFs present in your database will need to be converted to client-side application code.
Stored Procedures
MySQL has recently implemented stored procedures in version 5 of its database server. While MySQL is committed to following standard SQL conventions, this is no guarantee that a T-SQL will work in MySQL unchanged. If you will not be using MySQL 5, you will need to rewrite your stored procedures to use client-side code.
Maintenance Planning
In addition to planning your data and application conversions, you will also need to look at converting your database maintenance strategies and tools. Some major backup vendors do provide backup tools for MySQL, so you may want to check with your existing vendor to determine if they provide an equivalent tool for MySQL. Backup strategies for MySQL are very similar to those of SQL Server: regular full backups should be done, with log files backed up in the interval.
Migration Tools
There are a wide variety of tools available to help you migrate a SQL Server or Access database to MySQL. We’ll look at several different tools so you can choose the one that best suits your needs. The tools we will look at will include the following:
- MSSQL2MYSQL
- Microsoft DTS
- SQLyog
- Access Export
- Text Import/Export
SQLYog and the Microsoft DTS wizard offer graphical interfaces that can be used with both MSSQL and Microsoft Access to import tables into MySQL. MSSQL2MYSQL is a script by Michael Kofler that can convert not only the table structure and data, but converts the index information as well. If you use Microsoft Access you may not have access to the above tools, but you can use the data export features of Access.
MSSQL2MYSQL
MSSQL2MYSQL is a creation of Michael Kofler, author of The Definitive Guide to MySQL by Apress. MSSQL2MYSQL is a Visual Basic script that can be executed using either a Microsoft Visual Basic 6 installation or an application that supports VBA such as Microsoft Word or Excel.
Details on usage can be found at the author’s web site, which also includes a listing of GUI front-ends that can be used to make MSSQL2MYSQL a bit more user-friendly for non-programmers.
To use MSSQL2MYSQL with VB6, simply copy the text located at http://www.kofler.cc/mysql/mssql2mysql.txt and paste it into the code section of a VB form. You will need to change the constants at the beginning of the code to match your SQL Server and MySQL installations, and you can then proceed to run the VB6 application and your conversion will take place. MSSQL2MYSQL does not provide any visual feedback on the progress of your conversion, and provides a simple messagebox upon completion.
A nice feature of MSSQL2MYSQL is the ability to dump all statements into a text file, which you can then review and edit before executing on the MySQL server.
Microsoft Data Transformation Services
Microsoft DTS is a data manipulation tool that is included with Microsoft SQL Server. DTS is excellent for moving data between various formats and systems such as databases, spreadsheets, and even HTML. The Microsoft Data Transformation Service can be very complex, but most of us will only ever need to use the Import/Export Wizard that is included with DTS.
Using DTS is fairly straightforward, you choose an ODBC data source to read data from, and then select an ODBC data source to convert the data to. You are then given a list of tables to convert, with an option of renaming the destination table and even performing basic transformations on the data before it is inserted into the target database. These transformations are performed using Visual Basic scripting. In addition, you are given control over the table creation statements to be used, allowing you to fine-tune the MySQL table definitions to add parameters such as table handler (InnoDB, BDB, etc) to the script that will be executed.
DTS also has the ability to perform scheduled data transformations, something that can be very useful when you are using MySQL for analysis of SQL Server data or when you just want the latest data available as you work on your application migration.
SQLyog
SQLyog is a third-party commercial tool available to help administrators manage MySQL in a GUI environment. SQLyog is provided by by webyog, a MySQL partner, and a thirty day trial of the tool is provided. SQLyog provides an ODBC import tool that is similar to DTS, offering a straightforward interface that is perhaps even simpler to use than DTS.
SQLyog is capable of scheduled imports of data, and can also be used to synchronize both data and schema between multiple MySQL servers.
Access Export
If you are a Microsoft Access user but do not have access to Microsoft DTS or SQLyog, you may want to use the export capability of Microsoft Access. Access can export its tables to a variety of formats, including ODBC. This allows you to export an Access table to MySQL by way of the Connector/ODBC ODBC driver provided by MySQL AB.
To export an Access table to MySQL, right-click on the table in question and choose the ‘Export’ option. After several steps your data will be exported to MySQL. The column-type choices made by Access may need to be modified, and you should be aware that Access will not export index information with the data, meaning that you will need to implement indexes on your tables after exporting them.
Text Import/Export
One final way to import data is to export the data from MSSQL/Access in a text format and import it directly into MySQL. When exporting, common formats such as tab-delimited or comma-delimited will work fine for later import into MySQL.
When taking this approach, you will need to manually create the MySQL tables, then import the data with the LOAD DATA command in the mysql command-line client. Additional information on the LOAD DATA command can be found in the “LOAD DATA INFILE syntax” section of the MySQL Reference Manual.
While perhaps the most labor-intensive and time-consuming, this approach gives you the highest level of control over table schema as you manually create the tables before importing data.
Source: http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html
===
Additional Info regarding date format issue for text import
As MS Access Date format is dd/mm/yyyy 00:00:00 and MYSQL Date Format is yyyy-mm-dd
1. Changed the field definition for the date field to varchar
2. Imported normally
3. ran this query using substring_index
mm/dd/yyyy 00:00:00 ==> yyyy-mm-dd
update Customer set CreationDate=concat(
substring(substring_index(CreationDate,”/”,-1),1,4),”-”,
substring_index(CreationDate,”/”,1),”-”,
substring_index(substring_index(CreationDate,”/”,2),”/”,-1)
)dd/mm/yyyy 00:00:00 ==> yyyy-mm-dd
update Customer set CreationDate=concat(
substring(substring_index(CreationDate,”/”,-1),1,4),”-”,
substring_index(substring_index(CreationDate,”/”,2),”/”,-1),”-”,
substring_index(CreationDate,”/”,1)
)
4. Then I changed the field definition back to Date
Add A Comment