SQL Server CDC 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, orTRANSACTION_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 = ONdoes 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 ( For the target table this has the following effects:
|
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
snapshotWritingConcurrencyto 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_COMMITTEDunless 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;
|
|
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
}
}'