Edit

Share via

Migration​ planning: ​Azure Synapse Analytics dedicated SQL pools to Fabric Data Warehouse

Applies to: ✅ Warehouse in Microsoft Fabric

This article details the strategy, considerations, and methods of migration of data warehousing in Azure Synapse Analytics dedicated SQL pools to Microsoft Fabric Warehouse.

Tip

An automated experience for migration from Azure Synapse Analytics dedicated SQL pools is available using the Fabric Migration Assistant for Data Warehouse. This article contains important strategic and planning information.

Migration introduction

As Microsoft introduced Microsoft Fabric, an all-in-one SaaS analytics solution for enterprises that offers a comprehensive suite of services, including Data Factory, Data Engineering, Data Warehousing, Data Science, Real-Time Intelligence, and Power BI.

This article focuses on options for schema (DDL) migration, database code (DML) migration, and data migration. Microsoft offers several options, and here we discuss each option in detail and provide guidance on which of these options you should consider for your scenario. This article uses the TPC-DS industry benchmark for illustration and performance testing. Your actual result might vary depending on many factors including type of data, data types, width of tables, data source latency, etc.

Prepare for migration

Carefully plan your migration project before you get started and ensure that your schema, code, and data are compatible with Fabric Warehouse. There are some limitations that you need to consider. Quantify the refactoring work of the incompatible items, as well as any other resources needed before the migration delivery.

Another key goal of planning is to adjust your design to ensure that your solution takes full advantage of the high query performance that Fabric Warehouse is designed to provide. Designing data warehouses for scale introduces unique design patterns, so traditional approaches aren't always the best. Review the performance guidelines, because although some design adjustments can be made after migration, making changes earlier in the process will save you time and effort. Migration from one technology/environment to another is always a major effort.

The following diagram depicts the Migration Lifecycle listing the major pillars consisting of Assess and Evaluate, Plan and Design, Migrate, Monitor and Govern, Optimize and Modernize pillars with the associated tasks in each pillar to plan and prepare for the smooth migration.

Diagram of the Migration Lifecycle.

Runbook for migration

Consider the following activities as a planning runbook for your migration from Synapse dedicated SQL pools to Fabric Warehouse.

  1. Assess and Evaluate
    1. Identify objectives and motivations. Establish clear desired outcomes.
    2. Discovery, assess, and baseline the existing architecture.
    3. Identify key stakeholders and sponsors.
    4. Define the scope of what is to be migrated.
      1. Start small and simple, prepare for multiple small migrations.
      2. Begin to monitor and document all stages of the process.
      3. Build inventory of data and processes for migration.
      4. Define data model changes (if any).
      5. Set up the Fabric Workspace.
    5. What is your skillset/preference?
      1. Automate wherever possible.
      2. Use Azure built-in tools and features to reduce migration effort.
    6. Train staff early on the new platform.
      1. Identify upskilling needs and training assets, including Microsoft Learn.
  2. Plan and Design
    1. Define the desired architecture.
    2. Select the method/tools for the migration to accomplish the following tasks:
      1. Data extraction from the source.
      2. Schema (DDL) conversion, including metadata for tables and views
      3. Data ingestion, including historical data.
        1. If necessary, re-engineer the data model using new platform performance and scalability.
      4. Database code (DML) migration.
        1. Migrate or refactor stored procedures and business processes.
    3. Inventory and extract the security features and object permissions from the source.
    4. Design and plan to replace/modify existing ETL/ELT processes for incremental load.
      1. Create parallel ETL/ELT processes to the new environment.
    5. Prepare a detailed migration plan.
      1. Map current state to new desired state.
  3. Migrate
    1. Perform schema, data, code migration.
      1. Data extraction from the source.
      2. Schema (DDL) conversion
      3. Data ingestion
      4. Database code (DML) migration.
    2. If necessary, scale the dedicated SQL pool resources up temporarily to aid speed of migration.
    3. Apply security and permissions.
    4. Migrate existing ETL/ELT processes for incremental load.
      1. Migrate or refactor ETL/ELT incremental load processes.
      2. Test and compare parallel increment load processes.
    5. Adapt detail migration plan as necessary.
  4. Monitor and Govern
    1. Run in parallel, compare against your source environment.
      1. Test applications, business intelligence platforms, and query tools.
      2. Benchmark and optimize query performance.
      3. Monitor and manage cost, security, and performance.
    2. Governance benchmark and assessment.
  5. Optimize and Modernize
    1. When the business is comfortable, transition applications and primary reporting platforms to Fabric.
      1. Scale resources up/down as workload shifts from Azure Synapse Analytics to Microsoft Fabric.
      2. Build a repeatable template from the experience gained for future migrations. Iterate.
      3. Identify opportunities for cost optimization, security, scalability, and operational excellence
      4. Identify opportunities to modernize your data estate with the latest Fabric features.

'Lift and shift' or modernize?

In general, there are two types of migration scenarios, regardless of the purpose and scope of the planned migration: lift and shift as-is, or a phased approach that incorporates architectural and code changes.

Lift and shift

In a lift and shift migration, an existing data model is migrated with minor changes to the new Fabric Warehouse. This approach minimizes risk and migration time by reducing the new work needed to realize the benefits of migration.

Lift and shift migration is a good fit for these scenarios:

  • You have an existing environment with a small number of data marts to migrate.
  • You have an existing environment with data that's already in a well-designed star or snowflake schema.
  • You're under time and cost pressure to move to Fabric Warehouse.

In summary, this approach works well for those workloads that is optimized with your current Synapse dedicated SQL pools environment, and therefore doesn't require major changes in Fabric.

Modernize in a phased approach with architectural changes

If a legacy data warehouse has evolved over a long period of time, you might need to re-engineer it to maintain the required performance levels.

You might also want to redesign the architecture to take advantage of the new engines and features available in the Fabric Workspace.

Design differences: Synapse dedicated SQL pools and Fabric Warehouse

Consider the following Azure Synapse and Microsoft Fabric data warehousing differences, comparing dedicated SQL pools to the Fabric Warehouse.

Table considerations

When you migrate tables between different environments, typically only the raw data and the metadata physically migrate. Other database elements from the source system, such as indexes, usually aren't migrated because they might be unnecessary or implemented differently in the new environment.

Performance optimizations in the source environment, such as indexes, indicate where you might add performance optimization in a new environment, but now Fabric takes care of that automatically for you.

T-SQL considerations

There are several Data Manipulation Language (DML) syntax differences to be aware of. Refer to T-SQL surface area in Fabric Data Warehouse. Consider also a code assessment when choosing method(s) of migration for the database code (DML).

Depending on the parity differences at the time of the migration, you might need to rewrite parts of your T-SQL DML code.

Data type mapping differences

There are several data type differences in Fabric Warehouse. For more information, see Data types in Microsoft Fabric.

The following table provides the mapping of supported data types from Synapse dedicated SQL pools to Fabric Warehouse.

Synapse dedicated SQL pools Fabric Warehouse
money decimal(19,4)
smallmoney decimal(10,4)
smalldatetime datetime2
datetime datetime2
nchar char
nvarchar varchar
tinyint smallint
binary varbinary
datetimeoffset* datetime2

* Datetime2 does not store the extra time zone offset information that is stored in. Since the datetimeoffset data type is not currently supported in Fabric Warehouse, the time zone offset data would need to be extracted into a separate column.

Tip

Ready to migrate?

To get started with an automated migration experience, see Fabric Migration Assistant for Data Warehouse.

For more manual migration steps and detail, see Migration​ methods for ​Azure Synapse Analytics dedicated SQL pools to Fabric Data Warehouse.

Related content