Skip to content

SQL Server Streaming

DataForge supports streaming ingestion for SQL Server tables using either SQL Server Change Data Capture (CDC) or SQL Server Change Tracking. Row-level changes can land in your lakehouse within seconds of occurring. Three areas require configuration before streaming sources are active: SQL Server itself, a Databricks Serverless SQL Warehouse for stream processing, and DataForge.

SQL Server Configuration

Choose the SQL Server tracking method before creating stream sources:

  • Change Tracking is the default option for newly created SQL Server stream sources. It tracks changed rows by primary key and lets DataForge read the current row values from the source table.
  • CDC uses SQL Server CDC change tables and log sequence numbers (LSNs). Use this option when the source database and tables are configured for CDC.

Change Tracking

Follow Microsoft instructions to enable Change Tracking: https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server?view=sql-server-2017

For Change Tracking streams:

  1. Enable snapshot isolation on the source database.
  2. Enable Change Tracking on the source database.
  3. Enable Change Tracking on each source table.
  4. Confirm each table has a stable primary key.
  5. Set a Change Tracking retention period long enough for the expected maximum agent downtime.

Example SQL Server setup:

ALTER DATABASE [DatabaseName] SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE [DatabaseName]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE [SchemaName].[TableName]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);

DataForge currently uses row-level Change Tracking. Column-level tracking is not required. If the Change Tracking retention window expires before the agent can process a saved version, DataForge automatically reinitializes the stream with a full reload.

CDC

For CDC streams, CDC must be enabled at both the database and table level before DataForge can stream from a table. Follow Microsoft instructions to enable CDC: https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-ver17

Databricks Configuration

Streaming ingestion requires a dedicated Databricks SQL Warehouse. We recommend starting with a serverless size 2XS instance. Set the Max Scale on the warehouse based on the number of tables you plan to stream and your target latency. A useful rule of thumb:

Max serverless instance scale = MAX( {# of tables} / {target latency in seconds}, max stream threads / 8 )

If Max Scale is set too low, actual latency per replicated source will exceed your target. Monitor utilization and adjust as needed.

DataForge Configuration

Once your SQL Server and SQL Warehouse are ready, configure the agent and sources in DataForge:

  • Configure the agent connection to the SQL Server database by populating the SQL Warehouse http path in the Agent parameters.

You can create stream sources two ways. The faster option is from the connection metadata tab: select your tables, click Create Sources, and choose Stream as the source type. Alternatively, create a new source manually and set Processing Type to Stream. DataForge will automatically update the refresh type and key columns based on the SQL Server table metadata. Enter the full table name in schema.table format in the source query field. Once the source is saved, click Start Stream to start streaming.

For SQL Server stream sources, set Tracking Method to one of:

  • change_tracking for SQL Server Change Tracking.
  • cdc for SQL Server Change Data Capture.

When creating sources from the connection metadata tab, DataForge defaults SQL Server stream sources to Change Tracking. The source query must be a table name, not a SELECT statement. Use schema.table or [schema].[table] format.

Initial Load

Stream sources automatically perform a full initial load the first time the ingestion stream starts. For very large tables (10M+ rows), the initial load can take a long time. In those cases, we recommend performing the initial load in batch mode first and then switching to streaming:

  1. Create a batch source for the table.
  2. Enable the Save Stream Watermark ingestion parameter. This records the current CDC LSN or Change Tracking version so the source can switch to incremental streaming after the initial load completes.
  3. Run the first ingestion in batch mode.
  4. Change the source Processing Type to Stream.
  5. In the source query field, remove the SELECT ... portion, leaving only the schema and table name in [schema].[table] format.
  6. Save and click Start Stream.
  7. Check the process logs to confirm the stream started in incremental mode.

For Change Tracking, the saved watermark is captured before the batch load starts. Rows changed during the batch load may be seen again by the first incremental stream interval, but the keyed merge process handles those duplicate rows.

Monitoring

Open the Agent page and navigate to the Monitoring tab to observe real-time CPU and RAM utilization. Adjust your Max Batch Threads and Max Stream Threads settings to keep utilization below 70-80%. If you see latency creeping above your target, revisit the SQL Warehouse Max Scale setting. Any change to the Agent parameters requires you to restart the agent before the changes are in effect.