i

Description

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

Source

Raysonho @ Open Grid Scheduler / Grid Engine · page d’origine

Licence

CC0 Domaine public

Photo d’origine, non retouchée. L’arrière-plan en est une version stylisée par le site.

SQL Server en SaaS — architecture

Vous saurez choisir entre Azure SQL Database, Managed Instance et SQL database in Fabric, puis entre DTU, vCore, serverless, Hyperscale et pools élastiques.

En bref

• Vous saurez choisir entre Azure SQL Database, Managed Instance et SQL database in Fabric, puis entre DTU, vCore, serverless, Hyperscale et pools élastiques.
• Vous saurez concevoir un datamart en étoile performant côté SQL (columnstore, index, partitionnement, statistiques, Query Store).
• Vous saurez choisir le mode de connexion Power BI (Import, DirectQuery, composite, agrégations, tables hybrides, actualisation incrémentielle, Direct Lake via mirroring) et le réseau (passerelle, VNet data gateway, endpoints privés).
• Vous saurez placer la sécurité (Entra, SSO, RLS côté SQL ou côté modèle) et industrialiser (PBIP, Git, deployment pipelines, multi-environnements).
• Cadre du sujet : voir la vue d'ensemble. Les pièges associés sont dans 07, l'IA dans 08.

Sur cette page

1. Vue d'ensemble de l'architecture
2. Les offres de base de données
   1. 🟢 Novice — Choisir l'offre
   2. 🟡 Pratique — Les limites qui comptent pour un datamart
3. Les modèles de service et de coût
   1. 🟢 Novice — DTU ou vCore ?
   2. 🟡 Pratique — Les niveaux vCore
   3. 🟡 Pratique — Serverless, pools élastiques
   4. 🔴 Expert — Dimensionner avec des données
4. Le datamart en étoile côté SQL
   1. 🟢 Novice — Pourquoi l'étoile
   2. 🟡 Pratique — Règles de conception utiles à Power BI
5. Index, columnstore, partitionnement
   1. 🟡 Pratique — Choisir le bon index
   2. 🔴 Expert — Partitionnement
6. Statistiques et Query Store
   1. 🟡 Pratique — Statistiques
   2. 🟡 Pratique — Query Store
7. Connexion Power BI : choisir le mode
   1. 🟢 Novice — Les trois modes
   2. 🟡 Pratique — Composite, agrégations, tables hybrides
   3. 🔴 Expert — Choisir par scénario
8. Actualisation incrémentielle et planification
   1. 🟡 Pratique — Mécanique
   2. 🟡 Pratique — Limites d'actualisation (état 2026-10, à vérifier dans la documentation officielle)
   3. 🔴 Expert — Orchestrer l'actualisation après la consolidation
9. Réseau : passerelle ou pas
   1. 🟡 Pratique — Les quatre configurations
10. Identité, SSO et RLS
    1. 🟢 Novice — Qui se connecte à la base ?
    2. 🟡 Pratique — RLS côté SQL ou côté modèle
    3. 🔴 Expert — Rétro-filtrage et sécurité des relations
11. Mirroring et Direct Lake
    1. 🟡 Pratique — Le principe
12. ALM et multi-environnements
    1. 🟡 Pratique — Les trois couches à versionner
    2. 🟡 Pratique — Deployment pipelines et multi-environnements
    3. 🔴 Expert — Gouverner le cycle de vie
13. Notes /5 des variantes d'architecture
14. Voir aussi

Niveau : 🟢→🔴

Vue d'ensemble de l'architecture#

 Application SFA ──► Base applicative ──► Consolidation T-SQL ──► DATAMART (étoile)
                                                                     │
        ┌────────────────────────────────────────────────────────────┤
        │ Import / DirectQuery / composite              mirroring ───┤
        ▼                                                            ▼
  Modèle sémantique (Power BI service)                     OneLake (Delta) ──► Direct Lake
        │                                                            │
        └──────────────► Rapports (multilingues, RLS) ◄──────────────┘

Trois zones de décision, indépendantes mais couplées : où vit la base (offre et niveau de service), comment Power BI la lit (mode et réseau), qui voit quoi (identité et RLS). Un quatrième axe traverse tout : comment on livre (ALM).

Les offres de base de données#

🟢 Novice — Choisir l'offre#

🟡 Pratique — Les limites qui comptent pour un datamart#

• Pas de requêtes inter-bases classiques sur Azure SQL Database : une consolidation qui lit une base applicative depuis le datamart passe par des tables externes (CREATE EXTERNAL DATA SOURCE ... TYPE = RDBMS), des requêtes élastiques, ou un chargement par pipeline. C'est un point de friction fréquent pour une architecture « deux bases » (applicative + datamart).
• Pas de SQL Agent dans Azure SQL Database : la planification passe par Azure Automation, Elastic Jobs, Data Factory/pipelines Fabric, une Function ou un Logic App.
• Pas de xp_cmdshell, pas de fichiers locaux : les imports de fichiers se font depuis Blob Storage (BULK INSERT/OPENROWSET avec source externe).
• Taille maximale : de quelques centaines de Go sur les niveaux classiques à 128 To sur Hyperscale (valeurs exactes à vérifier dans la documentation « resource limits », elles évoluent).
• Collation : fixée à la création de la base ; la changer implique de recréer et recharger. Choisissez-la avant tout (voir 07).

Les modèles de service et de coût#

🟢 Novice — DTU ou vCore ?#

• DTU (Database Transaction Unit) : une unité composite (CPU + mémoire + IO) vendue par paliers (Basic, Standard, Premium). Simple, opaque. Adapté à de petites bases à charge stable.
• vCore : on choisit le nombre de cœurs, la génération matérielle, le niveau (General Purpose, Business Critical, Hyperscale). Lisible, comparable à un serveur, permet les remises de réservation et l'avantage hybride (Azure Hybrid Benefit) quand vous possédez des licences.

🟡 Pratique — Les niveaux vCore#

🟡 Pratique — Serverless, pools élastiques#

• Serverless (General Purpose et Hyperscale) : le calcul s'ajuste entre un minimum et un maximum de vCore, facturé à la seconde, avec pause automatique après inactivité. Excellent pour dev/test et rapports consultés de jour. Piège : la reprise après pause prend de l'ordre de la minute et fait échouer ou ralentir la première requête (une actualisation planifiée qui réveille la base peut dépasser un délai de connexion).
• Pools élastiques : plusieurs bases se partagent un budget de DTU/vCore. Rentable si les pics des bases ne coïncident pas (ex. un datamart par client, un par environnement). Piège : la voisinage bruyant interne — une base gourmande prive les autres, et la mesure de performance devient celle du pool, pas de la base.
• Offre gratuite : Azure SQL Database propose une offre gratuite mensuelle de calcul serverless et de stockage pour l'apprentissage et le prototypage (volumes précis à vérifier, état 2026-10).

🔴 Expert — Dimensionner avec des données#

1. Collecter 1 à 2 semaines de métriques (sys.dm_db_resource_stats : CPU, IO données, IO journal, mémoire, workers) ; l'historique long est dans master.sys.resource_stats.
2. Calculer le 95e centile par ressource ; la ressource limitante décide du niveau (souvent l'IO journal en consolidation, pas le CPU).
3. Choisir le niveau de service puis tester la pire fenêtre (consolidation + actualisation Power BI simultanées).
4. Prévoir un plan B de montée en charge documenté (commande ALTER DATABASE ... MODIFY (SERVICE_OBJECTIVE = ...) ; l'opération est en ligne mais bascule brièvement les connexions).

-- Profil de consommation sur l'heure écoulée (granularité 15 s)
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;

-- Passer le datamart sur un autre objectif de service
ALTER DATABASE [Datamart] MODIFY (EDITION = 'GeneralPurpose', SERVICE_OBJECTIVE = 'GP_Gen5_8');

Le datamart en étoile côté SQL#

🟢 Novice — Pourquoi l'étoile#

Un schéma en étoile sépare les faits (événements mesurables : visites, commandes, relevés de linéaire) des dimensions (axes d'analyse : client, produit, date, utilisateur). Power BI et le moteur SQL en tirent parti : jointures simples, cardinalités claires, filtres qui descendent des dimensions vers les faits. Référence : The Data Warehouse Toolkit (Kimball). Voir la matrice PD, modélisation et architecture.

🟡 Pratique — Règles de conception utiles à Power BI#

• Clés de substitution entières (INT/BIGINT) sur les dimensions ; une clé composite textuelle coûte de la mémoire en Import et des jointures lentes en DirectQuery.
• Une table de dates complète (continue, sans trou) avec attributs calendaires et fiscaux ; marquée comme table de dates dans le modèle.
• Granularité explicite de chaque fait (« une ligne = une visite × un produit × un jour ») documentée dans les propriétés étendues SQL.
• Types étroits : date plutôt que datetime, decimal(18,4) borné, bit pour les booléens. Moins de cardinalité = meilleure compression VertiPaq.
• Pas de colonnes inutiles : chaque colonne exposée coûte en mémoire ; masquez ou retirez les identifiants techniques non utilisés.
• Vues d'exposition (CREATE VIEW rpt.vFaitVisite) : couche de contrat stable entre datamart et modèle. Renommer une colonne de table sans toucher la vue évite de casser les rapports (voir la matrice BI, modèle sémantique).
• Libellés multilingues : table de traduction (libellé × langue) plutôt que colonnes Lib_FR, Lib_EN ; le modèle expose les traductions au niveau des métadonnées (cultures du modèle) — voir 07.

Index, columnstore, partitionnement#

🟡 Pratique — Choisir le bon index#

Règles de CCI : charger par lots de ≥ 102 400 lignes (idéalement ≥ 1 048 576) pour obtenir des rowgroups compressés directement ; surveiller sys.dm_db_column_store_row_group_physical_stats (rowgroups ouverts, supprimés, trop petits) ; REORGANIZE pour compacter, REBUILD si fragmentation lourde (les deux en ligne selon 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 — Partitionnement#

Le partitionnement (fonction + schéma de partition, généralement par mois) sert trois objectifs : fenêtre glissante (supprimer/archiver par SWITCH sans DELETE), actualisation incrémentielle ciblée (reconstruire un index de partition), et élimination de partitions dans les requêtes filtrées par date. Il ne rend pas une requête mal conçue plus rapide, et il ajoute de la complexité (alignement des index, statistiques par table et non par partition sauf statistiques incrémentielles).

CREATE PARTITION FUNCTION pf_Mois (date)
AS RANGE RIGHT FOR VALUES ('2026-01-01','2026-02-01','2026-03-01'); -- à étendre par automatisation

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

Statistiques et Query Store#

🟡 Pratique — Statistiques#

Les statistiques alimentent l'optimiseur. Azure SQL les crée et les met à jour automatiquement (AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS, et selon la version un seuil dynamique). Après un gros chargement (consolidation), le seuil automatique peut tarder ; mettez à jour explicitement les tables modifiées en fin de chargement (UPDATE STATISTICS dbo.FaitVisite WITH FULLSCAN ou SAMPLE), surtout sur des colonnes à distribution asymétrique.

🟡 Pratique — Query Store#

Query Store est activé par défaut sur Azure SQL Database. Il capture requêtes, plans, statistiques d'exécution par intervalle. Usages pour le BI :

• identifier les requêtes DirectQuery les plus coûteuses (filtre par query_text ou par application/identifiant de connexion de Power BI) ;
• repérer une régression de plan après un changement de niveau de compatibilité ou une montée de niveau de service ;
• forcer un plan connu bon (sp_query_store_force_plan) en attendant une correction durable ;
• activer le réglage automatique (AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)) pour que le moteur annule seul une régression détectée.

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;

Connexion Power BI : choisir le mode#

🟢 Novice — Les trois modes#

🟡 Pratique — Composite, agrégations, tables hybrides#

• Modèle composite : certaines tables en Import (dimensions), d'autres en DirectQuery (gros faits). Les relations entre modes sont « limitées » (limited relationships) : les jointures se font en mémoire, non pas en SQL, et peuvent ralentir.
• Agrégations : une table agrégée en Import (par jour × produit) répond à la majorité des visuels ; le détail ne descend en DirectQuery que sur demande. Gain typique : un à deux ordres de grandeur sur les visuels de synthèse. Exige que les agrégations soient gérées (mappées dans le modèle) et que le niveau d'agrégat couvre les filtres usuels.
• Tables hybrides : une table avec actualisation incrémentielle dont la partition la plus récente est en DirectQuery (option « Obtenir les données les plus récentes en temps réel avec DirectQuery »). Le passé est en Import compressé, le « jour en cours » est lu en direct.
• Mode de stockage dual : une dimension peut être servie en Import ou en DirectQuery selon le contexte de la requête.

🔴 Expert — Choisir par scénario#

// Power Query : pousser le filtre de date vers la 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

Vérifiez le query folding (clic droit sur l'étape → « Afficher la requête native ») : sans folding, Power BI rapatrie toute la table puis filtre en mémoire, ce qui annule l'intérêt de l'incrémentiel.

Actualisation incrémentielle et planification#

🟡 Pratique — Mécanique#

Paramètres RangeStart/RangeEnd (type date/heure), politique (stocker N mois/années, actualiser les M derniers jours), option « détecter les modifications de données » (colonne de date de dernière modification, interroge un MAX), option « uniquement les périodes complètes ». Le service Power BI crée des partitions (années, mois, jours) et ne retraite que les récentes. Conditions : requête pliable vers SQL, colonne de date de filtre immuable (la date de visite ne doit pas changer rétroactivement ; sinon utiliser la date de modification).

Politique type : stocker 5 ans, actualiser 10 derniers jours, détecter changements sur [DateMaj]
→ partitions annuelles anciennes (figées), mensuelles récentes, quotidiennes 10 derniers jours

🟡 Pratique — Limites d'actualisation (état 2026-10, à vérifier dans la documentation officielle)#

Taille de modèle (ordre de grandeur) : environ 1 Go par modèle en licence Pro ; sur capacité Premium/Fabric, la limite dépend de la référence (SKU) et du format de grand modèle (« large semantic model storage format »), jusqu'à la mémoire de la capacité. Mieux vaut lire la page de limites à jour plutôt que de se fier à ces valeurs.

🔴 Expert — Orchestrer l'actualisation après la consolidation#

Planifier à heure fixe est fragile : si la consolidation dépasse son horaire, le modèle charge des données partielles. Préférer un déclenchement par événement : la dernière étape de la consolidation écrit un marqueur (dbo.Journal_Consolidation avec statut OK), et un pipeline (Fabric, Data Factory, Power Automate ou Azure Automation) appelle l'API REST d'actualisation :

# Déclenche l'actualisation d'un modèle (jeton Entra requis)
$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"

L'API d'actualisation améliorée (enhanced refresh) permet de cibler des tables/partitions, d'annuler, de suivre l'état — préférable pour des enchaînements fins.

Réseau : passerelle ou pas#

🟡 Pratique — Les quatre configurations#

La passerelle de réseau virtuel (VNet data gateway) est un service managé par Microsoft déployé dans un sous-réseau délégué : pas de VM à administrer, mise à l'échelle par nombre de nœuds ; prise en charge de connecteurs choisis (dont SQL Server/Azure SQL), à vérifier pour chaque connecteur et mode (état 2026-10).

Identité, SSO et RLS#

🟢 Novice — Qui se connecte à la base ?#

Quatre identités possibles : le modèle lui-même (un compte de service, de base ou principal de service Entra) ; l'utilisateur final (SSO en DirectQuery) ; un compte SQL (déconseillé) ; l'identité de l'espace de travail (Fabric). En Import, c'est presque toujours une identité de service stockée dans les informations d'identification de la source du modèle. En DirectQuery avec SSO, c'est l'utilisateur qui est présenté à SQL.

🟡 Pratique — RLS côté SQL ou côté modèle#

Règle de décision : Import → RLS modèle ; DirectQuery avec exigence d'audit → RLS SQL + SSO ; les deux pour une défense en profondeur (le modèle filtre, la base garantit).

-- RLS SQL : un utilisateur ne voit que son secteur
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 — Rétro-filtrage et sécurité des relations#

Quand la RLS filtre une dimension, le filtre se propage vers les faits par la relation ; mais une mesure qui compte les membres d'une autre dimension (nombre de produits, de clients) varie selon le rôle, car le filtre remonte et restreint les dimensions via les faits (filtre bidirectionnel de sécurité). Contrôles : désactiver la propagation bidirectionnelle sauf besoin, tester chaque rôle avec « Afficher en tant que », comparer les totaux avec et sans rôle. Voir la matrice BI, sécurité RLS.

Mirroring et Direct Lake#

🟡 Pratique — Le principe#

Le mirroring d'Azure SQL Database vers Fabric réplique la base (par journal de modifications) dans OneLake au format Delta, quasiment en continu, sans pipeline à écrire et sans consommer de capacité de calcul Fabric pour la réplication elle-même (selon la documentation, à vérifier). Il est disponible en disponibilité générale pour Azure SQL Database (état 2026-10, annonce officielle sur le blog Fabric) ; le mirroring d'Azure SQL Managed Instance l'est aussi ; pour SQL Server hébergé, vérifier le statut courant. Sur la copie, on obtient un point de terminaison d'analytique SQL en lecture seule et on peut bâtir un modèle Direct Lake.

Azure SQL Database ─(journal de modifications)─► OneLake (Delta, miroir) ─► Direct Lake ─► rapport
            │                                              │
       OLTP/consolidation                      analytique, data science, autres modèles

Avantages : plus de charge de lecture BI sur la base de production ; un exemplaire partagé des données pour plusieurs modèles ; fraîcheur de l'ordre de la minute à quelques minutes. Contraintes : tables sans clé primaire ou types non pris en charge selon la liste en vigueur ; la sécurité (RLS SQL) n'est pas automatiquement répliquée sur la copie — à redéfinir côté Fabric ; consommation de capacité Fabric par les requêtes sur le miroir et par le modèle Direct Lake.

Pour le pendant lakehouse et les pipelines, voir 02-onelake-mobile-saas-architecture.md ; pour la comparaison complète, 09-comparatif-onelake-vs-sqlserver.md.

ALM et multi-environnements#

🟡 Pratique — Les trois couches à versionner#

1. La base : schéma et données de référence, versionnés comme code. Projet de base de données (SQL Database Project / SSDT, DACPAC) ou kits de scripts rejouables et idempotents (CREATE OR ALTER, MERGE de paramètres). Un kit doit être rejouable sans casser l'existant : pas de DROP aveugle ; les personnalisations client sont rejouées après migration.
2. Le modèle sémantique et les rapports : format PBIP (projet Power BI) avec définition du modèle en TMDL (texte) et du rapport en PBIR (fichiers JSON par page/visuel) ; versionnables avec Git, fusionnables, comparables. Le dossier GDS et données SQL/BI détaille les attentes ; Git en profondeur et TFVC en profondeur décrivent les systèmes de gestion de version.
3. Les paramètres d'environnement : serveur, base, identité, politique d'actualisation — externalisés (paramètres de modèle, règles de paramètres des deployment pipelines), jamais codés en dur.

🟡 Pratique — Deployment pipelines et multi-environnements#

DEV (Git, PBIP)  ──►  TEST (recette métier)  ──►  PROD
  base DEV              base TEST                 base PROD
  (serverless, petit)   (copie anonymisée)        (niveau dimensionné)

• Deployment pipelines Fabric/Power BI : promotion de contenu (modèle, rapports) entre espaces de travail de développement, test, production ; règles de paramètres et de sources de données pour basculer de serveur par environnement ; comparaison de contenu entre étapes.
• Intégration Git de Fabric : l'espace de travail se synchronise avec une branche (Azure DevOps ou GitHub) ; engagement/mise à jour depuis l'interface ; le dossier contient les artefacts au format texte.
• CI/CD : build qui valide la syntaxe TMDL et le modèle (analyses statiques, Tabular Editor Best Practice Analyzer), déploiement de la base (DACPAC ou kit), puis déploiement du modèle (pipelines ou outils d'API/XMLA). Voir CI/CD et CI/CD pour la data.
• Environnements : une base DEV allégée (serverless avec pause), TEST représentative (même volumétrie ou un échantillon stratifié, données anonymisées — RGPD), PROD dimensionnée. La recette de chiffres compare TEST à une référence validée par le métier (recette et validation métier).

🔴 Expert — Gouverner le cycle de vie#

• Branches courtes et fusions fréquentes ; interdiction de modifier directement l'espace de travail de production.
• Modèle partagé (« golden dataset ») certifié, rapports minces qui s'y connectent ; changements de modèle annoncés via un calendrier de versions.
• Stratégie de migration de la base : une consolidation complète n'est pas toujours nécessaire ; reconstruire seulement les tables touchées, et tester la portion modifiée avant tout archivage.
• Observabilité : journal de consolidation (début, fin, lignes, statut) dans la base ; suivi des actualisations (API, journaux Fabric) ; alertes sur échecs et sur dérive de durée.
• Documentation vivante : propriétés étendues SQL → catalogue/documentation générés (voir la matrice PD, métadonnées et lineage).

Notes /5 des variantes d'architecture#

Contexte : équipe de 3 à 8 personnes, datamart 10–500 Go, 50–500 utilisateurs, reporting d'entreprise (voir NOTATION). Appréciations d'expert, non des mesures.

Lecture : pour presque tous les projets de reporting d'entreprise, la première ligne l'emporte. Les autres se justifient par une exigence précise, pas par la mode. (Pour le critère « complexité », 5/5 signifie « peu complexe ».)

Voir aussi#

• Vue d'ensemble · Difficultés et pièges · IA
• Architecture OneLake · Comparatif · Bonnes pratiques transverses · Checklist
• Matrice BI : couche données et ETL · performance et volumétrie · versionnement et industrialisation
• Matrice PD : délais et industrialisation DataOps
• Outils : stratégies de branches · Azure DevOps/TFS
• Glossaire

✻ Rédigé par Claude Sonnet 5.5