Migrating from MySQL to SQL Azure Using SSMA
[Updated 2/7/2012 Selina Jia - Microsoft SQL Server Migration Assistant (SSMA) for MySQL v5.2. The information provided below is still valid for SSMA for MySQL v5.2. Users should download the lastest SSMA for MySQL]
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 [Updated: Please obtain the lastest SSMA for MySQL] (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 https://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 you 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 Server Migration: How to use SSMA” blog post that I will outline below.
Click on the Connect to MySQL toolbar command to complete the MySQL connection information.
Expand out the Databases node to expand the Sakila database and check the Tables folder as shown below.
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.
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.
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.
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 as shown below.
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.
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.
Additional SQL Azure Resources
To learn more about SQL Azure, see the following resources.
Comments
- Anonymous
November 14, 2012
The system appear to have error, it say i need a 5.1.6 driver and above, but i already installed 5.2.2 ?? how to resolve this error ?