"Zero Cost" - Data migration between MS-SQL, MYSQL and ORACLE

In this article, let us discuss about how to implement offline Migration / Replication between MS-SQL | MySQL | Oracle in ZERO cost using native utilities.

Introduction:

Use Case:

If :

- Your business org doesn't deal with criticalworkloads

- Replication can be done offline / Batch Synchornizaiton

- Your org don't want to invest additional cost for Replication.

then :

you are in right page.

In this use-case, let we do replication from MS-SQL server to MySQL. The same logic can be implemented for Oracle database too.

Microsoft SQL Server is a well-known Relational Database Management System (RDBMS) developed by Microsoft. As a database server that stores and retrieves data as requested by other software applications on the same computer or a remote computer using the client-server model. Microsoft provides APIs to access SQL Server over the internet as a web service.

 For additional information, refer here

MySQL is an open source, relational database management system (RDBMS) based on structured query language (SQL). MySQL is available on all major operating systems, including Windows, Linux and Solaris. It is free to use for individuals and non-production environments under the GNU General Public License; however, if used commercially, a commercial license is required

For additional information, refer here

What is expected:

• Data from Master table required to be replicated to Target Master Table

• " Change Data " ( Insert / Delete / Update ) in master table should be replicated in TARGET master table

• Replication is expected ONLY during offline

Proposed Solution

-        With help of trigger, we easily able to log the transaction of Insert / Update /

Delete  in separate log table along with internal flag for each transaction.

-        At end of day or during business off hours, from source log table, fetch ONLY

the records that has CHANGE DELTA and  based on FLAG order it appropriately

-        Spool the result from previous step to spreadsheet.  That’s It !!!!

-        Now, using native tools, push the records to TARGET database via batch job.

Here is the architecture flow of OFF Line Replication from MS-SQL Server to MySQL

Architecture

How it works:

At Eagle Eye :

@ MSSQL

• Changes need to logged to audit able by enabling trigger

• Unload the source data to flat file

@ MY-SQL

• From flat file, CDC data are inserted to staging table

• Using trigger, need to insert/Delete/Update to maser table

At Hawk Eye : ( Still more in detail ),

• S1: Create audit table for transaction type like Insert , Delete, Update. ( To make process simple, i created three audit tables in the name of LOG_INS, LOG_DEL, LOG_UPD). It holds only PK and action of transaction

  • 
    

Audit Table Defn

Note: Inspite of three audit tables, we can have single audit table to capture CDC ( actions like Insert/Update/Delete) operations. But it increases complexity while unloading the data from Source Tables)

• S2: Create appropriate Triggers at Source

  • Create "For Insert " and "For Update" trigger to log complete transaction data to LOG_INS and LOG_UPD audit table

  
  

• S3: Create master table at TARGET with same definition of source ( note: ensure to have appropriate data types )

• S4: Create two Staging table for DELETE and UPDATE operation at TARGET .

  • From , flat file ( CSV ) data are inserted into Staging Table

  • From, Staging table , data are pushed to MASTER table

Replication Methodology

Stage -1 :

• Do Initial UnLoad

  • spool to flat file (csv) from SOURCE (MS-SQL) master table

• Do Initial Load

  • Load data from flat file to TARGET (MYSQL) master table

Stage-2:

• Enable CDC trigger

  • Once trigger is enabled, it will start logging of all transaction ( ins/del/upd) information in appropriate audit tables.

  • Audit tables have important information of PK,, Action, systemtimestamp.

Stage-3:

• Intermittently , unload ONLY the modified data to flat file ( csv), based on PK and timestamp

• Transfer flat file to TARGET location

Stage-4

• At  during sync time / end of day, run sync batch job

  o   Load data to staging table of TARGET .

  o   From staging table, data is deleted | updated to master table using trigger.

  
  
  
  

Note:

Why Stage Tabile:

• it consolidates in appropriate order of operation as per timestamp

• Ensures data integrity prior to replication in Target Master Table for Update and Delete Operation

• Optionally, For newly inserted records at SOURCE master table does not require to stage table. It can be loaded directly to TARGET master table without stage table.

Technical Challenge

-        Data type of both databases has difference in few datatypes like integer, number, etc

-        Hence user should ensure to have appropriate table definition in TARGET database according to SOURCE  ( Table definition ) database prior to initial data loading.

Please view for complete demo

Conclusion

This article demonstrates to a lightweight replication framework that replicates data from MS SQL Server to MySQL using only native tools and triggers.

Using native tools from MS-SQL , MY-SQL , Oracle, we can capture CDC records using trigger that helps CDC data to  replicate to target database with NO additional cost for replication

 In Modern Data Platform, it has capable of integrating heterogenous data type to native format for  meaningful business insights in ZERO cost.