SymmetricDS
SymmetricDS adalah tools Open Source dalam hal Distributed database, replikasi dan/atau transformasi data.
Tools ini juga cross platform baik database maupun operatin System nya, jadi bisa di aplikasikan di Windows
maupun linux dan juga hampir semua mainstream database sudah di support.
OK tanpa berlama-lama lagi, ikuti saja langkah-langkahnya. dalam tutorial di bawah ini saya menggunakan
OS linux dan database oracle dengan asumsi 1 server sebagai HQ dan 1 lagi sebagai server store
* Download SymmetricDS dari Webnya
http://www.symmetricds.org/download
pada saat tutorial ini di buat symmetrcds sudah versi 3.7.33
* buat folder /opt/symds di kedua node (server HQ dan server Store)
* extract source symmetric DS kemudian move ke folder yg telah kita sediakan di kedua node (server HQ dan server Store)
* copy file /opt/symds/samples/corp-000.properties ke /opt/symds/engines/hq-0000.properties
di node HQ
* edit hq-0000.properties sesuaikan dengan settinga database kita
engine.name=hq-0000
# The class name for the JDBC Driver
db.driver=oracle.jdbc.driver.OracleDriver
# The JDBC URL used to connect to the database
db.url=jdbc:oracle:thin:@192.168.9.6:1521:orcl
# The user to login as who can create and update tables
db.user=symds
# The password for the user to login as
db.password=symds
registration.url=
sync.url=http://192.168.9.6:31415/sync/hq-0000
# Do not change these for running the demo
group.id=hq
external.id=0000
# Don't muddy the waters with purge logging
job.purge.period.time.ms=7200000
# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=5000
# This is how often the push job will be run.
job.push.period.time.ms=10000
# This is how often the pull job will be run.
job.pull.period.time.ms=10000
# Kick off initial load
initial.load.create.first=true
* copy file /opt/symds/samples/store-001.properties ke /opt/symds/engines/store-0001.properties
di node Store
* edit store-0001.properties sesuaikan dengan settinga database kita di server store
engine.name=store-0001
# The class name for the JDBC Driver
db.driver=oracle.jdbc.driver.OracleDriver
# The JDBC URL used to connect to the database
db.url=jdbc:oracle:thin:@192.168.9.29:1521:orcl
# The user to login as who can create and update tables
db.user=symds
# The password for the user to login as
db.password=symds
# The HTTP URL of the root node to contact for registration
registration.url=http://192.168.9.6:31415/sync/hq-0000
# Do not change these for running the demo
group.id=store
external.id=0001
# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=5000
# This is how often the push job will be run.
job.push.period.time.ms=10000
# This is how often the pull job will be run.
job.pull.period.time.ms=10000
* Siapkan database HQ di server, dan sesuaikan dengan setting di edit tadi, nama databasenya orcl
di sini kita cretae schema dengan nama symds dan password symds, jika perlu kita juga boleh
persiapkan table table yg akan kita gunakan nanti.
* Siapkan database Store di server, dan sesuaikan dengan setting di edit tadi, nama databasenya orcl
di sini kita cretae schema dengan nama symds dan password symds, jika perlu kita juga boleh
persiapkan table table yg akan kita gunakan nanti.
* jika kita ingin mencoba dengan sample yg di sediakan dari symmetricds kita bisa import ke system kita
../bin/dbimport --engine corp-000 --format XML --alter-case create_sample.xml
perintah di atas jika kita menggunakan sample data dari symds dan engine kita bernama corp-000
tapi karena kita pakai sample data sendiri bisa kita skip
* jalankan script import SymmedtricDS Tables
../bin/symadmin --engine hq-0000 create-sym-tables
* jalankam script import sample data HQ
../bin/dbimport --engine hq-0000 insert_config.sql
file insert_config.sql
delete from sym_trigger_router;
delete from sym_trigger;
delete from sym_router;
delete from sym_channel where channel_id in ('sale_transaction', 'item');
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_host;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description,last_update_time,create_time)
values('sales', 1, 100000, 1, 'sales transactional data from store',current_timestamp,current_timestamp);
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description,last_update_time,create_time)
values('master', 1, 100000, 1, 'master data to store',current_timestamp,current_timestamp);
insert into sym_node_group (node_group_id,description,last_update_time,create_time)
values ('hq','Server Group on HQ',current_timestamp,current_timestamp);
insert into sym_node_group (node_group_id,description,last_update_time,create_time)
values ('store','Node Group on Store',current_timestamp,current_timestamp);
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
values ('hq', 'store', 'W');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
values ('store', 'hq', 'P');
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('m_price','M_PRICE','master',current_timestamp,current_timestamp);
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('m_item','M_ITEM','master',current_timestamp,current_timestamp);
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('m_outlet_price','M_OUTLET_PRICE','master',current_timestamp,current_timestamp);
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('t_pos_bill','T_POS_BILL','sales',current_timestamp,current_timestamp);
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('t_pos_bill_item','T_POS_BILL_ITEM','sales',current_timestamp,current_timestamp);
insert into sym_router
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('hq_2_store', 'hq', 'store', 'default',current_timestamp, current_timestamp);
insert into sym_router
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('store_2_hq', 'store', 'hq', 'default',current_timestamp, current_timestamp);
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('m_item','hq_2_store', 100, current_timestamp, current_timestamp);
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('m_price','hq_2_store', 100, current_timestamp, current_timestamp);
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('m_outlet_price','hq_2_store', 100, current_timestamp, current_timestamp);
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('t_pos_bill','store_2_hq', 200, current_timestamp, current_timestamp);
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('t_pos_bill_item','store_2_hq', 200, current_timestamp, current_timestamp);
insert into sym_node
(node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,
database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count,
batch_in_error_count,created_at_node_id)
values ('0000','hq','0000',1,null,null,null,null,null,current_timestamp,null,0,0,'0000');
insert into sym_node
(node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,
database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count,
batch_in_error_count,created_at_node_id)
values ('0001','store','0001',1,null,null,null,null,null,current_timestamp,null,0,0,'0000');
insert into sym_node_security (node_id,node_password,registration_enabled,registration_time,
initial_load_enabled,initial_load_time,created_at_node_id)
values ('0000','5d1c92bbacbe2edb9e1ca5dbb0e481',0,current_timestamp,0,current_timestamp,'0000');
insert into sym_node_identity values ('0000');
jika ingin menggunakan sample bawaan dari symmetricds
../bin/dbimport --engine corp-000 insert_sample.sql
* jika kita ingin mencoba dengan sample yg di sediakan dari symmetricds kita bisa import ke system kita
../bin/dbimport --engine store-001 --format XML --alter-case create_sample.xml
perintah di atas jika kita menggunakan sample data dari symds dan engine kita bernama corp-000
tapi karena kita pakai sample data sendiri bisa kita skip
* jalankan HQ service SymmetricDS
../bin/sym --engine hq-0000 --port 31415
* jalankan store 0001 service
../bin/sym --engine store-0001 --port 31415
* baca dan amati log di screen daemon
* Simulasikan registrasi node store-0001 di HQ
../bin/symadmin --engine hq-0000 open-registration store 0001
* simulasikan penulisan data di server Corp (pull). ini contoh data jika menggunakan sample bawaan symmetricds
sedangkan sample data untuk table yg penulis buat (maaf bukan untuk konsumsi public)
insert into "item" ("item_id", "name") values (110000055, 'Soft Drink');
insert into "item_selling_price" ("item_id", "store_id", "price") values (110000055, '001', 0.65);
insert into "item_selling_price" ("item_id", "store_id", "price") values (110000055, '002', 1.00);
amati apakah ada perubahan baik di log maupun di database client
* simulasikan penulisan data di store 001 (push). ini contoh data jika menggunakan sample bawaan symmetricds
sedangkan sample data untuk table yg penulis buat (maaf bukan untuk konsumsi public)
insert into "sale_transaction" ("tran_id", "store_id", "workstation", "day", "seq")
values (1000, '001', '3', '2007-11-01', 100);
insert into "sale_return_line_item" ("tran_id", "item_id", "price", "quantity")
values (1000, 110000055, 0.65, 1);
* Ada 3 table yg penting untuk memahami cara kerja symmetricds. di sisi pengirim perhatikan table berikut
* sym_data
table yg akan merekam semua perubahan data
select * from sym_data order by data_id desc;
* sym_data_event
perubahn di eksekusi oleh bath bath yg ada pada table ini
select * from sym_outgoing_batch where batch_id = x; -- ganti x dengan angka
* sym_outgoing_batch
pastikan perubahan data terekam,terkirim dan terkonfirmasi di server
gunakan batch id di table sebelumnya
select * from sym_outgoing_batch where batch_id = ?; -- ganti x dengan angka
batch awalnya berstatus "NE" kalau belum terkirim. ketika node penerima mengkonfirmasi pengiriman,
statusnya berganti "OK" atau "ER" kalau Error
* Disisi penerima, cek table ini
* sym_incoming_batch
periksa penerimaan data di tabel ini
select * from sym_incoming_batch where batch_id = ?; -- ganti x dengan angka
* Sedangkan untuk membuat suatu trigger berikut table yg harus di configurasi
sym_channel
sym_node
sym_node_group
sym_node_group_link
sym_router
sym_trigger
sym_trigger_router
* Encrypted Passwords
properti db.users dan db.password bisa dalam bentuk encrypted text, ini untuk meningkatkan keamanan system.
text yang di awali dengan enc: menandakan bahwa itu ter encrypt. untuk meng encrypt text ikuti command berikut
symadmin -e {engine name} encrypt-text text-to-encrypt
atau
symadmin -p {properties file} encrypt-text text-to-encrypt
Sementara sekian dulu yah. kritik dan saran sangant saya harapkan
