SQL Server change tracking: why we read changes on a short interval

A cache is only useful if it tells the truth. redfly serves reads from Redis, a memory store, and keeps that copy in step with the customer's SQL Server database. To do that we need a reliable answer to one question, over and over: which rows changed since we last looked?

SQL Server gives two built-in answers. One is change tracking, a lightweight log of which rows changed and in what order. The other is a heavier feature that reads the transaction log (the database's own journal of every write) and reconstructs each change in full. We use change tracking, and we read it on a short interval.

What change tracking actually keeps

When change tracking is switched on for a table, SQL Server keeps a small side table of primary keys and a version number. Every saved (committed) insert, update or delete bumps a database-wide version counter and notes the key of the affected row against that version. It does not store the old values or the new values; it stores the fact that a row changed, and when, in version order.

That is deliberately minimal. The side table stays small because it holds one entry per changed row, not one entry per change, and SQL Server prunes it on a retention period the administrator sets. Because the bookkeeping happens inside the same transaction as the write, it is consistent with the data; there is no window where a row has changed but the tracking table does not yet know.

The reader's job is then simple. Remember the last version number you processed, ask SQL Server for every key that changed after it, fetch the current values of those rows, and save the new version. That is the whole loop.

Why we poll a version number instead of reading the log

The log-reading alternative is more powerful on paper. It gives before and after images of each row. It also needs more from the server: a log reader process that scans the transaction log, a scheduled job to run it, and extra tables holding the captured history. Those pieces have to be monitored, kept working after schema changes, and cleaned up on their own schedule.

For a cache, that power is wasted. Redis does not need to know what a row used to contain; it needs the row's current state, or to know that the row is gone. Change tracking answers precisely that question, and the current values come from an ordinary query against the table itself.

Reading a version number on an interval also has a pleasing operational shape:

• No triggers on the customer's tables, so writes carry no extra code path.
• No log reader and no agent job, so nothing extra runs on the database server between our reads.
• A single number tells you where you are; recovery after a restart is "start from the last version you saved".
• The load each read places on the server is bounded by how much changed, not by how much time passed.

That last point matters on a busy production server. The cost of one read is one lookup against a small side table plus a fetch of the changed rows. In a busy period it scales with the write rate, which is the fairest deal a database can offer.

What "a short interval" means for freshness

The honest consequence of reading on an interval is that Redis lags the database by a little. A write commits, the next read of change tracking picks it up, the changed rows are fetched and written to Redis, and only then does a read through the redfly API see the new value. On the streamed engines, PostgreSQL and MongoDB, the database pushes each change to us as it happens. On SQL Server we go and ask, so the lag is the interval plus the time to apply the change.

We describe that as a short lag, not instant. For the reads redfly is built to serve, permission checks, lookups, profile loads and the rest of the small constant traffic behind every click, that is well inside what applications already tolerate from read-only database copies (replicas) and caches. Writes still go straight to the database, so anything that must see its own write immediately can read it back from the source of truth.

A cache a moment behind, and kept correct, beats a cache that is instant and occasionally wrong.

Two things keep the lag from quietly growing. The Sync Service watches for drift (the copy quietly diverging from the source) between Redis and the database and repairs it, and it can backfill a table from scratch if the version it remembers has aged out of the retention window. Those are the parts that are easy to forget when a team builds this by hand.

What this asks of the database administrator

Change tracking has to be enabled on the database and on each table redfly will serve. It is a setting, not a schema change, and it does not alter the tables themselves. Retention should be set longer than the longest outage you expect the sync process to survive; a couple of days is a common choice.

Beyond that, the connection redfly uses needs permission to read change tracking and to read the tables it serves.

redfly fits here as the piece that runs this loop for you: reading change tracking on a short interval, keeping Redis in step, and handling backfill, drift and recovery so that your application code never contains a line of cache logic.