[Updated 2/6/2012 Selina Jia - 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 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[Updated: Please obtain the lastest SSMA for MySQL] (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.
If you followed the blog post MySQL to SQL Server Migration: How to Use SSMA, 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.
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.
You will next perform the following steps that I described in MySQL to SQL Server Migration: How to Use SSMA post:
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.
We will use the Database Diagram to fix up the foreign key issues. You can zoom back out to 100% to see the tables better.
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.
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:
` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE,
17:
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.
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.
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
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.
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.
In this blog, pointed out that not all errors reported by SSMA are errors. In the case of surrogate keys, you can ignore the ON UPDATE CASCADE foreign key clause because the application or user shouldn’t be modifying these values. I also showed how to deal with full-text indexes with MySQL. There are still some interesting schema items in the Sakila database that were called out as warning that should be addressed. I also need to discuss how to migrate the problematic views and stored procedures in the Sakila database.