Microsoft SQL Server Migration Assistant for MySQL does NOT support SQL Server Express!

Fairly recently, I was given a MySQL database and told that I needed to use the bulk of this data to power a web application that was using Microsoft SQL Server.

The first major task with this was finding a way of getting the tables across to a SQL Server format that I could work with, and I'd deal with the actual process of getting the data into my database later using a load of custom scripts.

I decided on using the Microsoft SQL Server Migration Assistant for MYSQL to do the heavy work for me, and now that I know what can go wrong with this process, I'd recommend that most users take this route.

Notes

Every time I ran the conversion tool on a database, the MSSQL Agent shut down

Don't use MS SQL Server Express edition as the destination DBMS. Make sure you use a fully qualified version of SQL Server; Developer Edition did it for me. You need a full version because SQL Server Express shuts the SQL Server Agent down and won't let it start again - Trying to get it started again is a RED HERRING. You think that the agent is stopping you from doing a successful import, but it's just one of many reasons. Get the proper version of SQL Server, and this will work straight away.

Nullable columns after creating the schema

Once the schema has been created within MSSQL, it's time to import data into your newly created tables. For some reason, when the migration assistant created the SQL Server tables, it didn't make the columns that needed to be nullable, well, nullable. So you have to do that by hand. The easiest way to spot it is when your migration falls over because it's trying to insert a null value into your table.

Get the MYSQL ODBC plugin

You can get a list of ODBC plugins at the MYSQL website. This is how MYSQL and MSSQL know how to talk to each other.