Skip to content

Instantly share code, notes, and snippets.

@MakiseKurisu
Created December 19, 2018 17:43
Show Gist options
  • Save MakiseKurisu/cc84bd806408761308e928c70733a911 to your computer and use it in GitHub Desktop.
Save MakiseKurisu/cc84bd806408761308e928c70733a911 to your computer and use it in GitHub Desktop.
Setup SQL Server to MariaDB replication with SymmetricDS
#!/bin/bash
# 1. Prepare Environment
apt update; apt upgrade -y
apt install unzip default-jre -y
wget https://netcologne.dl.sourceforge.net/project/symmetricds/symmetricds/symmetricds-3.9/symmetric-server-3.9.14.zip
cd /opt/
# 2. Install and Set Up
unzip ~/symmetric-server-3.9.14.zip
mv symmetric-server-3.9.14/ symmetric-server/
cd symmetric-server/
echo "3.9.14" > version.txt
SOURCE=sqlserver
SOURCE_IP=192.168.0.1
SOURCE_DB_NAME=db
SOURCE_USER=user
SOURCE_PASSWORD=pw
DEST=mariadb
DEST_IP=192.168.0.2
DEST_DB_NAME=dbo
DEST_USER=user
DEST_PASSWORD=pw
echo "engine.name=$SOURCE" >> engines/$SOURCE.properties
echo "group.id=src" >> engines/$SOURCE.properties
echo "external.id=000" >> engines/$SOURCE.properties
echo "sync.url=http://localhost:31415/sync/$SOURCE" >> engines/$SOURCE.properties
echo "registration.url=" >> engines/$SOURCE.properties
echo "db.driver=net.sourceforge.jtds.jdbc.Driver" >> engines/$SOURCE.properties
echo "db.url=jdbc:jtds:sqlserver://$SOURCE_IP:1433/$SOURCE_DB_NAME;sendStringParametersAsUnicode=false;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880" >> engines/$SOURCE.properties
echo "db.user=$SOURCE_USER" >> engines/$SOURCE.properties
echo "db.password=$SOURCE_PASSWORD" >> engines/$SOURCE.properties
echo "auto.reload=true" >> engines/$SOURCE.properties
echo "initial.load.create.first=true" >> engines/$SOURCE.properties
echo "engine.name=$DEST" >> engines/$DEST.properties
echo "group.id=dest" >> engines/$DEST.properties
echo "external.id=001" >> engines/$DEST.properties
echo "sync.url=http://localhost:31415/sync/$DEST" >> engines/$DEST.properties
echo "registration.url=http://localhost:31415/sync/$SOURCE" >> engines/$DEST.properties
echo "db.driver=com.mysql.jdbc.Driver" >> engines/$DEST.properties
echo "db.url=jdbc:mysql://$DEST_IP/$DEST_DB_NAME?useSSL=false&tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull" >> engines/$DEST.properties
#echo "db.driver=net.sourceforge.jtds.jdbc.Driver" >> engines/$DEST.properties
#echo "db.url=jdbc:jtds:sqlserver://$DEST_IP:1433/$DEST_DB_NAME;sendStringParametersAsUnicode=false;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880" >> engines/$DEST.properties
echo "db.user=$DEST_USER" >> engines/$DEST.properties
echo "db.password=$DEST_PASSWORD" >> engines/$DEST.properties
bin/sym_service install
bin/sym_service start
bin/symadmin open-registration --engine $SOURCE dest 001
bin/dbsql --engine $SOURCE <<- END
insert into sym_node_group_link
(source_node_group_id, target_node_group_id, data_event_action, is_reversible)
values
('src', 'dest', 'P', 1);
insert into sym_router
(router_id, source_node_group_id, target_node_group_id, create_time, last_update_time)
values
('Default','src', 'dest', current_timestamp, current_timestamp);
insert into sym_trigger
(trigger_id, source_schema_name, source_table_name, channel_id, create_time, last_update_time)
values
('Default', 'dbo', '*,!sysdiagrams', 'default', current_timestamp, current_timestamp);
insert into sym_trigger_router
(trigger_id, router_id, create_time, last_update_time)
values
('Default', 'Default', current_timestamp, current_timestamp);
exit
END
less logs/symmetric.log
watch -n 0 tail -n 50 logs/symmetric.log
# 3. Uninstall
bin/symadmin --engine $DEST uninstall
bin/symadmin --engine $SOURCE uninstall
bin/sym_service stop
bin/sym_service uninstall
cd ..
rm -rf symmetric-server/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment