SQL Server to Databricks Implementation Guide

Use this guide to take a SQL Server reporting or analytics project from a first table to an operational handover. DataForge's Outcrop agent runs inside your network and replicates table changes into governed hub tables in your own Databricks account. The DataForge UI currently calls Outcrop Agent.

Streaming replication supports SQL Server sources and targets Databricks. Snowflake deployments use batch ingestion. Start with one representative table and one report, then expand after you have measured correctness, freshness, and cost.

1. Agree on the outcome and owners

Before configuration, record:

• The report or analytics use case, source tables, stable keys, row counts, and expected change volume.
• A freshness objective measured from source commit to availability in the report, including the percentile and measurement window.
• The longest expected outage, initial-load window, and acceptable recovery time.
• A SQL Server DBA, Outcrop host/network owner, Databricks administrator, and reporting owner.

Use all currently supported versions of SQL Server, subject to edition-specific CDC availability and configuration requirements. Check Microsoft's SQL Server lifecycle and the feature matrix for your exact version and edition. End-of-life versions require a separate compatibility assessment; this guide does not establish support for them.

2. Choose CDC or Change Tracking

Decision	Change Tracking	Change Data Capture (CDC)
How changes are read	Changed primary keys identify rows; Outcrop reads their current values.	Outcrop reads SQL Server CDC change tables using log sequence numbers (LSNs).
Source setup	Enable snapshot isolation and database/table Change Tracking; a primary key is required.	Enable database/table CDC and keep capture and cleanup jobs healthy.
Keys	A stable primary key is required.	Keyed replication requires a primary key or unique index in the CDC configuration and net-change support. Without keys, detected changes trigger full reloads.
Retention	Retain change versions long enough to survive downtime and catch-up.	Retain captured changes long enough to survive downtime and catch-up.

New SQL Server stream sources default to Change Tracking. Choose the method explicitly with your DBA. Both paths replicate current table state; DataForge's keyed CDC path consumes net changes, so the destination is not an archive of every intermediate event or before-image. Independent table streams also do not establish a single transactionally consistent reporting snapshot across tables.

Microsoft explains the feature differences. Keep SQL Server CDC separate from DataForge's batch Capture Data Changes processing step when discussing the design.

3. Prepare connectivity and permissions

Install Outcrop on a host that can reach SQL Server. Use Java 21 and follow Installing Outcrop for supported hosts, service identity, and configuration. Source access uses SQL Server authentication. Allow the configured SQL Server port from the Outcrop host, plus outbound HTTPS to DataForge, authentication, cloud storage, and the Databricks workspace. Confirm DNS and certificate trust with the network owner.

Separate setup privileges from the runtime login:

• CDC setup: a DBA enables CDC at the database and table levels. Database enablement requires sysadmin; table enablement requires sysadmin or db_owner. For SQL Server, confirm SQL Server Agent and the applicable capture/cleanup jobs are running. For keyed replication, enable net changes with the intended key/index.
• CDC runtime: grant access to the source columns needed for the full load and all captured columns, plus membership in the capture instance's gating role when configured. Verify CDC metadata is visible to the runtime login.
• Change Tracking setup: a DBA enables snapshot isolation and Change Tracking at database and table levels, checks primary keys, and sets retention. Column-level tracking is not required by DataForge.
• Change Tracking runtime: grant SELECT on the replicated columns, including keys, and VIEW CHANGE TRACKING on each table. Confirm table metadata and tracking versions are visible.
• Databricks: use a service identity with warehouse use permission and access to the intended catalog/schema, hub tables, and views. Include the create/alter/write privileges required for initial setup and schema recovery; warehouse access alone is insufficient. Scope grants with the Databricks administrator to the DataForge deployment.

Use Microsoft's CDC enablement, CDC read permissions, and Change Tracking guidance for DBA procedures. Store credentials in the approved configuration flow; keep credentials and customer row values out of handover screenshots and logs.

4. Configure the warehouse and first stream

1. Provision a dedicated Databricks Serverless SQL Warehouse. A 2XS warehouse is a starting point to evaluate, not a capacity guarantee. Set scaling limits and budget with the Databricks owner.
2. Configure the warehouse HTTP path in the Outcrop parameters and configure the SQL Server connection. Apply required Outcrop parameter changes with a restart.
3. From connection metadata, select a representative table and choose Create Sources → Stream, or create a source with Processing Type: Stream.
4. Set Tracking Method to change_tracking or cdc. Enter the table name as schema.table or [schema].[table]; the streaming query is not a SELECT statement.
5. Save and select Start Stream. Verify the initial load completes, the expected keys/refresh type are detected, and incremental processing advances.

See SQL Server Streaming for parameter details and the warehouse cost tag. Record warehouse size and cluster scaling separately: size controls resources per cluster, while scaling controls concurrent cluster count. See Databricks warehouse settings.

5. Plan the initial load

The first stream run performs a full load. Estimate its source load, transfer volume, destination cost, and duration before onboarding large tables.

For a batch-first load, create a batch source, enable Save Stream Watermark, select the intended tracking method, and run the initial ingestion. After it completes, change Processing Type to Stream, replace the query with the table name, and start the stream. Confirm in the process logs that it resumes incrementally. Keep the saved watermark inside the retention window throughout onboarding.

For Change Tracking, the watermark is captured before the batch read. Rows changed during loading can be read again; the keyed merge handles repeats. Do not describe this as exactly-once event delivery. Follow the initial-load procedure and reconcile the resulting table.

6. Validate data and reporting freshness

Use an approved pilot table for controlled changes. Agree on permission before writing test records in a customer environment.

Check	Acceptance evidence
Initial load	Compare keys, counts, and representative values at an agreed reconciliation boundary.
Inserts and updates	Confirm new rows and final values reach the hub table and consuming report.
Deletes	Confirm physical source deletes remove matching hub rows. A soft-delete column is an ordinary update; agree how reports filter it.
Restart/resume	Confirm progress resumes from the watermark and final state reconciles.
Freshness	Record source commit and destination/report availability times; report p50, p95, maximum, failures, and the measurement window.
Cost	Record warehouse size, scaling, DBUs/billed usage, change volume, and any shared workloads over the same window.

Stream sources do not run rules, relations, or validations directly. To enrich replicated data, create a virtual output and a second, batch Loopback source. Include that batch schedule and BI refresh in the freshness measurement. See Rules and Enrichments.

7. Handle schema changes and retention expiry

Schema changes require a stream restart. After restart, DataForge automatically alters the destination schema and performs the required recovery or full reload. Schedule the restart and allow for source reads, warehouse work, and reconciliation before treating recovery as complete.

For CDC, newly added source columns are not automatically included in an existing SQL Server capture instance. Have the DBA update the capture configuration before restarting, and validate the capture-instance transition with DataForge. Do not assume a two-instance migration is supported automatically. Review dropped/renamed columns, type changes, key changes, and downstream report dependencies as explicit migration work. See Microsoft's CDC schema-change behavior.

When a CDC or Change Tracking watermark expires, DataForge reinitializes the stream with a full reload. Recovery restores current table state; it cannot reconstruct change history already removed by SQL Server cleanup. Set retention to cover outage, detection, initial loading, catch-up, and an operational margin. Monitor retention headroom and SQL Server capture health rather than relying on reloads as routine operation.

After recovery, check logs, progress timestamps, keys/counts, deletes, downstream reports, and cost. Investigate the outage cause before closing the incident.

8. Tune latency and cost, then hand over

Choose Cost, Balanced, or Latency with a target and worker limits; see Choosing a Streaming Strategy. These policies tune Outcrop scheduling and do not guarantee a Databricks bill or report freshness. A scheduled polling period is not measured end-to-end latency.

Monitor Outcrop heartbeat, CPU/RAM, registered active streams, successful source progress, errors/retries, and full reloads. In Databricks, inspect query queueing/execution, cluster scaling, and billing. Tune source query performance, Outcrop concurrency, warehouse capacity, and downstream schedules against representative load. See Databricks warehouse monitoring.

Before handover, record:

• Approved table inventory, tracking methods, keys, retention, and source/Databricks permission owners.
• Initial-load and recovery timings, schema-change/restart procedure, and incident escalation contacts.
• Alert thresholds for missing heartbeat/progress, failures, retention risk, freshness breaches, and cost.
• Credential rotation, service restart/update ownership, and rollback/reconciliation procedures.
• Measured correctness, report freshness and cost results, their dates, and who accepted them.

Need help scoping the first reporting outcome? Bring the table inventory, source versions, freshness objective, and Databricks constraints to a First Mile conversation. The pilot should establish project fit and acceptance evidence before a production rollout.