News Stay informed about the latest enterprise technology news and product updates.

Golden's Rules: Migrating from SQL Server to MySQL

Databases are a key piece of every organization's software infrastructure. They are complex pieces of software that we rely on to rapidly spit out data in response to queries and to reliably track transactions, never losing track of the fact that a payment has been made (or received).

Because databases are so important, they have long been the royalty of the software world -- venerated for the key role they play, closely monitored for their plans and whims and richly rewarded for their position at the top of the software society.

Just as democracy came to Western societies, commoditization has arrived in the database world. New arrivals in the market, based on open source, have made databases available to everyone at no cost. Perhaps the best known of these open source databases is MySQL.

A question arises, however. How can you move from the oligarchy of commercial databases to the democracy of open source? More specifically, how can you move from Microsoft's SQL Server to MySQL?

The migration is relatively straightforward, but it must be accomplished with a well-structured plan. This article outlines the three essential steps of the migration process.

Step 1: Migrate your data

Although both SQL Server and MySQL are standards-based databases, they are not identical in their supported datatypes, metadata organization or internal data manipulation capabilities. So, the first step in migrating to MySQL is to understand how well your existing SQL Server database can be reflected in a MySQL instance.

If your application has stuck with ANSI-standard datatypes, you'll probably be in pretty good shape in terms of your ability to construct equivalent table structures in MySQL. To the extent that you have used nonstandard datatypes, you may need to spend some time planning a satisfactory mapping.

Beyond the data elements themselves, you'll need to address some metadata areas. Indexes are a primary example. While both SQL Server and MySQL both offer indexes, they do not operate identically, so be sure to consider which indexes you want to transfer and which indexes you want to change. Step 3 (below), the Migrating Process, addresses the implications of indexes on the database.

More of Golden's Rules:

Golden's Rules: Red Hat/JBoss pairing bad news for BEA, Novell

Golden's Rules: Oracle, VCs put froth on OSBC's latte

The final area to address in data migration is data extensions, particularly stored procedures and triggers. These are not standardized and vary in functionality and implementation. If you've used data extensions in your database, be prepared to do some re-engineering.

This process probably sounds like a lot of work, and it can be. Fortunately, tools can help you in this part of the migration process. Vendors like GoldenGate Software Inc. and Embarcadero Technologies Inc. offer some commercial tools. If buying a commercial tool to migrate to an open source database seems somehow wrong, MySQL, itself, offers one.

Each one of these tools makes it as easy as pointing to the existing SQL Server database, identifying the desired MySQL location and pressing a button to start the migration process. The tools will build the new database, create tables, indexes and so forth and migrate the data. If any aspects of the existing database can't be easily migrated, the tools will flag them for your attention.

Definitely consider taking advantage of one of these migration tools; otherwise, the data transfer process is a lot of time-consuming manual work that is also very prone to errors.

Step 2: Migrate your application(s)

Once your data is migrated, it's time to move on to the real reason you have a database in the first place: your application(s). A database without an application is like a safety deposit box without a key; it contains something valuable, but that something is useless because you can't get at it.

This part of the migration process can be trickier than migrating your data. Your potential for success depends upon whether you have access to the application source code.

Assuming you do have access to the application source, you can search through it for SQL statements. If the application uses standard methods of access like ODBC or JDBC, you should be in good shape, although even these access methods allow for database-specific extensions. If the application takes advantage of them, then you'll have to consider how to translate them to MySQL-appropriate functionality. These extensions are typically used in conjunction with database-specific datatype extensions, so if you uncovered any of those during Step 1, you should be prepared to encounter statement extensions as well.

The application migration process gets tricky if you have proprietary applications running against your SQL Server database. Because you probably won't have source code for these applications, the only way to tell whether they'll work against your new MySQL database is to test them. If they work, great! If not, your only hope is to go back to the application vendor and ask if they have a MySQL-friendly version. If they don't, you'll unfortunately have to stick with SQL Server for that application.

Step 3: Migrate your operations

Now that you've migrated your database and application, you can flip the switch, right? You can just point users at the new application/database instance and start running on MySQL?

Actually, no.

Even though you've probably done some preliminary testing to ensure that the application functions properly, you still need to do some work before you migrate your operational environment.

First and foremost, be sure your organization is ready to make the move. Do you need time to get people prepared to support MySQL? Formal training, or at least informal familiarization time, may be needed. You will certainly need a project plan and schedule to ensure you've accomplished all necessary steps before you go into production.

Another important task is to test the new application/database combination. Additional testing is important beyond pure functionality testing. A fully loaded database should be implemented so that you can test the application's performance with a full dataset. A number of different types of queries and update/insert/delete statements should be performed to confirm that the right performance levels are present. As noted above, differences between the two databases in terms of statement parsing and query plan optimization can impact database performance, so it's critical to test those aspects of the system.

Naturally, you should confirm that all operational tasks are understood and can be performed with the new database. As an example, a regular backup schedule is important, so make sure you know how to do this with MySQL and that your regular schedule is in place with the new database.

Only after you've got everyone's skills ready and you have done a full shakedown of the new database/application combination should you perform the final operational migration. Your full preparation will pay off in a smooth transition. You will be up and running on MySQL.

Dig Deeper on Linux servers

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.