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
- DATA SOURCE ODBC Configuration
- Required privileges on MS-SQL DB
- DB Servers Data Types Mapping
- Sample MS-SQL data
- Migration using MySQL Workbench
- Data and Log analysis
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
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.
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.
Required privileges on MS-SQL DB
VIEW ANY DATABASE
VIEW ANY DEFINITION
If proper permission not given then at the migration it will throw warning as follow,
DB Servers Data Types Mapping
Available datatype in MS-SQL:
Sample MS-SQL data
Sample table and data with different MS-SQL datatype
-- integer data types
-- decimal and numeric Data datatypes
-- datetime datatype