MySQL to SQL Server Migration: Method for Correcting Schema Issues

In the blog post MySQL to SQL Server Migration: How to Use SSMA, I showed the basic steps of migrating the MySQL Sakila-DB sample database to SQL Server 2008 R2 Express using the SQL Server Migration Assistant for MySQL v1.0 (SSMA). The challenge is that SSMA identified 9 errors with the assessment report associated with the customer, film, film_text, payment, rental, staff, and store tables with an estimated manual conversion time of 9.5 hours. There are three classes of errors that came for the report: (1) SSMA does not convert FULLTEXT indexes; (2) Cascaded foreign key changed to NO ACTION to avoid circular references; (3) Cascaded foreign key changed to NO ACTION to avoid multiple paths. I will walk through each of these scenarios for the successful migration of the Sakila-DB. 

Getting Started with Migrating MySQL’s Sakila Database to SQL Server

If you followed the blog post MySQL to SQL Server Migration: How to UseSSMA, you will have a database already named Sakila on your .\SQLEXPRESS instance. You will want to go into SQL Server Management Studio, connect to the .\SQLEXPRESS instance, open up the Object Explorer if it’s not already open, right click on the Sakila database and issue the Delete command. Use the option in the  Delete Object dialog to Close existing connections and click OK.

Running the SSMA for MySQL

Once you have started SSMA for MySQL, you will create a new project called SakilaAllTables and then follow the steps to Reconnect to MySQL. Inside of the MySQL Metadata Explorer, expand out the Databases node along with the Sakila database. Just check the Tables box and leave the other objects unchecked as shown below.

Right click on the Tables node and select the Create Report command. You will see a report that shows the 9 errors across the three classes of errors as I described in earlier.

SSMA is essentially telling you that it’s going to ignore the errors and that you’ll need to go back and fix them up.

Migrate the Tables and Data

You will next perform the following steps that I described in MySQL to SQL Server Migration: How to Use SSMA post:

1. Reconnect to SQL Server. You’ll be prompted again to create the Sakila database as part of the connection process.
2. Convert Schema. You’ll create the model within the SQL Server Metadata Explorer. You can ignore the errors, warnings and informational messages in the Output window for now.
3. Synchronize with the Database. You’ll right click on the Tables node under the dbo schema and issue the Synchronize with Database command and click ok to create the tables on the SQL Server instance.
4. Migrate Data. Finally, you will navigate back up to the Tables node in the MySQL Metadata Explorer and issue the Migrate Data command. Follow the connection dialogs and all 16 tables should populate without errors.

Check Out the Results Using SQL Server Management Studio (SSMS)

If you have SQL Server Management Studio still open after deleting the first version of the Sakila database, right click on the Databases node and issue the Refresh command to see the new Sakila database. I’ll now walk through the steps to create a diagram for the Sakila database.

1. Go ahead and expand out the Sakila database and you will see one folder called Database Diagrams.
2. Right click on the Database Diagrams folder and issue the Install Diagram Support command. This command creates the stored procedures in the database so that you can see the relationships between the tables. Click Yes for the confirmation message box.
3. Right click on the Database Diagrams folder and now issue the New Diagram command.
4. Within the Add Table dialog, select all of the tables and click the Add button.

Once all the tables are loaded into the diagram, click on the Close button and then execute the Database Diagram | Zoom > To Fit command. You should see a diagram that looks like the one below. 

Migrating MySQL Cascaded Foreign Keys to Avoid Circular References: M2SS0036

Within the SSMA assessment report, one of the errors listed was as follows: “M2SS0036: SQL Server Migration Assistant for MySql Error message: ON UPDATE action was changed to NO ACTION to avoid circular references of cascaded foreign keys. (2) Estimated manual conversion time: 2 hr(s)”. In SQL Server, you can end up with some interesting scenarios where cascading foreign keys could cause some interesting problems. Here is what the help file says about cascading foreign keys:
"The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default."
SSMA works around the problem by pointing out potential problems and creating the foreign keys using the No Action clause.

Don’t Assume That the Database Design is Correct

Before you go about thinking that you need to start writing table triggers to correct the reported error, you need to ask yourself if the use of cascaded updates is the proper design for the application. Let’s take a look at the Staff table first looking at the MySQL create table statement below.

   1:  CREATE TABLE `staff` (

   2:    `staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

   3:    `first_name` varchar(45) NOT NULL,

   4:    `last_name` varchar(45) NOT NULL,

   5:    `address_id` smallint(5) unsigned NOT NULL,

   6:    `picture` blob,

   7:    `email` varchar(50) DEFAULT NULL,

   8:    `store_id` tinyint(3) unsigned NOT NULL,

   9:    `active` tinyint(1) NOT NULL DEFAULT '1',

  10:    `username` varchar(16) NOT NULL,

  11:    `password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

  12:    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  13:    PRIMARY KEY (`staff_id`),

  14:    KEY `idx_fk_store_id` (`store_id`),

  15:    KEY `idx_fk_address_id` (`address_id`),

  16:    CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE,

  17:    CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE

  18:  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Line 17 contains the foreign key constraint that caused the SSMA conversion error. In this case, the desired behavior just looking at the design of the schema is that when the store_id value changes in the store table, make sure the change gets cascaded into the store_id value for Staff table. In addition, there are cascading update foreign keys against store_id for the Customer and Inventory tables. This seems like a natural thing to do, but you need to ask yourself – do I really need to change the key values if the column doesn’t contain meaningful information? In the case of most of the primary keys in the Sakila database, the values are all some form of an integer value. In the world of database design, this is called a Surrogate key. Since the application doesn’t care about the values, there is no reason to change them.
Although the foreign key on line 16 showed no errors with the cascade update rule for address_id changes from the Address table, there is no reason to use the cascade update rule for the same reason.
The recommended course of action would be to turn off the update cascade option for all foreign keys that are made up of surrogate key values, but there is no harm leaving them at this point.

Migrating MySQL Cascaded Foreign Keys to Avoid Multiple Paths: M2SS0037

In the SSMA assessment report, you will find that four tables have ON UPDATE foreign keys that have issues. These tables include: Film, Payment, Rental, and Customer. Let’s see what’s behind this class of errors.

• Film table. Has one reported error where the foreign key pointing back to the Language table using the language_id column has a problematic ON UPDATE CASCADE clause. SSMA creates the foreign key, but uses the NO ACTION clause. This happened because there is another foreign key pointing back to the Language table using the Film.original_language_id column that uses the ON UPDATE CASCADE clause. Because both use a surrogate key, the errors can be ignored. If you want to change the film$fk_film_language_original foreign key to not use the ON UPDATE CASCADE clause, you can do the following:
• In SSMS, click on the New Query command on the toolbar.
• Paste in the following code and then execute the query.

USE sakila

GO

ALTER TABLE dbo.film

    DROP CONSTRAINT film$fk_film_language_original

GO

ALTER TABLE dbo.film WITH NOCHECK ADD CONSTRAINT

    film$fk_film_language_original FOREIGN KEY

    (

    original_language_id

    ) REFERENCES dbo.language

    (

    language_id

    ) ON UPDATE  NO ACTION 

     ON DELETE  NO ACTION 

GO

• Payment table. Each of the three foreign keys on the Payment table use surrogate keys, so the errors ON UPDATE CASCASE clause can be safely ignored.
• Rental table. Each of the three foreign keys on the Rental table use surrogate keys, so the errors ON UPDATE CASCASE clause can be safely ignored.
• Customer table. Each of the two foreign keys on the Customer table use surrogate keys, so the errors ON UPDATE CASCASE clause can be safely ignored.

Migrating MySQL FULLTEXT Indexes to SQL Server Full-Text Search: M2SS0035

The free SQL Server 2008 R2 Express with the Advanced Services download package provides Full-Text search capabilities similar to MySQL Full-Text search feature. SSMA for MySQL v1.0 doesn’t support the migration of FULLTEXT indexes, but the process is fairly straight forward. You will need to install the Full-Text feature for the SQL Server instance before beginning.

Converting MySQL FULLTEXT Indexes

MySQL can create FULLTEXT indexes on tables that do not have a unique key index. MySQL can also create more than one FULLTEXT index on a table.
The WITH PARSER option can be used only with FULLTEXT indexes. It associates a parser plug-in with the index if full-text indexing and searching operations need special handling. 
Consider the schema for the Film_text table:

CREATE

    TABLE `film_text`

        (

            `film_id` smallint(6) NOT NULL, 

            `title` varchar(255) NOT NULL, 

            `description` text, 

             PRIMARY KEY  (`film_id`) , 

             FULLTEXT  KEY `idx_title_description` (`title`, `description`)  

        )  ENGINE = MyISAM DEFAULT  CHARSET = utf8;

SSMA created the file_text table, but ignored the FULLTEXT key. To create the SQL Server index, you would use the following code:

USE [sakila];

GO

CREATE FULLTEXT CATALOG [Films] AS DEFAULT;

GO

CREATE FULLTEXT INDEX ON dbo.film_text([description]) KEY INDEX pk_film_text_film_id;

GO

You only need to create the catalog once and then use it for other full-text indexes as needed.