Search docs.molo17.com
    Configuration guide Connectors

    Set up a SQL Server Change Tracking source

    The SQL Server Change Tracking agent reads the changes that SQL Server itself records for each table. It needs no SQL Server Agent job and no transaction log reader, but it needs a primary key on every table and it keeps the history for a limited time only. This guide enables Change Tracking on the database and on the tables, sets the retention, creates a user with only the permissions the agent uses, runs a snapshot and live changes into a MySQL target, and shows what happens when a permission is missing, a table has no primary key, or an entity is stopped for longer than the retention.

    In this guide

    You will decide between Change Tracking and Change Data Capture (CDC). You will turn on Change Tracking on the database with a retention that fits your maintenance windows, and on each table. You will create a login that can read the tables and the changes, create the source connection in the Control Plane, add an entity, and start it with a full copy followed by live changes. You will see that Change Tracking delivers the net change of a row, pause an entity and resume it, cause an expired position on purpose and recover from it, and let Gluesync enable Change Tracking by itself.

    Change Tracking or CDC

    Gluesync has two agents for SQL Server sources. Pick one before you prepare the database.

    Change Tracking agent CDC agent

    Needs a primary key on the table

    Yes. SQL Server refuses to enable Change Tracking without one, and a logical key in Gluesync does not change that

    No. Tables without a primary key work, see MS SQL Server CDC reference

    What you get for a row

    The net change since the last read: one insert, update or delete per key, with the current values of the row. An insert followed by four updates arrives as one insert

    Every change that the capture job found in the transaction log, and the agent supports before and after images

    Extra services

    None. SQL Server records the changes inside the transaction

    SQL Server Agent must run its capture job

    Minimum version

    SQL Server 2016, all editions (the Control Plane tile says so)

    SQL Server 2008 Enterprise and above (the Control Plane tile says so)

    History kept

    For the retention you set (default 24 hours when Gluesync enables it). An entity that is stopped for longer needs a new snapshot

    Until the cleanup job of CDC removes it

    Permissions

    ALTER to switch it on, VIEW CHANGE TRACKING to read, no db_owner

    db_owner on the database to run the CDC functions

    Choose Change Tracking when every table has a primary key, you want the lightest load on the source, and a net change per row is enough. Choose CDC when you have tables without a primary key, or when you need every intermediate change or the old values of a row.

    Quick information

    Goal

    Replicate a SQL Server table with a snapshot followed by change data capture (CDC) through Change Tracking, and keep the history long enough for your stops

    Audience

    Data engineers and database administrators who own a SQL Server database

    Level

    Intermediate

    Time required

    About 45 minutes

    Area

    SQL Server → Change Tracking; Control Plane → Pipelines → MS SQL Server Change tracking agent → Base settings and Advanced settings; entity → start dialog

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have a SQL Server 2016 or later database and you can run T-SQL on it as a sysadmin or as the database owner. This guide was tested with SQL Server 2022;

    2. Core Hub can open a connection to the server, on the port you use or with the dynamic port discovery that the Port field offers when you enter 0;

    3. You have a target to write to. This guide uses a MySQL database and its connection details: any target works, and the steps for the source are the same;

    4. You can sign in to the Control Plane with a role that can create pipelines;

    5. You have a source table with a primary key. This guide uses dbo.orders with 20,000 rows (id int primary key, customer varchar(50), status varchar(20), amount decimal(10,2), created_at datetime2(3)), a second table dbo.customers with a primary key, and a table dbo.audit_log without one.

    The messages quoted in this guide were captured in the Control Plane and in the Core Hub log with SQL Server 2022 (16.0.4305) as the source and MySQL 8.0 as the target. The options of the agent are listed in the MS SQL Server Change Tracking reference.

    Guide steps

    Check the version and the primary keys

    SQL

    Connect to the source database as a sysadmin and read the version. Then list the tables that have no primary key. Change Tracking cannot be enabled on them:

    SELECT @@VERSION;

SELECT s.name AS schema_name, t.name AS table_name
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE NOT EXISTS (
    SELECT 1 FROM sys.indexes i
    WHERE i.object_id = t.object_id AND i.is_primary_key = 1)
  AND t.name NOT LIKE 'MSchange_tracking%'
ORDER BY 1, 2;
    Expected result

    The version is 2016 or later. The second query returns the tables you cannot replicate with this agent. In the lab it returned dbo.audit_log. Add a primary key to each table that you need, or use the CDC agent for those tables. SQL Server creates MSchange_tracking_history itself once Change Tracking is on, so the query ignores it.

    A unique index is not enough: SQL Server asks for a primary key. See Choose keys for tables without a primary key for the keys of tables that the CDC agent reads.
    Turn on Change Tracking on the database and set the retention

    SQL

    Enable Change Tracking on the database. CHANGE_RETENTION is how long SQL Server keeps the changes. Choose it from the longest time an entity can be stopped, with a margin, because an entity that restarts after the retention cannot continue, see step 10:

    ALTER DATABASE [demo]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

    Read the setting back:

    SELECT d.name, t.retention_period, t.retention_period_units_desc, t.is_auto_cleanup_on
FROM sys.change_tracking_databases t
JOIN sys.databases d ON d.database_id = t.database_id;
    Expected result

    The database is listed with 7 DAYS and is_auto_cleanup_on set to 1. To change the retention later, run ALTER DATABASE [demo] SET CHANGE_TRACKING (CHANGE_RETENTION = 14 DAYS);: the change applies at once and does not restart anything.

    Keep AUTO_CLEANUP = ON. With it off, SQL Server does not remove the old changes, and the tracking tables grow without a limit. A long retention costs space in the tracking tables, so do not set it far above what you need.
    Turn on Change Tracking on the tables

    SQL

    Enable it for each table that you replicate. This is the statement that Gluesync runs when it enables the table itself:

    ALTER TABLE [dbo].[orders]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

ALTER TABLE [dbo].[customers]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

    Read the state of the tables and the current version:

    SELECT t.name, ct.begin_version, ct.min_valid_version, ct.is_track_columns_updated_on
FROM sys.change_tracking_tables ct
JOIN sys.tables t ON t.object_id = ct.object_id;

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version,
       CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.orders')) AS min_valid_version;
    Expected result

    Both tables are listed with is_track_columns_updated_on set to 1. The minimum valid version of a table is the version at which you enabled it on that table: no entity can read changes from before that number. Remember this number, it comes back in step 10.

    On a table without a primary key, SQL Server stops the statement:

    Msg 4997, Level 16, State 1
Cannot enable change tracking on table 'audit_log'. Change tracking requires a primary key on the table. Create a primary key on the table before enabling change tracking.
    Create the Gluesync login

    SQL

    Gluesync does not need db_owner. For a source with Change Tracking already enabled, it needs to sign in, to select from the tables and to read the changes through CHANGETABLE, which needs VIEW CHANGE TRACKING. Replace the password:

    USE master;
CREATE LOGIN gluesync WITH PASSWORD = '<a strong password>', CHECK_POLICY = OFF;
GO
USE demo;
CREATE USER gluesync FOR LOGIN gluesync;
ALTER ROLE db_datareader ADD MEMBER gluesync;
GRANT VIEW CHANGE TRACKING ON SCHEMA::dbo TO gluesync;
GO

    Granting it on the schema covers the tables you add later. Grant it on single tables with GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.orders TO gluesync; if you prefer.

    Check the login from the machine where Core Hub runs. The query reads the changes of a table, which is what the agent does:

    sqlcmd -C -S <host>,1433 -U gluesync -d demo -Q "SELECT TOP 3 CT.id, CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION FROM CHANGETABLE(CHANGES dbo.orders, 0) AS CT"
    Expected result

    The query returns rows with SYS_CHANGE_OPERATION set to I, U or D, or no rows if nothing changed since you enabled the table. Without the grant, SQL Server answers The VIEW CHANGE TRACKING permission was denied on the object '<table>', database 'demo', schema 'dbo'.

    ALTER on the tables, or on the database, is needed only if you let Gluesync enable Change Tracking itself, see step 11. VIEW SERVER STATE is not required: in the test, an entity ran and replicated with a login that did not have it.
    Create the source connection and the pipeline

    UI

    1. In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

    2. In 1. Setup source, select the MS SQL Server tile. It shows 2 connectors available. Choose MS SQL Server Change tracking agent, described as Change tracking agent for MS SQL Server 2016 and above;

    3. In Base settings, fill:

      • Host name / Address: the DNS name or address of the server, as Core Hub sees it;

      • Port (leave 0 to use dynamic port discovery): 1433, or the port of your instance;

      • Database name: the database with the tracked tables;

      • Username and Password: the login of step 4;

    4. Keep Enable TLS off and Trust server certificate on for a lab server. For a production server, turn on Enable TLS and use the certificate options of the reference;

    5. Select Test and continue. The source appears with a green check mark and the line <user>@<host>:<port>. Select Next.

    Agents setup wizard with the MS SQL Server Change tracking agent
    Figure 1. The Base settings of the MS SQL Server Change tracking agent.

    1. In 2. Setup target(s), choose your target agent and fill its connection. For the MySQL target of this guide, enter the host, 3306, the database, the user and the password, then turn on Allow public key retrieval and keep Trust server certificate on if your lab server uses the default self-signed certificate. Select Test and continue, then Next;

    2. Enter a Pipeline name and select Finish.

    Expected result

    The pipeline appears in the HEALTHY group of the pipeline list with the SQL Server and the target icons, and the entity table is empty. The connection test signs in and reads the version. It does not read changes, so a missing VIEW CHANGE TRACKING shows only when an entity starts, see the troubleshooting table.

    Add the entity and create the target table

    UI

    1. In the pipeline, select Entities. The Add entities page opens with the Object browser. Expand the schema dbo and tick the table orders. The Field mapping dialog opens;

    2. Under Target, choose the Target Schema, then type orders in Target Table and choose Create table "orders". The dialog shows New table orders will be created on confirm. and the proposed target types. The primary key of the source is the key of the target;

    3. Select Confirm. The Create table orders dialog shows the read-only SQL statement. Read it, then select Create table orders to run it on the target;

    4. Back on the Add entities page, select Confirm and continue.

    Field mapping dialog with the source table dbo.orders and the target table demo.orders
    Figure 2. The Field mapping of the table: SQL Server types on the left, the proposed MySQL types on the right.
    Expected result

    The entity appears in the pipeline on Hold as dbo.orders. With a MySQL target, the types are int, varchar(50), varchar(20), decimal(10, 2) and datetime(3), and the primary key stays on id.

    A table without a primary key shows the errors No key on the source and No key on the target in the Field mapping dialog. Choosing a logical key clears them, but Change Tracking still cannot be enabled on that table, see the troubleshooting table.
    Start the entity with a full copy

    UI + SQL

    1. On the row of the entity, select the play button. The Sync changes (CDC) dialog opens;

    2. Under Start from keep Full copy, Recommended for the first start. It copies all the rows, then follows the changes. The other choices are Where it stopped, which is not available before the first run, and Now, which skips what changed before the start;

    3. Under Data already in the target keep Keep and merge, and under Speed keep Standard;

    4. Select Start entity.

    Sync changes CDC dialog for dbo.orders with Start from Full copy selected
    Figure 3. The start dialog of a new entity, with Full copy and Keep and merge.

    When the copy ends, count the rows on both sides:

    -- on the source
SELECT COUNT(*) FROM dbo.orders;
-- on the target
SELECT COUNT(*) FROM demo.orders;
    Expected result

    Both counts are 20000. The entity is Active. The Last trans. column shows - until the entity reads its first change, then it shows the Change Tracking version that the entity has read up to. The version is a counter of the whole database, so it is not a number of rows.

    Pipeline orders-ct-to-mysql with the entities dbo.customers and dbo.orders in the Active group
    Figure 4. The entity is active and follows the changes.
    Follow live changes and check the three operations

    SQL

    On the source, insert, update and delete one row each:

    INSERT INTO dbo.orders VALUES (20001, 'customer-new', 'new', 19.90, SYSDATETIME());
UPDATE dbo.orders SET status = 'paid', amount = 25.00 WHERE id = 1;
DELETE FROM dbo.orders WHERE id = 2;

    After a few seconds, read the three keys on the target:

    SELECT id, customer, status, amount FROM demo.orders WHERE id IN (1, 2, 20001) ORDER BY id;
SELECT COUNT(*) FROM demo.orders;

    You can also see what SQL Server recorded on the source:

    SELECT CT.id, CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION
FROM CHANGETABLE(CHANGES dbo.orders, 0) AS CT
ORDER BY CT.SYS_CHANGE_VERSION;
    Expected result

    Row 1 shows paid and 25.00, row 20001 exists, and row 2 is gone. The count is 20000: one row was added and one was deleted. CHANGETABLE lists 20001 as I, 1 as U and 2 as D, each with its own version, and the Last trans. column of the entity moves to the last version.

    See net changes and pause and resume an entity

    UI + SQL

    Change Tracking keeps one entry for each key, with the latest operation and the version of the latest change. Gluesync then reads the current row. To see it, pause the entity first:

    1. Select the pause button of the entity, then Pause entity. The entity goes to Hold;

    2. On the source, insert a row and update it four times, each statement in its own transaction:

      INSERT INTO dbo.orders VALUES (30001, 'customer-net', 'new', 1.00, SYSDATETIME());
UPDATE dbo.orders SET status = 'picked' WHERE id = 30001;
UPDATE dbo.orders SET status = 'packed' WHERE id = 30001;
UPDATE dbo.orders SET status = 'shipped' WHERE id = 30001;
UPDATE dbo.orders SET status = 'delivered' WHERE id = 30001;

    3. Read what Change Tracking holds. Use the version that the entity shows in Last trans. as the second argument:

      SELECT CT.id, CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION
FROM CHANGETABLE(CHANGES dbo.orders, 3) AS CT;

    4. Select the play button, choose Where it stopped, which continues from the last checkpoint, and select Start entity.

    Expected result

    CHANGETABLE returns one row for key 30001, operation I, with the version of the last of the five changes, even though the server used five versions. After the resume, the target holds the row with the status delivered, and Tot. processed events of the pipeline grew by one, not by six. The gluesync_source_pending_rows metric counts rows too, not changes: it is a lower bound of the work that is waiting.

    If your target must see every intermediate state of a row, Change Tracking cannot give it. Use the CDC agent.
    Survive a stop: the retention and the expired position

    UI + SQL

    An entity remembers the last Change Tracking version it read. When it starts with Where it stopped, SQL Server must still hold every change after that version. If it does not, Gluesync refuses to continue, because it would skip changes without telling you. The oldest version that SQL Server can still return is the minimum valid version of the table. It moves past the saved position in three cases:

    • the entity was not read for longer than the retention, and the cleanup of SQL Server removed the older changes;

    • somebody ran TRUNCATE TABLE, which Change Tracking does not record row by row;

    • Change Tracking was switched off and on again on the table.

    To see the failure on a test system, pause the entity and switch Change Tracking off and on again on its table:

    ALTER TABLE dbo.orders DISABLE CHANGE_TRACKING;
UPDATE dbo.orders SET status = 'gap' WHERE id = 6;
ALTER TABLE dbo.orders ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version,
       CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.orders')) AS min_valid_version;

    Then start the entity with Where it stopped. The entity goes to Error, and the Control Plane shows the notification Change tracking position expired. The Core Hub log has the full text:

    Change tracking no longer holds the changes of [dbo].[orders] after version 8, the position this entity resumes from: the oldest version it can still return is 12. The changes in between were removed by the change tracking retention cleanup (the entity was not read for longer than the retention), by a TRUNCATE TABLE or by change tracking being disabled and enabled again on the table. Run a new snapshot of the entity to resynchronize it. ...

    To recover:

    1. Pause the entity if it is retrying;

    2. If the cause was the retention, first raise it above the time the entity was stopped, or the next stop ends the same way:

      ALTER DATABASE [demo] SET CHANGE_TRACKING (CHANGE_RETENTION = 14 DAYS);

    3. Select the play button, choose Full copy and Keep and merge, and select Start entity.

    Expected result

    The snapshot copies the table again, the row you changed while Change Tracking was off (gap) is on the target, and the entity follows live changes from the new version. Nothing is lost, but the whole table is read again. For a large table, that is the cost of a retention that was too short.

    A stop, a Core Hub outage or a snapshot that lasts longer than the retention all end this way. Size the retention for the longest of them.
    Optional: let Gluesync enable Change Tracking

    UI + SQL

    By default, Gluesync does not change your database. If Change Tracking is off on a table, the snapshot of that entity runs, and the notification Table Cdc Not Enabled says Change data capture not enabled for entity dbo.customers and tables [dbo.customers]. Sync cannot be started. Only snapshot can be executed. Switch the setting on if you want Gluesync to run the statements of steps 2 and 3:

    1. Open the source of the pipeline and go to Advanced settings;

    2. Select Add property, choose Enable automatic Change Data Capture / Change Tracking activation as Key, and turn on the Value;

    Edit source page
    Figure 5. The property that switches the automatic activation on.

    1. Select Test and continue.

    The activation runs when the connection is saved, not when an entity starts. It needs these permissions for the login of step 4:

    GRANT ALTER ON DATABASE::[demo] TO gluesync;           -- to enable it on the database
GRANT ALTER ON OBJECT::dbo.customers TO gluesync;      -- to enable it on a table

    When the database is not enabled yet, Gluesync runs ALTER DATABASE …​ SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 24 HOURS, AUTO_CLEANUP = ON). The Source change retention in hours property of Advanced settings sets that number, 24 by default. It applies when Gluesync enables Change Tracking on the database. To change a database that is already enabled, use ALTER DATABASE, see step 2.

    Expected result

    With ALTER on the database, a database without Change Tracking shows 24 HOURS and is_auto_cleanup_on set to 1 after the save. With ALTER on a table, the table is listed in sys.change_tracking_tables after the save.

    Tune the speed

    UI

    1. In the start dialog, under Speed select Faster or Custom. Custom adds Reading, with Read in parallel (off by default: it splits the table into ranges read at the same time), and Writing, with the Write method for During the copy and During live changes (Standard or Bulk load) and Writers during the copy (from 1 to 10);

    2. Open the source from the pipeline list and select Advanced settings to add Query parallelism (4 by default), Max Concurrency (0 means no limitation) or Max Snapshot Concurrency (0 means no limitation) if you need to hold the load on the source down, see SQL Server source advanced settings;

    3. If the target supports bulk operations, set the Write method to Bulk load for the copy and for live changes. See Tune replication throughput and Bulk load.

    A snapshot that runs longer than the retention is a risk: size the retention for the snapshot too, see step 10.

    Troubleshooting

    Symptom or message Cause Fix

    Cannot enable change tracking on table 'audit_log'. Change tracking requires a primary key on the table. Create a primary key on the table before enabling change tracking. Gluesync shows the notification Change Tracking could not be enabled on the tables of entity dbo.audit_log: Change Tracking needs a primary key, and [dbo].[audit_log] has none, so it cannot be enabled before one is added.

    The table has no primary key. SQL Server error 4997 has no permission fix.

    Add a primary key and enable the table again, or read the table with the CDC agent.

    Field mapping shows No key on the source and No key on the target

    The table has no primary key and no logical key was chosen.

    Add a primary key at the source. A logical key clears the message, but SQL Server still refuses Change Tracking on the table.

    Gluesync could not enable Change Tracking on database 'demo2', and the configuration of the agent is refused until it is enabled. with the server error User does not have permission to alter database 'demo2', the database does not exist, or the database is not in a state that allows access checks.

    The automatic activation is on, Change Tracking is off on the database, and the login lacks ALTER on the database. The message contains the statement to run.

    Run the ALTER DATABASE statement of the message as an administrator, or run GRANT ALTER ON DATABASE::[<database>] TO gluesync; and save the source again.

    Table Cdc Not Enabled: Change data capture not enabled for entity dbo.customers and tables [dbo.customers]. Sync cannot be started. Only snapshot can be executed.

    Change Tracking is off on the table and the automatic activation is off. The snapshot works, the live changes cannot start. The notification says "Change data capture" for this agent too.

    Run ALTER TABLE …​ ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); or turn on the automatic activation, then save the source with Test and continue.

    Change Tracking could not be enabled on the tables of entity dbo.customers with the server error Cannot find the object "customers" because it does not exist or you do not have permissions.

    The automatic activation is on and the login lacks ALTER on the table. SQL Server answers with the "does not exist" wording when it hides an object from a user.

    Run the statement that the notification shows as an administrator, or GRANT ALTER ON OBJECT::dbo.customers TO gluesync; and save the source again.

    Permission denied and Entity sync start error: The VIEW CHANGE TRACKING permission was denied on the object 'customers', database 'demo', schema 'dbo'.

    The login can read the table but not its changes. The connection test passed, because it does not read changes.

    Run GRANT VIEW CHANGE TRACKING ON SCHEMA::dbo TO gluesync;. The entity retries and recovers by itself, no restart is needed.

    Change tracking position expired: Change tracking no longer holds the changes of [dbo].[orders] after version 8, the position this entity resumes from: the oldest version it can still return is 12. …​

    The entity was stopped for longer than CHANGE_RETENTION, or the table was truncated, or Change Tracking was disabled and enabled again on it.

    Raise CHANGE_RETENTION above the stop, then start the entity with Full copy and Keep and merge. See step 10.

    A row that changed many times arrives once, with the last values

    Change Tracking keeps the net change of each key. This is not an error.

    Use the CDC agent if the target needs every intermediate change or the old values.

    For a legacy server that does not speak TLS 1.2, see MS SQL Server Change Tracking Troubleshooting.

    Pipeline page with the entity dbo.customers in Error and the notifications Entity sync start error and Permission denied with the message The VIEW CHANGE TRACKING permission was denied on the object customers database demo schema dbo
    Figure 6. A start that fails because the login has no VIEW CHANGE TRACKING on the table: the notifications show the server message.
    Pipeline page with the notification Change Tracking could not be enabled on the tables of entity dbo.audit_log and the message Change Tracking needs a primary key
    Figure 7. The automatic activation cannot enable a table without a primary key.

    Related guides