SQL Server to Databricks migration: T-SQL conversion, collation and CDC

SQL Server warehouses move to Databricks to put BI, data engineering and machine learning on one platform, on open Delta tables, without scaling a single database server. T-SQL is close enough to ANSI SQL that most queries move with small changes. The work is in procedures, Agent jobs, SSIS packages and one quiet difference: collation.

This is the approach we recommend.

1. Inventory and assess

List databases, tables, views, stored procedures, functions, triggers, SQL Server Agent jobs, SSIS packages, linked servers and the reports that read the warehouse. Query Store, DMVs and job history show what actually runs; retire the rest before converting it.

Run the Lakebridge analyzer over the T-SQL and SSIS code. It sizes the work by object type and complexity, which turns a guess into a plan.

2. Know the differences

SQL Server	Databricks	What to watch
Case-insensitive collation (common default)	UTF8_BINARY (case-sensitive) by default	Joins, filters and GROUP BY on text change. Use UTF8_LCASE where needed (Databricks Runtime 16.1+ and Databricks SQL).
NVARCHAR, VARCHAR(n)	STRING	Length limits are not enforced the same way; add checks if they matter.
DATETIME, DATETIME2, DATETIMEOFFSET	TIMESTAMP, TIMESTAMP_NTZ	Check precision and time zones.
IDENTITY	GENERATED ... AS IDENTITY	Values will differ; reconcile on business keys.
Clustered and non-clustered indexes	None; liquid clustering, statistics	Drop index logic; cluster large tables on common filters.
Stored procedures, TRY...CATCH	Databricks SQL scripting and procedures, or notebooks and Lakeflow pipelines	Simple procedures convert; heavy procedural logic is often better rewritten as pipelines.
#temp tables, table variables	Temporary views or intermediate Delta tables	Usually straightforward.
Triggers	Pipeline logic or change data feed	Move out of the database.
Linked servers	Lakehouse Federation or ingestion	Replace cross-server queries.
SQL Server Agent	Lakeflow Jobs	Rebuild schedules and dependencies.
SSIS	Lakeflow pipelines, notebooks or dbt	See SSIS to Databricks.
Logins, roles, row-level security	Unity Catalog grants, row filters, column masks	Re-create on groups, not users.

3. Ingest with CDC, convert the code

• Ingest: use the Lakeflow Connect SQL Server connector to land tables as Delta using change data capture or change tracking, so the lakehouse stays in sync during the parallel run. Query anything not yet migrated in place with Lakehouse Federation.
• Convert: run Lakebridge’s transpiler on views and procedures, then fix what it flags. Organise the result into bronze, silver and gold layers (see medallion architecture) rather than recreating one database schema.
• Govern: register everything in Unity Catalog from day one, with permissions on groups.

4. Reconcile and repoint reports

Compare every table by domain: row counts, totals and row-level differences matched on business keys. Lakebridge’s reconcile component automates much of this. Collation and timestamp precision explain most mismatches. Repoint Power BI, SSRS and other reports to Databricks SQL warehouses once owners sign off.

5. Retire SQL Server

Disable Agent jobs and SSIS packages on each domain’s cutover day, keep the source read-only for an agreed period, then decommission instances and licences as they empty. Check licence renewal dates early: they often set the real deadline.

The short version

Assess with Lakebridge, ingest with the Lakeflow Connect SQL Server connector, convert T-SQL with tooling and rewrite heavy procedures as pipelines, fix collation with UTF8_LCASE where needed, move Agent jobs to Lakeflow Jobs, reconcile on business keys and retire SQL Server domain by domain.

Other targets: SQL Server to Snowflake and SQL Server and SSIS to Microsoft Fabric. Browse all migration guides or compare platforms with the platform chooser.

Sources

• Microsoft SQL Server to Databricks migration guide, Databricks
• Lakebridge overview, Databricks Labs
• SQL Server connector for Lakeflow Connect and Lakehouse Federation for SQL Server, Databricks documentation
• Collation, Databricks SQL reference

Further reading from other practitioners

• Modernizing Microsoft SQL Server to Databricks, Fractal
• Navigating SQL Server to Databricks migration, MSRcosmos