SQL Server to Databricks migration

azure16 min read

Databricks can take over the reporting and data processing work you run on SQL Server. It has tools to assess the workload, convert code, and compare the results. But a working migration also needs the logic inside your stored procedures, SSIS packages, and scheduled jobs.

Start by deciding what actually needs to move. An order-entry application can keep its SQL Server database while its reports move to Azure Databricks. You can finish that analytics migration without replacing the application or shutting down its database.

Updated September 25, 2026.

Start with one report people actually use#

Consider a sales report built from SQL Server tables, several stored procedures, and a nightly SSIS package. Move that whole path together so you can compare the old and new reports. That gives you a useful first project and a better estimate for the rest.

Work backward from the report to its tables and jobs. Find out who owns each part and what else depends on it. Include application writes, linked servers, service accounts, and external systems in that picture. A small procedure with a hidden application dependency can require more work than a large, independent table.

The cost comparison needs to cover the whole workload. Compare the existing licenses and infrastructure with Databricks compute, storage, ingestion, networking, and maintenance. A continuous ingestion gateway belongs in that calculation, even when the reporting warehouse is idle. Warehouse auto-stop uses minutes, not seconds. Serverless warehouses default to ten minutes; the minimum is five through the UI or one through the API.

Migration is one option for an overloaded SQL Server. Read-scale replicas can also offload analytical queries. The right choice depends on the workload and the platform you want to operate.

Support deadlines can influence that choice. SQL Server 2016 extended support ended on July 14, 2026. Eligible Extended Security Updates provide critical security updates through July 17, 2029, without restoring normal product support.

Where Lakebridge and BladeBridge fit#

Lakebridge is the Databricks Labs migration toolkit. BladeBridge is one of the converters within it. The similar names can make them sound like competing products.

Lakebridge's Profiler examines the live database workload. Its Analyzer reads exported code and reports complexity. Use those results to find the procedures and packages that need a closer look. For conversion, the Lakebridge tool guide recommends Morpheus for SQL Server SQL and BladeBridge for SSIS and other ETL sources. Switch is an experimental, model-based converter with API token costs. The SQL Server dialect in the CLI is --source-dialect mssql.

There is also a separate agentic code converter in the Databricks workspace. It uses Genie Code to convert T-SQL and other dialects. It is in Beta and requires administrator enablement. Its documented limits are 300 files per batch and 1,000 lines per script.

Try these tools on a few of your actual procedures before you estimate the savings. Include the joins, temporary tables, and error handling that appear in the real workload. Then compare the results with SQL Server. Code that runs can still produce the wrong sales total.

Lakebridge is a Labs project without a formal Databricks support SLA. Decide who will investigate conversion problems before you depend on it for the project.

SSIS needs a closer look#

An SSIS package can contain much more than a data copy. Its branches, retries, file operations, and embedded code may all affect the final result. Ten simple packages can be less work than one package full of custom code.

BladeBridge can convert SSIS packages to experimental Databricks notebooks. Its documented limitations require Python rewrites for C# or Visual Basic bodies in Script Tasks and Script Components. Inspect those bodies during assessment so they appear in the estimate.

Declarative pipelines and Lakeflow Jobs are candidate targets for the data transformations and orchestration. Keep the behavior people rely on, including retries and actions outside the database. A fuzzy lookup, for example, needs its own matching tests; an ordinary join does not reproduce it.

Decide how the data will stay current#

The first data copy and the changes after it need a coordinated plan. Lakeflow Connect offers both change-based and query-based ingestion for SQL Server, with different operating requirements.

Approach What it does What to plan for
Lakeflow Connect with Change Tracking or CDC Captures source changes Continuous gateway, source retention, permissions, and recovery
Integrated CDC pipeline Extracts and applies CDC changes in one scheduled pipeline Account-team enablement, staging volume, retention, and compute choice
Query-based ingestion Queries changed rows on a schedule A suitable cursor, source query load, and explicit delete handling
Bulk copy Loads a historical snapshot Source load and a consistent handoff to incremental capture
Lakehouse Federation Queries SQL Server in place Read-only access, query pushdown, and load on the source

The SQL Server CT/CDC connector became generally available on August 26, 2025. Databricks recommends Change Tracking for tables with a primary key. CDC can capture tables without one. When both are enabled, the connector uses Change Tracking. The documented minima are SQL Server 2012 for Change Tracking and 2012 SP1 CU3 for CDC. CDC also requires Enterprise Edition before SQL Server 2016. Those compatibility minima do not establish vendor lifecycle support.

This connector uses a continuous classic-compute gateway and serverless ingestion. If an outage lasts beyond source retention, uncaptured changes can expire and force a full refresh. Routine source changes can also mean another full copy. Under the connector limits, a column rename or data-type change requires a full refresh of the affected table. With Change Tracking, a source node change requires a full refresh of every table in the pipeline. That includes an Availability Group failover. The connector reads from the primary SQL Server instance, so include that extra source load when you test recovery.

The integrated CDC pipeline offers a separate scheduled option without a continuous gateway. It supports classic or serverless compute and still uses a staging volume. Ask the Databricks account team to enable it. Check its source requirements and limits before you replace the gateway-based design. Compare schedule latency, source retention, recovery, and total cost with the standard connector.

Query-based ingestion needs no gateway or staging volume. It uses scheduled queries and a monotonically increasing cursor column. It captures the latest row state between runs, rather than every intermediate change. That difference matters if you need a complete change history. Hard-delete tracking is Beta and requires explicit configuration.

For a bulk load, one option is ADF copy to Parquet in ADLS, followed by Auto Loader. Auto Loader supports Parquet input, not Delta tables. A Delta-table path needs Delta reads instead. Spark JDBC reads offer another route. Parallel reads need partition settings and numPartitions; more connections can increase pressure on SQL Server. The lower and upper bounds divide the work rather than filter the source rows.

The first copy and the later changes need to meet at a known point in the source data. A gap can lose changes; an overlap can load them twice. Before combining a bulk load with a managed connector, confirm that the connector supports that initial-load method.

Lakehouse Federation can help you explore the source and compare results during the transition. It is read-only, so it cannot replace linked-server writes. Its cost and performance depend on the workload and SQL Server query pushdown.

SQL that looks familiar can behave differently#

A procedure can look correct and still treat a key, timestamp, or failed transaction differently. These differences deserve attention before you translate the rest of the code. The target platform also needs clear ownership and permissions. The related platform guide and Terraform and DABs guide cover that foundation.

Keys and identity columns#

Databricks primary-key, foreign-key, and unique constraints are informational. They do not reject duplicates or orphan records. A migration that depends on SQL Server enforcing those rules needs equivalent checks in its pipelines. NOT NULL and CHECK constraints enforce different rules.

Identity columns use BIGINT and disable concurrent transactions on the table. Their values need not be contiguous. GENERATED ALWAYS rejects supplied identity values; GENERATED BY DEFAULT permits them. If the migration only needs to preserve existing keys, an ordinary column may be enough.

Dates, text, and data types#

A local appointment time and a timestamp for a global event have different meanings. That is why DATETIME2 needs a deliberate choice between TIMESTAMP and TIMESTAMP_NTZ. The latter stores local date-time values without timezone conversion. Source values with more than six fractional-second digits also need precision tests.

Databricks SQL defaults to UTC, but its session timezone can change. Compare the source and target settings before you trust daily totals or date boundaries. SQL Server ROWVERSION, also called TIMESTAMP, is a binary concurrency token rather than a date. Its application behavior needs separate treatment.

Text comparisons deserve similar attention. Case, accents, locale, and trailing spaces can change joins and grouped results. A call to lower() does not reproduce every SQL Server collation rule. Explicit collations require Runtime 16.1 or later; trailing-space-insensitive support requires 16.2 or later.

Decimal precision, rounding, nulls, and string-to-number conversions also belong in the comparison tests. The same type name is not enough to prove the calculation works the same way.

Stored procedures and transactions#

Databricks supports SQL stored procedures in Unity Catalog on Databricks SQL and Runtime 17.0 or later. Its SQL scripting uses condition handlers in place of T-SQL TRY/CATCH.

Multi-table transactions on eligible managed Delta tables became generally available on July 22, 2026. That gives you a way to preserve atomic operations across tables, subject to the transaction requirements. Written tables must be Unity Catalog managed tables with catalog commits enabled.

BEGIN ATOMIC ... END runs on SQL warehouses, serverless compute, and clusters on Runtime 18.0 or later. Interactive BEGIN TRANSACTION, COMMIT, and ROLLBACK use SQL warehouses. The migration still needs tests for isolation, conflicts, retries, and the client driver.

Temporary state can also affect a procedure chain. Temporary tables are session scoped and support Databricks SQL and Runtime 18.1 or later, with compute restrictions. Their existence does not establish identical SQL Server #temp behavior across procedures and client sessions.

Updates and table layout#

MERGE needs unambiguous source matches. Duplicate-match evaluation differs between Runtime 15.4 LTS and 16.0 or later. For new change-processing pipelines, Databricks recommends AUTO CDC in place of APPLY CHANGES. Sequence values must be non-null, with unambiguous updates per key and sequence.

Dynamic SQL has a target in EXECUTE IMMEDIATE. Parameter markers handle values; IDENTIFIER handles dynamic object names where required. Those changes still need tests against the original queries.

SQL Server indexes do not transfer directly. Databricks recommends liquid clustering for new tables. It replaces partitioning and Z-order and cannot coexist with them on the same table. Use the report workload to assess the resulting query performance.

Prove the reports before you switch them#

Go back to that first sales report. Matching row counts help, but the report owner needs the same revenue totals, customer groupings, and date boundaries. Compare the systems at the same point in the source changes. Otherwise, normal updates can look like migration errors.

Lakebridge Reconcile provides schema, row, and value comparisons. The report owner still needs to check the calculations and exceptions people use to make decisions. The report also needs to refresh on time, work for its intended users, and recover after a failed run. Measure its performance and cost under the expected load.

A converted query can return the right numbers and still take too long. Test the joins and expressions in the actual report against a realistic amount of data. If the report slows down, the query profile helps you find where it spends time, including expensive joins, large scans, and memory spills. Use that evidence to decide what to change in the query or table layout.

Power BI needs its own test through the new Databricks connector. Authentication, Import or DirectQuery mode, private networking, and query folding can affect the result. A successful database query does not prove that the report refresh works.

Include existing SSRS reports in those tests, too. SSRS supports ODBC data sources, and Databricks provides ODBC connectivity for BI tools. That gives you a path to evaluate before you decide to replace the reports themselves. Parameter support depends on the ODBC driver. Test a report with its actual parameters and credentials, then check its scheduled subscriptions and exported output.

Move consumers before you retire the old system#

Some reports or applications may still need their data in SQL Server. In that case, the migration also needs a way to copy the processed results back. Azure Data Factory supports Delta Lake as a source and SQL Server as a destination. That copy becomes part of the report's refresh time, cost, and recovery tests.

If you write back through JDBC, check what happens inside the destination table as well as how fast rows arrive. The Microsoft JDBC driver has separate bulk-copy settings for insert triggers, identity values, and null values. With useBulkCopyForBatchInsert=true, driver version 12.10 or later supports those settings; each defaults to false. That means insert triggers do not fire, SQL Server assigns identity values, and destination defaults can replace nulls. Choose the settings that preserve the application's behavior, then test the load with its actual tables and triggers.

Move one workload at a time when its dependencies allow it. The first working report gives you evidence for the next and a smaller problem if you need to switch back. Agree who makes that decision and what would trigger it before the switch.

If you will retire the source database, stop its writes before the final data sync. Let Databricks receive the remaining changes, then compare the results before you switch the reports.

If the source still serves an application, keep its writes active. Choose a point in the change history that both systems have reached, then compare their data at that point.

Keep a tested way to switch back for the agreed support period. Retire each old job or server only after you know what depends on it and where that work now runs. The sales report can run on Databricks while the order-entry database continues to serve its application.

Sources#

The sources below cover the migration tools and product behavior discussed in this article. Inline links provide details for individual SQL features.

Migration tools#

Data movement#

SQL behavior and reports#

Release dates and support#

Practitioner experiences#

These discussions describe individual workloads and the problems their authors encountered. The product documentation above explains the supported behavior.

The practitioner notes collect more discussions from r/dataengineering and r/databricks.

Referenced by

Related topics and tags