By Bill Ramos, Advaiya Inc.
[Updated 2/6/2012 Jiafei Yuan- 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’ll walk through the various options you have for controlling data type migration changes within SQL Server Migration Assistant for MySQL v1.0 [Updated: Please obtain the lastest SSMA for MySQL](SSMA). SSMA provides several difference ways to map data types from MySQL to SQL Server that includes: project level settings; type mapping at the table level, charset mapping at the table level, and modifying the resulting data types after performing the convert schema command.
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 want change the SSMA behavior for mapping data types from MySQL to SQL Server for the entire project, you can use the Type Mapping page within the Default Project Settings dialog as shown below.
To get to the dialog, select the Tools | Default Project Settings command. Then click on the Type Mappings selector in the lower left portion of the Default Project Settings dialog. In general, you shouldn’t have to change the default settings. With this dialog, you have the ability to change the type mapping based on the display width, precision or scale of the source type to a specific type in SQL Server. In the example above, the second line maps a bigint data type with a display with from *, 255. The * in this case means anything less than 255. With SSMA, you may choose to optimize the storage on SQL Server if the display value is less than 10.
To adjust the bigint mapping, first select the bigint[*,255] mapping by selecting it in the dialog and issuing the Remove command. This makes room for the lower mappings.
Next select the Add command to display the New Type Mapping dialog as shown below.
In this example, if the display length of the bigint is 2 or less, it could be mapped to a tinyint. You can then do the same for bigint(3…4) to map to a smallint, bigint(5…9) to map to an int, and bigint(10…255) to bigint. After adding these mappings, the Type Mapping table should look like the one below.
This will change the data type mappings for all new projects in SSMA.
In this next example, I’ve created a new project and connected to the MySQL Sakila database using SSMA. Please refer to MySQL to SQL Server Migration: How to use SSMA on how to setup the connection. If you navigate to the Tables node under the Sakila database, you will see the Type Mapping tab shown below.
Any modifications that you make to the Type mappings will impact all the tables in the Sakila database. At the right side of the tool, you’ll see category: Tables that will remind you of the scope of your change.
If you then navigate to a specific table, you can control the settings for just that table and you’ll see category set to the table name as a reminder.
Both SQL Server and MySQL use the NVARCHAR and NCHAR data types to store two byte Unicode values. If you know that the data for your table columns don’t use Unicode values, you may want to save storage space my using the SQL Server VARCHAR or CHAR data types. The first place that you would think to turn to is the Type Mapping dialog. The problem is, when you try to create a mapping from NVARCHAR to VARCHAR, the New or Edit Type Mapping dialog does not offer a VARCHAR choice as shown below.
SSMA does this because the ultimate determination if whether or not MySQL CHAR and VARCHAR data type support for Unicode is controlled by table’s DEFAULT CHARSET and COLLATE values. For more information on use of character set in refer to MySQL’s character sets and collations demystified)
The Sakila database tables are all set to use a DEFAULT CHARSET=utf8. This means that by default, SSMA will migrate a VARCHAR or CHAR column data type to NVARCHAR to NCHAR because UTF-8 uses Unicode two bytes for each character.
Let’s see how this works with the Address table in Sakila. Select the Address table in the MySQL Metadata Explorer with everything else checked off and then select the Create Report command. SSMA converts all of the MySQL VARCHAR to SQL Server NVARCHAR.
If you want SSMA to use VARCHAR for the SQL Server table to save storage if you don’t need Unicode support, you need to change the Charset mapping for UTF-8 as follows.
When you run the Create Report command, you will see that SSMA converted the MySQL VARCHAR types to SQL Serve VARCHAR types. SSMA also displays a information message – M2SS0055: Data type was converted to VARCHAR according to character set mapping for utf8 character set. This message can be ignored and just acts as a reminder that a mapping took place.
With the final option for data type mapping, SSMA allows you to change data types after the Convert Schema command before you write the changes to the target SQL Server database. You would use this option if the table has a mixture of columns that have Unicode data. In this example, we’ll use the Actor table to demonstrate how to convert the data types after the convert action as follows:
You can confirm the result by going into Object Explorer within SQL Server Management Studio and expanding the Columns node for the dbo.actor table as shown below.
For MySQL procedures and functions, you also have the ability to control data type and charset mappings for variable declarations or input/output arguments. In the example below, the rewards_report procedure has a parameter named MIN_MONTHLY_PURCHASES with a data type of TINYINT UNSIGNED.
If you wanted to map the this data type throughout the procedure to a SQL Server smallint data type, you can perform the following steps.
SSMA provides three options for adjusting MySQL data types to SQL Server.
All three options are available for Oracle, Sybase and Access versions of SSMA as well.
Hi I am currently converting mysql database to SQL server using SSMA but I cant find how to map source data type 'time' to target data type 'time(0)' is this possible? By the way this is a great tool nice job thanks!!!
SSMA for MySQL isn't supported map source data type 'time' to target data type 'time (precision)', you can change target DB after the conversion.