i

Description

Câble réseau bleu terminé par sa fiche transparente, sur fond blanc.

Source

Raysonho @ Open Grid Scheduler / Grid Engine · original page

Licence

CC0 Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

SQL Server in SaaS — architecture

You will know how to choose between Azure SQL Database, Managed Instance, and SQL database in Fabric, then between DTU, vCore, serverless, Hyperscale, and elastic pools.

In brief

• You will know how to choose between Azure SQL Database, Managed Instance, and SQL database in Fabric, then between DTU, vCore, serverless, Hyperscale, and elastic pools.
• You will know how to design a high-performance star-schema datamart on the SQL side (columnstore, indexes, partitioning, statistics, Query Store).
• You will know how to choose the Power BI connection mode (Import, DirectQuery, composite, aggregations, hybrid tables, incremental refresh, Direct Lake via mirroring) and the network (gateway, VNet data gateway, private endpoints).
• You will know how to implement security (Entra, SSO, RLS on the SQL side or model side) and industrialize (PBIP, Git, deployment pipelines, multi-environment).
• Scope of the topic: see SQL Server in SaaS — overview. Related pitfalls are in 07, AI in 08.

On this page

1. Architecture overview
2. Database offerings
   1. 🟢 Novice — Choosing the offering
   2. 🟡 Practical — The limits that matter for a datamart
3. Service and cost models
   1. 🟢 Novice — DTU or vCore?
   2. 🟡 Practical — vCore tiers
   3. 🟡 Practical — Serverless, elastic pools
   4. 🔴 Expert — Sizing with data
4. The star-schema datamart on the SQL side
   1. 🟢 Novice — Why the star
   2. 🟡 Practical — Design rules useful for Power BI
5. Indexes, columnstore, partitioning
   1. 🟡 Practical — Choosing the right index
   2. 🔴 Expert — Partitioning
6. Statistics and Query Store
   1. 🟡 Practical — Statistics
   2. 🟡 Practical — Query Store
7. Power BI connection: choosing the mode
   1. 🟢 Novice — The three modes
   2. 🟡 Practical — Composite, aggregations, hybrid tables
   3. 🔴 Expert — Choose by scenario
8. Incremental refresh and scheduling
   1. 🟡 Practical — Mechanics
   2. 🟡 Practical — Refresh limits (as of 2026-10, verify in official documentation)
   3. 🔴 Expert — Orchestrate refresh after consolidation
9. Network: gateway or not
   1. 🟡 Practical — The four configurations
10. Identity, SSO, and RLS
    1. 🟢 Novice — Who connects to the database?
    2. 🟡 Practical — RLS on the SQL side or model side
    3. 🔴 Expert — Back-filtering and relationship security
11. Mirroring and Direct Lake
    1. 🟡 Practical — The principle
12. ALM and multi-environment
    1. 🟡 Practical — The three layers to version
    2. 🟡 Practical — Deployment pipelines and multi-environment
    3. 🔴 Expert — Governing the lifecycle
13. /5 Ratings of architecture variants
14. See also

Level: 🟢→🔴

Architecture overview#

 Application SFA ──► Application database ──► T-SQL consolidation ──► DATAMART (star)
                                                                     │
        ┌────────────────────────────────────────────────────────────┤
        │ Import / DirectQuery / composite              mirroring ───┤
        ▼                                                            ▼
  Semantic model (Power BI service)                        OneLake (Delta) ──► Direct Lake
        │                                                            │
        └──────────────► Reports (multilingual, RLS) ◄──────────────┘

Three decision areas, independent but coupled: where the database lives (offering and service tier), how Power BI reads it (mode and network), who sees what (identity and RLS). A fourth axis runs through everything: how you deliver (ALM).

Database offerings#

🟢 Novice — Choosing the offering#

🟡 Practical — The limits that matter for a datamart#

• No traditional cross-database queries in Azure SQL Database: consolidation that reads an application database from the datamart goes through external tables (CREATE EXTERNAL DATA SOURCE ... TYPE = RDBMS), elastic queries, or pipeline loading. This is a frequent friction point for a “two-database” architecture (application + datamart).
• No SQL Agent in Azure SQL Database: scheduling is handled by Azure Automation, Elastic Jobs, Data Factory/Fabric pipelines, a Function, or a Logic App.
• No xp_cmdshell, no local files: file imports are done from Blob Storage (BULK INSERT/OPENROWSET with external source).
• Maximum size: from a few hundred GB on standard tiers up to 128 TB on Hyperscale (exact values should be checked in the “resource limits” documentation, they evolve).
• Collation: fixed when the database is created; changing it means recreating and reloading. Choose it first (see 07).

Service and cost models#

🟢 Novice — DTU or vCore?#

• DTU (Database Transaction Unit): a composite unit (CPU + memory + IO) sold in tiers (Basic, Standard, Premium). Simple, opaque. Suitable for small databases with stable workload.
• vCore: you choose the number of cores, hardware generation, and tier (General Purpose, Business Critical, Hyperscale). Clear, comparable to a server, enables reservation discounts and Azure Hybrid Benefit when you own licenses.

🟡 Practical — vCore tiers#

🟡 Practical — Serverless, elastic pools#

• Serverless (General Purpose and Hyperscale): compute scales between a minimum and maximum vCore, billed per second, with auto-pause after inactivity. Excellent for dev/test and daytime-consulted reports. Pitfall: resume after pause takes on the order of a minute and makes the first query fail or slow down (a scheduled refresh that wakes the database can exceed a connection timeout).
• Elastic pools: multiple databases share a DTU/vCore budget. Cost-effective if database peaks do not coincide (e.g., one datamart per customer, one per environment). Pitfall: internal noisy neighbor effect — one greedy database starves the others, and performance measurement becomes pool-level, not database-level.
• Free offer: Azure SQL Database provides a monthly free serverless compute and storage offer for learning and prototyping (exact volumes to verify, status as of 2026-10).

🔴 Expert — Sizing with data#

1. Collect 1–2 weeks of metrics (sys.dm_db_resource_stats: CPU, data IO, log IO, memory, workers); long history is in master.sys.resource_stats.
2. Calculate the 95th percentile per resource; the limiting resource determines the tier (often log IO in consolidation, not CPU).
3. Choose the service tier, then test the worst window (consolidation + simultaneous Power BI refresh).
4. Plan a documented scale-up Plan B (ALTER DATABASE ... MODIFY (SERVICE_OBJECTIVE = ...) command; operation is online but briefly flips connections).

-- Consumption profile over the past hour (15 s granularity)
SELECT TOP (240) end_time, avg_cpu_percent, avg_data_io_percent,
       avg_log_write_percent, avg_memory_usage_percent, max_worker_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

-- Switch the datamart to another service objective
ALTER DATABASE [Datamart] MODIFY (EDITION = 'GeneralPurpose', SERVICE_OBJECTIVE = 'GP_Gen5_8');

The star-schema datamart on the SQL side#

🟢 Novice — Why the star#

A star schema separates facts (measurable events: visits, orders, shelf readings) from dimensions (analysis axes: customer, product, date, user). Power BI and the SQL engine take advantage of this: simple joins, clear cardinalities, filters that flow down from dimensions to facts. Reference: The Data Warehouse Toolkit (Kimball). See the PD matrix, modeling and architecture.

🟡 Practical — Design rules useful for Power BI#

• Integer surrogate keys (INT/BIGINT) on dimensions; a composite text key costs memory in Import and causes slow joins in DirectQuery.
• A date table that is complete (continuous, with no gaps) with calendar and fiscal attributes; marked as a date table in the model.
• Explicit granularity for each fact (“one row = one visit × one product × one day”) documented in SQL extended properties.
• Narrow types: date rather than datetime, bounded decimal(18,4), bit for booleans. Less cardinality = better VertiPaq compression.
• No unnecessary columns: every exposed column costs memory; hide or remove unused technical identifiers.
• Exposure views (CREATE VIEW rpt.vFaitVisite): a stable contract layer between the datamart and the model. Renaming a table column without touching the view avoids breaking reports (see the BI matrix, Semantic Model).
• Multilingual labels: translation table (label × language) rather than Lib_FR, Lib_EN columns; the model exposes translations at the metadata level (model cultures) — see 07.

Indexes, columnstore, partitioning#

🟡 Practical — Choosing the right index#

CCI rules: load in batches of ≥ 102,400 rows (ideally ≥ 1,048,576) to get directly compressed rowgroups; monitor sys.dm_db_column_store_row_group_physical_stats (open, deleted, too-small rowgroups); REORGANIZE to compact, REBUILD for heavy fragmentation (both online depending on version).

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FaitVisite ON dbo.FaitVisite;

SELECT OBJECT_NAME(object_id) AS tbl, state_desc, COUNT(*) AS rowgroups,
       SUM(total_rows) AS lignes, AVG(100.0 * deleted_rows / NULLIF(total_rows,0)) AS pct_supprime
FROM sys.dm_db_column_store_row_group_physical_stats
GROUP BY object_id, state_desc;

🔴 Expert — Partitioning#

Partitioning (function + partition scheme, generally by month) serves three objectives: sliding window (delete/archive with SWITCH without DELETE), targeted incremental refresh (rebuild a partition index), and partition elimination in date-filtered queries. It does not make a poorly designed query faster, and it adds complexity (index alignment, statistics by table and not by partition except incremental statistics).

CREATE PARTITION FUNCTION pf_Mois (date)
AS RANGE RIGHT FOR VALUES ('2026-01-01','2026-02-01','2026-03-01'); -- to be extended through automation

CREATE PARTITION SCHEME ps_Mois AS PARTITION pf_Mois ALL TO ([PRIMARY]);

Statistics and Query Store#

🟡 Practical — Statistics#

Statistics feed the optimizer. Azure SQL creates and updates them automatically (AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS, and depending on version a dynamic threshold). After a large load (consolidation), the automatic threshold may lag; explicitly update changed tables at the end of the load (UPDATE STATISTICS dbo.FaitVisite WITH FULLSCAN or SAMPLE), especially on columns with skewed distributions.

🟡 Practical — Query Store#

Query Store is enabled by default on Azure SQL Database. It captures queries, plans, and execution statistics by interval. BI uses:

• identify the most expensive DirectQuery queries (filter by query_text or by Power BI application/login identifier);
• spot a plan regression after a compatibility level change or service tier upgrade;
• force a known good plan (sp_query_store_force_plan) while waiting for a durable fix;
• enable automatic tuning (AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)) so the engine can automatically roll back a detected regression.

SELECT TOP (20) q.query_id, qt.query_sql_text,
       SUM(rs.count_executions) AS exec_count,
       SUM(rs.avg_cpu_time * rs.count_executions)/1000.0 AS cpu_ms_total
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY cpu_ms_total DESC;

Power BI connection: choosing the mode#

🟢 Novice — The three modes#

🟡 Practical — Composite, aggregations, hybrid tables#

• Composite model: some tables in Import (dimensions), others in DirectQuery (large fact tables). Relationships across modes are “limited” (limited relationships): joins are done in memory, not in SQL, and can slow things down.
• Aggregations: an aggregated table in Import (by day × product) serves most visuals; detail only drills down to DirectQuery on demand. Typical gain: one to two orders of magnitude on summary visuals. Requires aggregations to be managed (mapped in the model) and the aggregate level to cover usual filters.
• Hybrid tables: a table with incremental refresh where the most recent partition is in DirectQuery (option “Get the latest data in real time with DirectQuery”). Historical data is in compressed Import, while the “current day” is read live.
• Dual storage mode: a dimension can be served in Import or in DirectQuery depending on query context.

🔴 Expert — Choose by scenario#

// Power Query: push the date filter to the source (query folding)
let
  Source   = Sql.Database("serveur.database.windows.net", "Datamart"),
  Faits    = Source{[Schema="rpt", Item="vFaitVisite"]}[Data],
  Filtre   = Table.SelectRows(Faits, each [DateVisite] >= RangeStart and [DateVisite] < RangeEnd)
in
  Filtre

Check query folding (right-click the step → “View Native Query”): without folding, Power BI pulls the entire table and then filters in memory, which defeats the purpose of incremental refresh.

Incremental refresh and scheduling#

🟡 Practical — Mechanics#

RangeStart/RangeEnd parameters (date/time type), policy (store N months/years, refresh the last M days), option to “detect data changes” (last modified date column, queries a MAX), option for “only complete periods.” The Power BI service creates partitions (years, months, days) and only reprocesses recent ones. Conditions: a query that is foldable to SQL, and an immutable date filter column (visit date should not change retroactively; otherwise use the modified date).

Typical policy: store 5 years, refresh last 10 days, detect changes on [DateMaj]
→ old yearly partitions (frozen), recent monthly partitions, daily partitions for the last 10 days

🟡 Practical — Refresh limits (as of 2026-10, verify in official documentation)#

Model size (order of magnitude): about 1 GB per model with Pro licensing; on Premium/Fabric capacity, the limit depends on the SKU and the large model storage format (“large semantic model storage format”), up to the capacity memory limit. It is better to read the up-to-date limits page than rely on these values.

🔴 Expert — Orchestrate refresh after consolidation#

Scheduling at a fixed time is fragile: if consolidation runs late, the model loads partial data. Prefer an event-driven trigger: the last consolidation step writes a marker (dbo.Journal_Consolidation with OK status), and a pipeline (Fabric, Data Factory, Power Automate, or Azure Automation) calls the refresh REST API:

# Triggers model refresh (Entra token required)
$body = @{ type = "full"; commitMode = "transactional"; maxParallelism = 4; retryCount = 2 } | ConvertTo-Json
Invoke-RestMethod -Method Post -Headers @{ Authorization = "Bearer $jeton" } `
  -ContentType "application/json" -Body $body `
  -Uri "https://api.powerbi.com/v1.0/myorg/groups/$groupeId/datasets/$modeleId/refreshes"

The enhanced refresh API allows targeting tables/partitions, canceling, and tracking status — preferable for fine-grained orchestration.

Network: gateway or not#

🟡 Practical — The four configurations#

The VNet data gateway is a Microsoft-managed service deployed in a delegated subnet: no VM to administer, scales by number of nodes; supports selected connectors (including SQL Server/Azure SQL), to be verified per connector and mode (as of 2026-10).

Identity, SSO, and RLS#

🟢 Novice — Who connects to the database?#

Four possible identities: the model itself (a service account, database account, or Entra service principal); the end user (SSO in DirectQuery); a SQL account (not recommended); the workspace identity (Fabric). In Import mode, it is almost always a service identity stored in the model's data source credentials. In DirectQuery with SSO, the user identity is passed to SQL.

🟡 Practical — RLS on the SQL side or model side#

Decision rule: Import → model RLS; DirectQuery with audit requirement → SQL RLS + SSO; both for defense in depth (the model filters, the database enforces).

-- SQL RLS: a user only sees their sector
CREATE FUNCTION sec.fnFiltreSecteur(@SecteurId int)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS ok
FROM sec.AccesUtilisateur a
WHERE a.SecteurId = @SecteurId AND a.Login = USER_NAME();

CREATE SECURITY POLICY sec.PolSecteur
ADD FILTER PREDICATE sec.fnFiltreSecteur(SecteurId) ON dbo.FaitVisite
WITH (STATE = ON);

🔴 Expert — Back-filtering and relationship security#

When RLS filters a dimension, the filter propagates to facts through the relationship; but a measure that counts members of another dimension (number of products, customers) varies by role, because the filter flows back and restricts dimensions through facts (bidirectional security filtering). Controls: disable bidirectional propagation unless needed, test each role with “View as,” compare totals with and without role. See the BI matrix, RLS security.

Mirroring and Direct Lake#

🟡 Practical — The principle#

Mirroring from Azure SQL Database to Fabric replicates the database (via change feed) into OneLake in Delta format, almost continuously, with no pipeline to write and without consuming Fabric compute capacity for replication itself (according to documentation, to be verified). It is generally available for Azure SQL Database (status 2026-10, official announcement on the Fabric blog); mirroring for Azure SQL Managed Instance is also GA; for hosted SQL Server, check current status. On the copy, you get a read-only SQL analytics endpoint and can build a Direct Lake model.

Azure SQL Database ─(change feed)─► OneLake (Delta, mirror) ─► Direct Lake ─► report
            │                                              │
       OLTP/consolidation                      analytics, data science, other models

Benefits: no more BI read load on the production database; one shared data copy for multiple models; freshness on the order of a minute to a few minutes. Constraints: tables without a primary key or unsupported types based on the current list; security (SQL RLS) is not automatically replicated to the copy — must be redefined on the Fabric side; Fabric capacity is consumed by queries on the mirror and by the Direct Lake model.

For the lakehouse counterpart and pipelines, see 02-onelake-mobile-saas-architecture.md; for the full comparison, 09-comparatif-onelake-vs-sqlserver.md.

ALM and multi-environment#

🟡 Practical — The three layers to version#

1. The database: schema and reference data, versioned as code. Database project (SQL Database Project / SSDT, DACPAC) or replayable, idempotent script bundles (CREATE OR ALTER, parameter MERGE). A bundle must be re-runnable without breaking what already exists: no blind DROP; customer customizations are replayed after migration.
2. The semantic model and reports: PBIP format (Power BI project) with model definition in TMDL (text) and report in PBIR (JSON files per page/visual); versionable with Git, mergeable, comparable. The Source Control for Data: SQL and Power BI section details expectations; Git In Depth and TFVC in Depth describe version control systems.
3. Environment parameters: server, database, identity, refresh policy — externalized (model parameters, deployment pipeline parameter rules), never hardcoded.

🟡 Practical — Deployment pipelines and multi-environment#

DEV (Git, PBIP)  ──►  TEST (business acceptance testing)  ──►  PROD
  DEV database          TEST database                        PROD database
  (serverless, small)   (anonymized copy)                   (sized tier)

• Fabric/Power BI deployment pipelines: content promotion (model, reports) across development, test, and production workspaces; parameter rules and data source rules to switch servers by environment; content comparison between stages.
• Fabric Git integration: the workspace syncs with a branch (Azure DevOps or GitHub); commit/update from the UI; the folder contains artifacts in text format.
• CI/CD: build that validates TMDL syntax and the model (static analysis, Tabular Editor Best Practice Analyzer), database deployment (DACPAC or bundle), then model deployment (pipelines or API/XMLA tools). See CI/CD and CI/CD for data: foundations, datamarts, pipelines, and reports.
• Environments: a lightweight DEV database (serverless with pause), representative TEST (same volume or a stratified sample, anonymized data — GDPR), sized PROD. Numeric acceptance testing compares TEST against a business-validated reference (Acceptance testing and business validation).

🔴 Expert — Governing the lifecycle#

• Short-lived branches and frequent merges; no direct edits in the production workspace.
• Shared model (“golden dataset”) certified, with thin reports connected to it; model changes announced via a release calendar.
• Database migration strategy: full consolidation is not always necessary; rebuild only affected tables, and test the modified portion before any archiving.
• Observability: consolidation log (start, end, rows, status) in the database; refresh tracking (API, Fabric logs); alerts on failures and duration drift.
• Living documentation: SQL extended properties → generated catalog/documentation (see the PD matrix, Metadata, Lineage, and Documentation).

/5 Ratings of architecture variants#

Context: team of 3 to 8 people, 10–500 GB datamart, 50–500 users, enterprise reporting (see RATING). Expert assessments, not measurements.

How to read: for almost all enterprise reporting projects, the first row wins. The others are justified by a specific requirement, not by trends. (For the “complexity” criterion, 5/5 means “not very complex.”)

See also#

• overview · difficulties and pitfalls · AI
• OneLake architecture · comparison · cross-cutting good practices · checklist
• BI Matrix: Data Layer and ETL · Performance and Data Volume · Versioning and Industrialization of Reports
• PD Matrix: Lead Time and Industrialization (DataOps)
• Tools: Branching Strategies · Azure DevOps/TFS
• Glossary

✻ Written by Claude Sonnet 5.5