[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.
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 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.
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.
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.
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.
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.
To learn more about SQL Azure, see the following resources.
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 ?