Fabric Connectivity Series Part #3 - Inbound SQL DW connections on Workspace Private Endpoints
Design secure, private connectivity to Fabric SQL endpoints using workspace-level private links. In this step-by-step guide, learn how to deploy the required resources, and validate end-to-end private connectivity for Data Warehouse workloads.An Overview of Lakehouses and Data Warehouses
Unlocking the Future of Data: Lakehouses vs. Data Warehouses In today’s data-driven world, choosing the right architecture is crucial for turning information into insight. Are traditional data warehouses still the gold standard, or are modern lakehouses rewriting the rules? Dive into our latest article as we explore how these two powerful approaches stack up — and discover which one could transform your data strategy.
# Top 10 Anti-Patterns in Fabric Warehouse Production Code Part -2
You fixed the loops, batched the loads, and listed your columns explicitly. Good. But your Fabric Warehouse still has hidden cost leaks. In Part 2, we tackle the six anti-patterns that don't show up until your tables are large and your team is shipping fast: scalar UDFs that silently dodge Fabric's inlining engine, MERGE statements that join on the wrong column (and why case-sensitivity makes it worse), small-file fragmentation from over-partitioned lakehouse shortcuts — and the OPTIMIZE + VACUUM + V-Order trio that fixes it — plus three CI/CD habits (missing column lists, hardcoded workspace GUIDs, and DROP-recreate cycles) that quietly erase your statistics, your permissions, and your Monday morning peace of mind. Ten anti-patterns. Two posts. One rule: Fabric Warehouse is not SQL Server.Automating SQL Permissions Deployment in Microsoft Fabric Data Warehouse
Fabric Deployment Pipelines and Git integration currently don’t transfer SQL object-level permissions when moving a Warehouse from Dev to Prod. After seeing this come up in some customer cases, I put together two small scripts that help you identify permissions in your Dev environment and export them so you can reapply them in Prod. Sharing them here so others can benefit as well.Open Mirroring in Fabric: Mirror Any Data?
At Ignite November '24, Open Mirroring was announced. Up until this release, mirroring data into Fabric for downstream processing and analytics had been focused on a handful of core data technologies such as Azure SQL Database. Open Mirroring now increases the possibily of any data source being mirrored into Fabric. In this article we'll go through the steps necessary to create an Open Mirroring database and then work through how data can be inserted, updated, and deleted.Fabric Connectivity Series Part #2 - Inbound SQL DW connections on Private Endpoints - Step by Step
Content This is a sequence for a series of connectivity posts Fabric Connectivity Series Part #1 - Inbound SQL DW connections on Public Endpoints Introduction You can use private links to provide secure access for data traffic in Fabric. Azure Private Link and Azure Networking private endpoints are used to send data traffic privately using Microsoft's backbone network infrastructure instead of going across the internet. Using private endpoints with Fabric provides the following benefits: Restrict traffic from the internet to Fabric and route it through the Microsoft backbone network. Ensure only authorized client machines can access Fabric. Comply with regulatory and compliance requirements that mandate private access to your data and analytics services. Scope of this post How to create Fabric Private Endpoint (Step-By-Step) How to configure VM, using hosts file to be able to connect private to Fabric DW Not on scope of this post Main focus is DW / SQL Endpoint private connections. Others endpoint will not be covered here Will dedicated other post just for DNS name resolution Routing / NSG / Firewall Before you go forward, I suggest you read carefully this documentation on explanation, considerations and limitations ( Private links for secure access to Fabric) I created image below to be more visual on how Private Endpoint works Some important thing you will notice Public internet clients going directly to Fabric FQDN resolving to public IP will fail Personal computer going directly to internet, Or a corp machine Power BI Service (Running from cloud). Even though part of your tenant. The machine that act as client is not part of YOUR network. You will need OnPrem Data Gateway or VNET Data gateway to be able to access it private Access from other Azure VNETs need to be enabled using Azure VNET peering or VPN between azure networks. Lot's of customers use Hub-Spoke architecture Client inside same VNET usually allowed by defaut, but good to double check Important security note: A private endpoint is a single directional technology that lets clients initiate connections to a given service but doesn't allow the service to initiate a connection into the customer network. You will need OnPrem Data Gateway or VNET Data gateway to be able to access it private Setup Will follow steps documented at Set up and use private links Step 1 - Setup Private Endpoint on Fabric Go to Fabric (https://app.fabric.microsoft.com) Config > Admin Portal On Tenant Setting you will find Azure Private Link Do not block public internet access before PE is completely set and tested Step 2 - Setup Private Link Service (PLS) for PowerBI on Azure Portal You need to start from Create a Resource > Custom deployment Select Build you own template in the editor Use template below <resource-name> is the name you choose for the Fabric resource. (Name for resource to be created. Ex: FabricPLS) <tenant-object-id>. See How to find your Microsoft Entra tenant ID. { "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#", "contentVersion": "1.0.0.0", "parameters": {}, "resources": [ { "type":"Microsoft.PowerBI/privateLinkServicesForPowerBI", "apiVersion": "2020-06-01", "name" : "<resource-name>", "location": "global", "properties" : { "tenantId": "<tenant-object-id>" } } ] } Save the template Select Resource Group and Region and create resource Step 3 - Create Private Endpoint (PE) Create new resource > Private Endpoint Select Name like FabricPE and region Select Resource Type and Resource (PLS created on Step 2) Select what network and subnet will be placed this Private endpoint Keep default (Yes) to integrate names with Private DNS Zones. This will be needed for automatic name resolution (Will be explained later) Finish deployment Step 4 - Name resolution DNS is a complex topic and I can dedicate another post for more details on that If you access you Fabric Private Endpoint > DNS configuration you will find 2 entries that you need. On my scenario IP (10.0.2.7) assigned to "mwc-global.pbidedicated.windows.net" this represents your FQDN xxxx-xxxx.datawarehouse.fabric.microsoft.com IP (10.0.2.9) assigned to "xxxx.pbidedicated.windows.net" this it the TDS (redirected connection) If you test name resolution from any public network, name still resolvable to public IP nslookup xxxx-xxxxx.datawarehouse.fabric.microsoft.com So you or network team / DNS team need to get this resolved to private, where we have some options Hosts File: Workaround on client machine with fixed entry on Hosts File (Good for quick test) Azure DNS Custom DNS For simplification: Using Host file for this test. I'm ignoring whatever DNS answer and forcing name resolution See below some tests done on my test VM 1 - NSLookup only check DNS request. It IGNORE hosts file entry 2 - We can see DNS Server used 10.0.0.4 3 - Public IP DNS see 20.150.161.150 4 - Test-NetConnection try to make real connection 5 - Remote address is different (HOSTs file entry) 6 - Hosts File 7 - Forcing IPs to specific FQDN and redirect name Step 5 - Testing Connection Testing using SSMS + Wireshark monitoring client hello packages with filter below tls.handshake.extensions_server_name contains "fabric" or tls.handshake.extensions_server_name contains "pbi" We can see from network trace the 2 connections to Fabric went all private Step 6 - Next steps ⚠️ At this point we only used HOSTs file solution. Only fix TSQL connection, you need to make sure all endpoints are resolving to private Will be shared on next posts for the series How to setup Azure DNS / Custom DNS with Conditional Forwarder How to create routes, and check NSGsThe Importance of New Case-Insensitive Collation in Fabric Warehouse
The Fabric Warehouse service, launched in summer 2023, simplifies end-to-end data and analytics solutions. This T-SQL based platform provides SQL developers with a dedicated Data Warehousing environment featuring auto-scale and fault-tolerance capabilities. It uses the Parquet file format and Delta Lake, which offer new possibilities in Data Warehousing. This blog post will discuss the new case insensitive collation feature.
