Search docs.molo17.com

    Microsoft SQL Server CDC logo 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;

    • a user with the db_datareader role and the VIEW CHANGE TRACKING permission on the tables. This is enough to run snapshots and to read changes. Gluesync needs db_owner or the ALTER permission on the database and the tables only to enable Change Tracking for you, see Enable automatic Change Data Capture / Change Tracking activation below. Without it, enable Change Tracking yourself;

    • 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.
    Gluesync enables Change Tracking on the database and on the tables only when Enable automatic Change Data Capture / Change Tracking activation is on. The user then needs the ALTER permission on the database and on the tables. With the property off, Gluesync posts a warning that lists the statements to run by hand.

    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 in hours: (optional, defaults to 24) Number of hours that SQL Server keeps the changes. Gluesync applies it only when it enables Change Tracking on the database. For a database that is already enabled, change CHANGE_RETENTION with ALTER DATABASE. An entity that stays stopped for longer than the retention needs a new snapshot;

    • Enable automatic Change Data Capture / Change Tracking activation: (optional, defaults to false) Lets Gluesync enable Change Tracking on the database and on the tables when you save the connection. It needs the ALTER permission;

    • Transaction isolation level: (optional, defaults to TRANSACTION_READ_COMMITTED) String value that specifies the transaction isolation level to use for the database connection. Supported values are: TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, or TRANSACTION_SERIALIZABLE.

    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,
                "sourceChangeRetention": 24,
                "enableAutomaticChangeCaptureActivation": false
          }
    }'

    Enabling Change Tracking

    Change Tracking must be on for the database and for every table you replicate. Gluesync can enable it for you when Enable automatic Change Data Capture / Change Tracking activation is on. Otherwise, you enable it yourself.

    Enabling Change Tracking on the database

    Run this statement with a user that has the ALTER permission on the database. CHANGE_RETENTION is how long SQL Server keeps the changes. Choose it longer than the longest time an entity can stay stopped. When Gluesync enables Change Tracking, it uses Source change retention in hours (24 by default).

    ALTER DATABASE AdventureWorks2022
    SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 24 HOURS, AUTO_CLEANUP = ON);

    Enabling Change Tracking at table level

    Run this statement with a user that has the ALTER permission on the table.

    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.

    For troubleshooting common issues, including permission errors, missing primary keys, and TLS handshake failures with legacy SQL Server, see MS SQL Server Change Tracking Troubleshooting.