Enable change capture on SQL Server

To sync only what changed (an Incremental task) instead of re-reading the whole table every run, Elglide uses SQL Server's native change capture. Two mechanisms are supported — Change Tracking (CT) and Change Data Capture (CDC). Elglide auto-detects whichever you enable; if a table has both, it prefers CDC (gentler on your production writes). You don't have to choose in the UI — just enable one on the source and create the task.

Permissions required

Two distinct privilege levels — keep them separate:

• To turn it on (one-time, a DBA): db_owner on the database. CT uses ALTER DATABASE / ALTER TABLE; CDC uses sys.sp_cdc_enable_db / sys.sp_cdc_enable_table and needs SQL Server Agent running.
• For Elglide's connection login (ongoing, least-privilege):
  • SELECT on each synced table (the first Full load + reading current values) — db_datareader covers this.
  • CT: VIEW CHANGE TRACKING on each tracked table (to call CHANGETABLE).
  • CDC: access to the generated cdc.* change functions — membership in the gating role you pass to @role_name, or (if @role_name = NULL) SELECT on the cdc schema.
  • It does not need db_owner or sysadmin.

A least-privilege read login looks like:

-- one-time, run as db_owner:
CREATE USER [elglide_reader] FOR LOGIN [elglide_reader];
ALTER ROLE db_datareader ADD MEMBER [elglide_reader];                 -- SELECT on all tables
GRANT VIEW CHANGE TRACKING ON [dbo].[YourTable] TO [elglide_reader];  -- needed for CT
GRANT SELECT ON SCHEMA::cdc TO [elglide_reader];                      -- needed for CDC

Which one should I enable?

	Change Tracking (CT)	Change Data Capture (CDC)
How it captures	Synchronous — inside each write transaction	Asynchronous — a background job reads the transaction log
Impact on your writes	Small overhead on every INSERT/UPDATE/DELETE	Near-zero — decoupled from the write path
Needs SQL Server Agent	No	Yes (CDC runs as Agent jobs)
Best for	Lower-write tables, simplest setup	Busy / high-write tables (recommended)

---

Option A — Change Tracking (CT)

1. Turn it on at the database level

Run once per database (requires db_owner):

ALTER DATABASE [YourDatabase]
    SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);

2. Turn it on for each table

ALTER TABLE [dbo].[YourTable]
    ENABLE CHANGE_TRACKING
    WITH (TRACK_COLUMNS_UPDATED = OFF);

3. Grant the Elglide read account

GRANT VIEW CHANGE TRACKING ON [dbo].[YourTable] TO [elglide_reader];

---

Option B — Change Data Capture (CDC)

1. Turn it on at the database level

Run once per database (requires db_owner):

EXEC sys.sp_cdc_enable_db;

2. Turn it on for each table

EXEC sys.sp_cdc_enable_table
    @source_schema        = N'dbo',
    @source_name          = N'YourTable',
    @role_name            = NULL,   -- NULL = any user with SELECT can read; or name a gating role
    @supports_net_changes = 1;

3. Give the Elglide read account access to the changes

Grant SELECT on the table and ensure the account can read the generated cdc.* functions (member of the gating role above, or SELECT on the cdc schema):

GRANT SELECT ON [dbo].[YourTable]  TO [elglide_reader];
GRANT SELECT ON SCHEMA::cdc        TO [elglide_reader];

---

About retention (both CT and CDC)

Both keep a retention window of changes. If a sync ever falls behind that window (e.g. it was paused for days), the change history it needed is gone. Elglide detects this automatically, performs a one-time full re-sync to re-baseline, and resumes incremental from there — no manual cleanup needed. For frequently-synced tables the default retention is plenty.

Verify in Elglide

After enabling, open the connection's Pipelines tab. Tables with change capture show a CT or CDC badge. Create a task on one and it runs Incremental automatically — a first Full load to seed the target, then change-only syncs after that.