Static 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.

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 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.

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 CoreHub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

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

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.