SQL Database in Fabric: Transactional Engine, Translytical by Design

SQL database in Fabric is the operational, transactional database that lives inside Microsoft Fabric alongside the platform's analytical engines. It shares the same underlying SQL Database Engine used by Azure SQL Database, which makes it the natural home for OLTP workloads — order systems, application backends, anything that needs row-by-row inserts and updates with strong consistency — without leaving the Fabric workspace ^[S1].

What makes it worth a dedicated look isn't the transactional engine itself — that part is a known quantity to anyone who has run Azure SQL. It's what happens to the data the moment it lands. Every SQL database created in a Fabric workspace is automatically and continuously replicated into OneLake in near-real time, converted to Parquet as it goes, so Spark notebooks, Power BI, and data engineering pipelines can read it without an export, an ETL job, or a connector to babysit ^[S1].

That combination — a full transactional engine with an automatic, zero-effort analytical mirror — is what Fabric calls a translytical pattern, and it's the reason this capability shows up in both application-development and data-platform conversations. 2026 has added to that story on two fronts worth calling out up front: a T-SQL-native way to diagnose the health of the Delta tables sitting behind the SQL analytics endpoint, and a wave of platform features — from full collation support to Fabric Apps integration — that are reshaping what SQL database can plug into. The rest of this article walks through how the pattern is built, how to use it well, and what's actually happening under the hood.

Core concepts#

An OLTP engine with an analytical shadow#

When you provision a SQL database in a workspace, Fabric automatically creates a companion SQL analytics endpoint alongside it — a read-only door into the replicated OneLake data that never touches the transactional workload ^[S1]. Application traffic hits the primary engine; analytical traffic hits the endpoint. Neither one waits on the other.

Provisioning a SQL database automatically creates a companion SQL analytics endpoint, giving read-only analytical access without affecting the transactional workload.

This separation is the architectural core of the translytical pattern: write traffic runs through the transactional engine, read/analytics traffic runs through the OneLake-backed endpoint, and the two paths don't contend for the same resources ^[S1].

Autonomous by default#

SQL database in Fabric ships with automatic tuning enabled out of the box, including automatic index creation — the kind of index management a DBA would traditionally do by hand happens on its own ^[S1]. Combined with Entra-only authentication (SQL authentication isn't supported at all, and a caller needs at least Read item permission on the database before a connection is allowed to succeed), the operational surface is deliberately smaller than a self-managed SQL Server instance ^[S1].

Because the OneLake-replicated data lands as Delta tables, a SQL analytics endpoint on one database can join across other Fabric items — warehouses, mirrored databases, other SQL databases — using ordinary three-part T-SQL naming, with no data movement required to make the join happen ^[S1].

Where it fits: AI, reverse ETL, and operational data stores#

SQL database also supports vector data types and integrates with Azure OpenAI and orchestration frameworks like LangChain and Semantic Kernel, which positions it as a viable store for retrieval-augmented generation (RAG) and semantic search inside Fabric — not just conventional row-oriented OLTP ^[S1].

Microsoft has since named two further usage patterns beyond the original translytical-app story. Reverse ETL pushes curated analytical output from a Warehouse or Lakehouse back into a SQL database, so applications, APIs, and GraphQL endpoints can consume it operationally instead of leaving that curated data stranded in an analytics-only store. An operational data store pattern consolidates several operational systems into one normalized, near-real-time SQL database for lightweight reporting ^[S1]. Both patterns lean on the same replication plumbing described above — they just point the direction of travel differently: reverse ETL flows curated data back into the OLTP engine, rather than out of it.

What's new in 2026#

A mid-2026 roundup of Microsoft SQL platform updates lists several SQL database in Fabric items, and it's worth being precise about which ones are shipped versus which are still on the way — the roundup itself draws that line explicitly ^[S4].

Shipped:

• Fabric Apps support — Fabric Apps, the platform's feature for packaging and sharing content, gained SQL database as a supported data source ^[S4].

Preview:

• Full collation support is listed as a Preview capability ^[S4].
• A migration assistant for SQL database in Fabric is in Preview, intended to help move existing databases into the Fabric-hosted engine ^[S4].
• Time Travel querying was extended, in preview, to the SQL analytics endpoint itself via a T-SQL OPTIONS clause — a single query can read historical data as of a timestamp within the configured retention window, directly against the read-only endpoint rather than only against the live transactional copy ^[S5].

Coming soon (explicitly not yet shipped for SQL database in Fabric):

• Change Event Streaming is available today across Hyperscale and SQL Managed Instance, but the roundup is explicit that SQL database in Fabric support is coming soon, not already live ^[S4].
• Regex-powered Dynamic Data Masking is listed as a coming-soon Preview capability, without a stated Fabric-specific availability date ^[S4].
• Local time-zone support is listed as coming soon in Preview, shared across Hyperscale, SQL Managed Instance, and SQL database in Fabric together ^[S4].

Fabric Apps as an SQL database front end#

Inference below flags where a third-party (tier-6) source describes a specific product built on this pattern; treat it as illustrative, not a Microsoft-documented capability.

One concrete illustration of what "Fabric Apps + SQL database" can look like in practice: a third-party guide describes a product (Rayfin) whose deployment provisions a Fabric SQL database with its schema generated automatically from TypeScript decorator-annotated data model classes, rather than hand-written DDL, and defines row-level security policies declaratively in that same TypeScript code alongside the model ^[S6]. This is a single vendor's implementation choice, not a Microsoft-documented Fabric Apps capability — read it as one example of the pattern space Fabric Apps + SQL database opens up, not as a general recommendation.

How it works / best practices#

Do this: check table health before you optimize, not after#

Rule: run sp_get_table_health_metrics against the SQL analytics endpoint before deciding whether a Lakehouse Delta table needs OPTIMIZE/compaction — don't optimize blind.

Why: it's a built-in stored procedure exposed through the SQL analytics endpoint that gives a T-SQL-native way to check the structural and physical health of Lakehouse Delta tables, and it's callable as ordinary T-SQL from Fabric pipelines, Azure Data Factory, dbt, or any SQL-based orchestration tool — no Spark session required to invoke it ^[S3].

Example — diagnose before you act:

-- Runs against the SQL analytics endpoint; no Spark session needed
EXEC sp_get_table_health_metrics
    @schema_name = 'dbo',
    @table_name  = 'SalesOrders';

The procedure's output surfaces exactly the signals you'd want before triggering a maintenance job: snapshot and checkpoint version information to judge whether a new Delta checkpoint is warranted, and a file-size distribution across bins from 1 KiB up to over 16 GiB plus row-count distribution, so small-file fragmentation is visible at a glance ^[S3]. It also returns dedicated anomaly-detection columns — PotentialAnomalyType and PotentialAnomalyDescription — flagging conditions such as too many small files, too many deleted rows, no recent checkpoint, or invalid file statistics, each with a numeric type code so the check can be automated rather than eyeballed ^[S3].

-- A pipeline-friendly pattern: only trigger OPTIMIZE when an anomaly is flagged
SELECT PotentialAnomalyType, PotentialAnomalyDescription, FileSizeBins, RowCount
FROM sp_get_table_health_metrics('dbo.SalesOrders')
WHERE PotentialAnomalyType IS NOT NULL;

Do this: design for the endpoint, not around it#

Rule: treat the SQL analytics endpoint as your default path for reporting and downstream consumption instead of querying the transactional engine directly for analytics.

Why: the endpoint exists specifically so analytical queries don't compete with OLTP write traffic — that isolation is architectural, not a tuning knob you have to configure ^[S1].

Example — wrong (querying the primary for a report):

-- Runs against the transactional engine; competes with live writes
SELECT c.CustomerId, SUM(o.Amount) AS Lifetime
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId;

Example — right (querying the analytics endpoint / cross-item federation):

-- Runs against the SQL analytics endpoint (or from a Warehouse via
-- three-part naming), reading the OneLake-replicated Delta copy
SELECT c.CustomerId, SUM(o.Amount) AS Lifetime
FROM SalesDb.dbo.Orders o
JOIN SalesDb.dbo.Customers c ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId;

Because the replicated data is Delta/Parquet in OneLake, this same federated pattern lets a query in one SQL database join tables that physically live in a Warehouse or a mirrored database, using three-part names, with no linked server or copy step ^[S1].

Do this: use Time Travel at the endpoint for point-in-time reporting#

Rule: when a report needs data "as of" a past timestamp, query the SQL analytics endpoint's Time Travel OPTIONS clause instead of standing up a separate historical snapshot process.

Why: Time Travel querying was extended, in preview, to the SQL analytics endpoint specifically so a single T-SQL query can read historical data as of a given timestamp within the configured retention window ^[S5].

-- Preview: point-in-time read against the SQL analytics endpoint
SELECT OrderId, Amount, CreatedAt
FROM SalesDb.dbo.Orders
OPTIONS (TIMESTAMP AS OF '2026-06-01T00:00:00Z');

Do this: use SqlPackage for schema promotion, not manual scripts#

Rule: manage schema changes through .dacpac/SQL project deployments rather than hand-run DDL scripts across environments.

Why: SqlPackage's .bacpac/.dacpac support is what makes SQL database portable to and from Azure SQL Database and enables CI/CD-style incremental schema deployment ^[S1].

Example — a CI step publishing a schema change:

SqlPackage /Action:Publish \
  /SourceFile:"SalesDb.dacpac" \
  /TargetConnectionString:"Server=tcp:<workspace>.database.fabric.microsoft.com;Database=SalesDb;Authentication=Active Directory Default;"

What goes wrong: assuming Fabric capacity billing works like elastic pools#

SQL database in Fabric does not map onto Azure SQL elastic pools directly. Instead, a single Fabric capacity spans multiple SQL databases across different workspaces — consolidating billing and sharing resources in a way that's analogous to an elastic pool, while still keeping workspace-level security fully isolated ^[S1].

Lock down access with layered security, not just Entra auth#

Row-level security (RLS), object-level security (OLS), workspace-level customer-managed keys, and SQL auditing are all available on top of Entra-only authentication ^[S1]. Entra auth controls who can connect; RLS/OLS control what they see once connected — treat them as separate controls, not substitutes for each other.

Internals#

Architecture & design#

SQL database in Fabric is built on the same Hyperscale distributed-functions architecture that underlies Azure SQL Database, Managed Instance, and SQL Server on Azure VMs. Microsoft's Hyperscale architecture reference is explicitly versioned to cover SQL database in Fabric alongside those other deployment options, which confirms this is the same engine design, not a separate reimplementation for Fabric ^[S2].

The defining move Hyperscale makes is architecturally separating the relational query-processing engine from long-term data storage and durability. That split is what lets storage scale out smoothly to as much as 128 TB for a single database — a monolithic engine that keeps compute and storage bolted together can't do that without a much more invasive redesign ^[S2].

That same separation is architecturally consistent with how SQL database in Fabric replicates into OneLake: the engine is already built to propagate changes to consumers other than the primary compute node (page servers, replicas) without disrupting it, so extending that propagation to a Delta/Parquet mirror in OneLake is a natural extension of a pattern the engine already implements, rather than a bolted-on export pipeline ^[S2].

Underneath that compute/storage split, the durable Azure Storage layer itself carries a redundancy choice made at database creation time: locally redundant, zone-redundant, or read-access geo-redundant storage (including a geo-zone-redundant option), and whichever option is picked governs both data storage redundancy and backup storage redundancy for the life of the database ^[S2]. That choice is worth making deliberately up front — it isn't something you can casually change later.

How it works internally#

Under Hyperscale, the engine is composed of four cooperating component types communicating over Azure's redundant network — compute nodes, page servers, a log service, and Azure Storage — rather than one process handling query execution, logging, and storage together ^[S2].

Compute nodes run the same relational/language/query/transaction-processing engine used across other Azure SQL Database tiers. Each one maintains a local SSD-based cache called RBPEX (Resilient Buffer Pool Extension), sized to the compute tier, which minimizes round-trips to remote page servers for hot data pages ^[S2].

Page servers form a scaled-out storage tier: each page server owns a subset of the database's pages (up to 128 GB per page server), keeps a local SSD cache of those pages, serves them to compute nodes on demand, and stays current by replaying log records forwarded from the log service. Each page server also has its own redundant replica ^[S2].

The log service is the propagation backbone. It accepts transaction log records from the primary compute node and fans them out two ways: to page servers, which apply the changes to their owned pages, and to secondary compute replicas, which replay only the changes already resident in their buffer pool or RBPEX cache. It then persists the log records to Azure Storage — treated as effectively infinite — which removes the need for frequent log truncation ^[S2].

The compute topology itself is fixed in shape: exactly one primary node handles read-write transactions, up to four on-demand high-availability secondary replicas act as hot standbys that can offload reads, up to 30 named replicas support OLTP read-scale-out and HTAP scenarios (each of which can itself carry up to 4 HA replicas), and an optional geo-secondary in another Azure region exists for disaster recovery ^[S2].

Because log growth from missed backups or slow secondary replication is architecturally eliminated by the log service's continuous fan-out and durable persistence, Hyperscale databases sidestep a storage-pressure failure mode that's common in traditional log-shipping engines ^[S2].

The table-health diagnostic surfaced through sp_get_table_health_metrics is a useful window into what the replicated side of this architecture actually looks like day to day: because the SQL analytics endpoint is a read path over Delta tables in OneLake, its health signal is fundamentally about that storage layer's physical state — checkpoint recency, file-size fragmentation, deleted-row bloat — not about the OLTP engine's transactional health, which is a separate concern entirely ^[S3].

Performance characteristics#

The compute/storage separation is what lets the two dimensions scale independently: you can grow storage or add read compute (via HA or named replicas) without touching the primary OLTP engine's write path ^[S2]. That independence is the structural reason SQL database in Fabric can absorb analytical read growth — via the SQL analytics endpoint and named replicas — without it becoming an OLTP performance problem.

Backups are implemented as storage snapshots of the data files rather than a traditional full/differential/log backup chain. That means restore time stays fast regardless of overall database size, and a database can be restored to any point in time within its retention window ^[S2].

Coming soon — this depth isn't in the knowledge base yet. It needs an L4/L5 source such as a Microsoft engineering blog or benchmark publishing measured throughput/latency numbers for the OneLake replication path itself (e.g. replication lag under write load, Parquet conversion throughput) beyond the qualitative "near-real-time" characterization. Tracked in content/queue.md.

Worked example: an operational + analytical order system#

Take a retail order-processing scenario: a checkout service writes orders through the SQL database's transactional engine, and a reporting dashboard needs near-real-time sales figures — plus an occasional historical snapshot — without slowing down checkout.

1. The application writes normally, through Entra-authenticated connections:

INSERT INTO dbo.Orders (OrderId, CustomerId, Amount, CreatedAt)
VALUES (@OrderId, @CustomerId, @Amount, SYSUTCDATETIME());

No SQL logins are involved — the connection string authenticates via Entra ID, and the caller (a managed identity, in this case) must already hold Read/Write item permission on the database ^[S1].

2. Fabric replicates the change into OneLake automatically. No pipeline, no scheduled export — the platform converts the new row into the Delta/Parquet mirror on its own, continuously ^[S1].

3. Before wiring up new reporting, a quick health check confirms the replicated table isn't fragmented:

EXEC sp_get_table_health_metrics @schema_name = 'dbo', @table_name = 'Orders';

This runs without a Spark session, straight from the same pipeline or orchestration tool that manages the rest of the workload ^[S3].

4. The dashboard queries the analytics endpoint, not the primary:

SELECT CAST(CreatedAt AS date) AS OrderDate, SUM(Amount) AS DailyRevenue
FROM SalesDb.dbo.Orders
GROUP BY CAST(CreatedAt AS date)
ORDER BY OrderDate DESC;

This runs against the read-only SQL analytics endpoint backed by OneLake, so it never competes with the checkout service's write traffic for the same engine resources ^[S1].

5. When someone asks for last quarter's numbers exactly as they stood on a given day, the same endpoint answers it with Time Travel instead of a separate historical export, subject to the preview's retention window ^[S5]:

SELECT CAST(CreatedAt AS date) AS OrderDate, SUM(Amount) AS DailyRevenue
FROM SalesDb.dbo.Orders
OPTIONS (TIMESTAMP AS OF '2026-03-31T23:59:59Z')
GROUP BY CAST(CreatedAt AS date)
ORDER BY OrderDate DESC;

6. If the dashboard also needs to enrich orders with product data sitting in a separate Fabric Warehouse, three-part naming resolves the join across items without any data movement, because both sides are ultimately Delta tables in OneLake ^[S1]:

SELECT o.OrderId, p.ProductName, o.Amount
FROM SalesDb.dbo.Orders o
JOIN ProductWarehouse.dbo.Products p ON p.ProductId = o.ProductId;

Inference: the exact connection-string syntax and managed-identity wiring above are typical Entra-authenticated T-SQL patterns; the claims establish that Entra-only auth and Read item permission are required, not the literal connection string format.