All geek questions in one place

Migrating data and schemas from MySQL to SQL Server

Are there free solutions for automatically migrating a database from MySQL to SQL Server Server, which "works simply"?

I try to complete this simple (at least I thought so) task all day. I tried:

  • SQL Server Management Studio Data Import Feature
  • Create an empty database
  • Tasks → Import Data ...
  • .NET Framework Data Provider for Odbc
  • Valid DSN (verified that it is connecting)
  • Copy data from one or more tables or views
  • Check 1 VERY simple table
  • Click Preview
  • Receive error message:

Preview data cannot be retrieved. FURTHER INFORMATION: ERROR [42000] [MySQL] [ODBC 5.1 Driver] [there-5.1.45-community] You have a SQL syntax error; check the manual that matches your version of MySQL server for the correct syntax for use next to '"table_name"' on line 1 (myodbc5.dll)

A similar error occurs if I go through the rest of the wizard and perform an operation. The unsuccessful step is to "Configure the source connection", the error refers to the extraction of column information, and then lists the above error. It can easily get column information when I change the column mappings, so I really don't know what the problem is.

I also tried to get various MySql tools to output ddl statements that SQL Server understands but failed.

I tried with MySQL v5.1.11 with SQL Server 2005 and with MySQL v5.1.45 on SQL Server 2008 (with ODBC drivers 3.51.27.00 and 5.01.06.00 respectively)

+4
source share
7 answers

There are two free tools provided by Microsoft.

I used only the second one and it worked for me without any glitches. To download the license file, registration with Microsoft was required. But it's free for everyone.

+3
source

This is really old, but if you use MySQL Connector NET and set SQL Server Mode = true in the connection string, this will solve your error.

+1
source

Recently, I successfully migrated a MySQL database to an MSSQL database . Below are the detailed steps:

Operating System: AWS Microsoft Windows Server 2012 R2 with SQL Server Standard

Tools used:

  • SQL Server 2014 Developer Studio SQL Developer,
  • Microsoft SQL Server v6.0.1 Migration Assistant for MySQL,
  • Remote Desktop Client and
  • Third-party ODBC driver MySql 5.1.13

1. Configuring AWS Windows Server enter image description here

2. In the list of ec2 instances of the AWS console, right-click on the Windows server and select connect. You will see a similar screen below. enter image description here

3. Click the "Get Password" button, which is required to connect to the remote desktop [# 4] and follow the instructions.

4. Connect to this instance of EC2 [# 1] with the default remote desktop client available on your local Ubuntu computer. Use credentials from # 2. enter image description here

5. Once you connect using the remote client, you can access the remote MSSQL server. Install the following tools.

6. Configuring ODBC data sources (64-bit):

  • Open Administrative Tools → click ODBC Data Sources (64-bit) and follow the steps to connect to the MySQL database.

7. Open SQL Server 2014 Management Studio SQL Developer and connect using Windows Authentication.

  • Create a target MSSql database for MySql migration.

8. Open the Microsoft SQL Server Migration Assistant: For more information, see the link: https://blogs.msdn.microsoft.com/ssma/2011/02/07/mysql-to-sql-server-migration-how-to- use-ssma /

  • Create a new project
  • Connect to MySql
  • Connect to MSSql
  • Schema conversion
  • Data transfer

8. You may have the problem indicated here. Please read in detail where I wrote the detailed permission. MySql 5.6 for MSSql 2014 server migration: ExecuteReader requires an open and accessible connection

+1
source

I am afraid there is no easy solution. SQL used in MySQL and T-SQL used in SQL Server 200X are different dialects of SQL. Not just changing the value of "auto_increment" to "identity", but the reserved words that create the problem. for instance

CREATE TABLE test ( user varchar(50) )

will fail in MySQL and crash in SQL Server 2008. Shorten the long story short - unfortunately, you will need to do this manually.

0
source
  • Export file from MySQL to CSV file.
  • Export create statements for tables from MySQL
  • Cry.
    3a. Disable foreign key checks on SQL Server
  • Configure create statements in SQLserver until they work.
  • Import CSV files to MySQL.
    5a. Enable foreign key checking on the SQL server.

Also see answers: migrate-from-mysql-to-sql-server-2008

0
source

Had a similar question about this error 42000, and for me I realized that setting the global MySQL mode to ansi_quotes would resolve it:

 set global sql_mode=ansi_quotes;
0
source

There are commercial solutions, but not free solutions. Rewriting SQL for the target dialect may be a trivial task, or very complex, depending on the complexity of your database. Re-writing CREATE TABLE statements is never complicated; it can be done manually without any surprises. Procedures, functions, and triggers are problematic.

0
source

Source: https://habr.com/ru/post/1304541/

More articles:


All Articles