MS SQL Server CDC: Change Tracking with Gluesync

Source data from MS SQL Server (via Change Tracking)

Prerequisites

To have Gluesync MS SQL Server Change Tracking connector working on your database instance you will need to have:

valid user credentials with permissions to read, and write to the target tables and respective database;
each of the tables that you intend to source changes from needs to respect the mandatory requirement to have Primary Keys defined;
the user given to Gluesync needs to have db_owner permissions on the database or enable CHANGE TRACKING on the database;
TLS 1.2 or higher enabled on the MS SQL Server instance for secure connections.

If your table doesn’t have a Primary Key defined and/or you’re using logical / clustered columns to uniquely define data in your MS SQL tables please check Gluesync CDC agent for MS SQL Server’s SQL Agent Transaction logs instead.

In the case of lack of Primary keys neither usage of cluster (compound) keys would work, so the custom definition of clustered indexes feature is not going to work in that specific case due to an MS SQL Server Change Tracking limitation.

If Gluesync is given with db_owner permissions on the database it will be able to automatically enable CHANGE TRACKING on the database if it’s not enabled yet.

Setup via Web UI

Hostname / IP Address: DNS record or IP Address of your server;
Port: Optional, defaults to 1433;
Database name: Name of your database;
Username: Username with read/write access to the tables;
Password: Password belonging to the given username;
Max connections count: Maximum number of connections the pool can instantiate.

Specific configuration

The following example shows how to apply the agent-specific configurations via Rest API.

Enable recursion protection: (optional, defaults to false) Boolean value that avoids recursion when two-way sync capability is configured on the same table;
Temporary table name prefix: (optional) Allow to specify a custom prefix for the temporary table created by Gluesync to improve performance. The typical use case is to allow multiple instances of Gluesync to run from the same source, thus avoiding conflicts at runtime;
Source change retention: (optional, defaults to 5) Number of retention days preserved in the MS SQL Server change tracking system.

Setup via Rest APIs

Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
        "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "host-address",
        "port": 1433,
        "databaseName": "db_name",
        "username": "",
        "password": "",
        "maxConnectionsCount": 100,
        "enableTls": true,
        "certificatePath": "/myPath/cert.pem"
      }
}'

Setup specific configuration

The following example shows how to apply the agent-specific configurations via Rest API.

curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/specific' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
      "configuration": {
            "enableRecursionProtection": false
      }
}'

Enabling Change Tracking

Gluesync can automatically enable Change Tracking on the database if it’s not enabled yet. This is a mandatory requirement for this agent to work.

Enabling Change Tracking on the database

Enabling Change Tracking on the database is a mandatory requirement for this agent to work. If you don’t have the required permissions to enable Change Tracking on the database you will need to enable it manually.

ALTER DATABASE AdventureWorks2022
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

Enabling Change Tracking at table level

Enabling Change Tracking at table level is a mandatory requirement for this agent to work. If you don’t have the required permissions to enable Change Tracking at table level you will need to enable it manually.

ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

Working with Before & After images

Before & after images are a feature that allows Gluesync to track the changes that have occurred in your database and comparing them with their previous values. This enables Gluesync to apply only the changes that have occurred, saving bandwidth and processing time.

This agent does not support Before & After images.

Troubleshooting

Here is the list of common errors.

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'SYS.SCHEMAS'.: Gluesync checks SYS.SCHEMAS table to verify that the GLUESYNC schema is in place, otherwise, he creates it. This error is thrown when insufficient permissions have been given to the user given to Gluesync. Please check permissions and try again.
IllegalArgumentException: Missing primary keys for entities…: At startup Gluesync checks for the required primary keys definition in defined table(s). This is a mandatory requirement of MS SQL Server Change Tracking. If you haven’t got any PK definition on your tables(s) you can’t proceed further using that connector. Please checkout Gluesync CDC connector for MS SQL Server instead.