.::: Migration SQL Server (MSSQL) to MariaDB/MySQL 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 SQLLine

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 (MSSQL)

use tgh;

select * from barang;

select * from customer;

select * from suplier;

select * from pasok;

select * from pembelian;

2. Capture destination Target (MariaDB/MySQL)

use tgh;

select * from barang;

select * from customer;

select * from suplier;

select * from pasok;

select * from pembelian;

C:\windows\System32>mysql -u root -p

Enter password: ****

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 4

Server version: 10.1.14-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use tka

Database changed

MariaDB [tka]> show tables;

Empty set (0.00 sec)

MariaDB [tka]>

C. Migration MSSQL to MariaDB/MySQL

1. open sqldataw SQLine Data

2. Tab Connection. complete form (as example)

Source MSSQL

SID/Service Name  = tgh

User  = sa

password  = xxx

press button Test Connection

Destination MariaDB/MySQL 

SID/Service Name  = tka

User  = root

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)

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

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

Reading the database schema (5 tables, 235 ms)

Validating table row count (4 concurrent sessions):

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

  dbo.barang - Not Equal (0 ms, session 1)

    Source: 6 rows (0 ms)

    Target: 5 rows (0 ms)

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

  dbo.customer - Not Equal (0 ms, session 1)

    Source: 6 rows (0 ms)

    Target: 5 rows (0 ms)

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

  dbo.pasok - Not Equal (15 ms, session 1)

    Source: 12 rows (15 ms)

    Target: 11 rows (15 ms)

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

  dbo.pembelian - Not Equal (0 ms, session 1)

    Source: 15 rows (0 ms)

    Target: 14 rows (0 ms)

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

  dbo.suplier - Ok (0 ms, session 1)

    Source: 6 rows (0 ms)

    Target: 6 rows (0 ms)

Row count differences (4 tables):

    1. dbo.barang (6 rows in source, 5 rows in target)

    2. dbo.customer (6 rows in source, 5 rows in target)

    3. dbo.pasok (12 rows in source, 11 rows in target)

    4. dbo.pembelian (15 rows in source, 14 rows in target)

Summary:

  Total number of tables:         5

    With the same row count:      1

    With the different row count: 4

  Total number of rows:           45 in source, 41 in target

    Row count difference:         4

  Validation time:                6.0 sec

  

D. Capture after migration  

1. Capture source Database (MSSQL)

use tgh;

select * from barang;

select * from customer;

select * from suplier;

select * from pasok;

select * from pembelian;

2. Capture destination Target (MariaDB/MySQL)

use tgh;

select * from barang;

select * from customer;

select * from suplier;

select * from pasok;

select * from pembelian;

C:\windows\System32>mysql -u root -p

Enter password: ****

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 4

Server version: 10.1.14-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [tka]> use tka

Database changed

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.00 sec)

MariaDB [tka]> select * from customer;

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

| KODE_CUSTOMER | NAMA_CUSTOMER | ALAMAT_CUSTOMER | KOTA_CUSTOMER | TELEPON_CUSTOMER |

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

| B-0002        | TOKO SURYA    | JL ABC 309      | BANDUNG       | (022) 432-6024   |

| B-0003        | TOKO MAHARANI | JL KOPO 333     | BANDUNG       | (022) 856-3333   |

| B-0004        | TOKO MULYA    | JL OTISTA 555   | BANDUNG       | (022) 422-5555   |

| J-0001        | TOKO KARISMA  | JL CIMANGGIS 34 | JAKARTA       | (021) 856-4209   |

| J-0002        | TOKO AYU      | JL CIMANGGIS 12 | JAKARTA       | (021) 856-1321   |

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

5 rows in set (0.00 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.00 sec)

MariaDB [tka]> select * from pasok;

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

| KODE_PASOK | KODE_BARANG | KODE_SUPLIER | TANGGAL_PASOK | JUMLAH_PASOK |

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

| PAS-E002   | ELK-01      | EJ-02        | 2002-01-01    |            5 |

| PAS-E003   | ELK-02      | EJ-01        | 2002-02-01    |            2 |

| PAS-E004   | ELK-02      | EJ-02        | 2002-02-02    |            3 |

| PAS-E005   | ELK-02      | EB-02        | 2002-01-01    |            2 |

| PAS-E006   | ELK-03      | EJ-01        | 2002-03-03    |            5 |

| PAS-E007   | ELK-03      | EJ-01        | 2002-03-04    |            2 |

| PAS-E008   | ELK-03      | EJ-01        | 2002-03-03    |            3 |

| PAS-E009   | ELK-03      | EB-01        | 2002-03-13    |            4 |

| PAS-E010   | ELK-03      | EB-01        | 2002-03-13    |            3 |

| PAS-E011   | ELK-04      | EB-01        | 2002-04-22    |           12 |

| PAS-E012   | ELK-04      | EB-02        | 2002-04-30    |            9 |

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

11 rows in set (0.00 sec)

MariaDB [tka]> select * from pembelian;

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

| KODE_PEMBELIAN | KODE_BARANG | KODE_CUSTOMER | TANGGAL_PEMBELIAN | JUMLAH_PEMBELIAN |

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

| BEL-E002       | ELK-01      | J-0001        | 2002-05-21        |                4 |

| BEL-E003       | ELK-01      | J-0002        | 2002-05-20        |                2 |

| BEL-E004       | ELK-01      | B-0001        | 2002-05-20        |                2 |

| BEL-E005       | ELK-01      | B-0002        | 2002-05-22        |                3 |

| BEL-E006       | ELK-02      | J-0001        | 2002-06-24        |                1 |

| BEL-E007       | ELK-02      | J-0002        | 2002-06-24        |                1 |

| BEL-E008       | ELK-02      | B-0001        | 2002-06-25        |                2 |

| BEL-E009       | ELK-02      | B-0002        | 2002-06-25        |                2 |

| BEL-E010       | ELK-03      | J-0001        | 2002-06-20        |                5 |

| BEL-E011       | ELK-03      | J-0002        | 2002-07-02        |                4 |

| BEL-E012       | ELK-03      | B-0001        | 2002-07-02        |                6 |

| BEL-E013       | ELK-03      | J-0001        | 2002-07-10        |                5 |

| BEL-E014       | ELK-04      | J-0002        | 2002-07-15        |               12 |

| BEL-E015       | ELK-04      | B-0002        | 2002-07-17        |               15 |

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

14 rows in set (0.00 sec)

MariaDB [tka]>

3. Test insert resutl migration MSSQL to MariaDB/ MySQL

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','2002-01-01',8);

insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','2002-05-20',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  

C:\windows\System32>mysql -u root -p

Enter password: ****

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 39

Server version: 10.1.14-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use tka;

Database changed

MariaDB [tka]> select * from barang where KODE_BARANG ='ELK-99';

Empty set (0.00 sec)

MariaDB [tka]> select * from suplier where KODE_SUPLIER ='EJ-99';

Empty set (0.00 sec)

MariaDB [tka]> select * from customer where KODE_CUSTOMER ='J-0099';

Empty set (0.00 sec)

MariaDB [tka]> select * from pasok where KODE_PASOK ='PAS-E099';

Empty set (0.00 sec)

MariaDB [tka]> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

Empty set (0.00 sec)

MariaDB [tka]>

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

Query OK, 1 row affected (0.05 sec)

MariaDB [tka]> 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');

Query OK, 1 row affected (0.05 sec)

MariaDB [tka]> 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');

Query OK, 1 row affected (0.04 sec)

MariaDB [tka]> insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','2002-01-01',8);

Query OK, 1 row affected (0.09 sec)

MariaDB [tka]> insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','2002-05-20',3);

Query OK, 1 row affected (0.04 sec)

MariaDB [tka]>

MariaDB [tka]> select * from barang where KODE_BARANG ='ELK-99';

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

| KODE_BARANG | NAMA_BARANG              | SATUAN_BARANG | STOK_BARANG |

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

| ELK-99      | Barang - After Migration | BUAH          |          20 |

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

1 row in set (0.00 sec)

MariaDB [tka]> select * from suplier where KODE_SUPLIER ='EJ-99';

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

| KODE_SUPLIER | NAMA_SUPLIER              | ALAMAT_SUPLIER | KOTA_SUPLIER | TELEPON_SUPLIER |

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

| EJ-99        | Suplier - After Migration | JL THAMRIN 12  | JAKARTA      | (021) 850-2301  |

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

1 row in set (0.00 sec)

MariaDB [tka]> select * from customer where KODE_CUSTOMER ='J-0099';

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

| KODE_CUSTOMER | NAMA_CUSTOMER              | ALAMAT_CUSTOMER | KOTA_CUSTOMER | TELEPON_CUSTOMER |

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

| J-0099        | Customer - After Migration | JL CIMANGGIS 34 | JAKARTA       | (021) 856-4209   |

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

1 row in set (0.00 sec)

MariaDB [tka]> select * from pasok where KODE_PASOK ='PAS-E099';

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

| KODE_PASOK | KODE_BARANG | KODE_SUPLIER | TANGGAL_PASOK | JUMLAH_PASOK |

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

| PAS-E099   | ELK-99      | EJ-99        | 2002-01-01    |            8 |

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

1 row in set (0.00 sec)

MariaDB [tka]> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

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

| KODE_PEMBELIAN | KODE_BARANG | KODE_CUSTOMER | TANGGAL_PEMBELIAN | JUMLAH_PEMBELIAN |

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

| BEL-E099       | ELK-99      | J-0099        | 2002-05-20        |                3 |

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

1 row in set (0.00 sec)

MariaDB [tka]>

MariaDB [tka]> delete from barang where kode_barang = 'ELK-99';

Query OK, 1 row affected (0.04 sec)

MariaDB [tka]> delete from suplier where kode_suplier = 'EJ-99';

Query OK, 1 row affected (0.08 sec)

MariaDB [tka]> delete from customer where kode_customer = 'J-0099';

Query OK, 1 row affected (0.06 sec)

MariaDB [tka]> delete from pasok where kode_pasok = 'PAS-E099';

Query OK, 1 row affected (0.04 sec)

MariaDB [tka]> delete from pembelian where kode_pembelian = 'BEL-E099';

Query OK, 1 row affected (0.04 sec)

MariaDB [tka]>

MariaDB [tka]> select * from barang where KODE_BARANG ='ELK-99';

Empty set (0.00 sec)

MariaDB [tka]> select * from suplier where KODE_SUPLIER ='EJ-99';

Empty set (0.00 sec)

MariaDB [tka]> select * from customer where KODE_CUSTOMER ='J-0099';

Empty set (0.00 sec)

MariaDB [tka]> select * from pasok where KODE_PASOK ='PAS-E099';

Empty set (0.00 sec)

MariaDB [tka]> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

Empty set (0.00 sec)

MariaDB [tka]>