SQL Server to Databricks migration playbook

A practitioner's guide to moving a SQL Server estate (T-SQL, SSIS, SQL Agent, stored procedures) onto an Azure Databricks lakehouse.

Target architecture decisions (Unity Catalog layout, env + medallion catalogs, Terraform vs DABs boundary, identity, grants, networking) are not repeated here. They live in lessons-learned/platform-lessons.md and best-practices/terraform-vs-dabs.md. Land the platform first, then migrate onto it. The temptation to start writing conversion notebooks before the catalogs, groups, and policies exist is the same mistake called out there.

Verified against docs as of 2026-06-24. Product names move fast; re-check before planning a migration.

Why companies move off SQL Server#

Most engagements are driven by two or three of these at once, not one:

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.

If the only driver is "SQL Server is expensive," push back. A lift-and-shift that reproduces the same imperative ETL on Spark rarely pays off. The win comes from redesigning onto medallion + governed data products, not from porting line by line.

Phase 1 — Assessment and discovery#

You cannot scope or price the work without an inventory. Build it before quoting fixed price.

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.

Tooling: Lakebridge (Databricks Labs, free, open source) has an Analyzer that scans metadata and legacy code and produces multi-tab reports inventorying objects and classifying each by complexity (low / medium / complex / very complex). Use it to seed the inventory, but treat output as a draft, not truth. 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

Script Task density inside SSIS is the single most reliable complexity proxy. A .dtsx that is mostly Script Tasks cannot be scoped from its structure; someone has to read the embedded C#. Flag these for manual estimation and do not let a converter's "supported" claim hide them.

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)

Naming, for reference against the rest of the hub:

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 / upsert. MERGE INTO on Delta is the right tool. For incremental CDC loads inside a declarative pipeline, prefer APPLY CHANGES INTO / apply_changes(), which handle ordering, dedup, out-of-order events, and SCD Type 1 / Type 2 declaratively instead of hand-written MERGE.
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, and put it on the gotchas list for every engagement.
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 supported source for assessment, conversion, and reconciliation. It is a Labs project — AS-IS, no SLA, not a GA product. Do not promise a client Databricks-backed support on it.
Partner SIs (Accenture, Capgemini, Tredence, Celebal and others) build on the same BladeBridge engine for large estates. Relevant if the volume exceeds what one consultant can hand-convert.
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.
Resist service sprawl. 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. Same discipline as the rest of the hub.

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 gotchas#

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.