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

4 Effective Ways to Help Protect Your Identity Online
Internet

4 Effective Ways to Help Protect Your Identity Online

June 25, 2022
The History of Online Gambling and Internet Casinos in Australia
Internet

The History of Online Gambling and Internet Casinos in Australia

June 27, 2022
Picuki: Sites Like Picuki Instagram Viewer and Editor
Apps

Picuki: Sites Like Picuki Instagram Viewer and Editor

June 24, 2022
11 Best Free Online Book Reading Sites In 2022
Internet

11 Best Free Online Book Reading Sites In 2022

June 19, 2022
10 Best Facebook Proxies for FB Automation Bots in 2022
Internet

10 Best Facebook Proxies for FB Automation Bots in 2022

June 1, 2022
Top 16 Binary Trading Apps in 2022
Apps

Top 16 Binary Trading Apps in 2022

June 1, 2022

Leave a Reply Cancel reply

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

Search

No Result
View All Result

Recommended

Service Robotics Market- Market Outline, Trends, Growth Rate And Competition (2020- 2025)

Service Robotics Market- Market Outline, Trends, Growth Rate And Competition (2020- 2025)

July 3, 2021
CMovies HD Alternatives

Top 34 Best CMovies HD Alternatives to watch movies 2022

February 26, 2022
Top 10 Best home design software 2021[Updated List]

Top 10 Best home design software 2021[Updated List]

October 26, 2021
external hard drives

Best External Hard Drives in 2022 [Updated List]

December 15, 2021
Best 5 Video Edit­ing Apps for iPhone

Best 5 Video Edit­ing Apps for iPhone

March 10, 2020
10 Best Digital Photo Frames In 2022

11 Best Digital Photo Frames In 2022

April 15, 2022
  • 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.