.::: Migration MariaDB/MySQL to MSSQL using SQLines Data Migration :::.

A. Prepare software migration

1. Download SQLines Data & extract 

http://www.sqlines.com/download

SQLines Data is a tool for data transfer, schema conversion and data migration validation/testing.

Note:

SQLines Data tool does not convert views, stored procedures, functions, and triggers, use SQLines SQL Converter to convert them.

2. Download SQLines SQL Converter (Optional)

SQLines SQL Converter

SQLines SQL Converter helps you convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers.

3. Extract SQLlines Data

4. download libmysql.dll and extract to directory SQLine

https://www.dll-files.com/libmysql.dll.html

https://www.dll-files.com/libmariadb.dll.html

5. if need. you can buu license 

http://www.sqlines.com/purchase

B. Capture before migration

1. Capture source Database (MariaDB/MySQL)

use tgh;

select * from barang;

select * from customer;

select * from suplier;

select * from pasok;

select * from pembelian;

MariaDB [tka]> show tables;

+---------------+

| Tables_in_tka |

+---------------+

| barang        |

| customer      |

| pasok         |

| pembelian     |

| suplier       |

+---------------+

5 rows in set (0.00 sec)

MariaDB [tka]> select * from barang;

+-------------+-------------+---------------+-------------+

| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |

+-------------+-------------+---------------+-------------+

| ELK-02      | LEMARI ES   | UNIT          |           8 |

| ELK-03      | TELEVISI    | UNIT          |          30 |

| ELK-04      | RADIO/TAPE  | BUAH          |          35 |

| ELK-05      | KOMPUTER    | UNIT          |          28 |

| ELK-06      | KIPAS ANGIN | BUAH          |          38 |

+-------------+-------------+---------------+-------------+

5 rows in set (0.03 sec)

MariaDB [tka]> select * from suplier;

+--------------+---------------------+----------------------+--------------+-----------------+

| KODE_SUPLIER | NAMA_SUPLIER        | ALAMAT_SUPLIER       | KOTA_SUPLIER | TELEPON_SUPLIER |

+--------------+---------------------+----------------------+--------------+-----------------+

| EB-01        | PT ULTRASOUND       | JL SUKARNO HATTA 103 | BANDUNG      | (021) 522-3305  |

| EB-02        | PT SUPERTRON        | JL INDUSTRI 37       | BANDUNG      | (021) 660-4091  |

| EB-03        | PT OCHANG           | JL KOSAMBI 44        | BANDUNG      | (022) 850-4444  |

| EB-04        | PT TUNGGAL JAYA     | JL KIARA CONDONG 77  | BANDUNG      | (022)740-7777   |

| EJ-01        | PT ACTRON           | JL THAMRIN 12        | JAKARTA      | (021) 850-2301  |

| EJ-02        | PT MULYA ELEKTRONIK | JL SUDIRMAN 45       | JAKARTA      | (021) 855-4262  |

+--------------+---------------------+----------------------+--------------+-----------------+

6 rows in set (0.01 sec)

MariaDB [tka]> exit

Bye

2. Capture destination Target (MSSQL)

use tgh;

select * from barang;

select * from customer;

select * from suplier;

select * from pasok;

select * from pembelian;

C. Migration MariaDB/MySQL to MSSQL

1. open sqldataw SQLLine Data

2. Tab Connection. complete form (as example)

Source MariaDB/MySQL

SID/Service Name         = tka

User = root

password = xxx

press button Test Connection

Destination MSSQL 

SID/Service Name     = tgh

User = sa

password = xxx

press button Test Connection

2. Tab Setting

Transfer option = Table recreate (Default)

Table list = *barang*,*customer*,*suplier*,*pasok*,*pembelian* (sample)

3. Press button Transfer & see tab Transfer log

4. Waiting & see tab log to detail

5. Migration success 

SQLines Data 3.3.107 x64 - Database Migration Tool.

Copyright (c) 2020 SQLines. All Rights Reserved.

FOR EVALUATION USE ONLY, MAY SKIP 1 ROW FROM A TABLE

Connecting to databases (2.0 sec)

  MySQL 10.1.14-MariaDB mariadb.org binary distribution x64 Win64 system YaSSL 2.3.9b (Ok, 2.0 sec)

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Ok, 16 ms)

Reading the database schema (5 tables, 94 ms)

Transferring database (4 concurrent sessions):

  tka.barang - Started (1 of 5, session 1)

  tka.barang - Open cursor (6 rows read, 0 ms, session 1)

  tka.barang - Drop target table (not exists, 140 ms, session 1)

  tka.barang - Create target table (0 ms, session 1)

  tka.barang - Data transfer complete (session 1)

     Rows read:     6 (0 rows/sec)

     Rows written:  6 (28 rows/sec, 128 bytes, 587 bytes/sec)

     Transfer time: 531 ms (0 ms read, 218 ms write)

  tka.customer - Started (2 of 5, session 1)

  tka.customer - Open cursor (6 rows read, 0 ms, session 1)

  tka.customer - Drop target table (not exists, 15 ms, session 1)

  tka.customer - Create target table (0 ms, session 1)

  tka.customer - Data transfer complete (session 1)

     Rows read:     6 (0 rows/sec)

     Rows written:  6 (0 rows/sec, 299 bytes, 0 bytes/sec)

     Transfer time: 31 ms (0 ms read, 0 ms write)

  tka.pasok - Started (3 of 5, session 1)

  tka.pasok - Open cursor (12 rows read, 0 ms, session 1)

  tka.pasok - Drop target table (not exists, 15 ms, session 1)

  tka.pasok - Create target table (0 ms, session 1)

  tka.pasok - Data transfer complete (session 1)

     Rows read:     12 (0 rows/sec)

     Rows written:  12 (0 rows/sec, 361 bytes, 0 bytes/sec)

     Transfer time: 46 ms (0 ms read, 0 ms write)

  tka.pembelian - Started (4 of 5, session 1)

  tka.pembelian - Open cursor (15 rows read, 0 ms, session 1)

  tka.pembelian - Drop target table (not exists, 16 ms, session 1)

  tka.pembelian - Create target table (0 ms, session 1)

  tka.pembelian - Data transfer complete (session 1)

     Rows read:     15 (0 rows/sec)

     Rows written:  15 (0 rows/sec, 467 bytes, 0 bytes/sec)

     Transfer time: 47 ms (0 ms read, 0 ms write)

  tka.suplier - Started (5 of 5, session 1)

  tka.suplier - Open cursor (6 rows read, 0 ms, session 1)

  tka.suplier - Drop target table (not exists, 16 ms, session 1)

  tka.suplier - Create target table (0 ms, session 1)

  tka.suplier - Data transfer complete (session 1)

     Rows read:     6 (0 rows/sec)

     Rows written:  6 (0 rows/sec, 325 bytes, 0 bytes/sec)

     Transfer time: 31 ms (0 ms read, 0 ms write)

Summary:

  Tables:        5 (5 Ok, 0 failed)

  Target DDL:    10 (10 Ok, 0 failed)

  Rows read:     45

  Rows written:  45

  Transfer time: 6.0 sec (7 rows/sec, 1.5 KB, 262 bytes/sec)

Logs:

  Execution log:             sqldata.log

  DDL SQL statements:        sqldata_ddl.sql

D. Capture after migration

1. Capture Maria /MySQL

MariaDB [tka]> desc barang;

+---------------+--------------+------+-----+---------+-------+

| Field         | Type         | Null | Key | Default | Extra |

+---------------+--------------+------+-----+---------+-------+

| KODE_BARANG   | char(6)      | NO   |     | NULL    |       |

| NAMA_BARANG   | varchar(25)  | YES  |     | NULL    |       |

| SATUAN_BARANG | varchar(20)  | YES  |     | NULL    |       |

| STOK_BARANG   | decimal(4,0) | YES  |     | NULL    |       |

+---------------+--------------+------+-----+---------+-------+

4 rows in set (0.20 sec)

MariaDB [tka]> desc suplier;

+-----------------+-------------+------+-----+---------+-------+

| Field           | Type        | Null | Key | Default | Extra |

+-----------------+-------------+------+-----+---------+-------+

| KODE_SUPLIER    | char(5)     | NO   |     | NULL    |       |

| NAMA_SUPLIER    | varchar(30) | YES  |     | NULL    |       |

| ALAMAT_SUPLIER  | varchar(30) | YES  |     | NULL    |       |

| KOTA_SUPLIER    | varchar(15) | YES  |     | NULL    |       |

| TELEPON_SUPLIER | varchar(15) | YES  |     | NULL    |       |

+-----------------+-------------+------+-----+---------+-------+

5 rows in set (0.01 sec)

MariaDB [tka]> desc customer;

+------------------+-------------+------+-----+---------+-------+

| Field            | Type        | Null | Key | Default | Extra |

+------------------+-------------+------+-----+---------+-------+

| KODE_CUSTOMER    | char(6)     | NO   |     | NULL    |       |

| NAMA_CUSTOMER    | varchar(30) | YES  |     | NULL    |       |

| ALAMAT_CUSTOMER  | varchar(30) | YES  |     | NULL    |       |

| KOTA_CUSTOMER    | varchar(15) | YES  |     | NULL    |       |

| TELEPON_CUSTOMER | varchar(15) | YES  |     | NULL    |       |

+------------------+-------------+------+-----+---------+-------+

5 rows in set (0.03 sec)

MariaDB [tka]> desc pasok;

+---------------+--------------+------+-----+---------+-------+

| Field         | Type         | Null | Key | Default | Extra |

+---------------+--------------+------+-----+---------+-------+

| KODE_PASOK    | char(10)     | NO   |     | NULL    |       |

| KODE_BARANG   | char(6)      | NO   |     | NULL    |       |

| KODE_SUPLIER  | char(5)      | NO   |     | NULL    |       |

| TANGGAL_PASOK | date         | YES  |     | NULL    |       |

| JUMLAH_PASOK  | decimal(4,0) | YES  |     | NULL    |       |

+---------------+--------------+------+-----+---------+-------+

5 rows in set (0.04 sec)

MariaDB [tka]> desc pembelian;

+-------------------+--------------+------+-----+---------+-------+

| Field             | Type         | Null | Key | Default | Extra |

+-------------------+--------------+------+-----+---------+-------+

| KODE_PEMBELIAN    | char(10)     | NO   |     | NULL    |       |

| KODE_BARANG       | char(6)      | NO   |     | NULL    |       |

| KODE_CUSTOMER     | char(6)      | NO   |     | NULL    |       |

| TANGGAL_PEMBELIAN | date         | YES  |     | NULL    |       |

| JUMLAH_PEMBELIAN  | decimal(4,0) | YES  |     | NULL    |       |

+-------------------+--------------+------+-----+---------+-------+

5 rows in set (0.04 sec)

MariaDB [tka]>

2. capture MSSQL 

E. Test insert resutl migration MariaDB/MySQL to MSSQL 

select * from barang where KODE_BARANG ='ELK-99';

select * from suplier where KODE_SUPLIER ='EJ-99';

select * from customer where KODE_CUSTOMER ='J-0099';

select * from pasok where KODE_PASOK ='PAS-E099';

select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);

insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');

insert into customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');

insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','1-Jan-02',8);

insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','20-May-02',3);

select * from barang where KODE_BARANG ='ELK-99';

select * from suplier where KODE_SUPLIER ='EJ-99';

select * from customer where KODE_CUSTOMER ='J-0099';

select * from pasok where KODE_PASOK ='PAS-E099';

select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

delete from barang where kode_barang = 'ELK-99';

delete from suplier where kode_suplier = 'EJ-99';

delete from customer where kode_customer = 'J-0099';

delete from pasok where kode_pasok = 'PAS-E099';

delete from pembelian where kode_pembelian = 'BEL-E099';

select * from barang where KODE_BARANG ='ELK-99';

select * from suplier where KODE_SUPLIER ='EJ-99';

select * from customer where KODE_CUSTOMER ='J-0099';

select * from pasok where KODE_PASOK ='PAS-E099';

select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

log