Azure Synapse: manage, improve or migrate

Usually not. Microsoft Learn advises those starting a new data warehousing project to use Fabric Data Warehouse. If you’re starting from scratch, read about setting up an Azure data platform.

This page is about existing Synapse environments: managing and improving them, and preparing for a move as part of your data and AI platform.

They examine every part of the workspace: dedicated SQL pools, serverless SQL, pipelines and Spark pools with notebooks. They identify slow queries, the sources of costs and pipelines that frequently fail.

They then improve the table design and data loading processes, set up monitoring and document what needs to happen during a move.

Often, the table design is the cause. A table in a dedicated SQL pool is spread across sixty distributions. With hash distribution, a column determines where each row goes. If a lot of data ends up in just a few distributions, the query has to wait for the slowest one.

Microsoft recommends a column with many unique values and few NULLs, rather than a date column. You can’t change a distribution column: you have to rebuild the table using CREATE TABLE AS SELECT.

With a dedicated SQL pool, you pay separately for compute and storage. If you pause the pool, you stop paying for compute but still pay for storage. So if you don’t use it all the time, you can pause it outside working hours.

Serverless SQL has no reserved capacity: you pay for the data your queries process. Heavy, repeated queries on the lake are the first place to look.

For dedicated SQL pools, Microsoft provides the Fabric Migration Assistant. It transfers the schema using a DACPAC file or a direct connection, and helps you fix objects that don’t transfer automatically. You copy the data with a copy job, then test and compare the old and new environments.

According to Microsoft, T-SQL is not fully equivalent. You also replace SQL users with Microsoft Entra users.

Microsoft describes a move from Synapse Spark to Fabric as generally involving copying and adapting, not an in-place migration. A Spark Migration Assistant is available for notebooks, Spark jobs and pools, but you’ll need to adapt code that uses Synapse-specific features and connections yourself.

The move also needs to cover metadata from the Hive Metastore, data access and permissions. You then check that everything still works as before.

Yes. This is a natural option if your team mainly works with Spark and Python, or if machine learning needs to run on the same data. You would move notebooks and data loading processes to Databricks, then set up storage and permissions again.

We’re a Databricks Partner and Microsoft Partner, and we compare both routes. Read more about implementing Databricks.

We found no end date in the documentation on Microsoft Learn. The Synapse pages do, however, advise those starting a new data warehousing project to use Fabric Data Warehouse, and say that existing dedicated SQL pool workloads can be upgraded to Fabric.

We recommend making that decision based on your own environment, not on rumours. Read more about implementing Microsoft Fabric.