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
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
B. Capture before migration
1. Capture source Database (Oracle)
use tgh;
select * from barang;
select * from customer;
select * from suplier;
select * from pasok;
select * from pembelian;
SQL> select * from barang;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG
------ ------------------------- -------------------- -----------
ELK-01 RICE COOKER BUAH 20
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
6 rows selected.
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK
---------- ------ ----- --------- ------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8
PAS-E002 ELK-01 EJ-02 01-JAN-02 5
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E004 ELK-02 EJ-02 02-FEB-02 3
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK
---------- ------ ----- --------- ------------
PAS-E012 ELK-04 EB-02 30-APR-02 9
12 rows selected.
SQL>
SQL> desc barang;
Name Null? Type
----------------------------------------- -------- ----------------------------
KODE_BARANG NOT NULL CHAR(6)
NAMA_BARANG VARCHAR2(25)
SATUAN_BARANG VARCHAR2(20)
STOK_BARANG NUMBER(4)
SQL> desc pasok;
Name Null? Type
----------------------------------------- -------- ----------------------------
KODE_PASOK NOT NULL CHAR(10)
KODE_BARANG NOT NULL CHAR(6)
KODE_SUPLIER NOT NULL CHAR(5)
TANGGAL_PASOK DATE
JUMLAH_PASOK NUMBER(4)
SQL>
use tgh;
select * from barang;
select * from customer;
select * from suplier;
select * from pasok;
select * from pembelian;
C. Migration Oracle to MSSQL
1. open sqldataw SQLLine Data
2. Tab Connection. complete form (as example)
Source Oracle
SID/Service Name = tka
User = aisyah
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 = *tgh.barang*,*tgh.customer*,*tgh.suplier*,*tgh.pasok*,*tgh.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 (47 ms)
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production (Ok, 47 ms)
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, 4.2 sec)
Transferring database (4 concurrent sessions):
TGH.PEMBELIAN - Started (1 of 5, session 1)
TGH.PASOK - Started (2 of 5, session 2)
TGH.PEMBELIAN - Open cursor (15 rows read, 63 ms, session 1)
TGH.PASOK - Open cursor (12 rows read, 31 ms, session 2)
TGH.SUPLIER - Started (3 of 5, session 3)
TGH.SUPLIER - Open cursor (6 rows read, 0 ms, session 3)
TGH.PEMBELIAN - Drop target table (not exists, 78 ms, session 1)
TGH.PASOK - Drop target table (not exists, 78 ms, session 2)
TGH.PASOK - Create target table (16 ms, session 2)
TGH.PEMBELIAN - Create target table (16 ms, session 1)
TGH.SUPLIER - Drop target table (not exists, 78 ms, session 3)
TGH.PASOK - Data transfer complete (session 2)
Rows read: 12 (387 rows/sec)
Rows written: 12 (750 rows/sec, 420 bytes, 25.6 KB/sec)
Transfer time: 156 ms (31 ms read, 16 ms write)
TGH.CUSTOMER - Started (4 of 5, session 2)
TGH.SUPLIER - Create target table (16 ms, session 3)
TGH.PEMBELIAN - Data transfer complete (session 1)
Rows read: 15 (238 rows/sec)
Rows written: 15 (484 rows/sec, 540 bytes, 17.0 KB/sec)
Transfer time: 203 ms (63 ms read, 31 ms write)
TGH.BARANG - Started (5 of 5, session 1)
TGH.BARANG - Open cursor (6 rows read, 0 ms, session 1)
TGH.BARANG - Drop target table (not exists, 16 ms, session 1)
TGH.BARANG - Create target table (15 ms, session 1)
TGH.SUPLIER - Data transfer complete (session 3)
Rows read: 6 (0 rows/sec)
Rows written: 6 (128 rows/sec, 325 bytes, 6.8 KB/sec)
Transfer time: 172 ms (0 ms read, 47 ms write)
TGH.BARANG - Data transfer complete (session 1)
Rows read: 6 (0 rows/sec)
Rows written: 6 (375 rows/sec, 141 bytes, 8.6 KB/sec)
Transfer time: 63 ms (0 ms read, 16 ms write)
TGH.CUSTOMER - Open cursor (6 rows read, 78 ms, session 2)
TGH.PEMBELIAN - Add PRIMARY KEY constraint (32 ms, session 3)
TGH.PASOK - Add PRIMARY KEY constraint (78 ms, session 4)
TGH.BARANG - Add PRIMARY KEY constraint (15 ms, session 3)
TGH.SUPLIER - Add PRIMARY KEY constraint (31 ms, session 1)
TGH.CUSTOMER - Drop target table (not exists, 31 ms, session 2)
TGH.CUSTOMER - Create target table (31 ms, session 2)
TGH.CUSTOMER - Data transfer complete (session 2)
Rows read: 6 (77 rows/sec)
Rows written: 6 (375 rows/sec, 299 bytes, 18.2 KB/sec)
Transfer time: 172 ms (78 ms read, 16 ms write)
TGH.PASOK - Add FOREIGN KEY constraint (172 ms, session 1)
TGH.PEMBELIAN - Add FOREIGN KEY constraint (140 ms, session 2)
TGH.CUSTOMER - Add PRIMARY KEY constraint (0 ms, session 4)
TGH.PEMBELIAN - Add FOREIGN KEY constraint (15 ms, session 1)
TGH.PASOK - Add FOREIGN KEY constraint (16 ms, session 3)
Summary:
Tables: 5 (5 Ok, 0 failed)
Target DDL: 19 (19 Ok, 0 failed)
Rows read: 45
Rows written: 45
Transfer time: 2.3 sec (19 rows/sec, 1.7 KB, 741 bytes/sec)
Logs:
Execution log: sqldata.log
DDL SQL statements: sqldata_ddl.sql
D. Capture after migration
1. Capture Oracle
SQL> select * from barang;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG
------ ------------------------- -------------------- -----------
ELK-01 RICE COOKER BUAH 20
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
6 rows selected.
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK
---------- ------ ----- --------- ------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8
PAS-E002 ELK-01 EJ-02 01-JAN-02 5
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E004 ELK-02 EJ-02 02-FEB-02 3
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK
---------- ------ ----- --------- ------------
PAS-E012 ELK-04 EB-02 30-APR-02 9
12 rows selected.
SQL>
SQL> desc barang;
Name Null? Type
----------------------------------------- -------- ----------------------------
KODE_BARANG NOT NULL CHAR(6)
NAMA_BARANG VARCHAR2(25)
SATUAN_BARANG VARCHAR2(20)
STOK_BARANG NUMBER(4)
SQL> desc pasok;
Name Null? Type
----------------------------------------- -------- ----------------------------
KODE_PASOK NOT NULL CHAR(10)
KODE_BARANG NOT NULL CHAR(6)
KODE_SUPLIER NOT NULL CHAR(5)
TANGGAL_PASOK DATE
JUMLAH_PASOK NUMBER(4)
SQL>
E. Test insert result migration Oracle 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
No comments:
Post a Comment