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_datareaderrole and theVIEW CHANGE TRACKINGpermission on the tables. This is enough to run snapshots and to read changes. Gluesync needsdb_owneror theALTERpermission 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, changeCHANGE_RETENTIONwithALTER 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 theALTERpermission; -
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.
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);
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.