Migrating MySQL Databases to SQL Azure using SSMA–Part 2

In the last post, we saw how to download and install SQL Server Migration Assistant for MySQL v5.0 and configure utility database, In this post we will see how to create a new migration project, add MySQL databases and SQL Server databases for conversion, analyze and prepare migration report.

Start the SSMA for MySQL (32 bit) by clicking on Start Menu –> All Programs –> Microsoft SQL Server Migration Assistant for MySQL 5.0

Create a New Project from File Menu

Specify the Name of Migration project, select the location where you want to save the migration project and Target to SQL Azure (Other supported targets are SQL Server 2005, 2008 / 2008 R2, “Denali”)

Click on “Connect to MySQL” to connect to Source MySQL Server

Specify the Server name, User name and Password, If required change the other parameters based on your MySQL Configuration and click “Connect”

Once Connected, MySQL Metadata Explorer will be populated with the list of MySQL Databases and UDFs available

For today’ demo, we will migrate “ClassisModels” database, you can expand the objects node to view the list of objects available in the database, You can select the list of object you want to migrate

Now click on “Connect to SQL Azure” on tool bar to connect to destination SQL Azure server and the specify the server name, user name and password and click “Browse”

Click “Yes” to proceed, The context menu will display the list of databases available and an option to create new database

Let us create New Database named “ClassicModels_DB” by clicking on “Create New Database”

Specify the name of the database and then select Database size and click “Create” to create the new database

Once the database is created, you will be able to see the above message, Click “Ok” to proceed. The new database name will be populated in database combo automatically

Click “Connect” to proceed

Once connected, SQL Azure database will be loaded in SQL Azure Metadata Explorer

Schema Mapping

Now let us modify the target schema, By default the Target schema will be set as Source database name, we can change it to our existing schema ex. dbo

Select the source schema and click “Modify”

Click on “…” button to browse and select the existing Schema, once selected, click “Select” to proceed

and then click “Ok” update the target the schema for selected source schema

Now we have successfully mapped the schema, let us map the types

Type Mapping

Type mapping window display the source type and destination type , if you want to change to change an existing mapping you can select a type and click “Edit”

Select the new Target Type and specify the length if required and click “Ok” to proceed

You can also add a new type mapping by clicking on “Add”

Character Set Mapping

In Character Set mapping window, you can view and review the character set mapped between Source and Destination database, you  can make changes by selecting the Target Charset Type combo and then click “Apply”

SQL Modes

In SQL Modes window, you can select what effective SQL Modes will be used during migration

You can make the preferred changes and click “Apply”

Now we have successfully created a new MySQL migration project, connected source and destination databases and configured the migration properties.

Let us now Analyze the migration and see the reports

Right click on the Source database name “Classicmodels” and click on “Create Report”

Once the report is created, it will be displayed automatically

If there is any errors in analysis, the object will be marked in Red, and also the messages will be displayed in bottom pane.

Based on the error message, we can fix the issue and then migrate.

In the next post, we will how to synchronize the MySQL schema to SQL Azure database and then migrate data.