Static Target MS SQL Server

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

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.

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 enabled for snapshot tasks and disabled for CDC tasks, you can enable it for CDC tasks by setting the its corresponding configuration to true from within the Agent’s specific configuration during the agent setup wizard.

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.

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