Fabric DW from SQL server

Hi Si_7777,

First of all, I can understand the overwhelming nature of moving to Fabric. There's a lot of options and tradeoffs to them, meaning there's a lot to sift through. You're asking good questions, which is a great place to start. I'll see if I can help you sort through it.

As for moving a data warehouse workload to Fabric, the first (and preferred) option you'd want to consider is a Fabric warehouse. It's design is intended for SQL analytical workloads, focusing on processing large amounts of data with a distributed architecture. For dimensional models, I think it's a great option and supports a lot of the tools needed for it, such as tables, views, functions, and stored procedures.

Even being a great option, there are some things to be aware of that will be different. Here are some of the things I've run into:

• As you noted, identity columns are not currently supported in Fabric warehouses, so you will have to create your own solution for making surrogate keys. Microsoft wrote an article with their recommended workaround for this case here: Generate unique identifiers in a warehouse table - Microsoft Fabric | Microsoft Learn. It takes a little more coding to get the same effect, but once you get it up and running, it's not that bad to work with.
• If you like using default constraints on columns, Fabric warehouses do not currently support these. You'll have to enforce your defaults within your code or pipelines. You can read more about them here: Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn
• ALTER TABLE statements have certain limitations, you can read about them here: ALTER TABLE (Transact-SQL) - SQL Server | Microsoft Learn
• Beyond those above, there are additional limitations for Fabric Warehouse tables here: Tables in data warehousing - Microsoft Fabric | Microsoft Learn
• Here's another article that talks about certain T-SQL things you can/cannot do: T-SQL surface area - Microsoft Fabric | Microsoft Learn

This may be a sizeable list of things to work around, but depending on the structure of your existing processes, there are a number of workarounds to get you where you want to go.

Regarding your note on the Fabric SQL databases and whether to migrate to that. In your situation, I would look at Fabric warehouse first before Fabric SQL databases. While a migration to Fabric SQL would be more straightforward because it's an Azure SQL DB engine, the intention of this environment is for OLTP workloads, which most times is not the best fit for a data warehousing scenario.

Hopefully this helps you determine your path forward. Once you've got your answer, be sure to accept it so others in the community can learn from what's shared.

Best of luck on planning a possible migration!

Thank you so much for taking the time to answer me.

It does help for me to decide Fabric DW is the correct way forward despite the steep learning curve.  Thank you again. 🙂