Migrate with the Fabric Migration Assistant for Data Warehouse

Applies to: ✅ Warehouse in Microsoft Fabric

The Fabric Migration Assistant is a migration experience to copy SQL pools in Azure Synapse Analytics seamlessly into Microsoft Fabric Data Warehouse.

This guide walks you through the steps to migrate from an Azure Synapse Analytics dedicated SQL pool to Fabric warehouse using a DACPAC file.

Tip

For more information on the Migration Assistant's features and capabilities, see Fabric Migration Assistant for Data Warehouse.

For more information on strategy and planning your migration, see Migration​ planning: ​Azure Synapse Analytics dedicated SQL pools to Fabric Data Warehouse.

Prerequisites

Before you begin, make sure you have the following ready:

• A Fabric workspace with an active capacity or trial capacity.
• Create a workspace or select an existing workspace you want to migrate into. The Migration Assistant will create a new warehouse for you.
• DACPAC file extracted from Azure Synapse Analytics dedicated SQL pool. A DACPAC (data-tier application package) file is built from SQL database projects and contains the metadata of database objects, including the schema of tables, views, stored procedures, functions, and more.
  • To create a DAC in Visual Studio 2022 with SQL Server Data Tools, see Extract a Data-tier Application (DAC) from an Azure Synapse dedicated SQL pool in Visual Studio 2022.
  • You can also use SDK-style database projects with VS Code or the SqlPackage command-line utility.

The AI-assisted migration features of the Migration Assistant to fix migration issues require Copilot to be activated:

• Your administrator needs to enable the tenant switch before you start using Copilot. For more information, see Copilot tenant settings.
• Your F2 or P1 capacity needs to be in one of the regions listed in Fabric region availability.
• If your tenant or capacity is outside the US or France, Copilot is disabled by default unless your Fabric tenant admin enables the Data sent to Azure OpenAI can be processed outside your tenant's geographic region, compliance boundary, or national cloud instance tenant setting in the Fabric Admin portal.
• Copilot in Microsoft Fabric isn't supported on trial SKUs. Only paid SKUs (F2 or higher, or P1 or higher) are supported at this time.
• For more information, see Overview of Copilot in Fabric and Power BI.

Copy metadata

1. Select the Migrate button on the item action deck.

   

2. Select the DACPAC file card from the Migrate to Fabric source menu.

   

3. On the Overview, review the information and select Next.

4. Select Choose file and upload the DACPAC file of your source data warehouse. When the upload is complete, select Next.

   

5. Provide the name of the new Fabric warehouse item you would like to migrate into. Select Next.

6. Review your inputs and select Migrate. A new warehouse item will be created, and the metadata migration begins.

   

   During this step, the Migration Assistant translates T-SQL metadata to supported T-SQL syntax in Fabric data warehouse. Once the metadata migration is complete, the Migration assistant opens. You can access the Migration Assistant at any time using the Migration button in the Home tab of the warehouse ribbon.

7. Review the metadata migration summary in the Migration Assistant. You'll see the count of migrated objects and the objects that need to be fixed before they can be migrated.

8. Select Show migrated objects to expand the section and see a list of objects that have been successfully migrated to your Fabric warehouse.

   

9. Review the Details to see the adjustments that were made to the objects.

   

   The State column indicates if the object's metadata was adjusted during the translation to be supported in Fabric Warehouse. For example, you might see that certain column datatypes or T-SQL language constructs are automatically converted to the ones that are supported in Fabric. The Details column shows the information about the adjustments that were made to the objects.

Fix problems using Migration Assistant

Some database object metadata might fail to migrate. Commonly, this is because the Migration Assistant couldn't translate the T-SQL metadata into those that are supported in a Fabric warehouse or the translated code failed to apply to T-SQL.

Let's fix these scripts with help from the Migration Assistant.

1. Select the Fix problems step in the Migration Assistant to see the scripts that failed to migrate.

   

2. Select a database object that failed to migrate. A new query opens under the Shared queries in the Explorer. This new query shows the metadata definition and the adjustments that were made to it as automatic comments added to the T-SQL code.

3. Review the comments in the beginning of the script to see the adjustments that were made to the script.

4. Review and fix the broken scripts using the error information and documentation.

5. To use Copilot for AI-powered assistance in fixing the errors, select Fix query errors in the Suggested action section. Copilot updates the script with suggestions. Mistakes can happen as Copilot uses AI, so verify code suggestions and make any adjustments you need.

   

6. Select Run to validate and create the object.

7. The next script to be fixed opens.

8. Continue to fix the rest of the scripts. You can choose to skip fixing scripts that you don't need during this step.

9. When all desired metadata is ready for migration, select the back button in the Fix problems pane to return the top-level view of the Migration assistant. Check the 2. Fix problems step in the Migration assistant.

Copy data using Migration Assistant

Copy data helps with migrating data used by the objects you migrate. You can use a Fabric Data Factory copy job to do it manually, or follow these steps for the copy job integration in the Migration assistant.

1. Select the Copy data step in the Migration Assistant.

2. Select Use a copy job button.

3. Assign a name to the new job, then select Create.

4. In Connect to data source page, provide Connection credentials the source Azure Synapse Analytics (SQL DW) warehouse. Select Next.

5. In the Choose data page, select the tables you want to migrate. The object metadata should already exist in the target warehouse. Select Next.

   

6. In the Choose data destination page, choose your new Fabric warehouse item from the OneLake catalog. Select Next.

7. In the Map to destination page, configure each table's column mappings. Select Next.

8. In the Copy job mode page, choose the copy mode. Choose a one-time full data copy (recommended for migration), or a continuous incremental copying. Select Next.

9. Review the job summary. Select Save + Run.

10. Once copy job is complete, check the 3. Copy data step in the Migration assistant. Select the back button on the top to return the top-level view of the Migration assistant.

Reroute connections

In the final step, the data loading/reporting platforms that are connected to your source need to be reconnected to your new Fabric warehouse.

1. Identify connections on your existing source warehouse.
   • For example, in Azure Synapse Analytics dedicated SQL pools, you can find session information including source application, who is connected in, where the connection is coming from, and if its using Microsoft Entra ID or SQL authentication:

   SELECT DISTINCT CASE 
            WHEN len(tt) = 0
                THEN app_name
            ELSE tt
            END AS application_name
        ,login_name
        ,ip_address
   FROM (
        SELECT DISTINCT app_name
            ,substring(client_id, 0, CHARINDEX(':', ISNULL(client_id, '0.0.0.0:123'))) AS ip_address
            ,login_name
            ,isnull(substring(app_name, 0, CHARINDEX('-', ISNULL(app_name, '-'))), 'h') AS tt
        FROM sys.dm_pdw_exec_sessions
        ) AS a;
   

2. Update the connections to your reporting platforms to point to your Fabric warehouse.
3. Test the Fabric warehouse with some reporting before rerouting. Perform comparison and data validation tests in your reporting platforms.
4. Update the connections for data loading (ETL/ELT) platforms to point to your Fabric warehouse.
   • For Power BI/Fabric pipelines:
     • Use the List Connections REST API to find connections to your old data source, the Azure Synapse Analytics dedicated SQL pool.
     • Update the connections to the new Fabric data warehouse using Manage Connections and Gateways experience under the Settings gear.
5. Once complete, check the Reroute connections step in the Migration assistant.

Congratulations! You're now ready to start using the warehouse.

Related content

• Fabric Migration Assistant for Data Warehouse