I will be presenting at New Jersey SQL Server User Group
4 Century Drive
Parsippany , NJ, 07054, U.S.A
Meeting Time: 6:30 PM EST – 8:30 PM EST
Session Title: “Migrating Data to SQL Azure”
Migrating on-premise data to SQL Azure and vice versa is one of the new challenge for Database Developers and Administrators.
This session demonstrates what data can be moved to SQL Azure and how easily we can migrate data from On Premise SQL Server to SQL Azure and also from other Heterogeneous database environments.
This session includes live demonstrations using SQL Azure DAC Import Framework tools, SQL Server/Azure Migration Tools and will be highly interactive session.
NJSQL User Group Web site http://www.njsql.org/
Join NJSQL User Group at http://groups.google.com/group/nj-sql-server-user-group
NJSQL Job Posts http://www.linkedin.com/groups?gid=1964935&trk=myg_ugrp_ovr
I will post the presentation slides tomorrow after the meeting
In the previous posts, we saw how to download and install SQL Server Migration Assistant for MySQL v5.0 and configure utility database, and how to create a new migration project, add MySQL databases and SQL Server databases for conversion, analyze and prepare migration report.
In this post we will how to synchronize the MySQL schema to SQL Azure database and then migrate data
To convert the MySQL schema to SQL Azure schema, select the source database “ClassicModels” in MySQL Metadata Explorer and the right click and click on “Convert Schema”
Once the schema is converted, we will get the messages in output window similar to this (Messages will vary based on your database), if the message returns any error we need to fix it before migration
Once the schema conversion is complete, to migrate the schema, Right click on Target database in SQL Azure Metadata explorer and click on “Synchronize with Database”
Specify the password for the target server and click “Connect”
The schema migration preview window will display the list of objects being migrated, Click “Ok” to proceed
Migration process will be displayed in Output window, Once complete the output window will display the below message
Now we have successfully migrated the schema, To migrate the data, right click on Source Database and click “Migrate Data”
Specify the MySQL Server credentials and click “Connect”
Specify SQL Azure Target server credentials and click “Connect” to proceed
The data migrations process will prepare the data and start uploading the data to SQL Azure, the process details will be displayed in output window and the following message and below Data Migration Report will be displayed once the data migration completes successfully.
You can save the Data Migration report as CSV file for your reference.
You can connect to SQL Azure server using Project Houston or SQL Server Management Studio and query the data
Now we have successfully migrated the database schema and data from MySQL database to SQL Azure
Hope you all find this information useful !!!
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
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
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 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”
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.
Microsoft SQL Server Migration Assistant (SSMA) for MySQL is a tool to automate migration from MySQL database to SQL Server or SQL Azure.
SSMA for MySQL v 5.0 is freely available from Microsoft, you can download it from the following link http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ac017a90-3b53-49b6-92a7-0f91623ef530
It also has free support. SSMA automates the migration process by doing schema analysis, preparing migration report and migrating data.
SSMA for MySQL v 5.0 supports migration from MySQL 4.1 and higher to SQL Server 2005 / 2008 / 2008 R2 and SQL Azure
To install and run this we need MySQL ODBC Connection v 5.1, which you can download from the following link http://www.mysql.com/downloads/connector/odbc/
Let us see how to install the SSMA for MySQL v 5.0
Once downloaded the setup from the above URL, extract the zip file and run “SSMA for MySQL 5.0”
Click “Next” to proceed
Accept the license agreement and click “Next” to proceed
Select Usage Report Settings, if you wish to report and then click “Next” to proceed
Select “Setup Type” and click Next to Proceed
Click “Install” to confirm and proceed with installation
Click “Finish” to complete the installation
Now we have successfully installed SSMA for MySQL v5.0.
In addition to this, we need to install SSMA for MySQL Extension Pack as well
Run the “SSMA for MySQL 5.0 Extension Pack.exe” and click “Next” to proceed
Accept the license agreement and Click “Next” to proceed
Select Setup type and click “Next” to proceed
Click “Install” to confirm and proceed with installation
Click “Next” to complete the installtion, and to start the configuration process of Utility database
Select the SQL Server instance on which you want to install the Utility database and click “Next” to proceed
Specify Authentication information and click “Next” to proceed
Provide a strong password to protect the authentication information stored in Utility database and click “Next” to proceed
Select “Install Utilities…” option and click “Next” to finish the installation
Once installed the following message will be displayed
If you want to install Utility database in another server, click “Yes” or click “No” to finish the installation
Click “Exit” to complete the installation.
Now we have successfully installed and configured the SSMA extension pack for MySQL.
In the next post we will see how to create a new migration project, analyze the MySQL database schema, prepare migration report and migrate data.
Step 1: Download and Install “Microsoft SQL Server Migration Assistant 2008 for Access v 4.2” from
Step 2: Register SSMA 2008 for Access and download License and install the License
Step 3: Run SSMA 2008 for Access v 4.2
Click Next to create a new Migration Project
Specify Project Name and Select Project File Location and select Migration To: “SQL Azure”
Click “Add Databases” to Select the Access Database(s) you want to migrate
If you are not sure the location of your database , click “Find Databases” to find it
You can search using the following criteria
1. Specify a folder such as C:\Databases
2. Specifying a part of the file name
3. Created or Updated Date
4. Size of the database
5. Owner of the database
After adding you databases, Click “Next” to proceed
Select the Tables and Queries you want to Migrate
Specify “SQL Azure” Server Name, username and password and the click “Browse”
Click “Yes” to Continue
You can select any existing SQL Azure database, or Create New SQL Azure Database
Specify Database Name, You can select Database Size as 1 GB or 10 GB and then click “Create”
Click “Ok” to proceed
If you still want to continue using Access as Front End for your SQL Azure database, check “Link Tables” option to create Linked Tables in your Access database and click “Next” to proceed
Once the scripts are prepared and ready, you will be prompted to confirm the SQL Azure information again
Enter the Password and click Connect
Review the List of tables and its associated Indexes and Triggers ready to be migrated and then click “Ok” to proceed
The above Legends explains the directions of data migration
You can click on legends “Blue Arrow” to change the direction, in the above screenshot, we have selected “Order Details” not to be migrated
Click “Ok” should start the migration process
After migration, you should be able see the tables migrated in SQL Azure server
For all the tables, a TimeStamp field will be added as last column for enabling continuous Data Migration
Using SQL Azure Metadata Explorer, You can generate Reports to identify the issues in migration or Migrate data from one or multiple tables
Sample Migration Report
Table level report
Now you can access your data in SQL Azure using SQL Server Management Studio or SQL Azure Management Portal or Any Applications supporting SQL Azure connections
Happy Migration !!!