SQL to Microsoft Fabric Migration: Beginner-Friendly Strategies for a Smooth Transition
Looking to unlock analytics on your SQL Server and seamlessly integrate it with Power BI for rich reporting and insights? Discover strategies in this post for migrating your SQL database to Microsoft Fabric, a unified platform that brings your data and analytics together effortlessly.
Previously, several methods for SQL database integration with Microsoft Fabric have been explored in detail. For reference, check out these posts:
- Microsoft Fabric: Mirroring SQLDB into Fabric
- Data Export and Lakehouse Creation with Microsoft Fabric
- Copying Data from Azure SQL Managed Instance to Microsoft Fabric DW using CETAS by Jovan Popovic
- A YouTube video by Guy in a Cube with Bob Ward, which covers SQL 2022’s support for data virtualization.
For streaming options, explore the Microsoft Fabric Event Streams documentation.
These resources serve as the foundation for this post as we dive into the available options:
- Notebooks and Pipelines – Azure SQL DB:
This one of the most basic and native options for migrating data inside of Fabric. As for Pipelines it is so easy you could even use the Assistant to do it.
From the Copy Assistant, for example you just nees to select SQL Server Database as Fig 1 – SQL, shows:
From the Microsoft Portal, copy the details of your Azure SQL Database as Fig 2-Azure Portal , shows:
Define the database that you will copy, given the right permissions to connect you should have something like this, Fig 3 – Connector:
Next steps you will define the destination inside of Fabric and the tables that will be copy. If you are trying to copy all the tables in one go ( please consider size of the table in latency before that), you have the options to copy everything and overwritten. If you want to do an increamental copy you will append the data and instead of copy the whole table. You need to define a logic, for that. Like for example, enable CDC(What is change data capture (CDC)? – SQL Server | Microsoft Learn), CDT(Work with change tracking – SQL Server | Microsoft Learn) or something custom that allows you to track changes in a query. Please note, as for the Pipelines you can use Query,Table or Stored Procedure option to make the copy of your data, Fig 4 – Copy.
As for the notebook the script is here(Data Export and Lakehouse Creation with Microsoft Fabric), the result will be a file in a parquet format as Fig 5 – File shows:
In case of any option you choose as for increamental copies you need to do some additional custom work to track the changes and sent over the Fabric.
Note: if you need to connect to onprem SQL Server using Microsoft Fabric and Pipelines please refer to this link: How to access on-premises data sources in Data Factory – Microsoft Fabric | Microsoft Learn
2. – CETAS – SQL MI, Synapse ( Serveless and DW):
CETAS is quite handy and easy way to migrate data into Microsoft Fabric it will relay in the shortcut to ADLS Gen2 as the source of the data migration.
Note: As for Synapse Migration you can delve into those links for more information microsoft/fabric-migration: Scripts and tooling to migrate DW and Spark workloads to Fabric. (github.com)
Migrating from Azure Synapse Spark to Fabric – Microsoft Fabric | Microsoft Learn
As I mentioned you have this link here for reference: Copying Data from Azure SQL Managed Instance to Microsoft Fabric DW using CETA
This is a reference how to recreate CETAS in a Serveless enviromnent: How to use CETAS on serverless SQL pool to improve performance and automatically recreate it – Microsoft Community Hub
Here is the simple example, this CETAS created using Synapse Serveless, on top of the ADLS Gen2 Account as Fig 6 – Serveless, shows:
3 – Mirror Azure SQL DB
Mentioned here in this post: Microsoft Fabric: Mirroring SQLDB into Fabric. It is one of the easiest way to migrate the data with the advantage tha takes care of the incremental changes for you, as Fig 7 – Mirror, shows. Relying on the Change Data Capture feature for tracking, but without persisting the data in the CDC tables, it is quite easy to configure and only supported at this point in time for Azure SQL Databases in the same tenant as Microsoft Fabric. Also still in preview by the time this post was written.
4 – SQL Server Streaming(Azure SQL Database, SQL VM, SQL MI and different options are supported)
This is an exciting and effective option for transferring data from sources like Azure SQL Database into Microsoft Fabric. However, it’s not limited to Azure SQL alone—here’s a list of currently supported options (in preview). Microsoft Fabric event streams overview – Microsoft Fabric | Microsoft Learn:
This option also relies on CDC; however, you’ll need to enable CDC yourself. Once enabled, CDC tables are created, and these tables will be used to send data changes to Microsoft Fabric.
Per documentation(Add Azure SQL Database CDC source to an eventstream – Microsoft Fabric | Microsoft Learn) enable CDC in your Azure SQL Database enviromnent as Fig 8 – CDC:
Once enabled use the preview option to create the event stream in Microsoft Fabric as Fig 9 – EventPreview, shows ( Microsoft Fabric Workspace -> New item -> Event stream):
Connect to the External Data Source and point to Azure SQL DB as Fig 10 – AzureSQLDB, shows:
Next enter the table name that you had previously enable CDC in your Azure SQL Database, as Fig 11 – Config, shows:
After completing this configuration, your SQL Server should be connected to the stream with the chosen format. I used JSON and then formatted it later using Transform Events. I set up the columns present in the JSON payload as follows, Fig 12 – Format and the Fig 13 – Transform events, shows:
After that define your destination, you can even create a new table to store that data. Fig 14 – Destinations, shows:
Publish this event and check the results. More details here: Edit and publish Microsoft Fabric eventstreams – Microsoft Fabric | Microsoft Learn. Fig 15 – Results:
Summary
The right option depends on your specific scenario, data volume, migration strategy, design, and other key considerations. This post provides an overview of the available options in Microsoft Fabric to help guide your evaluation.
