Today's blog post was written by Rob Jasinski, Principal Developer at Sonoma Partners.
Recently, we had a need to migrate data for a customer to Microsoft Dynamics CRM, but the data was located in a MySQL database. We have many tools, applications, and interfaces that rely on the source data being Microsoft SQL Server, so ideally we like to convert the data from a different database platform to SQL Server whenever possible. Since MySQL is open-source and considered the second most popular used RDBMS (according to Wikipedia), we use this platform more than others.
To convert a MySQL database, you’ll need to first have MySQL installed which can be downloaded from here. Then, you’ll need to install the SQL Server Migration Assistant tool. I won’t go through step-by-step on how to use this tool as there is a good blog here to get started. Instead I will go through some of obstacles I had and how I resolved them.
Restoring a Backup of the Database to Your MySQL Database
When I loaded the SSMA tool, I had issues connecting to the MySQL database. I finally had to completely uninstall the MySQL ODBC driver and re-install it to finally get a connection to work. For the SQL Server connection, SQL Server agent needed to be running so it was started. Next, check the target schema. I’m not quite sure how this is defaulted, but it was pointing to the wrong SQL database. I didn’t notice this at first and had to change it manually.
Before the data can be migrated, it must synchronized so the tables are created on the destination. The “How To” blog states it’s under Tools, but I didn’t see it there. I finally found it in the SQL Server metadata browser window, if I right-clicked the destination database, synchronize was an option.
Finally, when I tried to migrate the data, the process would start then the application (SSMA) would just suddenly close after about a minute or two. I retried several times even after a server restart and the same issue continued. After some research I found a message post to try and set the affinity of the application (SSMS) to just one CPU. After I tried that, it worked. All the data from MySQL was migrated to the SQL Server database.
Once we had the data migrated to SQL Server, we were able to use our common data scrubbing tools to clean and migrate the data into CRM.