European Fabric Conference in Vienna Sept 2025 takeways

FabConEurope25 was terrific in Vienna last week.  Great opportunity to meet Fabric and data experts, speak to the product teams and experts, and the presentations were fantastic.  The hardest part was deciding which session to attend as there are so many competing at the same time.  

My big takeaways:

• Fabric SQL is excellent.  The HA, managed service, redundancy, and shipping logs ensure that OneLake is in near real-time.  Fabric SQL supports new native geospatial types.  SQL has temporal tables (old news), but row, column and object-level (incl. table) security is part of OneLake.   There are a couple of things security reviewers will query, but they are addressed.
• Fabric Data Agent is interesting.  Connect to your SQL relational data and work with it.
• User-defined functions (UDF), including Translytical (write-back), HTTP in or out, wrap stored procedures, notebooks,.... - amazing.
• OneLake security is complex but can be understood, especially with containers/layers, such as Tenant, Workspace, Item, and Data.  There is more needed, but it's miles ahead of anything else, and Graph is the magic, so it will only continue to improve. - amazing, but understand security.  Embrace Entra and OAuth; use keys only as a last resort.
• Snowflake is our friend.  Parquet is fantastic, and Snowflake, including Iceberg, play well together with MS Fabric.  There are new versions of Delta Parquet on the way (and this will even make Fabric stronger, supporting both existing and the latest formats).
• Mirroring and shortcuts - don't ETL unless you need to shortcut, then mirror, then ETL.
• Use workspaces to build out simple medallion architectures.
• AI Search/Vector Search and SQL are crazy powerful.
• New Map functionality has arrived and is arriving on Fabric.  Org Apps for Maps is going to be helpful in the map space.  pmtiles are native... (if you know you know)
• Dataverse is great with Fabric and shortcuts, as I learned from Scott Sewell at an earlier conference.  Onelake coupled with Dataverse, is massively underutilised by most orgs, 
• Power BI also features new Mapping and reporting capabilities related to geospatial data.
• Other storage: CosmosDB (it has its place, but suddenly, with shortcuts, the biggest issue of cost can be massively reduced with the right design decisions).  Postgres is becoming a 1st class citizen, which is excellent on multiple levels. The CDC stuff is fantastic already.
• RTI on Fabric is going to revolutionise Open Telemetry and AI, networking through the OSI model, application testing, digital twins, and live monitoring,....  I already knew this, but it keeps getting better.  EventHub and notebooks are my new best friends.  IoT is the future; we all knew this, but now with Fabric, it will be much easier to implement safely and get early value.
• Direct Lake is a game changer for Power BI - not new, but it just keeps getting better and better thanks to MS Graph.
• Manage Private Endpoint as improved and should be part of all companies' governance.
• Purview... It's excellent and solves/simplifies DLP, governance and permissions.  I'm out of my depth on Fabric Purview and governance, and I know way more than most people on DLP and governance. Hire one of those key folks from Microsoft here.  
• Warehouse lineage of data is so helpful.  
• We need to understand Fabric Digital Twins, as it is likely to be a competitor or a solution we offer and integrate. 
• Parquet is brilliant and fundamentally is why AI is so successful.
• Powerful stuff in RDF for modelling domains - this is going to be a business in itself.  I'm clueless here, but I won't be in a few weeks.

Now the arr..

• Pricing and capacity are not transparent.  Watch out for the unexpected monster bill!  Saying that the monitoring and controls are in place, but switching off my tenant doesn't sit well with me if workloads aren't correctly set out. Resource governance at the workspace level will help fix the situation or design around it, but it will be more expensive.
• Workspace resource reservation does not exist yet; however, it can be managed using multiple fabric tenants. Distribution will be significantly improved for cost control with Workspace resource management.
• Licensing needs proper thought for an enterprise, including ours.  Reserve Fabric is 40% cheaper, and it cannot be suspended, so use the reserved fabric just as you would for most Azure Services.  Good design results in much lower cost with Workloads.  Once again, those who genuinely understand know my pain with the workload costs.
• Vendors and partners are too far behind (probably due to the pace of innovation)

Microsoft Fabric is brilliant; it is all under one simple managed autoscaling umbrella.  It integrates and plays nicely with other solutions, has excellent access to Microsoft storage, and is compatible with most of the others.  Many companies will move onto Fabric or increase their usage in the short term, as it is clearly the leader in multiple Gartner segments, all under one hood.  AI will continue to help drive its adoption by enterprises.

SQL Server on Microsoft Fabric

 Release to Public Preview in Nov 2024 and Ignite.

• SQL Server can be directly used/provisioned within MS Fabric.  This means you get your data instantly into OneLake for reporting, and you have a fully functional relational database.  
• Supports temporal tables (history)
• Enable GraphQL to allow secure OAuth access to the data.
• Stored Procs are supported, and GraphQL API can be enabled on each of them.
• Fully managed Service by Microsoft.

Elastic Database Client Library for client database segregation on Azure PaaS for SaaS

Overview:  Provide a logically separated database instance for each client on my SaaS solution.  Using the Elastic Database client library from Microsoft on Azure PaaS services provides logical security separation of data, performance is on a per customer, and easy scalability.  Use Azure SQL Elastic Pools (HA redundant secondary database, built in DR).  Also add temporal tables for a full history of all transactions.

PoC:

1. Provision 3 databases - A Shard Map Manager (Catalogue) database and 2 client databases (tenants/shards).
2. Add shard related metadata to the Catelogue database for each of these databases.
3. Create below Three service principals in Azure AD: 

• Management Service Principal: for creating shard metadata structure.  A database contained user in Shard Map Manager db and each tenant db.
• Access Service Principal: to load shard mapping at application side.  A database contained user in Shard Map Manager db.
• Connection Service Principal: to connect tenant database.  Database contained user in each tenant db.

                        Management Service Principal: for creating shard metadata structure

CREATE USER [shard-map-admin-sp] FROM EXTERNAL PROVIDER

EXEC sp_addrolemember N'db_ddladmin', N'shard-map-admin-sp'

EXEC sp_addrolemember N'db_datareader', N'shard-map-admin-sp'

EXEC sp_addrolemember N'db_datawriter', N'shard-map-admin-sp'

GRANT EXECUTE TO [shard-map-admin-sp]

 

Access Service Principal: to load shard mapping at application side

CREATE USER [shard-map-access-sp] FROM EXTERNAL PROVIDER

EXEC sp_addrolemember N'db_datareader', N'shard-map-access-sp'

GRANT EXECUTE TO [shard-map-access-sp]

                                                         

Connection Service Principal: to connect client/tenant database

CREATE USER [tenant-connection-sp] FROM EXTERNAL PROVIDER

EXEC sp_addrolemember N'db_datareader', N'tenant-connection-sp'

EXEC sp_addrolemember N'db_datawriter', N'tenant-connection-sp'

EXEC sp_addrolemember N'db_ddladmin', N'tenant-connection-sp'

GRANT EXECUTE TO [tenant-connection-sp]

References:

https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-database-client-library

Azure Elastic Pools - DB creation, schema alignment for SaaS

Overview:  Provisioning and seeding databases is pretty straight forward however ensuring multi-tenant databases schemas are aligned is a little tricky.  Azure has the Elastic Job Agent service that has been in preview for many years and is a good service.  It is extremely useful for updating multiple database instances en-masse.

There are 2 common scenarios: 

1. Provision a new database for a client.  This involves 1) creating the SQL database instance either on a server or an elastic pool. 2) Update the new database with the appropriate schema 3) insert any seed data into the database.

2. Update groups or all database instances to a specific schema i.e. change schema for all clients.

Scenario 1 needs to apply the schema to multiple databases and Elastic Job is perfect for this.  Scenrio 2 also needs to update a single database and can use the same T-SQL code to ensure new dbs have the correct schema applied.

Elastic Job Agent:

A job can be created that uses T-SQL (from source control) to ensure the schema of target databases are updated to a specific schema version.  A dedicate database is used to monitor and manage the jobs.  I call this the "agent-elasticdb" database.  

Note:  The Target can be all databases on a server, elastic pools, groups of name database instances or a single database.

If a job has multiple target database's to update, the updates are run in parallel.    

Encryption Options for Azure SQL

Overview:  With all IT storage, we are looking for encryption at rest and making sure the data is encrypted “over the wire” until it is stored storage.  For encryption in transit, Azure SQL supports TLS/SSL versions 1.0, 1.1,and 1.2.  If possible got for TLS 1.2.

Azure SQL Server Transparent Data Encryption (TDE) related to encryption at rest by encrypting the log and data files on the storage; Azure enforces TDE as the default on databases.  TDE can be turned off on your Azure SQL instance.  The disks that the database files and backups are block encrypted automatically by Azure.

Backups should also be encrypted, and if TDS is enabled on Azure, your backups are also automatically encrypted.  Tip: Validate your restore of Azure Backups to another instance.

Column encryption is useful for encrypting a column within a table.  I prefer to use a Key Vault and use a SQL column to point to the database for things like tokens and secrets, but something like credit card numbers column encryption is ideal.

Always Encrypted allows for one or more columns to be encrypted within a database.  Client application shall decrypt and provides for separation where database owners/access cannot validate/view the encrypted column/columns.

Encryption at Rest on Azure SQL Server (PASS) Summary:

1. Disk Encryption - Always can't change
2. TDE - Server-Side - On by default (can be turn off)
3. Column level encryption - Server-Side (Needs configuration, encryption done inside SQL for columns)
4. Always Encrypted - Client-Side.  Columns are encrypted inside the db and only the application can unencrypt the column.

Azure SQL Basic Options Summary

Overview:  Azure SQL is incredible.  There are a lot of options when choosing how to host database and performance good.  "handles patching, backups, replication, failure detection, underlying potential hardware, software or network failures, deploying bug fixes, failovers, database upgrades, and other maintenance tasks", from Microsoft Docs and Azure SQL.

Azure SQL is the PaaS database service that does the same functions as SQL Server did for us for many years as the workhorse for many organisations.  Microsoft initially only offered creating VM's and then installing SQL Server on-prem.   Azure SQL is Microsoft's PaaS SQL database as a Service offering on the cloud.  Azure SQL is a fully managed platform for SQL databases that Microsoft patches managed backups and high availability.  All the features that are available in the on-prem. Edition are also built into Azure SQL with minor exceptions.  I also like that the minimum SLA provide by Azure SQL is 99.99%.

Three SQL Azure PaaS Basic Options:

1. Single Database - This is a single isolate database with it's own guaranteed CPU, memory and storage.
2. Elastic Pool - Collection of single isolate databases that share DTUs (CPU, Memory & I/O) or Virtual Cores.
3. Manage Instance - You mange a set of databases, with guaranteed resources.  Similar to IaaS with SQL installed but Microsoft manage more parts for me.  Can only purchase using Virtual Core model (No DTU option).

Thoughts: Managed Instances recommend up to 100TB but can go higher.  Individual databases under elastic pools or single databases are limited to a respectable 4 TB.

Two Purchasing Options:

1. DTU - A single metric that Microsoft use to calculate CPU, memory and I/O.  
2. Virtual Cores - Allows you to choose you hardware/infrastructure.  One can optimise more memory than CPU ratio over the generalist DTU option.

Thoughts:  I prefer the DTU approach for SaaS and greenfield projects.  I generally only consider Virtual Cores, if I a have migrated on-prem. SQL onto a Managed Instance or for big workloads virtual cores can work out cheaper if the load is consistent.  There are exceptions but that is my general rule for choosing the best purchasing option.

Three Tiers:

1. General Business/Standard (There is also a lower Basic Level)
2. Business Critical/Premium
3. Hyperscale

Backups

Point in time backups are automatically stored for 7 to 35 days (default is 7 days), protected using TDE, and full, differential and transaction log backups are used for point in time recovery.  The backups are stored in blob storage RA-GRS (meaning in the primary region, and all the read-only backups are stored in a secondary Azure region).  3 copies of the data in the active Azure Zone and 3 read-only copies of the data.

Long-term retention backups can be kept for 10 years; these are only full backups.  The smallest retention is full backups retained for each week's full backup.  LTR is in preview and available for Managed Instances.

Azure Defender for SQL 

Monitors SQL database servers, checking vulnerability assessments (best practice recommendations) and Advanced Threat Protection, which monitors traffic for abnormal behaviour.

Checklist:

1. Only valid IPs can directly access the database. Deny public Access,
2. AAD security credentials, use service principals
3. Advanced Threat Protection has real-time monitoring of logs and configuration (it also scans for vulnerabilities), 
4. Default is to have encryption in transit (TLS 1.2) and encryption at rest (TDE) - don't change,
5. Use Dynamic data masking inside the db instance for sensitive data, e.g. credit cards
6. Turn on SQL auditing,

Note: Elastic Database Jobs (same as SQL Agent Jobs).

Azure offers MySQL, PostgreSQL and MariaDB as hosted PaaS offerings. 

Note: The Azure SQL PaaS Service does not support the filestream datatype: use varbinary or references to blobs. 

Updated: 2025 Sept

Azure Portal showing options to create SQL Azure Databases

SQL 2025 has been in GA and works well with Fabric - Consider using this as my default for SQL outside of Fabric moving forward, watch out for on-prem. upgrades for supported versions and coalescence.