How to Migrate Database from MySQL to SQL Server

MySQL and Microsoft SQL as we know, are the most commonly used Relational Database Management Systems (RDBMSs). While MySQL is an independent and open-source RDBMS supported by Oracle whereas MS SQL is a proprietary RDBMS of Microsoft.

If you want to migrate MySQL to SQL Server, then you’ve come to the right place. Through this piece of article, I will describe how to migrate data from MySQL to SQL Server quickly. Some easy solutions will be described to help you complete complex database migration tasks and save a lot of time. So, without wasting any more time, let’s start!

Why Migrate MySQL Database to SQL Server?

There may be a time when your MySQL database solution evolves into a mission-critical solution. Suddenly service level agreements become an issue for areas such as security, high availability, disaster recovery, scalability, data governance.

Migration from MySQL to SQL database is gaining momentum nowadays. Microsoft SQL Server empowers your entire data asset by supporting structured and unstructured data sources. Additionally, SQL Server has also been the least vulnerable database in a couple of years.

Migrate Using SQL Server Import & Export Wizard

Migrate Database from MySQL to MSSQL Server using SQL Import & Export Methods

  1. Download and Install MySQL ODBC Connector

    First of all, download and install the MySQL ODBC Connector. The newest release is available at http://dev.mysql.com/downloads/connector/odbc/

  2. Open ODBC Data Source Administrator & browse the MySQL ODBC driver

    Then, open your ODBC Data Source Administrator from the control panel >> Administrative Tools. On the System DSN tab click Add, browse the ‘MySQL ODBC driver’ and press Finish button.

  3. Enter MySQL server connection details

    In the next wizard, input your MySQL server connection details, test it and click OK.

  4. Set SQL_MODE

    Now, set SQL_MODE to ANSI_QUOTES on MySQL Server.

  5. Launch the SQL Server Management Studio

    After that, launch the SQL Server Management Studio.

  6. Create a new database

    Click Next, create a new database or use an existing database

  7. Click Import Data to launch the SQL Server Import and Export Wizard

    Right-click on the destination database and select Tasks then click Import Data to launch the SQL Server Import and Export Wizard.

  8. Choose the .NET Data Provider for ODBC

    Choose the .NET Data Provider for ODBC as Data Source and specify the DSN created previously.

  9. Enter the details of your SQL Server database

    Then you have to choose the SQL Server Native Client 10 as Destination, enter the details of your SQL Server database and click Next.

  10. Copy data from one or more tables

    Select Copy data from one or more tables or views and click Next.

  11. Choose the source and destination tables

    Choose the source and destination tables. You can enable identity insert using the Edit Mappings option.

  12. Execute the package

    Finally, execute the package and save it for future use if necessary.

Migrate MySQL Database to SQL Server Using Professional Tool

If the above method fails to do the migration then one of the best approaches is to do the automatic conversion of MySQL to MS SQL databases using specialized database converter tools such as Aryson MySQL to SQL Converter Tool.

The software supports database migration by allowing offline conversion of database records and attributes in an easy and hassle-free manner. In addition, the software quickly migrates your MySQL tables with indexes and keys from one database to another within the same MySQL server while preserving the integrity of the database. Moreover, the tool also comes with an option to merge MySQL data into an existing MySQL database. Essentially, the converter tool helps administrators and users save time and efforts in writing complex queries to transfer MySQL database files to MSSQL database file format. So, I would recommend you to try the free demo version of this utility to get the flair idea about database conversions.

Bottom Lines

Organizations can develop the need to migrate from MySQL servers to Microsoft SQL due to various limitations related to their scalability, performance, and security. While migration from one database to another can be done manually, it can be a too time-consuming and error-prone process.

A better option is to use specialized database converter software such as MySQL to MSSQL Converter, specifically designed to help DBAs and developers automate the process of converting database file format into another. The software also copy a table from one database to another database in MySQL.

5/5 - (3 votes)

About The Author:

Mithilesh Tata is dedicated to making the lives of his customers as simple as possible. He enjoys learning about new applications and utilities that can help people protect their data. Mithilesh also has a strong grasp on email migration & data recovery. He enjoys validating every component of the software, technical materials, and anything else that has to do with his working life.

Realted Post

offer image

Aryson Technologies

united states

2880 Zanker Road, Suite 203, San Jose, CA - 95134, USA

india

A-58 , 2nd Floor A Block Sector 57, Noida, Uttar Pradesh 201301

© Copyrights 2014-2024 by Aryson Technologies Private Limited - All Rights Reserved