TechVibe
  • Home
  • Tech
  • Apps
  • Business
  • Internet
  • Games
  • Lifestyle
  • Mobile
  • More
    • Entertainment
    • Mac
    • Tips & Tricks
    • Windows
No Result
View All Result
  • Home
  • Tech
  • Apps
  • Business
  • Internet
  • Games
  • Lifestyle
  • Mobile
  • More
    • Entertainment
    • Mac
    • Tips & Tricks
    • Windows
No Result
View All Result
TechVibe
No Result
View All Result
Home Internet

Convert Microsoft SQL to MySQL

by Patrick Walton
July 3, 2021
in Internet
0
SQL to MySQL

Microsoft SQL is one of the most widespread database management systems with simple to use interface. Sadly, this database management system has some disadvantages like the high total cost of ownership for big projects and restrictive licensing policy. This is why some users think about migrating their databases from MS SQL to a different database management system. So as to scale down the total cost of ownership they choose an alternative among open-source database systems.

There are two major and popular open-source DBMS: MySQL and PostgreSQL. MySQL is easier to set up and deploy than PostgreSQL, at the same time it provides most options that are expected from advanced database management systems such as security, scalability, high performance, a wide selection of administration tools.

This all makes MySQL the most effective alternative for projects non-experienced users who need a high level of reliability and data integrity. The transfer of data from MSSQL to MySQL isn’t that much complex and can be done by an inexperienced person if they strictly follow by the method use for transfer of database from SQL to MySQL.

The procedure of transferring database from SQL Server to MySQL consists of several steps:

  • export table definitions from the MS SQL database as SQL DDL statements
  • convert these statements into MySQL format and import to the target server
  • export MS SQL data into comma separated values (CSV) files
  • If it’s necessary, create transformations so as to adjust to MySQL format and load the data into the target database

This is how you’ll export table definitions from MS SQL database in form of SQL DDL statements:

  • SQL Server version 2008 and earlier permits scripting objects and data. Right-click on the database name in Management Studio, choose ‘Tasks’ menu, ‘Generate Scripts’ item. Go through the wizard and ensure to check ‘data’ that is false by default
  • SQL Server version 2012 and later can complete the same task as follows: right-click on the database in Management Studio, choose ‘Tasks’ menu, ‘Generate Scripts’ item. On the ‘Set scripting options’ tab click on ‘Advanced’, then choose ‘data only’, or ‘data and schema’ for ‘Types of data to script’ (in the ‘General’ section)

The resulting script should be improved before loading into MySQL as follows:

  • remove MS SQL specific statements (i.e. “SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON”, “SET ANSI_PADDING ON”)
  • replace square brackets around database object names by a quotation mark
  • remove square brackets around types
  • remove all optional keywords that aren’t supported by MySQL like ‘WITH NOCHECK’, ‘CLUSTERED’
  • remove all reference to filegroup like ‘ON PRIMARY’
  • replace types ‘INT IDENTITY(…)’ by ‘INT AUTO_INCREMENT’
  • replace Microsoft SQL query terminator ‘GO’ by MySQL one ‘;’

The data can also be exported via Microsoft SQL Management Studio as follows: right-click on the database, choose ‘Tasks’ menu, ‘Export Data’ item. Go through the wizard and choose ‘Microsoft OLE DB provider for SQL Server’ as data source, ‘Flat File Destination’ as the destination. After the export procedure is completed, all data is exported into the required CSV file.

If SQL Server database contains binary data, it’s needed to apply the workaround. Select ‘Write a query to specify the data to transfer’ option on ‘Specify Table Copy or Query’ wizard page. On following wizard page compose SELECT-query as follows:

select , , cast(master.sys.fn_varbintohexstr(

cast( as varbinary(max))) as varchar(max)) as

from ;

Note, this trick doesn’t work on large binary information (1MB+) because the query runs into the infinite hang. The resulting CSV files are imported into MySQL database via MySQL import tool.

The steps above are proving that database migration from Microsoft SQL to MySQL could be a difficult procedure requiring a lot of efforts when doing it manually. It can cause data loss or corruption as a result of the human factor. Fortunately, there are special tools to automate the entire process of database conversion. One amongst these tools is MS SQL to MySQL by Intelligent Converters.

The program provides all capabilities required for this kind of converter:

  • Converts schemas, data, indexes, constrains and views with all necessary attributes
  • Supports all versions of Microsoft SQL Server and Azure SQL
  • Supports all versions of MySQL running on Linux/Unix and Windows, MariaDB and Percona
  • Option to convert the source database into MySQL script file containing SQL statements to create all required database objects and replicate the data
  • Option to modify resulting table structure (rename columns, change type and attributes)
  • Option to filter data using SELECT-queries
  • Option to synchronize the destination database with SQL Server data
  • Supports multibyte character sets
  • Supports command line in order to script, schedule and automate the conversion process

Related Posts

Post-pandemic Trends in Recreation
Internet

Post-pandemic Trends in Recreation

January 27, 2023
Best 18 Emojis That Will Be Popular in 2022
Internet

Best 18 Emojis That Will Be Popular in 2022

January 3, 2023
Top 150+ Openload Alternatives for Watch Movies in 2023
Entertainment

Top 150+ Openload Alternatives for Watch Movies

December 27, 2022
60 DarkAnime Alternatives for Watching HD Anime Series
Entertainment

60 DarkAnime Alternatives for Watching HD Anime Series

December 25, 2022
How to Get a Free ESPN+ Trial and Other Streaming Offers
Internet

How to Get a Free ESPN+ Trial and Other Streaming

December 8, 2022
10 YouTube Niche Ideas for Your Channel In 2023
Internet

10 YouTube Niche Ideas for Your Channel In 2023

December 7, 2022

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

No Result
View All Result

Recommended

Facebook Photos to Google Photos

How to Move Facebook Photos to Google Photos

September 17, 2020
Buffstream.io Best Buffstream Alternatives sports live streaming

Buffstream.io Best Buffstream Alternatives sports live streaming

January 30, 2022
Top 10 Best VipBox Alternatives for Live Sports Streaming

Top 10 Best VipBox Alternatives for Live Sports Streaming

July 26, 2021
How to Make Steam Download Games Faster

How to Make Steam Download Games Faster

July 3, 2021
5 Best Minecraft Discord Servers in 2022

5 Best Minecraft Discord Servers in 2022

January 24, 2022
Dictionary Apps

Best Dictionary App for Android Devices

September 3, 2020
  • Write For Us
  • Contact us

Techvibe is a Technology blog delving into News, Discoveries and Guides Reviews.
TechVibe © Copyright 2021, All Rights Reserved.

No Result
View All Result
  • Games

TechVibe © Copyright 2021, All Rights Reserved.