Fabric DW from SQL server

[Si_7777]

Hi

Need to some general advice direction please.

We have a Data Warehouse running on SQL server.  I have been tasked with ‘moving’ our current solution to the cloud and I assumed Fabric Data Warehousing was the way forward.  We have dimensions and facts currently using SSIS for ETL into Staging and Data Warehouse facts (Change tracked tables). 

I would not say we want to copy the existing solution like-for-like into the cloud we did an Azure SQL server test many years ago but at the time was cost prohibitive.  We do allot of reporting in SSRS and are looking to leverage Power BI.

A few people have mentioned Fabric SQL databases but like I said I don’t necessarily want to recreate what we have, and I assumed Fabric Data Warehousing was the way forward.  I need to understand the options for Identity and surrogate keys (previous post).  As anyone else moved from onsite SQL server to Fabric Data Warehouse solution – are there any key points I need to be aware of.

Sorry for the ramble feeling little overwhelmed.

[DataBard · Accepted solution]

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!

[KonradZawel]

Migrating to Fabric DW from SQL Server is a significant step, and understanding the key differences, especially in terms of T-SQL and architecture, is critical. Many people face questions about compatibility and best practices for migration.

For anyone wanting to brush up on SQL Server Data Warehouse fundamentals or looking for practical tips on building one, this article will be very useful

[SQL_Data_101]

In SQL, there are several tasks we typically perform, such as analyzing execution plans, creating or updating indexes, identifying and terminating blocking queries, and monitoring resource usage. However, I'm wondering how these tasks are handled within Microsoft Fabric.

Specifically:

How do you check the execution plan and optimize queries without using indexes?
How can you identify which queries are blocking others or consuming excessive resources?
How do you manage session resources, such as killing sessions that use too much?
Is there a way to monitor schema or table capacity usage in Fabric?
Is there an alternative to materialized views for pre-computing and storing query results for performance optimization in Fabric?
How can I replicate the functionality of synonyms in Fabric, or how should I handle object references across different databases or schemas?
What are the recommended alternatives to temporary tables for session-based or short-lived data storage in Fabric?
How can I monitor schema or table-level capacity usage in Fabric to track resource consumption?
Actually looking for Daiyly activities of DBA

[DataBard · Accepted solution]

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!

[Si_7777 · Accepted solution]

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. 🙂

[DataBard]

Glad it helps!

If this is your answer, please 'Accept' the answer so others know that the question has been answered!