Search docs.molo17.com
    Configuration guide Connectors

    Set up a SQL Server CDC source with the SQL Server Agent

    The MS SQL Server CDC agent reads the change tables that SQL Server fills from its transaction log. SQL Server Agent runs the capture job that fills them, and the cleanup job that empties them. This guide turns on the Agent, enables CDC on a database and on its tables, sets the retention, and creates a Gluesync user with the fewest privileges that work. It then runs a snapshot and live changes into a MySQL target, handles a column added to a captured table, and shows what happens when the cleanup deletes changes that Gluesync has not read yet.

    In this guide

    You will check that SQL Server Agent runs, enable CDC on the database and on the tables, and read the two jobs that SQL Server creates. You will choose a retention for the cleanup job. You will create a Gluesync login with db_datareader only, and learn when db_owner or sysadmin is needed instead. You will create the connection and the entity, run a snapshot followed by live changes, add a column to a captured table, and recover from a retention gap.

    Quick information

    Goal

    Replicate a SQL Server table with a snapshot followed by change data capture (CDC), and keep the capture jobs, the retention and the schema changes under control

    Audience

    Database administrators who own a SQL Server instance, and data engineers who set up the pipeline

    Level

    Intermediate

    Time required

    About 60 minutes

    Area

    SQL Server → CDC, SQL Server Agent; Control Plane → Pipelines → MS SQL Server CDC 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 instance with SQL Server Agent available, for example Developer, Standard or Enterprise edition. Express has no SQL Server Agent. This guide was tested with SQL Server 2022 Developer on Linux;

    2. You can sign in with the sysadmin server role. Enabling CDC on a database needs it;

    3. You can restart the SQL Server service. On Linux, turning on the Agent needs a restart;

    4. Core Hub can open a connection to the instance on the SQL Server port, 1433 by default;

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

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

    7. You have a source table. This guide uses dbo.orders in the database shop, with 100,000 rows (id bigint primary key, customer varchar(50), status varchar(20), amount decimal(10,2), created_at datetime2). A table without a primary key works too, see Choose keys for tables without a primary key.

    The messages quoted in this guide were captured in the Control Plane, in the Core Hub log and in sqlcmd with SQL Server 2022 as the source and MySQL 8.0 as the target. The options of the agent are listed in the MS SQL Server CDC reference. If you need net changes with no SQL Server Agent, see Set up a SQL Server Change Tracking source.

    Guide steps

    Turn on SQL Server Agent and check it

    CLI + SQL

    CDC on SQL Server needs the Agent: its capture job reads the transaction log and writes the change tables. On Windows, start the SQL Server Agent service from SQL Server Configuration Manager or services.msc. On Linux, or in a container, turn it on and restart the service:

    /opt/mssql/bin/mssql-conf set sqlagent.enabled true
systemctl restart mssql-server

    In a container, set the environment variable MSSQL_AGENT_ENABLED=True instead, or run mssql-conf as root inside the container and restart it with docker restart <container>.

    Then connect as sysadmin and read the state of the Agent:

    EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Agent XPs';
SELECT servicename, status_desc FROM sys.dm_server_services;
    Expected result

    Agent XPs shows 1 in the columns config_value and run_value, and sys.dm_server_services lists SQL Server Agent (MSSQLSERVER) as Running. Before the restart, in the test, both columns were 0.

    Without the Agent you can still run sys.sp_cdc_enable_db and sys.sp_cdc_enable_table. They succeed and create the change tables and the two jobs, but SQL Server prints SQLServerAgent is not currently running so it cannot be notified of this action. once for each statement. Nothing fills the change tables until the Agent runs, so a Gluesync entity would read no changes.
    Enable CDC on the database and on the tables

    SQL

    CDC is enabled twice: once for the database, once for each table. Connect as sysadmin:

    USE shop;
EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'orders',
    @role_name = NULL,
    @supports_net_changes = 0;

    Repeat sys.sp_cdc_enable_table for every table you replicate. Two arguments matter:

    1. @role_name = NULL is the value Gluesync uses when it enables a table itself. It means that SQL Server does not gate the changes with a database role;

    2. @supports_net_changes = 0 is enough. Gluesync reads every change, not the net result per key, so you do not need the extra index that net changes need.

    Do not pass @capture_instance. Gluesync reads the capture instance that has the name <schema>_<table>, here dbo_orders. A capture instance with another name is ignored.

    Check the result:

    SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'shop';
SELECT name, is_tracked_by_cdc FROM sys.tables;
SELECT capture_instance FROM cdc.change_tables;
SELECT j.name, j.enabled FROM msdb.dbo.sysjobs AS j;
    Expected result

    is_cdc_enabled is 1, is_tracked_by_cdc is 1 for orders, and cdc.change_tables lists dbo_orders. msdb has two enabled jobs, cdc.shop_capture (CDC Log Scan Job) and cdc.shop_cleanup (CDC Cleanup Job). SQL Server creates them with the first table of the database.

    The change tables live in the schema cdc of the source database, for example cdc.dbo_orders_CT. The Object browser of the Control Plane lists them, with dbo.systranschemas. Do not add them as entities.
    Read the jobs and set the retention

    SQL

    List the settings of the two jobs:

    USE shop;
EXEC sys.sp_cdc_help_jobs;
    Expected result

    In the test, the capture job had maxtrans 500, maxscans 10, continuous 1 and pollinginterval 5. The cleanup job had retention 4320 and threshold 5000. SQL Server stores the retention in minutes, so 4320 is three days.

    The retention is the one setting you must decide. The cleanup job deletes the changes that are older than the retention, whether or not Gluesync has read them. Choose a value longer than the longest time an entity can be paused, in error or stopped, for example a weekend plus a margin. The change tables grow with it, so size the disk of the database too.

    -- 7 days, in minutes
EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080;

    Changes to the capture job, for example @pollinginterval or @maxtrans, apply when you stop and start the job:

    EXEC sys.sp_cdc_change_job @job_type = N'capture', @pollinginterval = 2;
EXEC sys.sp_cdc_stop_job N'capture';
EXEC sys.sp_cdc_start_job N'capture';
    The Source change retention in hours property that the agent lists applies to the Change Tracking agent only. For CDC, the retention is the cleanup job.
    Create the Gluesync user

    SQL

    Gluesync reads the table for the snapshot and the change tables for CDC. In the test, the least privilege that worked is db_datareader on the source database. Replace the password:

    CREATE LOGIN gluesync WITH PASSWORD = '<a strong password>';
USE shop;
CREATE USER gluesync FOR LOGIN gluesync;
ALTER ROLE db_datareader ADD MEMBER gluesync;

    With this user, the connection test passed, the snapshot copied 100,000 rows, and an insert, an update and a delete reached the target. The tables had @role_name = NULL. You enabled CDC yourself in step 2, so Gluesync needs no more.

    Give more only when you want Gluesync to enable CDC for you:

    Privilege What it allows When you need it

    db_datareader on the database

    Read the tables and the change tables, run the snapshot and CDC

    Always. This is the least privilege, when a DBA enabled CDC on the database and on the tables

    db_owner on the database

    Run sys.sp_cdc_enable_table

    Only if you turn on automatic activation, step 9, and want Gluesync to enable CDC on the tables. Also needed if you use recursion protection: the agent creates a small side table and enables CDC on it

    sysadmin server role

    Run sys.sp_cdc_enable_db

    Only if you want Gluesync to enable CDC on the database. The statement is refused to a db_owner

    To see the last rule yourself, connect as a db_owner that is not a sysadmin and run EXEC sys.sp_cdc_enable_db on a database without CDC.

    Expected result

    SQL Server answers Msg 22902 …​ Caller is not authorized to initiate the requested action. Sysadmin privileges are required.

    When a table is gated with @role_name = N’cdc_reader', SQL Server documents that a reader must be a member of that role. In the test, a user with only db_datareader read the changes of a gated table before and after the user joined the role. If you use a gating role, make Gluesync a member of it anyway, and check the result on your build.
    Create the SQL Server source connection

    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, open the MS SQL Server tile and choose MS SQL Server CDC agent. The other choice, MS SQL Server Change tracking agent, is a different agent;

    3. Fill Base settings:

      • Host name / Address: the DNS name or address of the instance, 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 CDC enabled, here shop;

      • Username and Password: the user of step 4;

    4. Turn on Enable TLS and Trust server certificate only if your instance requires encryption, see Enable HTTPS and TLS to databases;

    5. Select Test and continue. The source appears with <user>@<host>:<port>. Select Next.

    Edit source page of the MS SQL Server CDC agent with Host name mssql
    Figure 1. The Base settings of the MS SQL Server CDC 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. Turn on Allow public key retrieval and Trust server certificate if your lab server uses the default self-signed certificate. With Trust server certificate off, the save failed with Trust store not found, please upload a trust store when using verify server 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 no entity yet. The connection test checks the sign-in and the database only. It does not check that SQL Server Agent runs, that CDC is enabled, or that the user can read the change tables.

    When you edit a source and the sign-in fails, the Control Plane still saves the new values. It shows The connection was saved, but agent <id> of pipeline <id> did not accept the new credentials: …​ and the pipeline keeps the wrong credentials until you save good ones. See the troubleshooting table for the three messages that SQL Server returns.
    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.;

    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 on Hold as dbo.orders. The key of the target is the primary key id. On a MySQL target the types are bigint, varchar(50), varchar(20), decimal(10, 2) and datetime(6): the datetime2(7) of the source loses its seventh decimal digit.

    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. Where it stopped is not available before the first run, and Now 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.

    While the copy runs, read the row count on the target:

    -- on the target
SELECT count(*) FROM demo.orders;
    Expected result

    The target count grows until it equals the source count, 100000. The entity moves to the Active group and Last trans. shows a log sequence number (LSN) such as 0000003000000ce80005.

    Check the three operations

    SQL

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

    INSERT INTO dbo.orders VALUES (100001, '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, 100001) ORDER BY id;
SELECT count(*) FROM demo.orders;
    Expected result

    Row 1 shows paid and 25.00, row 100001 exists, and row 2 is gone. The count is 100000: one row was added and one was deleted.

    To see what the capture job does, look at the log scan sessions of SQL Server. error_count stays 0 when the capture works:

    SELECT TOP 3 session_id, start_time, end_time, tran_count, latency, error_count
FROM sys.dm_cdc_log_scan_sessions ORDER BY session_id DESC;
SELECT * FROM sys.dm_cdc_errors;

    If the capture job is stopped, changes wait in the transaction log and nothing reaches the target. In the test, a row written while the job was stopped (EXEC sys.sp_cdc_stop_job N’capture') was not in the change table and not on the target. After EXEC sys.sp_cdc_start_job N’capture' the row appeared on the target within seconds. Start the job again if you see no changes and the entity is healthy.

    Let Gluesync enable CDC on new tables

    UI + SQL

    Gluesync can run sys.sp_cdc_enable_table for you when you add an entity. The option is off by default, so an upgrade never makes a source with a small user refuse to connect.

    First, see what happens when it is off. Create a second table and add it as an entity the same way as in step 6:

    CREATE TABLE dbo.customers (id int NOT NULL PRIMARY KEY, name varchar(50), country char(2));
INSERT INTO dbo.customers VALUES (1, 'Acme', 'IT'), (2, 'Globex', 'US'), (3, 'Initech', 'DE');

    Start the new entity with Full copy.

    Expected result

    The snapshot runs and the rows reach the target. The entity stays on Hold, 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. Run the sys.sp_cdc_enable_table statement of step 2 for the table, then start the entity again.

    Notifications panel with the error Table Cdc Not Enabled above the information Snapshot finished for dbo.customers
    Figure 3. The notification for a table without CDC: the snapshot finishes, the sync does not start.

    To let Gluesync do it, turn the option on:

    1. Open the pipeline, select the gear icon of the pipeline and Edit source;

    2. Fill Password again, open Advanced settings and select Add property;

    3. Under Key choose Enable automatic Change Data Capture / Change Tracking activation and switch Value on;

    4. Select Test and continue.

    Edit source page with Advanced settings open
    Figure 4. The automatic activation property in the Advanced settings of the source.

    Gluesync enables CDC on the tables of an entity when you add the entity, and when you save the source again with the option on. It never disables CDC, because another entity or another tool may read the same table. With the option on, but a user that has only db_datareader, saving the source gives an error notification:

    Expected result

    Change Data Capture could not be enabled on the tables of entity dbo.customers. The message says that on [dbo].[customers] the server refused it, usually because the user lacks membership in the db_owner database role. It prints the statement to run by hand, USE [shop]; EXEC sys.sp_cdc_enable_table …​, and ends with Server error: Caller is not authorized to initiate the requested action. DBO privileges are required.

    With the user in db_owner, a new entity for dbo.customers enabled CDC on the table, and is_tracked_by_cdc became 1. In the test, an entity that existed before the grant did not retry the activation when it was started again: delete the entity and add it again, or enable the table yourself. For the database, the user needs sysadmin, as shown in step 4. Most teams keep the option off and let the DBA run step 2.

    Add a column to a captured table

    SQL + UI

    A capture instance has a fixed list of columns, set when CDC is enabled on the table. A column that you add later is not captured. In the test, after ALTER TABLE dbo.orders ADD note varchar(20) NULL, a new row with a note reached the target without error, with the other columns right and no note. CDC does not warn you.

    To capture the new column, recreate the capture instance while nothing writes to the table, and map the column. The order matters:

    1. Stop the writers of the table, or schedule the change in a quiet window. Wait until the entity has read every change, the Last rows and the Last trans. stop moving;

    2. Pause the entity with its pause button and Pause entity;

    3. Add the column to the target table, for example on MySQL ALTER TABLE orders ADD COLUMN note varchar(20) NULL;

    4. On the source, drop and enable the capture instance again. Changes written between the two statements are lost, which is why you stopped the writers:

      USE shop;
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'orders', @capture_instance = N'dbo_orders';
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'orders', @role_name = NULL, @supports_net_changes = 0;
SELECT column_name FROM cdc.captured_columns;

    5. On the row of the entity, select the three-dot menu and Edit mapping. The new column is listed on both sides, unticked. Tick note on the source and on the target, select Confirm, then Confirm and continue;

    6. Start the entity with Where it stopped.

    Field mapping dialog of an existing entity
    Figure 5. Edit mapping after the schema change: the new column note is listed, 5 of 6 columns are selected.
    Expected result

    cdc.captured_columns lists note. After the mapping is saved, the entity is in the Warning group on Hold with the notification Entity Configuration Changed. After Where it stopped, a row inserted with a note arrives with its note. Rows written before the change keep NULL in note on the target: copy them again with Full copy and Keep and merge if you need the values.

    For other changes, such as a new type, a key change or a dropped column, see Handle schema changes on a live replication.

    Know what a retention gap looks like

    UI + SQL

    Pause the entity, change the source, and let the cleanup job delete the changes before you resume. In the test the cleanup job removed changes that were older than the retention, and Gluesync did not know:

    1. Pause the entity;

    2. Insert a row, wait, insert another row, and run the cleanup. The test used the procedure that the cleanup job calls, with a low-water mark that is later than the first row:

      USE shop;
INSERT INTO dbo.orders VALUES (100030, 'gone-x', 'new', 1, SYSDATETIME());
INSERT INTO dbo.orders VALUES (100031, 'kept-y', 'new', 1, SYSDATETIME());

DECLARE @lw binary(10) = (SELECT MAX(start_lsn) FROM cdc.lsn_time_mapping);
EXEC sys.sp_cdc_cleanup_change_table @capture_instance = N'dbo_orders', @low_water_mark = @lw, @threshold = 5000;
SELECT id FROM cdc.dbo_orders_CT;

    3. Start the entity with Where it stopped.

    Expected result

    The change table holds only row 100031. After the resume, row 100031 is on the target and row 100030 is not. The entity is Active and neither the Control Plane nor the Core Hub log shows an error or a warning. The source had 100010 rows and the target 100009.

    When a saved position is older than the oldest change that remains, Gluesync starts again from the oldest change. The rows in between are lost silently. To realign the target, start the entity again with Full copy and Keep and merge. In the test the missing row appeared and both counts were 100011. Prevent the gap with a retention that is longer than any pause, and watch the age of the oldest change:

    SELECT MIN(tran_end_time) AS oldest_change FROM cdc.lsn_time_mapping;
    Tune the speed

    UI

    1. In the start dialog, under Speed select Faster or Custom to raise the number of readers and of writers. More lanes copy faster and put more load on both databases;

    2. The agent reads the snapshot with 4 parallel queries by default. The property Query parallelism of the source changes it, and Max Snapshot Concurrency (0 means no limitation) limits the snapshot, see SQL Server source advanced settings;

    3. For live changes, SQL Server decides the latency: the capture job polls the log every pollinginterval seconds, 5 by default. A smaller value lowers the delay and adds a little load, see step 3;

    4. If the target supports bulk operations, select Custom and switch Writing to Bulk load. See Tune replication throughput and Bulk load.

    Troubleshooting

    Symptom or message Cause Fix

    SQLServerAgent is not currently running so it cannot be notified of this action. when you enable CDC

    SQL Server Agent is off. CDC is enabled but no job runs, so the change tables stay empty.

    Turn on the Agent as in step 1, restart SQL Server, and check Agent XPs and the jobs cdc.<database>_capture and cdc.<database>_cleanup.

    Msg 22902 …​ Caller is not authorized to initiate the requested action. Sysadmin privileges are required.

    sys.sp_cdc_enable_db was run by a user that is not sysadmin, even if it is db_owner.

    A DBA with sysadmin runs it, or give sysadmin to the user for the activation only.

    Caller is not authorized to initiate the requested action. DBO privileges are required. in the notification Change Data Capture could not be enabled on the tables of entity …​

    Automatic activation is on and the user is not db_owner.

    Run the statement from the notification as a DBA, or add the user to db_owner. Delete and add the entity again if it does not retry.

    Table Cdc Not Enabled: Change data capture not enabled for entity …​ Sync cannot be started. Only snapshot can be executed.

    The table has no capture instance named <schema>_<table>. The snapshot works, the sync cannot start.

    Run sys.sp_cdc_enable_table for the table without @capture_instance, or turn on automatic activation. Check is_tracked_by_cdc and cdc.change_tables.

    Failed to initialize pool: Login failed for user 'gluesync'.

    The password is wrong, or the login is disabled or locked.

    Fix the password in Edit source and save again. The wrong value stays saved until you do.

    Failed to initialize pool: Cannot open database "shop" requested by the login. The login failed.

    The login exists but has no user in the database, or the database name is wrong.

    Run CREATE USER gluesync FOR LOGIN gluesync in the database, grant db_datareader, and check the Database name.

    Inserts, updates and deletes stop reaching the target, the entity is Active and shows no error

    The capture job is stopped, or the Agent is stopped.

    EXEC sys.sp_cdc_start_job N’capture', check sys.dm_cdc_log_scan_sessions and sys.dm_cdc_errors, and check that the Agent runs.

    A new column does not reach the target

    The capture instance was created before the column existed.

    Recreate the capture instance and update the mapping, as in step 10.

    Rows written during a pause are missing after the resume, and no error appears

    The cleanup job deleted the changes before Gluesync read them. Gluesync resumed from the oldest change that was left.

    Start the entity with Full copy and Keep and merge. Raise the retention of the cleanup job, step 3.

    Msg 22836 …​ Could not update the metadata for database when you enable CDC

    The server name stored in SQL Server does not match the real name, for example after a clone.

    See MS SQL Server CDC Troubleshooting.

    TRUNCATE TABLE fails on a replicated table

    SQL Server refuses to truncate a table that has CDC enabled.

    Delete the rows instead, or disable CDC on the table, truncate it, enable it again and start the entity with Full copy.

    Related guides