Showing posts with label endpoints. Show all posts
Showing posts with label endpoints. Show all posts

25 March 2025

🏭🗒️Microsoft Fabric: Security [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 25-Mar-2024

Microsoft Fabric Security
Microsoft Fabric Security [2]
[Microsoft Fabric] Security
  • {def} a comprehensive security framework designed for the Microsoft Fabric platform [1]
    • {goal} always on 
      • every interaction with Fabric is encrypted by default and authenticated using Microsoft Entra ID [1]
        • all communication between Fabric experiences travels through the Microsoft backbone internet [1]
        • data at rest is automatically stored encrypted [1]
        • support for extra security features [1]
          • ⇐ allow to regulate access to Fabric [1]
          • Private Links 
            • enable secure connectivity to Fabric by 
              • restricting access to the Fabric tenant from an Azure VPN
              • blocking all public access
            • ensures that only network traffic from that VNet is allowed to access Fabric features [1]
          • Entra Conditional Access 
        • the connection to data is protected by a firewall or a private network using trusted access [1]
          • access firewall enabled ADL Gen2 accounts securely [1]
            • can be limited to specific workspaces [1]
              • workspaces that have a workspace identity can securely access ADL Gen 2 accounts with public network access enabled, from selected virtual networks and IP addresses [1]
            • workspace identities can only be created in workspaces associated with a Fabric F SKU capacity [1]
        • helps users connect to services quickly and easily from any device and any network [1]
          • each request to connect to Fabric is authenticated with Microsoft Entra ID [1]
            • allows users to safely connect to Fabric from their corporate office, when working at home, or from a remote location [1]
        • {feature} Conditional Access
          • allows to secure access to Fabric on every connection by
            • defining a list of IPs for inbound connectivity to Fabric [1]
            • using MFA [1]
            • restricting traffic based on parameters such as country of origin or device type [1]
    • {goal} compliant
      • data sovereignty provided out-of-box with multi geo capacities [1]
      • support for a wide range of compliance standards [1]
      • Fabric services follow the SDL)
        • a set of strict security practices that support security assurance and compliance requirements [2]
        • helps developers build more secure software by reducing the number and severity of vulnerabilities in software, while reducing development cost [2]
    • {goal} governable
      • leverages a set of governance tools
        • data lineage
        • information protection labels
        • data loss prevention 
        • Purview integration 
    • configurable
      •  in accordance with organizational policies [1]
    • evolving 
      • new features and controls are added regularly [1]
  • {feature} managed private endpoints 
    • allow secure connections to data sources without exposing them to the public network or requiring complex network configurations [1]
      • e.g. as Azure SQL databases
  • {feature} managed virtual networks
    • virtual networks that are created and managed by Microsoft Fabric for each Fabric workspace [1]
    • provide network isolation for Fabric Spark workloads
      • the compute clusters are deployed in a dedicated network and are no longer part of the shared virtual network [1]
    • enable network security features
      • managed private endpoints
      • private link support
  • {feature} data gateway
    • allows to connect to on-premises data sources or a data source that might be protected by a firewall or a virtual network
    • {option} On-premises data gateway
      • acts as a bridge between on-premises data sources and Fabric 1[]
      • installed on a server within the network [1]
      • allows Fabric to connect to data sources through a secure channel without the need to open ports or make changes to the network [1]
    • {option} Virtual network (VNet) data gateway
      • allows to connect from Microsoft Cloud services to Azure data services within a VNet, without the need of an on-premises data gateway [1]
  • {feature} Azure service tags
    • allows to ingest data from data sources deployed in an Azure virtual network without the use of data gateways [1]
      • e.g. VMs, Azure SQL MI and REST APIs
    • can be used to get traffic from a virtual network or an Azure firewall
      • e.g. outbound traffic to Fabric so that a user on a VM can connect to Fabric SQL connection strings from SSMS, while blocked from accessing other public internet resources [1]
  • {feature} IP allow-lists
    • allows to enable an IP allow-list on organization's network to allow traffic to and from Fabric
    • useful for data sources that don't support service tags [1]
      • e.g. on-premises data sources
  • {feature} Telemetry
    • used to maintain performance and reliability of the Fabric platform [2]
    • the telemetry store is designed to be compliant with data and privacy regulations for customers in all regions where Fabric is available [2]
  • {process} authentication
    • relies on Microsoft Entra ID to authenticate users (or service principals) [2]
    • when authenticated, users receive access tokens from Microsoft Entra ID [2]
      • used to perform operations in the context of the user [2]
    • {feature} conditional access
      • ensures that tenants are secure by enforcing multifactor authentication [2]
        • allows only Microsoft Intune enrolled devices to access specific services [1] 
      • restricts user locations and IP ranges.
  • {process} authorization
    • all Fabric permissions are stored centrally by the metadata platform
      • Fabric services query the metadata platform on demand to retrieve authorization information and to authorize and validate user requests [2]
    • authorization information is sometimes encapsulated into signed tokens [2]
      • only issued by the back-end capacity platform [1]
      • include the access token, authorization information, and other metadata [1]
  • {concept} tenant metadata 
    • information about the tenant 
    • is stored in a metadata platform cluster to which the tenant is assigned
      • located in a single region that meets the data residency requirements of that region's geography [2]
      • include customer data 
      • customers can control where their workspaces are located
        • in the same geography as the metadata platform cluster
          • by explicitly assigning workspaces on capacities in that region [2]
          • by implicitly using Fabric Trial, Power BI Pro, or Power BI Premium Per User license mode [2]
            • all customer data is stored and processed in this single geography [2]
        • in Multi-Geo capacities located in geographies (geos) other than their home region [2]
          • compute and storage is located in the multi-geo region [2]
            • (including OneLake and experience-specific storage [2]
          • {exception} the tenant metadata remains in the home region
          • customer data will only be stored and processed in these two geographies [2]
  • {concept} data-at-rest
    • all Fabric data stores are encrypted at rest [2]
      • by using Microsoft-managed keys
      • includes customer data as well as system data and metadata [2]
      •  data is never persisted to permanent storage while in an unencrypted state [1]
        • data can be processed in memory in an unencrypted state [2]
    • {default} encrypted using platform managed keys (PMK)
      • Microsoft is responsible for all aspects of key management [2]
      • data-at-rest on OneLake is encrypted using its keys [3]
      • {alternative} Customer-managed keys (CMK) 
        • allow to encrypt data at-rest using customer keys [3]
          •   customer assumes full control of the key [3]
        • {recommendation} use cloud storage services with CMK encryption enabled and access data from Fabric using OneLake shortcuts [3]
          • data continues to reside on a cloud storage service or an external storage solution where encryption at rest using CMK is enabled [3]
          • customers can perform in-place read operations from Fabric whilst staying compliant [3] 
          • shortcuts can be accessed by other Fabric experiences [3]
  • {concept} data-in-transit
    • refers to traffic between Microsoft services routed over the Microsoft global network [2]
    • inbound communication
      • always encrypted with at least TLS 1.2. Fabric negotiates to TLS 1.3 whenever possible [2]
      • inbound protection
        •  concerned with how users sign in and have access to Fabric [3]
    • outbound communication to customer-owned infrastructure 
      • adheres to secure protocols [2]
        • {exception} might fall back to older, insecure protocols when newer protocols aren't supported [2]
          • incl. TLS 1
      • outbound protection
        • concerned with securely accessing data behind firewalls or private endpoints [3]


References:
[1] Microsoft Learn (2024) Security in Microsoft Fabric [link]
[2] Microsoft Learn (2024) Microsoft Fabric security fundamentals [link]
[3] Microsoft Learn (2024) Microsoft Fabric end-to-end security scenario [link]

Resources:
[R1] Microsoft Learn (2024) Microsoft Fabric security [link]
[R2] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

Acronyms:
ADL - Azure Data Lake
API - Application Programming Interface
CMK - Customer-Managed Keys
MF - Microsoft Fabric
MFA - Multifactor Authentication 
MI - Managed Instance 
PMK - Platform-Managed Keys
REST - REpresentational State Transfer
SDL - Security Development Lifecycle
SKU - Stock Keeping Unit
TLS  - Transport Layer Security
VM - Virtual Machine
VNet - virtual network
VPN - Virtual Private Network

16 March 2025

💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part XI: Database and Server Properties)

When taking over a SQL Server, respectively database, one of the first checks I do focuses on the overall configuration, going through the UI available for admins to see if I can find anything that requires further investigation. If no documentation is available on the same, I run a few scripts and export their output as baseline. 

Especially when documenting the configuration, it's useful to export the database options and properties defined at database level. Besides the collation and probably the recovery mode, typically the rest of the configuration is similar, though in exceptional cases one should expect also surprises that require further investigation! 

The following query retrieves in a consolidated way all the options and properties of a SQL database in Microsoft Fabric. 

-- database settings/properties 
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') Collation
--, DATABASEPROPERTYEX(DB_NAME(), 'ComparisonStyle')  ComparisonStyle
, DATABASEPROPERTYEX(DB_NAME(), 'Edition') Edition
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullDefault') IsAnsiNullDefault
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullsEnabled') IsAnsiNullsEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiPaddingEnabled') IsAnsiPaddingEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiWarningsEnabled') IsAnsiWarningsEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsArithmeticAbortEnabled') IsArithmeticAbortEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoClose') IsAutoClose
, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatistics') IsAutoCreateStatistics
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatisticsIncremental') IsAutoCreateStatisticsIncremental
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoShrink') IsAutoShrink
, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoUpdateStatistics') IsAutoUpdateStatistics
--, DATABASEPROPERTYEX(DB_NAME(), 'IsClone') IsClone
--, DATABASEPROPERTYEX(DB_NAME(), 'IsCloseCursorsOnCommitEnabled') IsCloseCursorsOnCommitEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsDatabaseSuspendedForSnapshotBackup') IsDatabaseSuspendedForSnapshotBackup
, DATABASEPROPERTYEX(DB_NAME(), 'IsFulltextEnabled') IsFulltextEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsInStandBy') IsInStandBy
--, DATABASEPROPERTYEX(DB_NAME(), 'IsLocalCursorsDefault') IsLocalCursorsDefault
--, DATABASEPROPERTYEX(DB_NAME(), 'IsMemoryOptimizedElevateToSnapshotEnabled') IsMemoryOptimizedElevateToSnapshotEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsMergePublished') IsMergePublished
--, DATABASEPROPERTYEX(DB_NAME(), 'IsNullConcat') IsNullConcat
--, DATABASEPROPERTYEX(DB_NAME(), 'IsNumericRoundAbortEnabled') IsNumericRoundAbortEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsParameterizationForced') IsParameterizationForced
--, DATABASEPROPERTYEX(DB_NAME(), 'IsQuotedIdentifiersEnabled') IsQuotedIdentifiersEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsPublished') IsPublished
--, DATABASEPROPERTYEX(DB_NAME(), 'IsRecursiveTriggersEnabled') IsRecursiveTriggersEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsSubscribed') IsSubscribed
--, DATABASEPROPERTYEX(DB_NAME(), 'IsSyncWithBackup') IsSyncWithBackup
--, DATABASEPROPERTYEX(DB_NAME(), 'IsTornPageDetectionEnabled') IsTornPageDetectionEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsVerifiedClone') IsVerifiedClone
--, DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported') IsXTPSupported
, DATABASEPROPERTYEX(DB_NAME(), 'LastGoodCheckDbTime') LastGoodCheckDbTime
, DATABASEPROPERTYEX(DB_NAME(), 'LCID') LCID
--, DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') MaxSizeInBytes
, DATABASEPROPERTYEX(DB_NAME(), 'Recovery') Recovery
--, DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') ServiceObjective
--, DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjectiveId') ServiceObjectiveId
, DATABASEPROPERTYEX(DB_NAME(), 'SQLSortOrder') SQLSortOrder
, DATABASEPROPERTYEX(DB_NAME(), 'Status') Status
, DATABASEPROPERTYEX(DB_NAME(), 'Updateability') Updateability
, DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') UserAccess
, DATABASEPROPERTYEX(DB_NAME(), 'Version') Version
--, DATABASEPROPERTYEX(DB_NAME(), 'ReplicaID') ReplicaID

Output:

Collation Edition IsAutoCreateStatistics IsAutoUpdateStatistics IsFulltextEnabled LastGoodCheckDbTime LCID Recovery SQLSortOrder Status Updateability UserAccess Version
SQL_Latin1_General_CP1_CI_AS FabricSQLDB 1 1 1 12/31/1899 1033 FULL 52 ONLINE READ_WRITE MULTI_USER 981

The query can be run also against the SQL analytics endpoints available for warehouses in Microsoft Fabric.

Output:

Collation Edition IsAutoCreateStatistics IsAutoUpdateStatistics IsFulltextEnabled LastGoodCheckDbTime LCID Recovery SQLSortOrder Status Updateability UserAccess Version
Latin1_General_100_BIN2_UTF8 DataWarehouse 1 1 1 12/31/1899 1033 SIMPLE 0 ONLINE READ_WRITE MULTI_USER 987

Respectively, for lakehouses:

Collation Edition IsAutoCreateStatistics IsAutoUpdateStatistics IsFulltextEnabled LastGoodCheckDbTime LCID Recovery SQLSortOrder Status Updateability UserAccess Version
Latin1_General_100_BIN2_UTF8 LakeWarehouse 1 1 1 12/31/1899 1033 SIMPLE 0 ONLINE READ_WRITE MULTI_USER 987

A similar output is obtained if one runs the query against SQL database's SQL analytics endpoint:

Output:

Collation Edition IsAutoCreateStatistics IsAutoUpdateStatistics IsFulltextEnabled LastGoodCheckDbTime LCID Recovery SQLSortOrder Status Updateability UserAccess Version
Latin1_General_100_BIN2_UTF8 LakeWarehouse 1 1 1 12/31/1899 1033 SIMPLE 0 ONLINE READ_WRITE MULTI_USER 987

SQL databases seem to inherit the collation from the earlier versions of SQL Server.

Another meaningful value for SQL databases is MaxSizeInBytes, which in my environment had a value of 3298534883328 bytes ÷ 1,073,741,824 = 3,072 GB.

There are however also server properties. Here's the consolidated overview:

-- server properties
SELECT --SERVERPROPERTY('BuildClrVersion') BuildClrVersion
 SERVERPROPERTY('Collation') Collation
--, SERVERPROPERTY('CollationID') CollationID
, SERVERPROPERTY('ComparisonStyle') ComparisonStyle
--, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ComputerNamePhysicalNetBIOS
, SERVERPROPERTY('Edition') Edition
--, SERVERPROPERTY('EditionID') EditionID
, SERVERPROPERTY('EngineEdition') EngineEdition
--, SERVERPROPERTY('FilestreamConfiguredLevel') FilestreamConfiguredLevel
--, SERVERPROPERTY('FilestreamEffectiveLevel') FilestreamEffectiveLevel
--, SERVERPROPERTY('FilestreamShareName') FilestreamShareName
--, SERVERPROPERTY('HadrManagerStatus') HadrManagerStatus
--, SERVERPROPERTY('InstanceDefaultBackupPath') InstanceDefaultBackupPath
, SERVERPROPERTY('InstanceDefaultDataPath') InstanceDefaultDataPath
--, SERVERPROPERTY('InstanceDefaultLogPath') InstanceDefaultLogPath
--, SERVERPROPERTY('InstanceName') InstanceName
, SERVERPROPERTY('IsAdvancedAnalyticsInstalled') IsAdvancedAnalyticsInstalled
--, SERVERPROPERTY('IsBigDataCluster') IsBigDataCluster
--, SERVERPROPERTY('IsClustered') IsClustered
, SERVERPROPERTY('IsExternalAuthenticationOnly') IsExternalAuthenticationOnly
, SERVERPROPERTY('IsExternalGovernanceEnabled') IsExternalGovernanceEnabled
, SERVERPROPERTY('IsFullTextInstalled') IsFullTextInstalled
--, SERVERPROPERTY('IsHadrEnabled') IsHadrEnabled
--, SERVERPROPERTY('IsIntegratedSecurityOnly') IsIntegratedSecurityOnly
--, SERVERPROPERTY('IsLocalDB') IsLocalDB
--, SERVERPROPERTY('IsPolyBaseInstalled') IsPolyBaseInstalled
--, SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') IsServerSuspendedForSnapshotBackup
--, SERVERPROPERTY('IsSingleUser') IsSingleUser
--, SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') IsTempDbMetadataMemoryOptimized
, SERVERPROPERTY('IsXTPSupported') IsXTPSupported
, SERVERPROPERTY('LCID') LCID
, SERVERPROPERTY('LicenseType') LicenseType
, SERVERPROPERTY('MachineName') MachineName
, SERVERPROPERTY('NumLicenses') NumLicenses
, SERVERPROPERTY('PathSeparator') PathSeparator
--, SERVERPROPERTY('ProcessID') ProcessID
, SERVERPROPERTY('ProductBuild') ProductBuild
--, SERVERPROPERTY('ProductBuildType') ProductBuildType
--, SERVERPROPERTY('ProductLevel') ProductLevel
--, SERVERPROPERTY('ProductMajorVersion') ProductMajorVersion
--, SERVERPROPERTY('ProductMinorVersion') ProductMinorVersion
--, SERVERPROPERTY('ProductUpdateLevel') ProductUpdateLevel
--, SERVERPROPERTY('ProductUpdateReference') ProductUpdateReference
--, SERVERPROPERTY('ProductUpdateType') ProductUpdateType
, SERVERPROPERTY('ProductVersion') ProductVersion
, SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime
, SERVERPROPERTY('ResourceVersion') ResourceVersion
, SERVERPROPERTY('ServerName') ServerName
, SERVERPROPERTY('SqlCharSet') SqlCharSet
, SERVERPROPERTY('SqlCharSetName') SqlCharSetName
, SERVERPROPERTY('SqlSortOrder') SqlSortOrder
, SERVERPROPERTY('SqlSortOrderName') SqlSortOrderName
, SERVERPROPERTY('SuspendedDatabaseCount') SuspendedDatabaseCount

Output (consolidated):

Property SQL database Warehouse Lakehouse
Collation SQL_Latin1_General_CP1_CI_AS SQL_Latin1_General_CP1_CI_AS SQL_Latin1_General_CP1_CI_AS
ComparisonStyle 196609 196609 196609
Edition SQL Azure SQL Azure SQL Azure
EngineEdition 12 11 11
InstanceDefaultDataPath NULL NULL NULL
IsAdvancedAnalyticsInstalled 1 1 1
IsExternalAuthenticationOnly 1 0 0
IsExternalGovernanceEnabled 1 1 1
IsFullTextInstalled 1 0 0
IsXTPSupported 1 1 1
LCID 1033 1033 1033
LicenseType DISABLED DISABLED DISABLED
MachineName NULL NULL NULL
NumLicenses NULL NULL NULL
PathSeparator \ \ \
ProductBuild 2000 502 502
ProductVersion 12.0.2000.8 12.0.2000.8 12.0.2000.8
ResourceLastUpdateDateTime 11/6/2024 3:41:27 PM 3/5/2025 12:05:50 PM 3/5/2025 12:05:50 PM
ResourceVersion 16.00.5751 17.00.502 17.00.502
ServerName ... .... ....
SqlCharSet 1 1 1
SqlCharSetName iso_1 iso_1 iso_1
SqlSortOrder 52 52 52
SqlSortOrderName nocase_iso nocase_iso nocase_iso
SuspendedDatabaseCount NULL 0 0

It's interesting that all three instances have the same general collation, while the Engine Edition of SQL databases is not compatible with the others [2]. The Server Names has been removed manually from the output from obvious reasons. The warehouse and lakehouse are in the same environment (SQL Azure instance, see sys.databases), and therefore the same values are shown (though this might happen independently of the environments used).

The queries were run in a trial Microsoft Fabric environment. Other environments can have upon case different properties. Just replace the "--" from the commented code to get a complete overview.

The queries should run also in the other editions of SQL Server. If DATABASEPROPERTYEX is not supported, one should try DATABASEPROPERTY instead.

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) SQL Server 2022: DATABASEPROPERTYEX (Transact-SQL) [link]
[2] Microsoft Learn (2024) SQL Server 2022: SERVERPROPERTY (Transact-SQL) [link]

25 February 2025

🏭💠🗒️Microsoft Fabric: T-SQL Notebook [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 25-Feb-2024

[Microsoft Fabric] T-SQL notebook

  • {def} notebook that enables to write and run T-SQL code within a notebook [1]
  • {feature} allows to manage complex queries and write better markdown documentation [1]
  • {feature} allows the direct execution of T-SQL on
    • connected warehouse
    • SQL analytics endpoint
    • ⇐ queries can be run directly on the connected endpoint [1]
      • multiple connections are allowed [1]
  • allows running cross-database queries to gather data from multiple warehouses and SQL analytics endpoints [1]
  • the code is run by the primary warehouse
    • used as default in commands which supports three-part naming, though no warehouse was provided [1]
    • three-part naming consists of 
      • database name
        • the name of the warehouse or SQL analytics endpoint [1]
      • schema name
      • table name
  • {feature} autogenerate T-SQL code using the code template from the object explorer's context [1] menu
  • {concept} code cells
    • allow to create and run T-SQL code
      • each code cell is executed in a separate session [1]
        • {limitation} the variables defined in one cell are not available in another cell [1]
        • one can check the execution summary after the code is executed [1]
      • cells can be run individually or together [1]
      • one cell can contain multiple lines of code [1]
        • users can select and run subparts of a cell’s code [1]
    • {feature} Table tab
      • lists the records from the returned result set
        • if the execution contains multiple result set, you can switch from one to another via the dropdown menu [1]
  • a query can be saved as 
    • view
      • via 'Save as' view
      • {limitation} does not support three-part naming [1]
        • the view is always created in the primary warehouse [1]
          • by setting the warehouse as the primary warehouse [1]
    • table
      • via 'Save as' table
      • saved as CTAS 
    • ⇐ 'Save as' is only available for the selected query text
      • the query text must be selected before using the Save as options
  • {limitation} doesn’t support 
    • parameter cell
      • the parameter passed from pipeline or scheduler can't be used [1]
    • {feature} Recent Run 
      • {workaround} use the current data warehouse monitoring feature to check the execution history of the T-SQL notebook [1]
    • {feature} the monitor URL inside the pipeline execution
    • {feature} snapshot 
    • {feature} Git support 
    • {feature} deployment pipeline support 

References:
[1] Microsoft Learn (2025) T-SQL support in Microsoft Fabric notebooks [link
[2] Microsoft Learn (2025) Create and run a SQL Server notebook [link
[3] Microsoft Learn (2025) T-SQL surface area in Microsoft Fabric [link
[4] Microsoft Fabric Updates Blog (2024) Announcing Public Preview of T-SQL Notebook in Fabric [link]

Resources:
[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

Acronyms
CTAS - Create Table as Select
T-SQL - Transact SQL

13 February 2025

🏭💠🗒️Microsoft Fabric: SQL Analytics Endpoint [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 12-Feb-2024

[Microsoft Fabric] SQL Analytics Endpoint

  • {def} a service that listens actively for requests providing a SQL-based experience for lakehouse Delta tables [1]
    • enables to query data in the lakehouse using T-SQL language and TDS protocol [1]
    • created by default for each lakehouses and mirrored databases provisioned in a workspace
      • every lakehouse has one SQL analytics endpoint [1]
    • manages the automatically generated tables so the workspace users can't modify them [1]
  • {feature} a background process is responsible for 
    • scanning lakehouse for changes [1]
      • automatically generates a table in the appropriate schema for every Delta table in the Lakehouse
        • tables are created with a minor delay [1]
        • data is refreshed automatically
          • the amount of time it takes to refresh the table is related to how optimized the Delta tables are [1]
        • {alternative} one can manually force a refresh of the automatic metadata scanning in the Fabric portal [1]
    • keeping SQL analytics endpoint up-to-date for all the changes committed to lakehouses in a workspace [1]
      • {characteristic} transparently managed
        • ⇐ the sync process is transparently managed by Microsoft Fabric platform [1]
        • when a change is detected in the lakehouse
          • a background process updates metadata [1]
          • the SQL analytics endpoint reflects the changes committed to lakehouse tables [1]
      • {characteristic} low latency
        • under normal operating conditions, the lag between a lakehouse and SQL analytics endpoint is less than one minute [1]
        • the actual length of time can vary from a few seconds to minutes [1]
      • the metadata synchronization is automatically triggered when the SQL Endpoint is opened [3]
        • ensures the SQL Analytics Endpoint remains up to date without the need for manual syncs [3]
          •  ⇐ provides a seamless experience [3]
  • {feature} can be enriched by adding database objects
    • schemas
    • views
    • procedures
    • other database objects
  • {feature} automatic metadata discovery
    • tracks changes committed to lakehouses [1]
      • is a single instance per Fabric workspace [1]
      • {issue} increased latency for changes to sync between lakehouses and SQL analytics endpoint, it could be due to large number of lakehouses in one workspace [1]
        • {resolution} migrate each lakehouse to a separate workspace [1]
          • allows automatic metadata discovery to scale [1]
      • {issue} changes committed to a lakehouse are not visible in the associated SQL analytics endpoint
        • create a new table in the  [1]
        • many committed rows may not be visible in the endpoint [1]
        • ⇐ may be impacted by ETL processing that generate large volumes of changes
        • {recommendation} initiate an on-demand metadata sync, triggered from the SQL query editor Refresh ribbon option [1]
          • forces an on-demand metadata sync, rather than waiting on the background metadata sync to finish [1]
      • {issue} if there's no maintenance scheduled for the parquet files, this can result in read overhead and this impacts time it takes to sync changes to SQL analytics endpoint [1]
        • {recommendation}schedule regular lakehouse table maintenance operations [1]
      • {limitation} not all Delta features are understood by the automatic sync process [1]
  • {feature} endpoint reprovisioning
    • if the initial provisioning attempt fails, users have the option to try again [2]
      • ⇐ without the need to create an entirely new lakehouse [2]
      • {benefit} empowers users to self-mitigate provisioning issues in convenient way in the UI avoiding the need for complete lakehouse re-creation [2]

References:
[1] Microsoft Learn (2024) Microsoft Fabric: SQL analytics endpoint performance considerations [link]
[2] Microsoft Learn (2024) Microsoft Fabric: What is the SQL analytics endpoint for a lakehouse? [link]
[3] Microsoft Learn (2024) Microsoft Fabric: What’s new in the Fabric SQL Analytics Endpoint? [link]

Resources:
[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]
Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 25 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.