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.
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.
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.
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”
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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 http://blogs.msdn.com/billramo and on Twitter at http://twitter.com/billramo.
Thank you for this. I've been banging my head against SSMA on and off for a few days. I followed your walk-through and data migrated perfectly.
leider wird bei der migration alles in das standart verzeichnis des sql servers geschrieben, wie ändere ich die Ziele pro datenbank?
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.
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 server
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 help
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.
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.
Newbie Question! Once the data is migrated does it mean that the data will not be available in Mysql Database ?
I will try this =D im off to mysql due to limitation =D
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 ?
Is it possible to get a license for SSMA for a machine without an internet connection?
Try ESF Database Migration Toolkit
Alternatively you can automate your migration/ sync job using
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?
The link in the SSMA app to the page to get the license key is not found... 404 error.