Navigating Enterprise Data Migration: Complexities from SQL Server, Oracle, IBM Db2 to MySQL/PostgreSQL

Data migration from proprietary relational databases—Microsoft SQL Server, Oracle Database, and IBM Db2—to open-source alternatives like MySQL and PostgreSQL is a strategic priority for enterprises seeking cost reduction, cloud flexibility, and vendor independence. However, this process is far from a simple "lift and shift." The core challenge lies in inherent structural, syntactic, and functional differences between proprietary and open-source databases, spanning data types, built-in functions, proprietary SQL syntax, transaction logic, and database objects.

This article breaks down the critical complexities of migrating to MySQL/PostgreSQL, with concrete SQL examples highlighting incompatible components and required transformations.

Executive Overview: Why Migration Is Not Trivial

Although SQL standard defines standard data types, syntax and system functions, each database system vendor define their own extra/alternative data types, syntax and system functions. This is especially so for proprietary products, and a major source of complexity and trouble to migrate to open source database systems. Data migration from proprietary databases (SQL Server, Oracle, Db2) to open source alternatives MySQL and PostgreSQL requires resolving incompatibilities across four foundational dimensions:

1. Data Type Mismatches: Direct type naming and behaviors differ across systems (e.g., binary types, date/time, large objects).
2. Non-standard System Functions: Vendor specific system functions.
3. Non-standard SQL Syntax: Non-ISO SQL syntax extensions for queries, DDL, DML, and stored procedures/functions.
4. Database Object Differences: Stored procedures/functions, triggers, indexes, views, and constraints with unique implementations.

Let's first see a detailed analysis of each complexity with actionable SQL examples, followed by an brief introduction to Yugong --- KlustronDB's powerful software for data migration, which solves a big part of above complexities to make such data migration reliable and fast.

---

Complexities and Challenges of Data Migration

1. Core Complexity: Data Type Incompatibilities

Data types are the foundation of database schemas, and there are some differences between any pair of source and destination database systems. Mismatches cause SQL statement failure, data loss, truncation, type errors, or application failures. Here are a few examples.

1.1 SQL Server → MySQL/PostgreSQL

SQL Server uses exclusive types like NVARCHAR(max), UNIQUEIDENTIFIER, MONEY, and IMAGE—none of which are natively supported in open-source databases. And it uses TIMESTAMP data type which isn't what one would presume(about a point in time) and which could confuse all other database systems.

SQL Server Type	MySQL Equivalent	PostgreSQL Equivalent	Risk
NVARCHAR(max)	LONGTEXT	TEXT	Truncation if unhandled
UNIQUEIDENTIFIER	CHAR(36)/UUID()	UUID	Invalid GUID storage
MONEY	DECIMAL(19,4)	MONEY (limited support)	Precision loss
IMAGE	LONGBLOB	BYTEA	Corrupted binary data

SQL Example: SQL Server DDL → Migration Fix

-- SQL Server (Source)
CREATE TABLE Customer (
    CustomerID UNIQUEIDENTIFIER PRIMARY KEY,
    FullName NVARCHAR(100) NOT NULL,
    Notes NVARCHAR(max),
    Balance MONEY,
    ProfileImage IMAGE
);

-- MySQL (Target)
CREATE TABLE Customer (
    CustomerID CHAR(36) PRIMARY KEY,  -- Replace UNIQUEIDENTIFIER
    FullName VARCHAR(100) NOT NULL,  -- Replace NVARCHAR (UTF-8 default)
    Notes LONGTEXT,  -- Replace NVARCHAR(max)
    Balance DECIMAL(19,4),  -- Replace MONEY
    ProfileImage LONGBLOB  -- Replace IMAGE
);

-- PostgreSQL (Target)
CREATE TABLE Customer (
    CustomerID UUID PRIMARY KEY,  -- Native UUID type
    FullName VARCHAR(100) NOT NULL,
    Notes TEXT,
    Balance NUMERIC(19,4),  -- Preferred over MONEY
    ProfileImage BYTEA  -- Native binary type
);

1.2 Oracle → MySQL/PostgreSQL

Oracle’s VARCHAR2, NUMBER, LONG, CLOB, and RAW types require careful mapping, especially for precision and large objects:

-- Oracle (Source)
CREATE TABLE Product (
    ProductID NUMBER(10) PRIMARY KEY,
    Description VARCHAR2(4000),
    Manual LONG, --- a long text but not a long integer as many programmers would assume
    LargeSpec CLOB,
    SerialNumber RAW(16)
);

-- MySQL (Target)
CREATE TABLE Product (
    ProductID INT(10) PRIMARY KEY,
    Description VARCHAR(4000),
    Manual LONGTEXT,
    LargeSpec LONGTEXT,
    SerialNumber BINARY(16)  -- Replace RAW
);

-- PostgreSQL (Target)
CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Description VARCHAR(4000),
    Manual TEXT,
    LargeSpec TEXT,
    SerialNumber BYTEA
);

1.3 IBM Db2 → MySQL/PostgreSQL

Db2’s GRAPHIC, DBCLOB, and DECIMAL types have strict open-source alternatives:

-- Db2 (Source)
CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    OrderDetails GRAPHIC(1000),
    AuditLog DBCLOB,
    TotalAmount DECIMAL(15,2)
);

-- PostgreSQL (Target)
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDetails VARCHAR(1000),
    AuditLog TEXT,
    TotalAmount NUMERIC(15,2)
);

---

2. Critical Complexity: Proprietary System Functions

Every proprietary database have many vendor-exclusive built-in functions for string manipulation, date/time operations, math, and system metadata. These functions break queries and view definitions in MySQL/PostgreSQL and require manual definition.

Refer to funciton mapping from MS SQL Server to MySQL, PostgreSQL and KlustronDB and from Oracle to MySQL, PostgreSQL and KlustronDB.

2.1 Date/Time Functions (Most Common Incompatibility)

Date and time handling is the most frequent pain point in migration—no two databases share identical date and time functions.

SQL Server → MySQL/PostgreSQL

-- SQL Server: Get current date + format date
SELECT GETDATE();  -- Current datetime
SELECT FORMAT(OrderDate, 'yyyy-MM-dd') FROM Orders;
SELECT DATEDIFF(day, OrderDate, GETDATE()) AS DaysPassed;

-- MySQL Equivalents
SELECT NOW();  -- Replace GETDATE()
SELECT DATE_FORMAT(OrderDate, '%Y-%m-%d') FROM Orders;
SELECT DATEDIFF(NOW(), OrderDate) AS DaysPassed;

-- PostgreSQL Equivalents
SELECT CURRENT_TIMESTAMP;  -- Replace GETDATE()
SELECT TO_CHAR(OrderDate, 'YYYY-MM-DD') FROM Orders;
SELECT CURRENT_DATE - OrderDate AS DaysPassed;

Oracle → MySQL/PostgreSQL

-- Oracle: Date functions
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(OrderDate, 'YYYY-MM-DD') FROM Orders;
SELECT TRUNC(SYSDATE - OrderDate) AS DaysPassed FROM Orders;

-- PostgreSQL Equivalents
SELECT CURRENT_DATE FROM DUAL;  -- PostgreSQL ignores DUAL
SELECT TO_CHAR(OrderDate, 'YYYY-MM-DD') FROM Orders;
SELECT CURRENT_DATE - OrderDate AS DaysPassed FROM Orders;

Db2 → MySQL/PostgreSQL

-- Db2 Date function
SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1;

-- MySQL/PostgreSQL
SELECT CURRENT_DATE;

2.2 String & System Functions

Vendor	Function	MySQL	PostgreSQL
SQL Server	ISNULL(col, 0)	IFNULL(col, 0)	COALESCE(col, 0)
SQL Server	LEN(string)	LENGTH(string)	LENGTH(string)
Oracle	NVL(col, 0)	IFNULL(col, 0)	COALESCE(col, 0)
Oracle	SUBSTR(string,1,5)	SUBSTRING(string,1,5)	SUBSTRING(string,1,5)
Db2	VALUE(col, 0)	IFNULL(col, 0)	COALESCE(col, 0)

SQL Example: SQL Server → Open-Source Rewrite

-- SQL Server
SELECT ISNULL(DisplayName, 'Guest') AS UserName, LOWER(Email) AS Email FROM Users;

-- MySQL
SELECT IFNULL(DisplayName, 'Guest') AS UserName, LOWER(Email) AS Email FROM Users;

-- PostgreSQL
SELECT COALESCE(DisplayName, 'Guest') AS UserName, LOWER(Email) AS Email FROM Users;

---

3. Major Complexity: Private SQL Syntax Extensions

Proprietary databases have a lot of non-ISO SQL syntax for pagination, conditional logic, stored procedures, and DDL—these are completely invalid in MySQL/PostgreSQL.

3.1 Pagination Syntax

Pagination is a universal feature, but every vendor uses a unique implementation:

SQL Server (TOP/OFFSET FETCH) → MySQL/PostgreSQL

-- SQL Server: Pagination
SELECT TOP 10 * FROM Customers ORDER BY CustomerID;
SELECT * FROM Customers ORDER BY CustomerID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL/PostgreSQL: Standard LIMIT/OFFSET
SELECT * FROM Customers ORDER BY CustomerID LIMIT 10;
SELECT * FROM Customers ORDER BY CustomerID LIMIT 10 OFFSET 10;

Oracle (ROWNUM) → MySQL/PostgreSQL

-- Oracle
SELECT * FROM (SELECT * FROM Customers ORDER BY CustomerID) WHERE ROWNUM <= 10;

-- MySQL/PostgreSQL
SELECT * FROM Customers ORDER BY CustomerID LIMIT 10;

3.2 Stored Procedure Syntax

Stored procedures are highly proprietary—the biggest migration hurdle for enterprise logic.

SQL Server Stored Procedure → PostgreSQL

-- SQL Server: Proprietary Stored Procedure
CREATE PROCEDURE GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

-- PostgreSQL Equivalent (PL/pgSQL)
CREATE OR REPLACE PROCEDURE GetCustomerOrders(p_CustomerID INT)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = p_CustomerID;
END;
$$;

Oracle Stored Procedure → MySQL

-- Oracle
CREATE PROCEDURE GetProductStock(p_ProductID INT)
IS
    StockQty INT;
BEGIN
    SELECT Stock INTO StockQty FROM Inventory WHERE ProductID = p_ProductID;
END;

-- MySQL
CREATE PROCEDURE GetProductStock(IN p_ProductID INT)
BEGIN
    DECLARE StockQty INT;
    SELECT Stock INTO StockQty FROM Inventory WHERE ProductID = p_ProductID;
END;

3.3 DDL & Constraint Syntax

• SQL Server: IDENTITY(1,1) for auto-increment → MySQL AUTO_INCREMENT / PostgreSQL SERIAL/GENERATED AS IDENTITY
• Oracle: SEQUENCE + TRIGGER for auto-increment → Open-source native auto-increment
• Db2: GENERATED ALWAYS AS IDENTITY → Minor syntax tweaks

SQL Example: Auto-Increment Migration

-- SQL Server
CREATE TABLE Users (UserID INT IDENTITY(1,1) PRIMARY KEY);

-- MySQL
CREATE TABLE Users (UserID INT AUTO_INCREMENT PRIMARY KEY);

-- PostgreSQL
CREATE TABLE Users (UserID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

---

4. Hidden Complexities: Advanced Database Features

Beyond basic syntax and types, enterprise-grade features create additional migration challenges:

1. Partitioning: SQL Server/Oracle/Db2 partitioning syntax differs from PostgreSQL declarative partitioning and MySQL partitioning.
2. Triggers: Vendor-specific trigger syntax and timing (INSTEAD OF in SQL Server, supported in PostgreSQL but not MySQL) and syntax require full rewrites.
3. Transactions & Isolation Levels: Proprietary isolation levels (e.g., SQL Server SNAPSHOT) need mapping to open-source standards.
4. Large Object (LOB) Handling: Oracle CLOB/Db2 DBCLOB require special migration tools to avoid data corruption in MySQL TEXT/PostgreSQL TEXT.

Summary

Data migration from SQL Server, Oracle, and Db2 to MySQL or PostgreSQL is a challenging and multi-phased project. However, the long-term benefits --- cost savings, cloud portability, and open-source community support --- far outweigh the short-term complexity and cost. Using automated tools like Yogong in a phased approach, enterprises can succeed with the migration to modern, open-source databases.

---

Introduction to Yugong

Yugong is a software to migrate database schemas and data from proprietary relational databases—Microsoft SQL Server, Oracle Database, and IBM Db2—to open-source alternatives like MySQL and PostgreSQL , as well as KlustronDB --- KlustronDB's flagship database system.

The name Yugong comes from a legendary acient Chinese farmer who was determined to move a gigantic mountain afront his house and farm, and with generations he and his descendants made it. Now with Yugong it takes no more than a few minutes to a few hours to migrate your huge amount of data from the 3 types of proprietary relational databases to one of MySQL, PostgreSQL, or KlustronDB.

What's migrated by Yugong

Table Definitions

Complete definitions of all tables in specified schema of the source database are migrated into the destination database. Specifically below database objects are migrated.

Table Basics

Each table created in destination database has same table name as its couterpart in source db, and defined in specified destination schema.

Column data types are mapped to proper types in target destination database, together with basic column info such as name, width, precision, scale, etc;

All comments of a table and all its columns are also migrated.

Table Affiliates

All of these table characteristics and affiliates are migrated properly:

1. Primary keys, indexes, foreign keys, check constraints;

2. All index properties, including its uniqueness, visibility, effectiveness, etc.

3. All extended column properties including nullability, default value, computed(virtual or generated) column definition, identity and visibility;

Table partitions.(TODO) Currently this is not yet migrated, i.e. a partitioned table in source database becomes a single table in destination database. Although the table is created properly other that that and and all rows migrated correctly.

Complete data of each migrated table

All rows are queried in each source table and inserted into its counterpart table in destination database.

Source tables are not supposed to be modified while it's being migrated otherwise latest changes would not go into destination table.

What's Not Migrated

1. Triggers on any table are not migrated.

2. Views, stored procedures, user defined functions, user defined types, database users and privileges are not migrated.

It takes manual rewrite to redefine all above in destination database, and using coding agant could sigfinicantly accelerate and automate such work.

---

Summary: Strategies for Successful Data Migration

Enterprises can perform data migration with these proven strategies:

1. Automated Migration with Yugong: Migrate schemas and data first, which can be done by Yugong automatically;
2. Remaining Heavy lift Define missing system functions used by queries, views, triggers, proceduers and functions in destination database instance. And one may also need to do so in step 1 for defaut value expressions and computed column value expressions; and then rewrite stored procedures/functions and triggers, views.
3. Application Code Refactoring: Update certain application queries as necessary to SQL syntax supported by your destination database system.
4. Testing and verification: Validate data integrity, query performance, and functional parity in staging environments.