Microsoft SQL is one of the most popular DBMS with easy to use interface. Unfortunately, this database management system has some disadvantages like high total cost of ownership (TCO) for large projects and restrictive licensing policy. This is the main reason why some users consider to convert SQL Server to another MySQL, selecting this open-source database system in order to reduce TCO.
There are two major and important open-source DBMS: MySQL and PostgreSQL. MySQL is easier to configure and deploy than PostgreSQL, at the same time it provides most features that are expected from advanced database management systems: security, scalability, high performance, wide range of administration tools. This all makes MySQL the best choice for projects non-experienced users who require high level of reliability and data integrity.
The procedure of converting SQL Server to MySQL usually consists of the several steps:
- export table definitions from the MS SQL database as SQL DDL statements
- transform these statements according to MySQL syntax (paying attention to safe types mapping and correct translation of default values) and load to the target database
- export SQL Server data into its string representation and store into temporary files of comma separated values (CSV) format
- if it is necessary, make transformations in order to comply with MySQL format and load the data into the target database
This is how you can export table definitions in form of SQL DDL statements to convert from SQL Server to MySQL:
- SQL 2008 and earlier allows scripting objects and data. Right-click on database name in Management Studio, select ‘Tasks’ menu, ‘Generate Scripts’ item. Go through the wizard and make sure to check ‘data’ which is false by default.
- SQL 2012 and later can do the same as follows: right-click on database in Management Studio, select ‘Tasks’ menu, ‘Generate Scripts’ item. Navigate to ‘Advanced’ section of the ‘Set scripting options’ tab. Select ‘data only’ or ‘data and schema’ option for ‘Types of data to script’ (in the ‘General’ section).
The resulting script must be improved before loading into MySQL as follows:
- remove all SQL Server specific statements and optional keywords that are not supported by MySQL
- remove square brackets around types and replace square brackets around database object names by double quotes
- remove all reference to filegroup such as ‘ON PRIMARY’
- replace types ‘INT IDENTITY(…)’ by ‘INT AUTO_INCREMENT’
- replace SQL Server query terminator ‘GO’ by MySQL one ‘;’
The data also can be exported via Microsoft SQL Management Studio as follows: right-click on database, select ‘Tasks’ menu, ‘Export Data’ item. Go through the wizard and select ‘Microsoft OLE DB Provider for SQL Server’ as data source, ‘Flat File Destination’ as the destination. After export procedure is completed, all data is exported into the specified CSV file.
If SQL Server database contains binary data, it is required to apply the workaround. Select ‘Write a query to specify the data to transfer’ option on ‘Specify Table Copy or Query’ wizard page. On the next wizard page compose SELECT-query as follows:
select {non-binary field #1}, {non-binary field #2}, cast(master.sys.fn_varbintohexstr(
cast({binary field name} as varbinary(max))) as varchar(max)) as {binary field name} from {table name};
Note, this trick does not work on large binary data (1MB+) because the query runs into infinite hang. The resulting CSV files are imported into MySQL database via mysqlimport tool.
The steps above prove that procedure to convert SQL Server to MySQL is quite complicated and requires a lot of efforts when doing it manually. It can even cause critical errors and data loss or corruption due to the human factor. Experienced database professionals never migrate large and complicated database manually despite of the fact they know every single detail of the procedure.
Fortunately, there are special tools to convert SQL Server to MySQL automatically. One of these tools has been developed by Intelligent Converters, ambassador of database migration and synchronization software of since 2001. Their converter works with all modern on-premises and cloud variations of SQL Server and MySQL including: MariaDB, Percona, Amazon RDS, ClearDB, Azure for MySQL and many others. The program carefully migrates table definitions, data, indexes, foreign keys and views. Option to export the source database into MySQL dump file can help for those situations when the target server does not allow remote connections.
