✅ Setup and start SQL Server database (Source)
✅ Setup and start Redis Enterprise database (Target)
✅ Setup and start Redis Connect
✅ Perform Initial load and CDC with Redis Connect
Docker compatible *nix OS and Docker installed.
Please have 8 vCPU*, 8GB RAM and 50GB storage for this demo to function properly. Adjust the resources based on your requirements. For HA, at least have 2 Redis Connect Connector instances deployed on separate hosts.
Execute the following commands (copy & paste) to download and setup Redis Connect and demo scripts.
i.e.
wget -c https://github.com/redis-field-engineering/redis-connect-dist/archive/main.zip && \
mkdir -p redis-connect/demo && \
unzip main.zip "redis-connect-dist-main/examples/mssql/*" -d redis-connect && \
cp -R redis-connect/redis-connect-dist-main/examples/mssql/demo/* redis-connect/demo && \
rm -rf main.zip redis-connect/redis-connect-dist-main && \
cd redis-connect && \
chmod a+x demo/*.sh && \
cd demoExpected output:
demo$ ls
README.md config_bdb.json emp_1000.csv insert_mssql.sh sample_sqls.sql setup_re_crdb.sh target_crdb.json
bcp_emp_1000.sh delete.sql extlib mssql_cdc.sql setup_mssql.sh split_network.sh update.sql
config delete_mssql.sh insert.sql restore_network.sh setup_re.sh stop_re_crdb.sh update_mssql.sh
Execute setup_mssql.sh
demo$ ./setup_mssql.sh 2019-latest 1433Validate SQL Server database is running as expected:
demo$ sudo docker ps -a | grep mssql
1a08b60611fd mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 2 weeks ago Up 2 weeks 0.0.0.0:1433->1433/tcp mssql-2019-latest-virag-cdc-1433
demo$ sudo docker exec -it $(docker ps -a --format "table {{.Names}}" | grep mssql) /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa -P Redis@123 -y80 -Y 40 -Q 'use RedisConnect;exec sys.sp_cdc_help_change_data_capture;'
Changed database context to 'RedisConnect'.
source_schema source_table capture_instance object_id source_object_id start_lsn end_lsn supports_net_changes has_drop_pending role_name index_name filegroup_name create_date index_column_list captured_column_list
---------------------------------------- ---------------------------------------- ---------------------------------------- ----------- ---------------- ---------------------- ---------------------- -------------------- ---------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
dbo emp cdcauditing_emp 965578478 933578364 0x000000C0000060900001 NULL 1 NULL cdc_reader PK__emp__AF4C318A8A59B13C NULL 2022-04-23 07:15:26.660 [empno] [empno], [fname], [lname], [job], [mgr], [hiredate], [sal], [comm], [dept]
Execute setup_re.sh
demo$ ./setup_re.shNOTE
The above script will create a 1-node Redis Enterprise cluster in a docker container, Create a target database with RediSearch module, Create a job management and metrics database with RedisTimeSeries module, Create a RediSearch index for emp Hash, Start a docker instance of grafana with Redis Data Source and Start an instance of RedisInsight.
Review options by running Redis Connect docker container
demo$ docker run \
-it --rm --privileged=true \
--name redis-connect-$(hostname) \
-v $(pwd)/config:/opt/redislabs/redis-connect/config \
-v $(pwd)/config/samples/credentials:/opt/redislabs/redis-connect/config/samples/credentials \
--net host \
redislabs/redis-connectExpected output:
-------------------------------
Redis Connect startup script.
*******************************
Please ensure that these environment variables are correctly mapped before executing start and cli options. They can also be found in /opt/redislabs/redis-connect/bin/redisconnect.conf
Example environment variables and volume mapping for docker based deployments
-e REDISCONNECT_JOB_MANAGER_CONFIG_PATH=/opt/redislabs/redis-connect/config/jobmanager.properties [OPTIONAL]
-e REDISCONNECT_LOGBACK_CONFIG=/opt/redislabs/redis-connect/config/logback.xml [OPTIONAL]
-e REDISCONNECT_JAVA_OPTIONS=-Xms1g -Xmx2g [OPTIONAL]
-e REDISCONNECT_EXTLIB_DIR=/opt/redislabs/redis-connect/extlib [OPTIONAL]
-v <HOST_PATH_TO_JOB_MANAGER_PROPERTIES>:/opt/redislabs/redis-connect/config
-v <HOST_PATH_TO_CREDENTIALS>:/opt/redislabs/redis-connect/config/samples/credentials
-v <HOST_PATH_TO_EXTLIB>:/opt/redislabs/redis-connect/extlib [OPTIONAL]
-p 8282:8282
Usage: [-h|cli|start]
options:
-h: Print this help message and exit.
-v: Print version.
cli: init Redis Connect CLI
start: init Redis Connect Instance (Cluster Member)
-------------------------------Start Redis Connect Instance
docker run \
-it --rm --privileged=true \
--name redis-connect-$(hostname) \
-v $(pwd)/config:/opt/redislabs/redis-connect/config \
-v $(pwd)/extlib:/opt/redislabs/redis-connect/extlib \
--net host \
redislabs/redis-connect startExpected output:
-------------------------------
Starting redis-connect v0.9.0.4 instance using Java 11.0.15 on virag-cdc started by root in /opt/redislabs/redis-connect/bin
Loading redis-connect instance configurations from /opt/redislabs/redis-connect/config/jobmanager.properties
Instance classpath /opt/redislabs/redis-connect/lib/*:/opt/redislabs/redis-connect/extlib/*
06:42:22.996 [main] INFO redis-connect-manager - ----------------------------------------------------------------------------------------------------------------------------
/####### /## /## /###### /##
| ##__ ## | ## |__/ /##__ ## | ##
| ## \ ## /###### /####### /## /####### | ## \__/ /###### /####### /####### /###### /####### /######
| #######/ /##__ ## /##__ ##| ## /##_____/ | ## /##__ ##| ##__ ##| ##__ ## /##__ ## /##_____/|_ ##_/
| ##__ ##| ########| ## | ##| ##| ###### | ## | ## \ ##| ## \ ##| ## \ ##| ########| ## | ##
| ## \ ##| ##_____/| ## | ##| ## \____ ## | ## ##| ## | ##| ## | ##| ## | ##| ##_____/| ## | ## /##
| ## | ##| #######| #######| ## /#######/ | ######/| ######/| ## | ##| ## | ##| #######| ####### | ####/
|__/ |__/ \_______/ \_______/|__/|_______/ \______/ \______/ |__/ |__/|__/ |__/ \_______/ \_______/ \___/
Powered by Redis Enterprise
06:42:28.003 [main] INFO redis-connect-manager - ----------------------------------------------------------------------------------------------------------------------------
06:42:29.843 [main] INFO redis-connect-manager - Instance: 29@virag-cdc successfully established Redis connection for JobManager - JobManager
06:42:29.866 [main] INFO redis-connect-manager - Instance: 29@virag-cdc successfully established Redis connection for JobManager - JobReaper
06:42:29.890 [main] INFO redis-connect-manager - Instance: 29@virag-cdc successfully established Redis connection for JobManager - JobClaimer
06:42:29.912 [main] INFO redis-connect-manager - Instance: 29@virag-cdc successfully established Redis connection for JobManager - HeartbeatManager
06:42:29.934 [main] INFO redis-connect-manager - Instance: 29@virag-cdc successfully established Redis connection for JobManager - MetricsReporter
06:42:30.037 [main] INFO redis-connect-manager - Instance: 29@virag-cdc skipped creating Job Claim Assignment Consumer Group since it already exists
06:42:30.042 [main] INFO redis-connect-manager - Instance: 29@virag-cdc successfully started JobManager service
06:42:30.044 [main] INFO redis-connect-manager - Instance: 29@virag-cdc successfully started JobReaper service
06:42:30.045 [main] INFO redis-connect-manager - Instance: 29@virag-cdc Metrics are not enabled so MetricsReporter threadpool will not be instantiated
06:42:30.047 [main] INFO redis-connect-manager - Instance: 29@virag-cdc successfully started JobClaimer service
06:42:36.006 [main] INFO redis-connect-manager - Started Redis Connect REST API listening on ["http-nio-8282"]
06:42:36.006 [main] INFO redis-connect-manager - ----------------------------------------------------------------------------------------------------------------------------
06:42:36.006 [main] INFO redis-connect-manager -
06:42:36.006 [main] INFO redis-connect-manager - Started Redis Connect Instance
06:42:36.006 [main] INFO redis-connect-manager -
06:42:36.006 [main] INFO redis-connect-manager - ----------------------------------------------------------------------------------------------------------------------------
06:42:40.044 [JobManagerThreadpool-1] INFO redis-connect-manager - Instance: 29@virag-cdc was successfully elected Redis Connect cluster leaderOpen browser to access Swagger UI - http://localhost:8282/swagger-ui/index.html
For quick start, use 'cdc_job' as jobName
Create Job Configuration - /connect/api/vi/job/config/{jobName}
For quick start, use the sample cdc-job.json configuration: SQL Server
Or Use curl to create the cdc-job configuration
demo$ curl -v -X POST "http://localhost:8282/connect/api/v1/job/config/cdc-job" -H "accept: */*" -H "Content-Type: multipart/form-data" -F "file=@config/samples/payloads/cdc-job.json;type=application/json"
INSERT few records into SQL Server table (source)
You can also use redis-connect-crud-loader to insert load large amount of data using a csv or sql file.
demo$ ./insert_mssql.shStart Job - /connect/api/vi/job/transition/start/{jobName}/{jobType}
Use 'load' as jobType
Or Use curl to start the initial load for cdc-job
demo$ curl -X POST "http://localhost:8282/connect/api/v1/job/transition/start/cdc-job/load" -H "accept: */*"
Query for the above inserted record in Redis (target)
demo$ sudo docker exec -it re-node1 bash -c 'redis-cli -p 12000 ft.search idx_emp "@empno:[1 11]"'
1) (integer) 11
2) "emp:1"
3) 1) "fname"
2) "Basanth"
3) "lname"
4) "Gowda"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "1"
11) "dept"
12) "1"
13) "job"
14) "FOUNDER"
15) "hiredate"
16) "2018-08-09 00:00:00.01"
17) "sal"
18) "200000.0"
4) "emp:11"
5) 1) "fname"
2) "Christian"
3) "lname"
4) "Mague"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "11"
11) "dept"
12) "1"
13) "job"
14) "PFE"
15) "hiredate"
16) "2019-07-09 00:00:00.11"
17) "sal"
18) "200000.0"
6) "emp:2"
7) 1) "fname"
2) "Virag"
3) "lname"
4) "Tripathi"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "2"
11) "dept"
12) "1"
13) "job"
14) "PFE"
15) "hiredate"
16) "2018-08-06 00:00:00.02"
17) "sal"
18) "2000.0"
8) "emp:3"
9) 1) "fname"
2) "Drake"
3) "lname"
4) "Albee"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "3"
11) "dept"
12) "1"
13) "job"
14) "RSM"
15) "hiredate"
16) "2017-08-09 00:00:00.03"
17) "sal"
18) "5000.0"
10) "emp:4"
11) 1) "fname"
2) "Nick"
3) "lname"
4) "Doyle"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "4"
11) "dept"
12) "1"
13) "job"
14) "DIR"
15) "hiredate"
16) "2019-07-09 00:00:00.04"
17) "sal"
18) "10000.0"
12) "emp:5"
13) 1) "fname"
2) "Allen"
3) "lname"
4) "Terleto"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "5"
11) "dept"
12) "1"
13) "job"
14) "FieldCTO"
15) "hiredate"
16) "2017-06-09 00:00:00.05"
17) "sal"
18) "500000.0"
14) "emp:6"
15) 1) "fname"
2) "Marco"
3) "lname"
4) "Shkedi"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "6"
11) "dept"
12) "1"
13) "job"
14) "SA"
15) "hiredate"
16) "2018-06-09 00:00:00.06"
17) "sal"
18) "2000.0"
16) "emp:7"
17) 1) "fname"
2) "Brad"
3) "lname"
4) "Barnes"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "7"
11) "dept"
12) "1"
13) "job"
14) "SA"
15) "hiredate"
16) "2018-07-09 00:00:00.07"
17) "sal"
18) "2000.0"
18) "emp:8"
19) 1) "fname"
2) "Quinton"
3) "lname"
4) "Gingras"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "8"
11) "dept"
12) "1"
13) "job"
14) "SDR"
15) "hiredate"
16) "2019-07-09 00:00:00.08"
17) "sal"
18) "200000.0"
20) "emp:9"
21) 1) "fname"
2) "Yuval"
3) "lname"
4) "Mankerious"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "9"
11) "dept"
12) "1"
13) "job"
14) "SA"
15) "hiredate"
16) "2019-07-09 00:00:00.09"
17) "sal"
18) "200000.0"Start Job - /connect/api/vi/job/transition/start/{jobName}/{jobType}
Use 'stream' as jobType
Or Use curl to start the stream for cdc-job
demo$ curl -X POST "http://localhost:8282/connect/api/v1/job/transition/start/cdc-job/stream" -H "accept: */*"
Confirm Job Claim - /connect/api/vi/jobs/claim/{jobStatus}
For quick start, use 'all' as jobStatus
Or Use curl to query the cdc-job status
demo$ curl -X GET "http://localhost:8282/connect/api/v1/cluster/jobs/claim/all" -H "accept: */*"
Expected output: [{"jobId":"{connect}:job:cdc-job","jobName":"cdc-job","jobStatus":"CLAIMED","jobOwner":"30@virag-cdc","jobType":"STREAM"}]
INSERT a record into SQL Server table (source)
demo$ sudo docker exec -it $(docker ps -a --format "table {{.Names}}" | grep mssql) bash -c '/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "Redis@123" -d RedisConnect'
1> insert into dbo.emp values(1002, 'Virag', 'Tripathi', 'SA', 1, '2018-08-06 00:00:00.000', '2000', '10', 1)
2> go
(1 rows affected)
1> quitQuery for the above inserted record in Redis (target)
demo$ sudo docker exec -it re-node1 bash -c 'redis-cli -p 12000 ft.search idx_emp "@empno:[1002 1002]"'
1) (integer) 1
2) "emp:1002"
3) 1) "fname"
2) "Virag"
3) "lname"
4) "Tripathi"
5) "comm"
6) "10.0"
7) "mgr"
8) "1"
9) "empno"
10) "1002"
11) "dept"
12) "1"
13) "job"
14) "SA"
15) "hiredate"
16) "2018-08-06 00:00:00.00"
17) "sal"
18) "2000.0"