SQL Server (Edge)
Hevo Edge supports the following variations of SQL Server as a Source:
Supported Configurations
| Supportability Category | Supported Values |
|---|---|
| Database versions | 2016, 2017, 2019, and 2022 |
| Maximum table count per connection | 15000 |
| Maximum column count per table | 1019 |
| Connection limit per database | No limit |
| Transport Layer Security (TLS) | 1.2 |
| Transparent Data Encryption (TDE) | Not supported |
Supported Features
| Feature Name | Supported |
|---|---|
| Capture deletes | Yes |
| Custom data (user-configured tables & fields) | No |
| Data blocking (skip objects and fields) | Yes |
| Resync (objects and Pipelines) | Yes |
| API configurable | No |
| Connecting through a private link | Connections via AWS PrivateLink are allowed. Subscription to a business plan is required. |
Supported Instance Types
| Instance Types | Supported |
|---|---|
| Amazon RDS SQL Server | |
| Primary instance | Yes |
| Availability group replica | No |
| Azure SQL Server | |
| Primary instance | Yes |
| Availability group replica | No |
| Generic SQL Server | |
| Primary instance | Yes |
| Availability group replica | No |
| Google Cloud SQL Server | |
| Primary instance | Yes |
| Availability group replica | No |
Supported Ingestion Modes
Hevo replicates data from SQL Server using its built-in Change Tracking (CT) and Change Data Capture (CDC) features. Before adding the SQL Server database as a Source in your Pipeline, you must enable at least one of these data tracking mechanisms at the database and table levels. For each Source table, Hevo ingests data using the tracking method enabled for that table. If neither CT nor CDC is enabled, Hevo cannot ingest data from that table and marks it as inaccessible in the Pipeline.
You can use change tracking when only the latest data is needed, and change data capture when you need to track data changes, including the order and type of operations applied to the data. When both mechanisms are enabled, Hevo uses CDC as the default ingestion mode.
Change Tracking
Change Tracking (CT) is a lightweight mechanism to track which rows in a table have changed. It does not record the order of changes or details of the values that were modified. CT captures only the primary key and the last effective operation type (insert, update, or delete) for the changed rows. SQL Server stores this information in an internal table that applications can query to determine the rows changed since the last data sync. The latest data is then fetched from the database table for the changed row.
Change Data Capture
Change Data Capture (CDC) obtains row-level changes by reading the SQL Server transaction logs. It records inserts, updates, and deletes along with before and after values, preserving the exact order of changes. SQL Server stores all the objects and metadata required for these row-level changes in the cdc schema. When CDC is enabled for the database and its tables, SQL Server automatically creates a dedicated cdc schema and user in that database. Applications fetch data that has changed since the last sync from this schema and apply it to the target tables.
Note: Currently, Hevo does not support CDC for its Generic SQL Server variant.
Comparing Change Tracking and Change Data Capture
The following table compares how CT and CDC handle incremental changes in the SQL Server database tables:
| Feature | Change Tracking | Change Data Capture |
|---|---|---|
| Change detail | Tracks that a row was changed | Captures all row-level changes |
| Old and new values | Not available | Available |
| Change order | Not preserved | Preserved |
| Data source | Internal tracking metadata | Transaction log |
| Primary key | Cannot be enabled for tables without a primary key | Can be enabled, but SQL Server may use a non-unique index to capture data changes |
| Storage usage | Low | Higher due to the cdc schema |