> ## Documentation Index
> Fetch the complete documentation index at: https://docs.superoffice.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Change tracking

> How Database Mirroring detects and streams changes from the source CRM to the replica.

The current Database Mirroring implementation uses **SQL Server Change Data Capture (CDC)** at the source database. Changes are read by a Debezium connector running in SuperOffice's infrastructure, published to per-tenant Kafka topics, and served to the client over a REST long-poll endpoint.

<Note>
  The [legacy WCF-based service](#legacy) used SQL Server **Change Tracking** (a different and lighter-weight feature) with the Log Sequence Number (LSN) as the position marker. The mechanism described below replaces that approach for new deployments.
</Note>

## How it works

1. The source SQL Server has CDC enabled on the tables that are subject to mirroring.
2. **Debezium** reads the CDC changes and publishes them to a per-tenant Kafka topic (`<ctx>-cdc`). Schema-change events are published to a parallel `<ctx>-schemahistory` topic.
3. The **Replication Service** exposes a REST endpoint with **long-polling**. It tracks consumer offsets per session.
4. The **Database Mirroring client** long-polls for new events, applies them to the replica, and commits its progress back to the service.

The position marker is the Kafka `topic:partition:offset`, not a SQL Server LSN.

## Latency

End-to-end latency is normally measured in **seconds**: a row changes in the cloud, the CDC connector publishes the event, the client picks it up on its next long-poll, and applies it to the replica. Actual latency depends on network conditions and replica throughput.

## Retention: 7 days

Change events are retained in Kafka for **7 days** by default. If the client is offline longer than that, events will have been purged from the topics and the client cannot fill the gap by replaying older events.

To recover, you re-snapshot the affected tables. See [Force re-sync][2].

## Schema-change detection

New tables and altered tables are discovered by a periodic schema-check (default: every hour, controlled by `SchemaUpdateIntervalSeconds` in `appsettings.json`). You can force an immediate check with:

```cmd theme={null}
SuperOffice.Online.Replication.Client.exe update-schema
```

<h2 id="legacy">
  Legacy SQL Server change tracking
</h2>

> \[!IMPORTANT]
> This section describes the original mechanism used by the legacy WCF-based mirroring service. It is kept here for context and for customers still running the legacy service.

The legacy implementation relied on the SQL Server **Change Tracking** feature (`CHANGETABLE`, `CHANGE_TRACKING_CURRENT_VERSION`), which is a lightweight mechanism implemented directly in the database engine. It uses an incremental 64-bit integer, the **Log Sequence Number (LSN)**, as the position marker.

Using this method, rather than the internal `TravelTransactionLog`, avoided complexity in situations such as:

* UDEF changes
* Merging two companies (logged differently from other operations)
* Service operations (not logged at all in `TravelTransactionLog`)

### How the legacy mechanism worked

1. When change tracking for a table was OFF, it was automatically turned ON by the mirroring system.
2. A **wipe** message was sent to the client.
3. All records in the table were considered changed and transmitted to the client.

### Legacy retention

Change tracking data in SQL Server was kept for 7 days. If a mirroring client service was down for more than a week, change data would be missing and the next synchronization would cause a complete, automatic repopulation of the mirror.

[2]: ./force-resync


## Related topics

- [Database Mirroring scenarios](/en/online/mirroring/scenarios.md)
- [Conversion tracking](/en/marketing/utm/learn/index.md)
- [Set up UTM tracking](/en/marketing/utm/admin/set-up.md)
- [Answer Accepted](/en/api/reference/restful/agent/ai_agent/answer-accepted.md)
- [Publish forms](/en/marketing/forms/learn/publish.md)
- [Answer Feedback](/en/api/reference/restful/agent/ai_agent/answer-feedback.md)