Search docs.molo17.com

    Microsoft SQL Server CDC logo SQL Server Change Tracking target

    How-to guide

    For a step-by-step walkthrough tested on a running Gluesync, see the how-to guide Prepare a SQL Server target.

    Target MS SQL Server

    Prerequisites

    To have Gluesync working on your MS SQL instance you will need to have:

    • Valid user credentials with permission to read, and write to the target tables and respective database;

    • TLS 1.2 or higher enabled on the MS SQL Server instance for secure connections

    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;

    • Use bulk operations during CDC: (optional, defaults to false) Boolean value that enables the use of bulk operations during CDC tasks;

    • Use bulk operations during Snapshot: (optional, defaults to true) Boolean value that enables the use of bulk operations during snapshot tasks;

    • Set fire triggers on bulk copy: (optional, defaults to true) Boolean value that enables the use of fire triggers on bulk copy;

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

    Bulk copy

    Bulk copy is a feature that allows the agent to copy data in bulk, which can be faster than copying data row by row.

    This agent supports bulk copy for both snapshot and CDC tasks. By default, bulk copy is disabled for both snapshot and CDC tasks, you can enable it by toggling the corresponding "Use bulk operations during" checkbox to true from within the Entity’s play/pause panel before running your task.

    Fire triggers on Bulk Copy

    Fire triggers is a feature that allows the agent to fire triggers on the target table, which can be used to update the target table with the latest data from the source table.

    By default, fire triggers is enabled during Bulk copy tasks, you can disable it by setting the its corresponding configuration to false from within the Agent’s specific configuration during the agent setup wizard.

    Direct copy during snapshot

    When bulk operations are enabled during the snapshot, the agent copies the snapshot rows straight into the target table, whether the Snapshot write method is INSERT or UPSERT. It does not write them to a staging table first and then run a DELETE and an INSERT. Every row is written once, so large snapshots finish faster.

    The agent uses the direct copy for a table when both of these are true:

    • Enable recursion protection is false;

    • The target table has a primary key, or an enabled unique index without a filter, made only of the entity’s key columns. A unique index created with IGNORE_DUP_KEY = ON does not qualify, because SQL Server would silently discard duplicates. Such tables use the staging table.

    To benefit from it, create a primary key or a unique index on the key columns of the target table, and leave recursion protection off. If either condition is not met, the snapshot goes through the staging table as before. The agent logs at INFO level the tables that go through the staging table, because they have no qualifying unique key or because the agent user cannot read the index metadata. It also logs when a duplicate key moves an entity back to the staging table.

    The direct copy keeps identity values, checks constraints and keeps them trusted. It always fires the triggers of the target table, as the INSERT of the staging path did: Set fire triggers on bulk copy applies only to the staging table, so the target triggers cannot be turned off on this path. The copy takes an explicit table lock on the target table for each chunk, where the staging path took it on the staging table, so readers of the target table wait while a chunk is written.

    If the target table already holds a row with the same key, for example when Disable truncate on target table before snapshot inserts is on or when a snapshot resumes, the agent writes that chunk through the staging table. The entity then stays on the staging table for the rest of the snapshot. The result is the same as without the direct copy.

    For a comparison of the key choices, see the how-to guide Choose a key strategy: physical, logical, RRN or computed.

    Updates with bulk operations during CDC

    When Use bulk operations during CDC (useBulkOperationsDuringCDC) is on for an entity, the agent does not apply an update as an UPDATE statement. It loads the change into a staging table, deletes the existing row from the target table and inserts the new version of it. With the option off, updates are applied as UPDATE statements.

    For the target table this has the following effects:

    • UPDATE triggers on the target table do not fire, while DELETE and INSERT triggers do;

    • Foreign keys that reference the table with ON DELETE CASCADE delete their child rows on every update, while ON DELETE SET NULL and ON DELETE SET DEFAULT change the child columns. A foreign key with NO ACTION makes the write fail while child rows exist;

    • If SQL Server Change Data Capture is enabled on the target table, it records a delete and an insert instead of an update;

    • Columns that Gluesync does not write, such as a column with a default value, are set again as for a new row.

    Identity fields handling

    This agent supports identity fields handling for both snapshot and CDC tasks. When encountering an identity field, the agent will force its value to be the same as the source table’s identity field value, resetting the "Force identity" option back to its default value after the commit is executed. Since, as per the Microsoft’s implementation, there can be only one table with a forced identity field insert operation per database, while the agent dynamically adapts to this casualty this operation might drastically decrease performance.

    Troubleshooting

    SQL Server deadlocks during snapshot (SQL error 1205)

    If you see frequent deadlock victims while snapshot is running, use this checklist:

    • Reduce write contention from Gluesync:

      • Set entity snapshotWritingConcurrency to the lowest value allowed by your version.

      • Reduce entity batch sizes (maxWriteBatchSize, maxDeleteBatchSize, maxWriteBulkSize).

    • Minimize trigger side effects during snapshot when possible:

      • Set Set fire triggers on bulk copy to false, or temporarily disable heavy triggers during snapshot windows.

    • Keep transaction isolation at TRANSACTION_READ_COMMITTED unless you have a tested reason to change it.

    • Enable row-versioned reads on the target database (server-side setting, not client-side):

      ALTER DATABASE [YourDb] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
      ALTER DATABASE [YourDb] SET ALLOW_SNAPSHOT_ISOLATION ON;

    READ_COMMITTED_SNAPSHOT is a SQL Server database option. It cannot be enabled from JDBC/Gluesync client settings.

    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
          }
    }'