MySQL to SQL Server Migration: How to Use SSMA
By Bill Ramos, Advaiya
[Updated 2/6/2012 Han Wong - 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, 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 [Updated: Please obtain the lastest SSMA for MySQL] (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
You’ll also want to download the “Guide to Migrating from MySQL to SQL Server 2008” white paper, though this blog and others to follow will keep you on track.
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
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.
In the License Management dialog box, click the license registration page link.
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.
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.
In the File Download dialog box, click Save.
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.
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:
S
Basic Steps for Migration of MySQL to SQL Server
To start a migration, you’ll need to perform the following high level steps:
Create a new project.
Connect to a MySQL database.
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.
The top area shows the overall statistics for the object. You might have to expand Statistics to view this information.
The Source area shows the source code of the object that is selected in the left pane. The highlighted areas show problematic source code.
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.
BIO
Bill Ramos is the SQL Server Work Stream Manager for Advaiya. During his 15 years at Microsoft as a program manager, he has been on teams that have shipped the following products: Project Houston, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005, SQL Server 2000, SQL Server 7.0, SQL Server 6.5, Ashton-Tate/Microsoft SQL Server for OS2 (at Ashton-Tate), Excel 2003, Access 2003, Access XP, Access 2000. You can find his personal blog at https://blogs.msdn.com/billramo and on Twitter at https://twitter.com/billramo.
Comments
Anonymous
March 06, 2011
The comment has been removedAnonymous
April 25, 2012
leider wird bei der migration alles in das standart verzeichnis des sql servers geschrieben, wie ändere ich die Ziele pro datenbank?Anonymous
July 11, 2012
Thank you for your valuable article, I want to ask you about a issue is that when I give “Migrate Data” Command. it gives a problem. As you have put a screen where we can see Data Migration Report. I have got same report but without "To and other Column data". it shows only "From" column data.Anonymous
March 14, 2013
una duda se puende migrar los procedimiento de mysql a sql server 2008 ? es que necesito migrar la base de datos pero tengo mas de 200 procedimientos y quiero cambiarme a sql serverAnonymous
April 23, 2013
Hello, I am unable to create convert schema after connecting to SQL Server.The convert schema option is deactivated..Should the name of MYSQL database and SQL Server database be the same?? Pls helpAnonymous
July 02, 2013
Hi, I am trying convert SQl statements from MySql to MS Sql using SSMA but getting error that SSMA ERROR: Unparsed Sql. I am adding sql in Statements and then generating report and getting thsi error. ThanksAnonymous
July 22, 2013
PDS I got the unparsed sql error when I tried to migrate some views. The account I was using on MySQL did not have access to the SQL statements in the view in the schema, so all that showed for those views in SSMA on the SQL tab was a blank screen. I started a new project using a different account which had access to the SQL, then it worked. I guess you have to check that the account has access to the SQL statements, and also that the SQL can be parsed. I did this in MySQL Query browser, open the schema, check you can run the SQL.Anonymous
July 22, 2013
Hi, Newbie Question! Once the data is migrated does it mean that the data will not be available in Mysql Database ?Anonymous
August 14, 2013
I will try this =D im off to mysql due to limitation =DAnonymous
August 27, 2013
Hi, Thanks for this article it's really helpfull, but when i try to create a report software returns this error "An unexpected error occurred. Please send the log file to product support. For more information, see "Getting SSMA Assistance" in the product documentation." Windows 7 32 Bit and ODBC Version is 5.1.8, any idea about how i can solve it ?Anonymous
September 06, 2013
Is it possible to get a license for SSMA for a machine without an internet connection?Anonymous
September 07, 2013
Try ESF Database Migration Toolkit http://www.easyfrom.net/Anonymous
September 09, 2013
Alternatively you can automate your migration/ sync job using convertdb.com/.../mssqlAnonymous
September 16, 2013
When I try to connect to a MySQL database, I put 'root' or some other account name for the username. But when I try to connect, SSMA adds an '@192.168.1. to the end of my username and then access is denied. How do you stop it from adding the dang @my-local computer info? I can't seem to stop if from doing this. Does this thing allow you to connect to remote computers?Anonymous
October 20, 2013
The link in the SSMA app to the page to get the license key is not found... 404 error.Anonymous
October 28, 2013
The registration page is gone. I hope who ever came up with the stupid idea to get a license this way got fired.Anonymous
October 28, 2013
Hey Chris, I understand your FRUSTRATION. Please reach out to Microsoft Support via the link : support.microsoft.com/.../default.aspx I bet you will get excellent response from this team ! Regards, AjayAnonymous
November 02, 2013
Do, any of you guys have a license have a license file that can be used since the page is down and Microsoft is a nightmare to get support from? CheersAnonymous
November 07, 2013
Hello , Thanks for the post but There is a big problem in the license in the SSMA for MYSQL which Microsoft didn't fix until now . The link to the license refer to a page that doesn't exist . social.msdn.microsoft.com/.../licence-page-for-microsoft-sql-server-migration-assistant-mysql-missingAnonymous
November 08, 2013
Thanks for bringing this to our attention. We have fixed the fwlink to point to a download for the license file. The direct download link is www.microsoft.com/.../details.aspx.Anonymous
January 07, 2014
Thanks much..... it really helped me to complete the migration from mysql to sql serverAnonymous
July 14, 2014
The comment has been removedAnonymous
August 26, 2014
When I covert schema the foreign key relationships are maintained, but after I migrate data the foreign keys gets lost.. Any ideas on why this might be happening? ThanksAnonymous
September 23, 2014
i have installed Mysql Administrator v1.2.2 beta. Now when I start to make the new Schema it give me the message that you cannot create the database and shows an error 404 ; Please help me How can I solve it.Anonymous
January 25, 2015
please i have downloaded the migration assistant, but it will not recognise the odbc for 5.1,5.2 0r 5.3 that i downloaded in various form 0f 32 or 64 bits, please what can i do, i am using windows 7 64 bit thanksAnonymous
March 11, 2015
Hi I am currently migrating data from mysql to SQL and i am using SSMA. I have successfully connected MySQL and SQL and when i am trying to convert the Schema i get an error stating " An unexpected error occurred. Please send the log file to product support. For more information, see "Getting SSMA Assistance" in the product documentation. Object reference not set to an instance of an object." Please help me unable to sort this and I am stuck in this from past few days.Anonymous
August 08, 2015
I had good experience with www.spectralcore.com/fullconvertAnonymous
October 20, 2015
I followed all the steps and the data failed to migrate at the very last step - telling me in the report that it, well, that it failed to migrate.Anonymous
October 23, 2015
Newbie Question! Once the data is migrated does it mean that the data will not be available in Mysql Database ? www.dauthu-dvbt2.comAnonymous
June 13, 2016
What happened or what was the remedy for errors for dependencies like foreign keys, triggers, constraints??Thanks,Harvey..