# 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](../lessons-learned/platform-lessons.md) and [Terraform and DABs ownership boundaries](../best-practices/terraform-vs-dabs.md). 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. ## Sources - https://www.databricks.com/blog/introducing-lakebridge-free-open-data-migration-databricks-sql - https://databrickslabs.github.io/lakebridge/docs/overview/ - https://github.com/databrickslabs/lakebridge - https://databrickslabs.github.io/lakebridge/docs/transpile/pluggable_transpilers/bladebridge/ - https://www.databricks.com/blog/welcoming-bladebridge-databricks-accelerating-data-warehouse-migrations-lakehouse - https://www.databricks.com/blog/announcing-sql-server-connector-lakeflow-connect-now-generally-available - https://www.databricks.com/blog/lakeflow-connect-efficient-and-easy-data-ingestion-using-sql-server-connector - https://learn.microsoft.com/en-us/azure/databricks/ingestion/lakeflow-connect/ - https://learn.microsoft.com/en-us/azure/databricks/ingestion/lakeflow-connect/sql-server-pipeline - https://learn.microsoft.com/en-us/azure/databricks/ingestion/lakeflow-connect/sql-server-source-setup - https://learn.microsoft.com/en-us/azure/databricks/ingestion/lakeflow-connect/sql-server-limits - https://learn.microsoft.com/en-us/azure/databricks/ingestion/lakeflow-connect/sql-server-concepts - https://learn.microsoft.com/en-us/azure/databricks/ingestion/lakeflow-connect/cdc-overview - https://learn.microsoft.com/en-us/azure/databricks/ingestion/lakeflow-connect/query-based-overview - https://www.databricks.com/blog/introducing-sql-stored-procedures-databricks - https://www.databricks.com/blog/introducing-sql-scripting-support-databricks-part-1 - https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-scripting - https://learn.microsoft.com/en-us/azure/databricks/ldp/concepts/ - https://docs.databricks.com/aws/en/ldp/best-practices - https://www.databricks.com/product/data-engineering/lakeflow-declarative-pipelines - https://learn.microsoft.com/en-us/azure/databricks/jobs/ - https://www.databricks.com/blog/whats-new-lakeflow-jobs-provides-more-efficient-data-orchestration - https://learn.microsoft.com/en-us/azure/databricks/query-federation/sql-server - https://www.databricks.com/blog/navigating-sql-server-databricks-migration-tips-seamless-transition