I will use SQL Server Migration Assistant for MySql to migrate an on-premise MySql database to an SQL Server instance running on an Azure virtual machine. I've got MySql workbench open, and I've done a select count from the tables in the employees database that I'm going to migrate. There is a reasonable amount of data here, with two tables having several hundred thousand rows and one table having 2.8 million rows. Switching over to SQL Server management studio, I've connected to the server instance on the virtual machine using a point-to-site VPN connection I set up earlier. I'll create an empty employees database on the virtual machine going with the default settings. Coming back to my desktop, I'll open up SQL Server migration Assistant and create a new project. We can see in the new project dialogue there are several options in the migrate to drop-down. There is SQL Server starting at 2012 up to the current version plus Azure SQL database, and Azure SQL managed instance. If you are migrating from an on-premise SQL Server to a VM-hosted instance, be mindful of the target server version. You can't go backward, so if you are running server 2019 on-premises and create one of the canned SQL Server VM's from the portal running server 2017, you may run into problems. In the case of a source MySql database, this is not an issue.

Connecting to MySql on-premise server, I'm getting a warning about the ODBC driver version and how SQL Server migration Assistant has not been tested with the 8.0 Unicode driver I'm using. I have a new installation of MySql, and this is a recent download of SQL Server Migration Assistant, so I'm going to trust that nothing too much has changed and there is good backward compatibility. In the MySql metadata explorer, I'll tick the employees database with its six tables and two views. Next, I'll log into the SQL Server using the private IP address of my point-to-site VPN with SQL Server authentication. Now that I'm all connected, I need to make one modification to the target schema because I don't want to have an employees database with an employees schema. I'll modify the target schema to be the default dbo. Next, click the convert schema button, which will parse the MySql definitions into SQL Server DDL statements. Before we can migrate the data, we need to apply the schema definition to our target database. We do that by right-clicking on the target database and select synchronize with database. The output shows that we have an issue with the dept_emp_latest_date view. When I open it up, we can see there is a large number specifying how many rows we would like returned as part of the TOP clause. I'll change that to 1 million, click save and re-synchronize the database. Having fixed that issue, I can now migrate the data by clicking the migrate data button, funnily enough. And annoyingly enough, I have to re-authenticate with both the source and target servers. The migration assistant output window is telling us the number of rows migrated for each table. If we go over to the target server, we can see all the tables have been created, plus two extra ones created by SQL Server migration Assistant to manage the migration process. This is not an insignificant amount of data to transfer, and the point-to-site VPN connection does a relatively good job compared to using a public IP address over the internet. That is, it is quite fast and stable. Once the migration has finished, we get a summary detailing how many records of each table were successfully migrated.