Thursday, June 3, 2010

Migrate a SQL Server or Access database to MySQL

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.

3 comments:

  1. Great information about migrating sql server.

    ReplyDelete
  2. Those guidelines additionally worked to become a good way to recognize that other people online have the identical fervor like mine to grasp a great deal more around this condition. and I could assume you are an expert on this subject. Same as your blog i found another one Data Migration Software .Actually I was looking for the same information on internet for Data Migration Assistant and came across your blog. I am impressed by the information that you have on this blog. Thanks a million and please keep up the gratifying work.

    ReplyDelete