Migrating from Microsoft SQL Server and Access to MySQL

By Mike Hillyer

Introduction

One of the more common themes present on the various MySQL forums and mailing lists is that of data migration. Typically requests are made by users of Microsoft® Access and Microsoft SQL Server who are looking to migrate their data (and client applications) to a MySQL database. Developers often ask for tools that can be used to convert an Access database to MySQL (or convert an MSSQL database to MySQL), without realizing that there is more to migrating an application to MySQL than simply converting data.

In this article I will cover the basics of migrating an application from an Access or SQL Server database to MySQL. We'll start with various reasons why you should (or should not) migrate your existing Access or SQL Server database to MySQL, then cover the planning stages of an application migration. Next we will look at the tools and methods for migrating your actual data from Access/MSSQL to MySQL, followed by some general guidelines for modifying your client application from a Microsoft database to MySQL. Finally, we'll look at some considerations to make when deploying your new MySQL database and application.

Why You Should Migrate to MySQL

Chances are good that if you are reading this article you already have an interest in migrating your application from Access or SQL Server to MySQL, or at least add support for MySQL to your existing Windows® application. The reasons for migrating an application vary, but let's look at a few of them.

MySQL is Cross-Platform

One great advantage of using MySQL is its cross-platform capabilities. You can develop your database on a Windows laptop and deploy on Windows Server 2003, a Linux server, an IBM mainframe, or an Apple XServe, just to name a few potential platforms. This gives you a lot of versatility when choosing server hardware. You can even set up replication using a master on a Windows platform with Linux slaves. It's incredibly easy to move between platforms: on most platforms you can simply copy the data and configuration files between servers and you are ready to go!

MySQL is Fast

An independent study by Ziff Davis found MySQL to be one of the top performers in a group that included DB2, Oracle, ASE, and SQL Server 2000. MySQL is used by a variety of corporations that demand performance and stability including Yahoo!, Slashdot, Cisco, and Sabre. MySQL can help achieve the highest performance possible with your available hardware, helping to cut costs by increasing time between server upgrades.

MySQL is Free

MySQL is Open Source software. As such you are free to examine the source code and make any changes you wish. As per its GPL license, you are free to redistribute those changes as long as your software is also Open Source. If you do not wish to make your software Open Source, you are free to do so as long as you do not distribute your application externally. If you adhere to the requirements of the GPL, MySQL is free for you to use at no cost. If you wish to distribute your closed-source application externally, you will find that the cost of a MySQL commercial license is extremely low (MySQL licenses start at only $249 US). MySQL AB also offers well priced commercial support that is significantly less expensive than some of its counterparts.

Customers Want Open Source

While the Open Source nature of MySQL may not be your driving reason for migrating, I have encountered multiple users who have moved to MySQL because their customers demanded it. Many customers want lower costs and the freedoms that come with using MySQL and other Open Source technologies in their infrastructure. Open Source software such as MySQL gives them freedom from future licensing and upgrade costs and gives them a future that is free from the surprises that can come when dealing with proprietary software.

Why You Should Not Migrate to MySQL

While converting an Access or SQL Server database to MySQL offers many benefits, it may not be the ideal solution for your application. Let's look at a few scenarios where a conversion to MySQL may not be ideal for you.

Single-User Portable Applications

There are many applications out there that use a combination of Microsoft Access and JET to manage data. These applications are only used by a single user, and often are used in situations where the data file is simply copied to a new machine when it needs to be moved. The reality is that there is not much benefit to using MySQL in such a situation. MySQL is designed as a multi-user server and is ideally suited to situations where concurrent access by anywhere from a few users to several hundred users is a priority. MySQL does offer an embedded server which can be useful when you wish to incorporate the database directly into an application, but it requires a specialized API which is not easily migrated to when an application is based on technologies such as ADO.

Applications With Incompatible Features

MySQL AB is constantly adding new features to MySQL, but there are always some features that SQL Server or Access will offer that are currently unavailable in MySQL. If you are using MySQL 4.0 you may find that a lack of prepared statements, stored procedures, subselects, and views affect the ease with which you can migrate an application to MySQL. This will of course depend on how extensively you have used such features in your application. In MySQL 4.1 we see the introduction of prepared statements and subselects. In MySQL 5.0 stored procedures and views have been introduced, although the stored procedure syntax will undoubtedly vary in some degree from Microsoft's T-SQL language.

You can often work around the differences between MySQL and MSSQL/Access. If your existing application uses stored procedures but you need to use MySQL 4.0 or 4.1 you can always move the logic that was present in your stored procedures to functions within your application. The difficulty of this will of course depend on the number of stored procedures you use and their complexity.

Large Applications Without Database Abstraction

One piece of advice I would give to any aspiring application developer would be to abstract database access. If your application uses proper database abstraction you will find that converting that application from Access or SQL Server can be done fairly smoothly. If your application is small and lacks abstraction you may also find conversion to be relatively simple as you will not have much code to convert. That being said, the complexity of your migration and the time required to perform the migration will increase as your application grows in size. This is not to say that it will eventually become impossible to convert a large application, but time and costs will possibly increase to the point that the costs of switching to MySQL outweigh the benefits.

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.

Application Modifications

Every database application is different, and as such there are no hard and fast rules that will apply to every application migration. Below we will discuss some of the areas that most developers will need to consider when migrating an Access or SQL Server database to MySQL.

Access Control

Many Windows applications use integrated Windows NT security to provide access control to their databases (also known as SSPI). This functionality is not currently available in the MySQL server and such authentication will have to be moved to the client application. Additionally, MySQL offers a high level of granularity when specifying database privileges, which can help increase application security when properly implemented.

Cursors

Although server-side cursors are a pending feature for MySQL, true server-side cursors are not currently implemented. If your application currently uses server-side cursors you may need to evaluate your application and determine whether the simulated server-side cursors provided by Connector/ODBC are adequate, or whether similar functionality can be achieved with client-side cursors in your application.

Stored Procedures And Views

Stored procedures and views are newly implemented features in MySQL 5. If your application relies heavily on either of these features it is recommended that you base your migration on the MySQL 5 server or find a way to move your stored procedures into the client application. MySQL is basing stored procedure syntax on the ANSI SQL standard, which will result in some incompatibilities between T-SQL syntax and MySQL stored procedure syntax. You should plan to perform rewrites on all but the most trivial of T-SQL stored procedures to bring then into conformance with the ANSI SQL standard.

General SQL Syntax

Almost all relational database systems deviate from the SQL standard in one way or another, often to add enhancements and other special features that were not addressed in the original standards. One key to a successful application migration will be to identify SQL queries and statements used in your application that will be incompatible with MySQL.

One area of concern is quoting of table names; While Access uses square brackets (i.e. SELECT myfield FROM [my table]), MySQL instead uses back-ticks (i.e. SELECT myfield FROM `my table`). When possible it is best to avoid using table names that require quoting. When this is not possible you will need to change your queries accordingly.

MySQL Optimizations

When migrating an application, it is important to note that MySQL has certain advantages that can be exploited in your application. MySQL is often faster at creating and destroying connections within an application than its counterparts, which can affect how you go about creating and destroying connections when developing. In addition, there are specialized functions available from within MySQL that can cut down on the amount of client-side programming needed. Finding and utilizing these advantages can help improve application performance and simplify client-side development.

Deployment Considerations

The migration of a database and client application is not a trivial undertaking. Not only does such a procedure take time to complete, but it often has to be performed on production systems with a minimum tolerance for downtime. The following are a few recommendations to take into consideration when migrating and deploying a database application.

Give Yourself Enough Time

Even the best planned migration can take longer than expected. When budgeting your time be sure to factor in unexpected delays and external interruptions. It is better to over-estimate on a migration project and be done early than to overshoot your schedule.

Perform Trial Migrations

Be certain to perform trial runs of your data migrations before doing any final work. I would recommend giving yourself at least a week leeway before any deadline to allow time to fix any problems that crop up during trial runs. Be careful about making changes between your last successful trial run and your production migration as even the most trivial of changes to your migration tool/script or client application can spell disaster during a production conversion run.

Perform A Limited Rollout Where Possible

If it is feasible in your situation, consider performing a limited deployment of your new system. Perhaps you have one or two customers/branches who are willing to try your new MySQL powered version in a beta program before you roll the update to all your customers. If this is not possible, you may be able to run one or two terminals in a test environment with live data pulled from your existing system using the scheduling capabilities of the migration tools mentioned earlier. In either case this would allow you to test the system with real data and real users before pushing your changes to all users as a whole.

Have Maintenance Plans In Place

Before beginning a production migration you need to ensure that you have an effective disaster recovery plan in place. Ensure that your backup hardware will be compatible with your new MySQL database, and that you have scheduled backups in place with a tested plan to recover your data. Because of the nature of data migrations, you may wish to perform backups with increased regularity in the initial weeks after conversion.

Conclusion

Increasing numbers of developers are migrating applications from Microsoft SQL Server and Microsoft Access to MySQL due to the increased performance, cross platform capability, and open nature of MySQL. When migrating it is important to plan ahead, determining if migration is the best solution in your situation and taking into account the various factors that could delay or impede a migration. There are various tools available to you to help with the migration of your data, and different factors to consider when converting a client application. Once conversion is complete, it is important to take time with deployment, performing trial runs and ideally performing a limited rollout, while ensuring that adequate disaster recovery measures are in place.

Finally, keep in mind that you can benefit from the experience of those who have gone before you. MySQL AB offers services and resources that can assist you in the migration of your data and applications. MySQL provides deployment consulting for companies looking to migrate to MySQL, and there are also a series of migration related forums available for you to review and post questions in.