MSSQL TO MYSQL DATA MIGRATION USING MYSQL WORKBENCH 6.3

Recently I was testing data migration from MSSQL to MySQL using MySQL Workbench. My aim was to include data with all datatype available in MSSQL for migration. In this following blog post will see data migration prerequisites, migration steps and few common errors.

About MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, Data migration and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

When a supported RDBMS product is being migrated, the MySQL Workbench Migration Wizard will automatically convert as much information as it can, but you may still be required to manually edit the automatically migrated schema for difficult cases, or when the default mapping is not as desired.

Generally speaking, only table information and its data are automatically converted to MySQL. Code objects such as views, stored procedures, and triggers, are not. But supported RDBMS products will be retrieved and displayed in the wizard. You can then manually convert them, or save them for converting at a later time.

The following MS-SQL versions are currently tested and supported by the MySQL Workbench Migration Wizard.

Microsoft SQL Server 2000, 2005, 2008, 2012

Prerequisite

Download and install MySQL Workbench GUI tool.

The MySQL Workbench Migration Wizard uses ODBC to connect to a source database, except for MySQL. You will need the ODBC driver installed that corresponds to the database you want to migrate from.

Preparation

To be able to migrate from Microsoft SQL Server, ensure the following:

The source SQL Server instance is running, and accepts TCP connections.
You know the IP and port of the source SQL server instance. If you will be migrating using a Microsoft ODBC driver for SQL Server (the default in Windows), you will need to know the host and the name of the SQL Server instance.
Make sure that the SQL Server is reachable from where you will be running MySQL Workbench. More specifically, check the firewall settings.
Make sure that the user account has proper privileges to the database that will be migrated.

DATA SOURCE ODBC Configuration

In order to set up a connectivity between MSSQL and MySQL, We need to configure DATA SOURCE drivers ODBC with MS-SQL connection information.

Add new System data source.

odbc_pic

Required privileges on MS-SQL DB

CONNECT SQL

VIEW ANY DATABASE

VIEW ANY DEFINITION

If proper permission not given then at the migration it will throw warning as follow,

blog_lalit_pic2

DB Servers Data Types Mapping

Refer:

https://dev.mysql.com/doc/workbench/en/wb-migration-database-mssql-typemapping.html

Available datatype in MS-SQL:

https://msdn.microsoft.com/en-us/library/ms187752.aspx

Sample MS-SQL data

Sample table and data with different MS-SQL datatype

-- integer data types

CREATE TABLE dbo.int_table

(

MyBigIntColumn bigint

,MyIntColumn  int

,MySmallIntColumn smallint

,MyTinyIntColumn tinyint

);

ALter table int_table add MyBitColumn bit;

ALter table int_table add CONSTRAINT bit_def default 1  for MyBitColumn;

INSERT INTO dbo.int_table VALUES (9223372036854775807, 214483647,32767,255);

update int_table SET MyBitColumn=1;

Alter table int_table alter column MyBitColumn bit not null;

-- decimal and numeric Data datatypes

CREATE TABLE dbo.num_table

(

MyDecimalColumn decimal(5,2)

,MyNumericColumn numeric(10,5)

);

INSERT INTO dbo.num_table VALUES (123, 12345.12);

Alter table num_table add MyMoneycolumn money;

Alter table num_table add MysmallMoneycolumn smallmoney;

INSERT INTO num_table (MyMoneyColumn,MysmallMoneycolumn) values(20.098,45.68);

alter table num_table add c_real real,c_float float (32);

INSERT INTO num_table (c_real,c_float) values(2.0,43.67897);

-- datetime datatype

------Read More......