Converting from MySQL to SQL Server: Working with Data Types

  • Article

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.

Controlling Data Type Mapping with Project Settings

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.

00 Default Project Settings

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.

01 New Type Mapping

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.

02 Bigint mapping

This will change the data type mappings for all new projects in SSMA.

Controlling Type Mapping at the Table Level

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.

03 Type Mapping Tables level

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.

Using Charset Mapping to Control the Conversion of MySQL VARCHAR to SQL Server VARCHAR/NVARCHAR

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.

04 varchar mapping

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.

05 Address table convert to 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.

05 Address table charset mapping

  1. Select the Address table in the MySQL Metadata Explorer. If you want to apply the change to all tables, you would select the Tables folder.
  2. Click on the Charset Mapping tab.
  3. Scroll down to select the utf8 charset name.
  4. Select CHAR/VARCHAR from the Target Charset Type drop down menu
  5. Click Apply to save the changes for the table or all tables if you applied the setting to the Tables folder.

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.

07 Mapped to varchar

Changing the Data Type Mapping After Convert Schema

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:

  1. If you haven’t done so already, use the Connect to SQL Server command to connect to your target SQL Server instance.

  2. Select just the actor table within the MySQL Metadata Explorer.

  3. Choose the Convert Schema command to create the actor table in the SQL Server Metadata Explorer with the results shown below
    08 Convert Schema

  4. Click inside of the Data Type cell for the first_name column and edit the nvarchar(45) data type value to varchar(45).

    Before
    09 change data type

    After
    10 change data type after

  5. Click the Apply button to save the data type change into the project model for the actor table.

  6. Right click on the actor table within the SQL Server Metadata Explorer and choose the Synchronize with Database command and confirm the changes to save the changes to the SQL Server instance.

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.

11 end result

Data Type Mapping for Procedures and Functions

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.

12 Procedure Mapping

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.

  1. Select the rewards report in the MySQL Metadata Explorer
  2. Navigate to the Type Mappings tab and look for unsigned tinyint and edit the conversion to map to a target type of smallint
  3. Run the Create Report and Convert Schema commands to generate the new SQL Server rewards_report stored procedure. You’ll see in the Parameters tab for SQL Server the new type.
  4. Run the Synchronize with Database command to write the updated stored procedure to SQL Server.

Summary

SSMA provides three options for adjusting MySQL data types to SQL Server.

  1. Type Mapping at the Project, Tables or Table level. Provides a way to map data types based on display width, precision or scale to an equivalent data type on SQL Server to reduce storage size.
  2. Charset Mapping at the Tables or Table Level. Provides a way to map how you want to convert character data types for tables to either CHAR/VARCHAR or NCHAR/NVARCHAR to save storage for the table if you aren’t using Unicode values.
  3. Direct schema change to the table. Provides a way to selectively change the resulting SQL Server data type before synchronizing the table to the target database.

All three options are available for Oracle, Sybase and Access versions of SSMA as well.