How to Migrate SQL Server to OceanBase Using Action OMS: A Step‑by‑Step Guide

1. Introduction

Domestic customers running core business systems on IBM DB2, Microsoft SQL Server and other databases need a high‑performance solution for migration to OceanBase. The official OMS tool does not support SQL Server, so Action OMS – a self‑developed version based on OMS 4.2 source code – fills this gap.

What is Action OMS? Action OMS builds on OMS’s capabilities and leverages ActionSky’s extensive database development experience to provide a customized migration and subscription solution. All OMS code is fully licensed to ActionSky, allowing source‑level debugging and custom development.

1.1 Action OMS Features

Data Migration : Supports migration of SQL Server 2008/2012/2014/2016/2017/2019 Enterprise editions to OceanBase.

Data Subscription : Real‑time subscription for SQL Server, DB2 and other databases, capturing change events for analytics, auditing, reporting, etc.

Action OMS adds the following internal columns to the target OceanBase tables to store change metadata:

__dts_commit_seq   varbinary(24)   Transaction ID + statement order
__dts_operation    int            1=Delete, 2=Insert, 3=Update‑old, 4=Update‑new
__dts_ts           TIMESTAMP      Change timestamp (SQL Server: transaction start, OB: commit)
__dts_capture_ts   TIMESTAMP      Capture time (only for OB source)
<source column>    <source type>   Original column data captured

1.2 Component Versions

Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (x64) Enterprise Edition

OceanBase V4.2.5 BP5

Action OMS 4.25.09.0

2. Source‑Side SQL Server Preparation

2.1 Create Migration User

Ensure the migration user has sysadmin and db_owner privileges. select is_srvrolemember('sysadmin'), is_member('db_owner'); If the privileges are missing, create the login and grant roles:

USE master;
CREATE LOGIN [renzy] WITH PASSWORD='Password!123', DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
ALTER SERVER ROLE [sysadmin] ADD MEMBER [renzy];
USE subscribe;
CREATE USER [renzy] FOR LOGIN [renzy] WITH DEFAULT_SCHEMA = dbo;
ALTER ROLE [db_owner] ADD MEMBER [renzy];

2.2 Ensure SQL Server Agent Is Running

exec master.dbo.xp_servicecontrol N'QUERYSTATE',N'SQLSERVERAGENT';

2.3 Enable CDC on Database

CDC (Change Data Capture) is required for Action OMS.

EXEC sys.sp_cdc_enable_db;

2.4 Enable CDC on Business Tables

-- Tables without CDC
select NAME, TYPE, is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = 0 and SCHEMA_ID=1 order by NAME;
-- Tables with CDC
select NAME, TYPE, is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = 1 and SCHEMA_ID=1 order by NAME;

2.5 Verify Primary Key Exists

SELECT TABLE_SCHEMA, PRIMARY_KEY, COUNT(*) FROM (
    SELECT SCHEMA_NAME(T.SCHEMA_ID) AS TABLE_SCHEMA,
           CASE WHEN K.NAME IS NOT NULL THEN 'HAS_PRIMARY_KEY' ELSE 'NO_PRIMARY_KEY' END AS PRIMARY_KEY
    FROM SYS.TABLES T
    LEFT JOIN SYS.key_constraints K ON T.object_id = K.parent_object_id AND K.type = 'PK'
    WHERE T.TYPE='U' AND T.SCHEMA_ID = 1
) A GROUP BY TABLE_SCHEMA, PRIMARY_KEY;

Action OMS automatically filters out tables without a primary key or a non‑null unique index; they will not appear in the migration list.

3. Configure Migration and Subscription Pipelines

3.1 Add SQL Server Data Source

After deploying Action OMS, add the SQL Server data source (one database per source).

3.2 Add OceanBase Data Source

Add an OceanBase (MySQL mode) data source.

3.3 Configure Migration Link

Create a new migration link in the Action OMS migration module. DDL synchronization is currently not supported.

3.4 Full‑Volume Data Validation

Action OMS supports full‑volume validation after migration, allowing re‑validation of inconsistent tables if needed.

3.5 Configure Subscription Link

Create a subscription link in the Action OMS subscription module. Only database‑level subscription is supported.

Subscription adds four internal columns (__dts_commit_seq, __dts_operation, __dts_ts, __dts_capture_ts) to the target tables to record change identifiers, timestamps, and operation types.

4. SQL Server Adaptation Notes

4.1 Timestamp Semantics

SQL Server’s timestamp (rowversion) is a binary(8) value used for row version control, while MySQL’s timestamp stores a datetime. Action OMS converts SQL Server timestamps to binary(8) in OceanBase to avoid semantic mismatch.

SQL Server timestamp (rowversion): binary(8), internal incrementing version, no timezone.
MySQL timestamp: datetime, stores Unix epoch seconds, timezone‑aware.

4.2 BIT Semantics

SQL Server BIT is a boolean (0/1). OceanBase BIT is a binary type. When querying via command‑line tools, use bin() to view the correct binary representation.

select bin(BIT_COLUMN) from table_name;

5. Summary

Action OMS extends OceanBase OMS to support SQL Server 2008‑2019 migration and real‑time subscription, providing low‑latency data sync, primary‑key validation, CDC‑based change capture, and handling of type differences such as timestamp and BIT. It offers a practical reference for enterprises undertaking SQL Server to OceanBase migration.