Migrating from MySQL to SQL Azure Using SSMA

In this blog post, I will describe how to setup your trial SQL Azure account for your migration project, create a “free” database on SQL Azure and walkthrough differences in the process of using SSMA to migrate the tables from the MySQL Sakila sample database to SQL Azure. For a walkthrough of how to migrate a MySQL database to SQL Server, please refer to the post “MySQL to SQL Server Migration:How to use SSMA”. This blog assumes that you have a local version of the MySQL Sakila-DB sample database already installed and that you have  SQL Server Migration Assistant for MySQL v1.0 (SSMA) installed and configured using the instructions provided in the “MySQL to SQL Server Migration: How to use SSMA” blog post.

Getting Started with SQL Azure

If you don’t have a SQL Azure account, you can get a free trial special at http://www.microsoft.com/windowsazure/offers/ through June 30th, 2011. The trial includes a 1GB Web Edition database. Click on the Activate button to get your account up and running. You’ll log in with your Windows Live ID and then complete a four step wizard. Once you are done, the wizard will take you to the Windows Azure Platform portal. If you miss this trail, stay tuned for additional trial offers for SQL Azure.

The next step is to create a new SQL Azure Server by clicking on the “Create a new SQL Azure Server” option in the Getting Started page. You will be prompted for your subscription name that you created in the wizard, the region where your SQL Azure server should be hosted, the Administrator Login information, and the firewall rules. You will need to configure the firewall rules to specify the IPv4 address for the computer where you will be running SSMA.

 

Click on the Add button to add your firewall rule. Give it a name and then specify a start and end range. The dialog will display your current IPv4 address so that you can enter it in for your start and end range.

 

Once your are done with the firewall rules, you can then click on your subscription name in the Azure portal to display the fully qualified server name that was just created for you. It will look something like this: x4ju9mtuoi.database.windows.net. You are going to use this server name for your SSMA project.

Using the Azure Portal to Create a Database

SSMA can create a database as part of the project, but for this blog post I’m going to walk through the process of creating the database using the portal and then use the SSMA feature to place the resulting database in a schema within the database. Within the Azure portal, with the subscription selected, you will click in the Create Database command to start the process.

 

Enter in the name of the database and then keep the other options as the defaults for your free trial account. If you have already paid for a SQL Azure account, you can use the Web edition to go up to 5 GB or switch to the Business edition to up the size limit between 10GB and 50GB. Once created, you will want to click on the Connection Strings – View button to display the connection string information you will use for your SSMA project shown below. The password value shows just a placeholder value.

You are now ready to setup your SSMA project.

Using SSMA to Migrate a MySQL Database to SQL Azure

Once you start SSMA, you will click on the New Project command shown in the image below, enter in the name of the project, and select the SQL Azure option for the Migration To control.

You will then follow the same processes described in the “MySQL to SQL ServerMigration: How to use SSMA” blog post that I will outline below.

1. Click on the Connect to MySQL toolbar command to complete the MySQL connection information.

    2.  Expand out the Databases node to expand the Sakila database and check the Tables folder as shown below.
 

      3. Click on the Create Report command in the toolbar button. You can ignore the errors. For information about the specific errors with converting the Sakila database, please refer to the blog post “MySQL to SQL Server Migration: Method for Correcting Schema Issues”. Close the report window.
      4. Click on the Connect to SQL Azure button to complete the connection to your target SQL Azure database. Use the first part of the server name and the user name for the administrator as shown below. If you need to change the server name suffix to match your server location, click on the Tools | Project Settings command, click on the General button in the lower left of the dialog and then click on the SQL Azure option in the tree above. From there you can change the suffix value. 

 

    5. Expand out the Databases node to see the name of the database created in the SQL Azure portal. You will see a Schemas folder under the database name that will be the target for the Sakila database as shown below. 

  6. With the Tables node selected in the MySQL Metadata Explorer, click on the Convert Schema command to create schema named Sakila containing the Sakila tables within the SSMA project  shown below. 

 

    7. Right click on the Sakila schema above and choose the Synchronize with Database command to write the schema changes to your SQL Azure database and then click OK to confirm the synchronization of all objects. This process creates a SQL Server schema object within your database named Sakila and then all the object from your MySQL database go into that schema.
   8. Select the Tables node for the Sakila database in the MySQL Metadata Explorer and then issue the Migrate Data command from the toolbar. Complete the connection dialog to your MySQL database and the connection dialog to the SQL Azure database to start the data transfer. Assuming all goes well, you can dismiss the Data Migration Report as shown below.

At this point, you now have all of the tables and their data loaded into your SQL Azure database contained in a schema named Sakila.

Validating the Results with the SQL Azure Database Manager

To verify the transfer of the data, you can use the Manage command from the Azure Portal and shown below. Just select the database and press the Manage command.

This will launch the SQL Azure Database Manager program in a new browser window with the Server, Database, and Login information prepopulated in the connection dialog. If you have other SQL Azure databases you want to connect to without having to go to the portal, you can always connect via the URL - https://manage-ch1.sql.azure.com/.
Once connected, you can expand the Tables node and select a table like sakila.film to view the structure of the table. You can click on the Data command in the toolbar to view and edit the table’s data as shown below.

 
The SQL Azure Database Manager will also allow you to write an test queries against your database by Database command and then selecting the New Query button in the ribbon.  To learn more about this tool, check out the MSDN topic – Getting Started with The Database Manager for SQL Azure.

MySQL to SQL Server Migration: Method for Correcting Schema Issues

In the blog post MySQL to SQL Server Migration: How to Use SSMA, I showed the basic steps of migrating the MySQL Sakila-DB sample database to SQL Server 2008 R2 Express using the SQL Server Migration Assistant for MySQL v1.0 (SSMA). The challenge is that SSMA identified 9 errors with the assessment report associated with the customer, film, film_text, payment, rental, staff, and store tables with an estimated manual conversion time of 9.5 hours. There are three classes of errors that came for the report: (1) SSMA does not convert FULLTEXT indexes; (2) Cascaded foreign key changed to NO ACTION to avoid circular references; (3) Cascaded foreign key changed to NO ACTION to avoid multiple paths. I will walk through each of these scenarios for the successful migration of the Sakila-DB. 

Getting Started with Migrating MySQL’s Sakila Database to SQL Server

If you followed the blog post MySQL to SQL Server Migration: How to UseSSMA, you will have a database already named Sakila on your .\SQLEXPRESS instance. You will want to go into SQL Server Management Studio, connect to the .\SQLEXPRESS instance, open up the Object Explorer if it’s not already open, right click on the Sakila database and issue the Delete command. Use the option in the  Delete Object dialog to Close existing connections and click OK.

Running the SSMA for MySQL

Once you have started SSMA for MySQL, you will create a new project called SakilaAllTables and then follow the steps to Reconnect to MySQL. Inside of the MySQL Metadata Explorer, expand out the Databases node along with the Sakila database. Just check the Tables box and leave the other objects unchecked as shown below.

Right click on the Tables node and select the Create Report command. You will see a report that shows the 9 errors across the three classes of errors as I described in earlier.

SSMA is essentially telling you that it’s going to ignore the errors and that you’ll need to go back and fix them up.

Migrate the Tables and Data

You will next perform the following steps that I described in MySQL to SQL Server Migration: How to Use SSMA post:

1. Reconnect to SQL Server. You’ll be prompted again to create the Sakila database as part of the connection process.
2. Convert Schema. You’ll create the model within the SQL Server Metadata Explorer. You can ignore the errors, warnings and informational messages in the Output window for now.
3. Synchronize with the Database. You’ll right click on the Tables node under the dbo schema and issue the Synchronize with Database command and click ok to create the tables on the SQL Server instance.
4. Migrate Data. Finally, you will navigate back up to the Tables node in the MySQL Metadata Explorer and issue the Migrate Data command. Follow the connection dialogs and all 16 tables should populate without errors.

Check Out the Results Using SQL Server Management Studio (SSMS)

If you have SQL Server Management Studio still open after deleting the first version of the Sakila database, right click on the Databases node and issue the Refresh command to see the new Sakila database. I’ll now walk through the steps to create a diagram for the Sakila database.

1. Go ahead and expand out the Sakila database and you will see one folder called Database Diagrams.
2. Right click on the Database Diagrams folder and issue the Install Diagram Support command. This command creates the stored procedures in the database so that you can see the relationships between the tables. Click Yes for the confirmation message box.
3. Right click on the Database Diagrams folder and now issue the New Diagram command.
4. Within the Add Table dialog, select all of the tables and click the Add button.

Once all the tables are loaded into the diagram, click on the Close button and then execute the Database Diagram | Zoom > To Fit command. You should see a diagram that looks like the one below. 

Migrating MySQL Cascaded Foreign Keys to Avoid Circular References: M2SS0036

Within the SSMA assessment report, one of the errors listed was as follows: “M2SS0036: SQL Server Migration Assistant for MySql Error message: ON UPDATE action was changed to NO ACTION to avoid circular references of cascaded foreign keys. (2) Estimated manual conversion time: 2 hr(s)”. In SQL Server, you can end up with some interesting scenarios where cascading foreign keys could cause some interesting problems. Here is what the help file says about cascading foreign keys:
"The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default."
SSMA works around the problem by pointing out potential problems and creating the foreign keys using the No Action clause.

Don’t Assume That the Database Design is Correct

Before you go about thinking that you need to start writing table triggers to correct the reported error, you need to ask yourself if the use of cascaded updates is the proper design for the application. Let’s take a look at the Staff table first looking at the MySQL create table statement below.

   1:  CREATE TABLE `staff` (

   2:    `staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

   3:    `first_name` varchar(45) NOT NULL,

   4:    `last_name` varchar(45) NOT NULL,

   5:    `address_id` smallint(5) unsigned NOT NULL,

   6:    `picture` blob,

   7:    `email` varchar(50) DEFAULT NULL,

   8:    `store_id` tinyint(3) unsigned NOT NULL,

   9:    `active` tinyint(1) NOT NULL DEFAULT '1',

  10:    `username` varchar(16) NOT NULL,

  11:    `password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

  12:    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  13:    PRIMARY KEY (`staff_id`),

  14:    KEY `idx_fk_store_id` (`store_id`),

  15:    KEY `idx_fk_address_id` (`address_id`),

  16:    CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE,

  17:    CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE

  18:  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Line 17 contains the foreign key constraint that caused the SSMA conversion error. In this case, the desired behavior just looking at the design of the schema is that when the store_id value changes in the store table, make sure the change gets cascaded into the store_id value for Staff table. In addition, there are cascading update foreign keys against store_id for the Customer and Inventory tables. This seems like a natural thing to do, but you need to ask yourself – do I really need to change the key values if the column doesn’t contain meaningful information? In the case of most of the primary keys in the Sakila database, the values are all some form of an integer value. In the world of database design, this is called a Surrogate key. Since the application doesn’t care about the values, there is no reason to change them.
Although the foreign key on line 16 showed no errors with the cascade update rule for address_id changes from the Address table, there is no reason to use the cascade update rule for the same reason.
The recommended course of action would be to turn off the update cascade option for all foreign keys that are made up of surrogate key values, but there is no harm leaving them at this point.

Migrating MySQL Cascaded Foreign Keys to Avoid Multiple Paths: M2SS0037

In the SSMA assessment report, you will find that four tables have ON UPDATE foreign keys that have issues. These tables include: Film, Payment, Rental, and Customer. Let’s see what’s behind this class of errors.

• Film table. Has one reported error where the foreign key pointing back to the Language table using the language_id column has a problematic ON UPDATE CASCADE clause. SSMA creates the foreign key, but uses the NO ACTION clause. This happened because there is another foreign key pointing back to the Language table using the Film.original_language_id column that uses the ON UPDATE CASCADE clause. Because both use a surrogate key, the errors can be ignored. If you want to change the film$fk_film_language_original foreign key to not use the ON UPDATE CASCADE clause, you can do the following:
• In SSMS, click on the New Query command on the toolbar.
• Paste in the following code and then execute the query.

USE sakila

GO

ALTER TABLE dbo.film

    DROP CONSTRAINT film$fk_film_language_original

GO

ALTER TABLE dbo.film WITH NOCHECK ADD CONSTRAINT

    film$fk_film_language_original FOREIGN KEY

    (

    original_language_id

    ) REFERENCES dbo.language

    (

    language_id

    ) ON UPDATE  NO ACTION 

     ON DELETE  NO ACTION 

GO

• Payment table. Each of the three foreign keys on the Payment table use surrogate keys, so the errors ON UPDATE CASCASE clause can be safely ignored.
• Rental table. Each of the three foreign keys on the Rental table use surrogate keys, so the errors ON UPDATE CASCASE clause can be safely ignored.
• Customer table. Each of the two foreign keys on the Customer table use surrogate keys, so the errors ON UPDATE CASCASE clause can be safely ignored.

Migrating MySQL FULLTEXT Indexes to SQL Server Full-Text Search: M2SS0035

The free SQL Server 2008 R2 Express with the Advanced Services download package provides Full-Text search capabilities similar to MySQL Full-Text search feature. SSMA for MySQL v1.0 doesn’t support the migration of FULLTEXT indexes, but the process is fairly straight forward. You will need to install the Full-Text feature for the SQL Server instance before beginning.

Converting MySQL FULLTEXT Indexes

MySQL can create FULLTEXT indexes on tables that do not have a unique key index. MySQL can also create more than one FULLTEXT index on a table.
The WITH PARSER option can be used only with FULLTEXT indexes. It associates a parser plug-in with the index if full-text indexing and searching operations need special handling. 
Consider the schema for the Film_text table:

CREATE

    TABLE `film_text`

        (

            `film_id` smallint(6) NOT NULL, 

            `title` varchar(255) NOT NULL, 

            `description` text, 

             PRIMARY KEY  (`film_id`) , 

             FULLTEXT  KEY `idx_title_description` (`title`, `description`)  

        )  ENGINE = MyISAM DEFAULT  CHARSET = utf8;

SSMA created the file_text table, but ignored the FULLTEXT key. To create the SQL Server index, you would use the following code:

USE [sakila];

GO

CREATE FULLTEXT CATALOG [Films] AS DEFAULT;

GO

CREATE FULLTEXT INDEX ON dbo.film_text([description]) KEY INDEX pk_film_text_film_id;

GO

You only need to create the catalog once and then use it for other full-text indexes as needed.

MySQL to SQL Server Migration: How to Use SSMA

In this blog, I’m going to walk you through the process of converting the MySQL Sakila-DB sample database to SQL Server 2008 R2 Express using the SQL Server Migration Assistant for MySQL v1.0 (SSMA). The Sakila-DB database has tables, views, stored procedures, functions and triggers that make the conversion interesting. The sample is based on the Inno-DB example, but does have one MyISAM table. SSMA also allows you to migrate your MySQL databases to SQL Azure, but we’ll save that topic for another post.

Downloading SQL Server 2008 R2 Express and SSMA

The easiest way to download SQL Server 2008 R2 Express, SQL Server Management Studio and SSMA is through the Microsoft Web Platform Installer (WPI). Once you’ve downloaded WPI, you can select from a variety of tools and products that can get you up and running using IIS, PHP, and SQL Server in no time.

I’ll focus on the minimum set of tools you need to get SQL Server 2008 R2 Express and SSMA up and running. once you launch WPI, click on the Products tab at the top tool and then select Database in the navigation page. In the image above, I’ve already installed the tools, but for the new install, you’ll click on the Add buttons to the right of the circled products to get you up and running. If you are running your application under PHP, you might also want to select one of the two PHP drivers for SQL Server as well. Once you’ve selected your tools, just click on the install button to start the process.

Downloading the MySQL ODBC Driver

WPI is not without flaws. SSMA requires the “MySQL OSBC Driver 5.1 or above” download to connect to your MySQL instance that comes from the MySQL downloads site. Once at the Download Connector/ODBC page, your need to download either the x32 or x64 version of the driver based on the machine architecture for the system you are running the SSMA client. Just follow the installation instructions from the installer. The default installation settings will be good enough to get you going.

Other Helpful Downloads for SSMA and this Blog

If you don’t already have the Sakila-DB database for MySQL installed, the link to the download and instructions for installing it can be found at the blog post titled “Learn MySQL With Sakila sample Mysql Database”

Using SSMA for MySQL

SQL Server Migration Assistant (SSMA) 2008 for MySQL lets you quickly convert MySQL database schemas to SQL Server 2008, SQL Server 2008 R2 or SQL Azure schemas, upload the resulting schemas the target instance and migrate the data using a single tool.

Licensing SSMA

SSMA is a free tool, but does require you to associate a Microsoft Live ID for identification purposes. You must download a registration key. To help you with the registration process, a License Key Required dialog box opens the first time that you start the SSMA program. Use the following instructions to download a license key and associate the key with SSMA.

To license SSMA

1.      Click Start, point to All Programs, point to Microsoft SQL Server Migration Assistant 2008 for MySQL, and then select Microsoft SQL Server Migration Assistant 2008 for MySQL.

2.      In the License Management dialog box, click the license registration page link.

3.      On the Sign In Web page, enter your Windows Live ID user name and password, and click Sign In.

A Windows Live ID is a Hotmail e-mail address, MSN e-mail address, or Microsoft Passport account. If you do not have one of these accounts, you will have to create a new account. To create a new account, click the Sign up now button.

4.      On the SQL Server Migration Assistant for MySQL License Registration Web page, fill in at least the required fields, which are marked with a red asterisk, and then click Finish.

5.      In the File Download dialog box, click Save.

6.      In the Save As dialog box, locate the folder that is shown in the License Management dialog box, and then click Save.

The default location is C:\Documents and Settings\user name\Application Data\Microsoft SQL Server Migration Assistant\m2ss.

7.      In the License Management dialog box, click Refresh License.

SSMA for MySQL User Interface

After SSMA is installed and licensed, you can use SSMA to migrate MySQL databases to SQL Server 2008 or SQL Azure. It helps to become familiar with the SSMA user interface before you start. The following diagram shows the user interface for SSMA, including the metadata explorers, metadata, toolbars, output pane, and error list pane:

Basic Steps for Migration of MySQL to SQL Server

To start a migration, you’ll need to perform the following high level steps:

1.      Create a new project.

2.      Connect to a MySQL database.

3.      After a successful connection, MySQL schemas will appear in MySQL Metadata Explorer. Right-click objects in MySQL Metadata Explorer to perform tasks such as create reports that assess conversions to SQL Server 2008 R2 Express. You can also perform these tasks by using the toolbars and menus.

You’ll then connect to your instance of SQL Server 2008 R2 Express. After a successful connection, a hierarchy of your existing databases will appear in SQL Server Metadata Explorer. After you convert MySQL schemas to SQL Server schemas, select those converted schemas in SQL Server Metadata Explorer, and then synchronize the schemas with SQL Server.

After you synchronize converted schemas with SQL Server 2008 R2 Express, you can return to MySQL Metadata Explorer and migrate data from MySQL schemas into target database.

Let’s walk through the specifics.

Create a MySQL Migration Project

To get started, you’ll create your new project using the File | New Project command.

You’ll enter in your project name and then confirm that you are migrating to SQL Server. The Migrate To dropdown also allows you to choose SQL Azure, but that’s for another post. Once you make your selection, you are locked into the target backend.

Connect to a MySQL Database

To Connect to your MySQL instance, you’ll issue the File | Connect to MySQL command or click on the tool bar button that launches the following dialog:

 

If you forgot to to install the MySQL ODBC driver mentioned at the beginning of this blog, simply go to the download site, install the driver, and then issue the Connect to MySQL command.

Create Report of Potential Conversion Issues

Once you are connected, you’ll see the MySQL instance in the MySQL Metadata Explorer. You’ll want to expand the Databases node along with the Sakila database node and then check the box next to Sakila. This selects the database you want to migrate. Next, right click on the Sakila database and select the Create Report command or press the Create Report command on the toolbar as shown below.

Here is an example of the Assessment Report for the Sakila database.

The Assessment Report window contains three panes:

·         The left pane contains the hierarchy of objects that are included in the assessment report. You can browse the hierarchy, and select objects and categories of objects to view conversion statistics and code.

·         The content of the right pane depends on the item that is selected in the left pane.

If a group of objects is selected, such as schema, the right pane contains a Conversion statistics pane and Objects by Categories pane. The Conversion Statistics pane shows the conversion statistics for the selected objects. The Objects by Categories pane shows the conversion statistics for the object or categories of objects.

If a function, procedure, table or view is selected, the right pane contains statistics, source code, and target code.

o        The top area shows the overall statistics for the object. You might have to expand Statistics to view this information.

o        The Source area shows the source code of the object that is selected in the left pane. The highlighted areas show problematic source code.

o        The Target area shows the converted code. Red text shows problematic code and error messages.

·         The bottom pane shows conversion messages, grouped by message number. You can click Errors, Warnings, or Info to view categories of messages, and then expand a group of messages. Click an individual message to select the object in the left pane and display the details in the right pane.

In future blog posts, we’ll work through the specific problems that are in this report. For now, we’ll ignore the problematic objects for the schema and data migration steps. For now, close the report and then uncheck Functions, Procedures and Views nodes to take them out of the conversion. Then uncheck the tables with errors as shown below.

Go ahead and click on the Create Reports command to verify that there are no errors.

Connect to SQL Server

It’s time to connect SSMA to your SQL Server 2008 R2 Express instance. For the Server name, you’ll need the server name and instance for the target server. Since we are using the WPI installation of SQL Server 2008 R2 Express, you’ll enter in the server name as .\SQLEXPRESS.

You can select an existing database to migrate to using the Database control. You can also type in the name of a new database. In this case, use Sakila as shown below.

 

Once you click connect, SSMA prompts you if you want to create the database. Choose Yes to create the new database. When connecting to SQL Server Express instances, you’ll receive the following warning indicating that you won’t be able to use the server-side data migration engine. This engine is used for larger migration projects.

You can Continue from this dialog to start the actual migration process.

Convert Schema

Now that you’ve connected to the target SQL Server instance, SSMA enables the Convert Schema command. Click the Convert Schema command. Once the conversion is finished, you should see the SQL Server Metadata Explorer populated with the tables listed in bold as shown below.

 

Synchronize with Database

To write the tables to the target, select the dbo node in the SQL Server Metadata Explorer and then issue the Tools | Synchronize with Database command. SSMA displays the Synchronize with Database dialog as shown below. In this example, the Tables node was manually expanded to show that no tables are actually on the database at this time.

When you click OK, SSMA issues the CREATE TABLE statements to create the objects on the SQL Server target. There are some errors in this example because many of the tables selected have foreign key relationships to some of the tables that we excluded earlier. These errors can be ignored for now.

Migrate Data

The last step is to migrate the data into the tables. To complete the migration, select the Tables node within the MySQL Metadata Explorer for the Sakila database. Then issue the Tools | Migrate Data command or press the command on the toolbar. The Data Migration process requires you to connect to the MySQL database and to the SQL Server database again. SSMA then proceeds with the data migration process and displays the Data Migration Reports as shown below.

 

Using SQL Server Management Studio

The migrated tables are now ready on the target SQL Server instance. To see the results, launch SQL Server Management Studio (SSMS) and connect using the server name as .\SQLEXPRESS. Expand out the Databases node to see the Sakila database. Expand the out the Sakila database tables and then right click on the actor table and issue the Select Top 1000 Rows command to view the data as shown below

SQL Server Management Studio that is part of the WPI is a free rich Windows client tool from Microsoft that offers a rich development and management experience like  SQLyog and MONyog.