SQL Server to Databricks migration

A guide to the migration of SQL Server workloads to an Azure Databricks lakehouse. It covers T-SQL, SSIS, SQL Server Agent, stored procedures, and data movement.

Target architecture decisions (Unity Catalog layout, env + medallion catalogs, Terraform vs DABs boundary, identity, grants, networking) are not repeated here. Read Databricks platform lessons and Terraform and DABs ownership boundaries. Build the governed platform before you migrate workloads.

The sources were checked on 2026-06-24. Check the current product documentation before you plan a migration.

Why organizations migrate from SQL Server#

Most migrations have more than one driver:

Scale and concurrency. A single SQL Server box couples storage and compute. Once analytical queries, batch ETL, and reporting all contend for the same instance, the only lever is a bigger box. Databricks decouples storage (ADLS / Delta) from compute (warehouses, job clusters) so each scales independently.
Cost and licensing. Enterprise Edition core licensing plus SQL Agent plus SSIS runtime plus the SAN underneath is a fixed bill whether or not it is used. Databricks is consumption-based; idle warehouses stop in seconds.
Unifying analytics and ML. BI, data engineering, and ML on one governed copy of the data. No more extracting to a separate ML environment.
End of life pressure. SQL Server 2016 left extended support in July 2026. Estates pinned to old versions have no patch coverage, which forces a decision.
Talent. Engineers hired after ~2018 default to Python, SQL, and cloud-native orchestration. SSIS and TM1-style stacks are getting hard to staff.

Do not migrate for cost alone. A direct move of the same imperative ETL to Spark can keep the same problems. Use the migration to create governed data products.

Phase 1 — Assessment and discovery#

Build an inventory before you estimate the migration.

Inventory checklist:

[ ] Databases, schemas, table/row/byte counts, growth rate.
[ ] T-SQL objects: tables, views, stored procedures, user-defined functions (scalar / table-valued), triggers, synonyms, computed columns.
[ ] SSIS packages (.dtsx): control flow, data flows, Script Tasks (C#/VB.NET), package configurations, connection managers.
[ ] SQL Agent jobs: schedules, steps, dependencies, alerts, operators.
[ ] Linked servers, distributed queries, cross-database joins.
[ ] Security: logins, database roles, object-level grants, schemas as security boundaries.
[ ] External consumers: SSRS, Power BI, Excel ODBC, application connection strings. These determine cutover risk more than the data does.

Lakebridge is a free, open source Databricks Labs project. Its Analyzer scans metadata and legacy code. The output lists objects and assigns a complexity level. Use this output to start the inventory, and then verify it. It is a Labs project, provided AS-IS with no SLA. See the GA-vs-Labs note below.

Complexity scoring (drives the estimate):

Signal	Low	High
Stored proc logic	set-based SELECT/INSERT	cursors, dynamic SQL, temp-table chains
SSIS package	straight source-to-target data flow	heavy Script Tasks, fuzzy lookups, custom components
T-SQL functions	standard string/date/math	CLR functions, `STRING_AGG` edge cases, recursion
Transactions	none / append-only	multi-statement, multi-table, rollback-dependent
Coupling	self-contained	linked servers, cross-DB, app-embedded SQL

Use the number of SSIS Script Tasks as a complexity indicator. A package with many Script Tasks requires a review of its embedded C# or Visual Basic code. Estimate this work manually.

Phase 2 — Target architecture mapping#

SQL Server	Databricks / Delta equivalent
Database / schema	Unity Catalog catalog / schema
Table (heap / clustered)	Delta table (managed, UC)
View	View or materialized view
Indexes	None to port. Use liquid clustering / Z-order / partitioning
Stored procedure	SQL stored procedure (UC), SQL scripting, or notebook (SQL/PySpark)
Scalar / table-valued function	SQL UDF or built-in; rewrite CLR functions
SSIS package	Lakeflow Declarative Pipeline (rebuild) or Lakeflow Job
SQL Agent job + schedule	Lakeflow Job + trigger
T-SQL queries	Databricks SQL / Spark SQL
Linked server	Lakehouse Federation (query without copying)
`IDENTITY` column	`GENERATED ALWAYS AS IDENTITY`
Temp tables (`#t`)	Temp views, CTEs, or temporary tables (GA on DBSQL)
MERGE	`MERGE INTO` (Delta)

Use these current product names:

Lakeflow Connect — managed ingestion (the SQL Server connector lives here).
Lakeflow Declarative Pipelines — formerly Delta Live Tables; the SSIS rebuild target.
Lakeflow Jobs — formerly Databricks Workflows; the SQL Agent replacement.

Phase 3 — Schema and code conversion#

Data type mapping#

SQL Server	Databricks (Delta)	Note
`INT`, `BIGINT`, `SMALLINT`	`INT`, `BIGINT`, `SMALLINT`	direct
`DECIMAL(p,s)`, `NUMERIC`	`DECIMAL(p,s)`	direct
`MONEY`	`DECIMAL(19,4)`	no native money type
`VARCHAR`, `NVARCHAR`, `TEXT`	`STRING`	length not enforced; validate at load
`DATETIME`, `DATETIME2`	`TIMESTAMP`	watch precision and time zone
`DATE`	`DATE`	direct
`BIT`	`BOOLEAN`
`UNIQUEIDENTIFIER`	`STRING`	no native GUID type
`VARBINARY`, `IMAGE`	`BINARY`
`XML`	`STRING`	parse downstream
`ROWVERSION` / `TIMESTAMP`	drop / replace	concurrency token, no equivalent

The constructs that cause the most rework#

Identity columns. GENERATED ALWAYS AS IDENTITY works, but you must omit the identity column from the INSERT/MERGE column list or it errors. There is no direct SET IDENTITY_INSERT ON to force explicit values; if the old keys must be preserved (FK references), use GENERATED BY DEFAULT and load the values.
MERGE and upsert. Use MERGE INTO on Delta. For incremental CDC in a declarative pipeline, use APPLY CHANGES INTO or apply_changes(). These functions manage order, duplicates, late events, and SCD Type 1 or Type 2 changes.
Temp tables and proc chains. Procs that stage into #temp across many steps rewrite cleanly to CTEs, temp views, or temporary tables. The bigger job is flattening imperative row-by-row logic into set-based transforms.
Transactions. T-SQL BEGIN TRAN / COMMIT / ROLLBACK across multiple tables does not map directly. Delta gives ACID per table/statement; multi-table multi-statement transactions are still maturing on Databricks SQL (verify current status). Designs that depend on all-or-nothing across tables need rethinking, often as idempotent reprocessing rather than rollback.
Error handling. No TRY/CATCH / @@ERROR. Databricks SQL scripting uses SQL-standard condition handlers (DECLARE ... HANDLER, SQLEXCEPTION, NOT FOUND). Rewrite, do not transpile literally.
Functions without an equivalent. CLR functions, some FORMAT() culture behavior, and proprietary date math need manual replacement. Catch these in assessment, not at test time.
Collation and case sensitivity. SQL Server is commonly case-insensitive on string comparison and ordering. Spark SQL is case-sensitive on data by default. This silently changes join and GROUP BY results. Normalize (lower()) or apply a collation explicitly. Include this risk in every migration plan.
Dynamic SQL. sp_executesql / EXEC() becomes EXECUTE IMMEDIATE with IDENTIFIER(). Mechanical but easy to get subtly wrong.

Conversion tooling, GA vs Labs vs partner#

Be precise about support status in the migration plan.

Lakebridge (Databricks Labs): free, open source, the supported-by-community successor to BladeBridge (which Databricks acquired). Three transpilers: BladeBridge (deterministic, rule-based), Morpheus (next-gen), and Switch (LLM-powered, converts to notebooks). Run via databricks labs lakebridge transpile --source-dialect tsql .... SQL Server is a source for assessment, conversion, and reconciliation. It is a Labs project. Databricks supplies it as-is, with no service-level agreement. Do not describe it as a GA product.
Partner system integrators build on the same BladeBridge engine for large estates. This option can help when the migration volume exceeds the internal team's capacity.
Reality check on any converter. Rule-based transpilation is inconsistent on stored procedures, nested queries, and dialect edge cases. Budget manual rework. A realistic split is converter for the bulk of straightforward SQL, hand-rebuild for the complex procs and every Script Task.

Phase 4 — SSIS migration#

Do not try to "convert" SSIS package-for-package. Rebuild the intent.

Straight source-to-target data flows → Lakeflow Declarative Pipeline. Streaming tables for ingest/append, materialized views for transforms. The declarative model collapses what was hundreds of lines of SSIS + Spark glue.
Orchestration / control flow (sequence containers, precedence constraints, job steps) → Lakeflow Jobs with task dependencies, branching, and for each.
Script Tasks (C#/VB.NET) → rewrite as PySpark/Python in a notebook task. No tool does this for you. These dominate the manual effort; estimate them individually.
Lookups / fuzzy lookups / SCD wizard → joins and APPLY CHANGES INTO.

Tooling: Lakebridge's BladeBridge transpiler can convert some ETL/orchestration to Databricks notebooks and workflows, but coverage is uneven. Treat its SSIS output as a starting skeleton.

Phase 5 — Data movement#

Two distinct problems: the one-time historical backfill, and ongoing change capture.

Lakeflow Connect SQL Server connector (GA, Sept 2025). Fully managed, built-in CDC and Change Tracking. This is the default recommendation for ongoing ingestion. Key facts to design around:
Change Tracking for tables with a primary key (lighter on the source); CDC for tables without one. If both are enabled, the connector uses Change Tracking.
Requires an ingestion gateway on classic compute running continuously; the pipeline itself runs serverless. If the gateway stops, change logs can be truncated at the source and affected tables need a full refresh. Design the gateway as always-on.
Supports Azure SQL Database, Azure SQL Managed Instance, RDS SQL, SQL on VMs, and on-prem via ExpressRoute / Direct Connect.
Versions: Change Tracking needs SQL Server 2012+; CDC needs 2012 SP1 CU3+ (Enterprise Edition for pre-2016). Unity Catalog and serverless must be on.
Bulk one-time load. For very large history where you do not want CDC from day zero: ADF copy to Parquet/Delta in ADLS, then Auto Loader; or a JDBC read from a Spark job. JDBC bulk reads need partitioning (partitionColumn, bounds) or they single-thread and crush the source.
Lakehouse Federation. Zero-copy query of SQL Server in place. Excellent during transition: gives target-side read access without moving data, and is the cheapest way to compare source vs target during parallel runs.
Limit the number of services. If Lakeflow Connect covers the ingest, you may not need ADF at all. Only add it for the bulk backfill if Connect's full-refresh path is too slow for the history volume.

Phase 6 — Migration strategy#

Strangler / phased (default). Migrate one data product or subject area at a time, leave the rest on SQL Server, repoint consumers incrementally. Lowest risk, and it lets you prove the platform on something small first.
Big-bang. Only for small, self-contained estates with a hard cutover date and few consumers. Rarely the right call for an SSIS-heavy shop.
Dual-run. Keep SQL Server and Databricks producing the same outputs in parallel for a defined window. Non-negotiable for anything feeding finance or regulatory reporting.

Phase 7 — Validation and reconciliation#

Parity is a success criterion, not a QA afterthought. The migrations that silently produce wrong numbers are the ones that treated validation as a final step.

[ ] Row counts per table, source vs target.
[ ] Column-level checksums / hash aggregates on key columns.
[ ] Aggregate reconciliation: SUM/MIN/MAX/COUNT on numeric and date columns.
[ ] Business-metric reconciliation. Tie out the actual reports (revenue by month, active customers) the business already trusts, not just raw tables. These catch logic errors that row counts miss.
[ ] Null and default behavior, especially where SQL Server had NOT NULL + default and the rebuilt logic does not.
[ ] Collation-sensitive results (joins, distinct counts, sorts).

Lakebridge ships a Validator/Reconcile component for row and aggregate reconciliation. Use it for the mechanical layer; build the business-metric checks by hand with the report owners.

Phase 8 — Cutover and decommission#

[ ] Freeze schema changes on the source for the cutover window.
[ ] Final CDC catch-up, then stop writes to SQL Server.
[ ] Repoint consumers (Power BI, apps, ODBC) to Databricks SQL warehouses.
[ ] Run in read-only parallel for an agreed period before decommissioning.
[ ] Archive SQL Server (final backup retained per policy) before tearing it down.
[ ] Decommission SSIS runtime, SQL Agent jobs, and linked servers. Cancel the licenses — that saving is often the line item that justified the project.

Common migration risks#

Case sensitivity flips join and GROUP BY results silently. Catch it in validation.
Implicit type coercion in T-SQL is more permissive than Spark; VARCHAR-to-number comparisons that worked in SQL Server throw or return different rows.
GETDATE() is server-local; Spark current_timestamp() is UTC. Time-zone drift shifts daily-boundary aggregates by a day.
Identity gaps: do not assume continuous identity values survive a reload.
SSIS Script Tasks hide business logic the DTSX structure does not reveal; never scope them from package metadata.
Lakeflow Connect gateway stopping = dropped changes = full refresh. Treat it as always-on infrastructure.
ROWVERSION concurrency tokens have no equivalent; the app pattern that used them needs redesign, not translation.
Stored procs relying on multi-table transactional rollback need a different design (idempotent reprocessing), not a transpile.