Migrating from Microsoft SQL Server and Access to …
Migrating from Microsoft SQL Server, Access, or another database to MySQL? Talk about your problems and solutions in the Migration section on the new MySQL Forums !
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.
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.
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!
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 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.
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.
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.
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.
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.
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.
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.
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.
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» .
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.
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 (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.
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.
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.
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:
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.
Source: