There was some discussion among the team about whether using MySQL worker role or SQL Azure. As the final decision is SQL Azure, the first task we need to do is migrating the MySQL database to SQL Azure. The most obvious tools we can work on is Microsoft SQL Server Migration Assistant (SSMA) for MySQL.
SSMA was working fine in SQL Server. It reconstructed the SQL Server equivalent schema from MySQL and copied the data from MySQL to SQL Server. However, there are a few catches when migrating to SQL Azure:
1. Tables without primary keys:
For some reason, some tables in the web site did not have primary keys. The problem is a table in SQL Azure need a clustered index. No primary key means no clustered index (in SSMA sense). We have used a development SQL Server to re-create the schema first. Then, look for the tables without primary keys and add the primary keys back.
2. Large records
The CMS designer has a concept that all data should be put into the database as the backup strategies. So, everything including images files, Flash video files, and video files were stored in some attachment table as well. SSMA could handle most of them but not all. However, when the record is large (say 10MB), it will eventually failed and interrupted the how migration process. It is a very traditional timeout problem. We are just dealing with two:
- Command timeout for SQL Server
- Idle timeout for MySQL
To conclude, the migration were performed in a two steps:
- Execute the SQL script to create the tables
- SSMA to copy most data
- Execute the PHP script to migrate the attachment metadata