Migrate data from SQL Server to OceanBase Database by using MySQL Workbench

MySQL Workbench is a database design and management tool provided by MySQL. It supports data migration. This topic describes how to use MySQL Workbench to migrate the schema and data of a table from a SQL Server database to OceanBase Database. MySQL Workbench connects to SQL Server through an ODBC data source. It supports automatic mapping of data types, but cannot migrate views and stored procedures.

Version compatibility

• OceanBase Database: V4.2.3 and later
• MySQL Workbench: mysql-workbench-community-8.0.38
• SQL Server: SQL Server 2016 Developer

Limitations

• MySQL Workbench cannot migrate views and stored procedures from SQL Server to OceanBase Database.

Prerequisites

Before you use MySQL Workbench to migrate data from SQL Server to OceanBase Database, make sure that:

• MySQL Workbench is installed. For more information, see MySQL Workbench official documentation.
• SQL Server Database is installed. Download SQL Server 2016 Developer from this link.
• OceanBase Database is deployed and a MySQL mode user tenant is created. For more information about how to create a user tenant, see Create a tenant.

Procedure

Step 1: Obtain the connection string of OceanBase Database

Contact the deployment personnel of OceanBase Database to obtain the connection string, for example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the IP address for connection. Use the IP address of the ODP for connection through ODP; use the IP address of the OBServer for direct connection.

• $port: the port for connection. The default port for connection through ODP is 2883; the default port for direct connection is 2881.

• $database_name: the name of the database.

  Notice

  The user for connecting to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the account for connection. The format of the account for connection through ODP is User@Tenant#Cluster or Cluster:Tenant:User; the format of the account for direct connection is User@Tenant.

• $password: the password of the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient in MySQL mode.

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Prepare data in SQL Server

To verify the migration effect, you can create a test table, insert test data, and create views and stored procedures to confirm the migration limitations in SQL Server.

1. Create a test table in SQL Server:

   CREATE TABLE tb1 (
       id int IDENTITY(1,1) NOT NULL,
       tinyint_col tinyint,
       smallint_col smallint,
       bigint_col bigint,
       bit_col bit,
       decimal_col decimal,
       numeric_col numeric,
       money_col money,
       smallmoney_col smallmoney,
       float_col float,
       real_col real,
       date_col date,
       time_col time,
       datetime2_col datetime2,
       datetimeoffset_col datetimeoffset,
       datetime_col datetime,
       smalldatetime_col smalldatetime,
       char_col char,
       varchar_col varchar,
       text_col text,
       nchar_col nchar,
       nvarchar_col nvarchar,
       ntext_col ntext,
       binary_col binary,
       varbinary_col varbinary,
       image_col image,
   uniqueidentifier_col  uniqueidentifier,
   xml_col xml,     
   CONSTRAINT PK_id PRIMARY KEY CLUSTERED (id)
   )
   

2. Insert test data (excluding the first column ID, which is automatically generated):

   INSERT INTO tb1 VALUES 
   (127, 
   32767, 
   9223372036854775807,
   'false', 
   12345.12,
   12345.12,
   922337203685477.5807,
   214748.3647,
   922337203685477.5807,
   922337203685477.5807,
   '2024-07-12',
   '23:59:59.999',
   '2024-07-12 10:10:00',
   '2024-07-12 10:10:00',
   '2024-07-12 10:10:00',
   '2024-07-12 10:10:00',
   'c',
   'v',
   'this is text col',
   'n',
   'n',
   'this is ntext col',
   0x48,
   0x48,
   0x48656c6c6f20576f726c64,
   '389e8aa5-3ff5-11ef-847f-0242ac120002',
   '<?xml version="1.0" encoding="UTF-8"?>
   <library>
       <book id="001">
           <title>xml example</title>
           <author>J.K. Rowling</author>
           <year>1997</year>
       </book>
   </library>')
   

3. Create views and stored procedures to verify the migration limitations of MySQL Workbench:

   CREATE VIEW vw1 AS SELECT * FROM tb1 WHERE id = 1;
   

   CREATE PROC proc1 AS SELECT * FROM tb1;
   

Step 3: Configure the ODBC data source

Before you use MySQL Workbench to migrate data from SQL Server, you must configure the ODBC data source of SQL Server.

1. Open MySQL Workbench and click the Migration Wizard icon (arrow icon) in the Database menu.
2. In the migration wizard, click Open ODBC Administrator to configure the ODBC data source.
3. In the ODBC Data Source Administrator, select the User DSN tab and click Add.
4. Select the SQL Server driver and click Finish.
5. Enter the data source name (such as SQL Server) and the server name (which can be viewed in Microsoft SQL Server Management Studio). Click Next.
6. Select the authentication method (we recommend that you use SQL Server authentication) and enter the username and password. Click Next.
7. Select the default database (such as test) and click Next.
8. Keep the default settings and select Simplified Chinese as the language. Click Finish.
9. Click Test Data Source to confirm that the connection is normal. Then, click OK.
10. In the ODBC Data Source Administrator, click OK to complete the data source configuration.

Step 4: Migrate data by using MySQL Workbench

After you configure the ODBC data source, you can use the migration wizard of MySQL Workbench to migrate the schema and data of SQL Server tables to OceanBase Database.

1. In MySQL Workbench, click Start Migration.

2. Select Microsoft SQL Server, ODBC Data Source as the source database type. The system automatically displays the data source name configured earlier.

3. Click Test Connection, enter the password of SQL Server, and click Next after confirming that the connection is normal.

4. Select MySQL as the target database type and select Standard (TCP/IP) as the connection method.

5. Enter the connection information of OceanBase Database:

   • Hostname: the IP address of OceanBase Database.
   • Port: the port of OceanBase Database (default value: 2881).
   • Username: the username of OceanBase Database.
   • Password: the password of OceanBase Database.

6. Click Test Connection, enter the password of OceanBase Database, and click Next after confirming that the connection is normal.

7. Select the database to be migrated. Since SQL Server has a schema layer that OceanBase Database does not have, we recommend that you ignore the schema layer when you map tables.

8. Select the migration objects. Note: MySQL Workbench cannot migrate views and stored procedures. Although you can select them, a prompt will appear later indicating that they cannot be migrated.

9. Click View: All Objects to view all objects and click Columns to view the field mapping. You can double-click the target database field to modify the field name and type.

10. Confirm that the views and stored procedures are not migrated (which is normal) and click Next.

11. Select whether to generate migration SQL statements and set the handling method for existing databases and tables (delete existing ones or retain them). Click Next.

12. If the target database table already exists, the system will prompt whether to continue. If you select to continue, the existing table schema will be deleted. Click Next.

13. Click Next to start the migration. For large amounts of data, you can generate data packages and copy them to the target database server for import.

14. After the migration is completed, click Next to view the migration report.

15. Click Finish to complete the migration.

Verify the results

After the migration is completed, verify the data in OceanBase Database:

1. Connect to OceanBase Database:

   obclient -h$host -P$port -u$user_name -p$password -D$database_name
   

2. Query the migrated tables and data:

   SELECT * FROM tb1;
   

3. Confirm that the data is correctly migrated and the table schema meets expectations.

4. Verify whether the views and stored procedures are migrated:

   • The views vw1 and stored procedures proc1 cannot be migrated. This is a limitation of MySQL Workbench.
   • You must manually create the corresponding views and stored procedures in OceanBase Database.

Appendix: Data type mapping

MySQL Workbench automatically maps data types during migration. Below is the main data type mapping from SQL Server to OceanBase:

Frequently Asked Questions

Q1: Why can't views and stored procedures be migrated?

Cause: MySQL Workbench's data migration feature primarily supports tables and data, and does not support migrating views, stored procedures, functions, and other database objects.

Solution: After migration, you need to manually create the corresponding views and stored procedures in OceanBase.

Q2: What should I do if I encounter a data type incompatibility error during migration?

Cause: SQL Server and OceanBase have differences in some data types, such as datetimeoffset and hierarchyid, which OceanBase does not support.

Solution:

1. Check the data type mapping table before migration.
2. Convert incompatible data types in SQL Server before migration.
3. Or manually modify the field mapping during migration.