Microsoft provide migration assistant which will allow you to migrate different database sources to MSSQL Database. In this tutorial we are going to implement the MySQL to MSSQL Database migration via SQL Server Migration Assistant. It will provide five different database migrations.
Database Migration Support:
- SQL Server Migration Assistant for Access
- SQL Server Migration Assistant for DB2
- SQL Server Migration Assistant for MySQL (We are going to use)
- SQL Server Migration Assistant for Oracle
SQL Server Migration Assistant for SAP ASE
Why we need this?
If you have existing application having MySQL database and you are wanted to switch it to MSSQL Database you can use this tutorial. SQL Database will provide you great support then MySQL & MySQLi in case of ERP and multi-tenant application backed structure,
On other hand if you have any scope of using Azure services, SQL based database will be a plus for your because it’s available under Azure Umbrella.
Why I’m using?
Actually I have to migrate WordPress website to ASP.NET Core application for a client. And it’s the requirement to retain the database schema and their data. That’s why I’m migrating it from MySQL to MSSQL along with data. Event that I can use MySQL with .NET application, but fairly I don’t want to be dependent on any third-party component expect of Microsoft.
MySQL to MSSQL Database Migration
- First of all you need MySQL database which you want to migrate.
- Then Install Microsoft SQL Server Migration Assistant for MySQL.
- After Installation open the software.
- Create a New Project.
- Then Connect MySQL.
- After successful MySQL connection it will load all the database available in your MySQL.
- Only select that database which you want to migrate.
- Then connect SQL Server same as MySQL.
- Then Select MySQL Database and click on Convert Schema.
- After schema conversion, in bottom panel there is schema which you have to sync with database as shown in image.
- A new panel open in which it will show you database comparison.
- When you will click Ok, it will create database tables in your SQL database.
- We are done now.
Remember that, when you click on “Convert Schema” on MySQL Database, make sure that you have selected the right SQL Database Node. If you will not select MSSQL Database, it will automatically add database schema in last SQL Database Node. On database synchronization tables are automatically created under the selected SQL Database.
In this tutorial we have learned to migrate MySQL database to MSSQL database. Keep in mind if you will not follow the procedure step by step then it will create tables under other database. It will give you an edge if you have to develop .NET application from PHP, WordPress or any MySQL backed application.