Close Menu

    Migration Guide: Converting Mysql Database To Sql Server

    0
    By on Technology

    In the database world, MySQL is known as free open-source system offering high performance and advanced features. Nevertheless, some companies and organizations consider to convert MySQL to SQL Server due to the following reasons: 

    • Integration with Microsoft ecosystem. Obviously, one of SQL Server advantages is native integration with other Microsoft products such as Office 365.
    • Enhanced performance. According to many benchmarks, SQL Server overcomes MySQL in handling complex transactions and queries.
    • Security. SQL Server offers additional features compared to MySQL in terms of security. For instance, it does not allow any process to access or manipulate data files or binaries. Instead, the database engine requires users to design special routines or manipulate files by running an instance.
    • Backup. MySQL backups the data in form of SQL statements blocking the database for entire duration of the procedure. This approach reduces risk of data corruption but it requires much time due to running numerous queries. On the other hand, SQL Server does not lock the data during backup/restore, that allows to save extra time and efforts. 
    • Enterprise-class support. Being a commercial database system developed by Microsoft, SQL Server is supplied with proven enterprise-class extensive support.

    There are multiple options to convert MySQL to SQL Server. This article explores the most common solutions for that task. 

    SQL Server Import & Export Wizard

    This method uses the standard tool from distribution pack of MS SQL to convert MySQL to SQL Server includes the following steps: 

    1. First, it is required to download and install the MySQL ODBC Connector, the driver to access MySQL data
    2. Open ODBC Data Source Administrator from the Windows control panel. On the ‘System DSN’ tab click ‘Add’ and select MySQL ODBC driver.
    3. Input connection details for MySQL server, test it and click OK. Set parameter SQL_MODE to ANSI_QUOTES.
    4. Launch the SQL Server Management Studio.
    5. Create a new SQL Server database or use an existing database.
    6. Right-click on the MySQL database and select Tasks > Import Data option to launch the SQL Server Import and Export Wizard.
    7. Choose ‘Data Source’ using option .NET Data Provider for ODBC. Select the DSN created on a previous step.
    8. Choose the SQL Server Native Client as the destination, enter connection details for SQL Server database and click Next.
    9. Finally, select to copy data from one or more tables or views and choose the destination tables.

    After completing these steps the related package will be executed to convert MySQL to SQL Server. 

    MySQL to SQL Converter

    The procedure described above may seem quite complicated and it requires extensive post-processing indeed. For those who looking for more simple and automated solution to convert MySQL to SQL Server, it is recommended to evaluate commercial tools specially designed for this purpose. 

    One of such tools is MySQL to SQL Server converter developed by Intelligent Converters, a software company specializing in database migration and synchronization for years. This tool implements highly efficient low-level techniques of reading and writing data combined with smart multi-threading algorithm that automatically tunes number of working threads and memory usage depending on the hardware platform. 

    Other features of MySQL to SQL Server converter:

    • All modern versions of on-premises and cloud MySQL and SQL Server are supported (including Azure SQL, Amazon RDS, Google Cloud)
    • Tables, data, indexes, constraints and views are converted with all required properties
    • Option to convert MySQL database into T-SQL script for those cases were direct connection to the target server is not available
    • Option to edit the target table (modify name, type, default value and other attributes of every column as well as exclude particular columns from conversion)
    • Merge or synchronize existing SQL Server with MySQL data
    • Filter data to migrate using SELECT-queries. The program allows to migrate result of SELECT-query as it would be a regular table.
    • Command line support allows to convert MySQL to SQL Server from script and schedule the database migration

    As every Intelligent Converters product, MySQL to SQL Server converter is supplied with unlimited support and 1-year subscription for updates – customers will receive new version of the tool for free within 1 year after purchasing.