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.

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

Target MS SQL Server