5. Administration des Bases de Données

L’administration des bases de données est un domaine crucial qui englobe plusieurs aspects techniques et organisationnels pour assurer la gestion efficace des données. Voici une version détaillée, mise à jour et approfondie du cours sur l’Administration des Bases de Données. Ce chapitre couvre les responsabilités techniques de l’Administrateur de Bases de Données (DBA).

Cours Détaillé : Administration des Bases de Données

L’administration des bases de données (DBA - Database Administrator) est un rôle stratégique qui garantit la disponibilité, la performance et la sécurité du patrimoine informationnel de l’entreprise.

I. Implantation Physique des Données

L’implantation physique est la manière dont le SGBD (Système de Gestion de Base de Données) interagit avec le matériel de stockage. Le choix du support influence directement les performances.

1. Hiérarchie et Choix du Stockage

Le temps d’accès aux données est le goulot d’étranglement principal. Le DBA doit choisir le bon support pour la bonne charge de travail :

RAM (Mémoire Vive) : La plus rapide. Le DBA configure de larges zones de mémoire (Buffer Cache) pour y garder les données “chaudes” (les plus utilisées).
SSD NVMe (Non-Volatile Memory express) : La norme actuelle pour les bases transactionnelles actives. Il offre une latence extrêmement basse (microsecondes) et un débit élevé.
SSD SATA : Bon compromis pour les serveurs intermédiaires ou les sauvegardes “froides” rapides.
HDD (Disques durs magnétiques) : Utilisés pour les archives massives (Data Warehouses) où le coût au Go prime sur la vitesse.

2. Gestion de l’usure et RAID

Pour les disques SSD, le DBA surveille l’endurance, mesurée en TBW (Terabytes Written) ou en DWPD (Drive Writes Per Day). Écrire trop de données use la mémoire flash.

Technologie Flash : SLC (plus rapide, durable, cher) vs QLC (moins cher, moins durable, pour l’archivage).
RAID (Redundant Array of Independent Disks) : Indispensable pour combiner performance et sécurité.
- RAID 1 (Miroir) : Sécurité parfaite (copie exacte), perte de 50% d’espace.
- RAID 5 : Parité répartie. Bon compromis, mais risque lent en cas de panne d’un disque.
- RAID 10 (1+0) : Combinaison de striping et miroir. Solution recommandée pour les bases de données critiques (haute performance et redondance).

II. Structure de Fichiers et Indexation

1. Organisation Physique des Fichiers

Les SGBD stockent les données dans des fichiers sur le disque. L’organisation interne vise à minimiser les I/O (Entrées/Sorties disque).

Pages ou Blocs : Unité de base du stockage (ex: 8 Ko ou 16 Ko). Le SGBD lit une page entière, même pour une seule donnée.
Extent : Groupe de pages contiguës alloué d’un coup pour optimiser l’allocation.

2. Les Index : L’Accélérateur de Recherche

Un index est une structure distincte de la table, stockée séparément.

Analogie : Un index de livre permet de trouver un mot clé sans lire le livre.
Fonctionnement : La structure la plus courante est le B-Tree (Arbre B).
- C’est un arbre équilibré où les données sont triées.
- La recherche passe par la racine et descend vers les feuilles.
- Complexité : O(log n), ce qui est extrêmement rapide comparé à un parcours séquentiel (O(n)).
Full Table Scan : Si aucun index n’est utilisable, le moteur lit toute la table. C’est acceptable pour les petites tables ou les requêtes renvoyant une grande partie des données, mais catastrophique pour les grandes bases.

III. Contrôle des Accès Concurrents

C’est la capacité à gérer plusieurs utilisateurs modifiant les mêmes données au même moment.

1. Les Problèmes de Concurrence

Sans contrôle, plusieurs anomalies surviennent :

Lecture Sale (Dirty Read) : Lire une donnée modifiée par une transaction non validée (qui pourrait être annulée ensuite).
Lecture Non Reproductible : Relire une donnée et trouver une valeur différente car quelqu’un l’a modifiée entre-temps.
Lecture Fantôme (Phantom Read) : Une requête renvoie un nombre de lignes différent lors de deux exécutions successives.

2. Mécanismes de Verrouillage (Locking)

Pour éviter cela, le SGBD pose des verrous :

Verrou Partagé (Shared Lock - S) : Pour la lecture. Plusieurs utilisateurs peuvent lire en même temps, mais personne ne peut modifier.
Verrou Exclusif (Exclusive Lock - X) : Pour l’écriture (INSERT, UPDATE, DELETE). Un seul utilisateur peut détenir ce verrou, les autres lecteurs doivent attendre.

3. Niveaux d’Isolation

Le DBA définit le niveau d’isolation (Standard SQL) selon les besoins :

Read Uncommitted : Aucune protection (rarement utilisé).
Read Committed : Par défaut dans la plupart des SGBD. On ne lit que les données validées.
Serializable : Isolation totale, comme si les transactions s’exécutaient une par une. Très sécurisé mais impacte la performance.

IV. Résistance aux Pannes (Haute Disponibilité)

La perte de données est inacceptable. Le DBA met en place des stratégies de résilience.

1. Les Journaux de Transaction (WAL - Write Ahead Log)

C’est le cœur de la résistance aux pannes. Toute modification est d’abord écrite dans un journal séquentiel sur le disque avant d’être appliquée aux tables.

En cas de crash, au redémarrage, le SGBD utilise le journal pour “rejouer” les transactions validées et annuler celles qui étaient en cours.

2. Objectifs de Récupération

Le DBA définit des indicateurs clés :

RPO (Recovery Point Objective) : Perte de données acceptable (ex: RPO de 0 = aucune perte).
RTO (Recovery Time Objective) : Temps maximal d’indisponibilité acceptable (ex: remise en service en moins de 1 heure).

3. Réplication et Clusters

Réplication Maître-Esclave (Master-Slave) : Le serveur principal (Maître) copie ses données vers un serveur de secours (Esclave). En cas de panne du maître, l’esclave peut prendre le relais (Failover).
Clustering Actif-Actif : Plusieurs serveurs travaillent ensemble. Si l’un tombe, les autres continuent.

V. Protection et Sécurité des Données

La sécurité se divise en couches (“Defense in Depth”).

1. Contrôle d’Accès (Droit d’entrée)

Application du principe du moindre privilège.

Authentification : Qui êtes-vous ? (Mot de passe complexe, certificats, MFA).
Autorisation : Qu’avez-vous le droit de faire ? (Rôles : ADMIN, USER, READ_ONLY).
Commande clé : GRANT SELECT, INSERT ON Table_Ventes TO Utilisateur_Vente;

2. Chiffrement (Cryptographie)

Au repos (At Rest) : Les fichiers sur le disque sont illisibles sans clé (ex: TDE - Transparent Data Encryption). Protège contre le vol de disque dur.
En transit (In Transit) : Les données circulant sur le réseau sont chiffrées via SSL/TLS. Protège contre l’écoute du réseau (Sniffing).

VI. Paramétrage et Maintenance Opérationnelle

1. Paramétrage (Tuning)

Les bases de données modernes sont gourmandes en mémoire. Le DBA ajuste :

Buffer Pool : Taille de la mémoire cache pour les données et index.
Log Buffer : Taille du tampon pour les journaux.
Connexions : Nombre maximum de connexions simultanées pour éviter la saturation du serveur.

2. Sauvegarde et Restauration

Il existe deux approches principales :

Sauvegarde Logique (Dump) : Export des données en commandes SQL (INSERT INTO...). Portable mais lent à restaurer.
Sauvegarde Physique (Snapshot/Hot Backup) : Copie brute des fichiers de données. Très rapide pour restaurer de grosses bases.

Le test de restauration est l’acte le plus important : Une sauvegarde qui ne se restaure pas est inutile.

VII. Bases de Données Réparties (Distributed Databases)

Face à l’explosion des volumes (Big Data), une seule machine ne suffit plus.

1. Scalabilité Horizontale (Sharding)

Contrairement à la scalabilité verticale (acheter un plus gros serveur), le sharding découpe la base en morceaux (“shards”) répartis sur plusieurs serveurs.

Exemple : Les utilisateurs A-M sur le Serveur 1, N-Z sur le Serveur 2.

2. Théorème CAP

En réparti, on doit choisir selon le théorème CAP :

C (Consistency) : Tous les nœuds voient les mêmes données au même moment.
A (Availability) : Le système répond toujours (même si les données ne sont pas à jour).
P (Partition Tolerance) : Le système continue de fonctionner même si le réseau coupe entre les nœuds.
Choix courant : Les bases NoSQL (ex: MongoDB, Cassandra) privilégient souvent A et P au détriment de C (consistance finale), tandis que les bases relationnelles distribuées privilégient C et P.

VIII. Audit et Optimisation (Performance Tuning)

Le travail du DBA est cyclique : surveiller, analyser, corriger.

1. Audit et Logs

Audit des accès : Qui a accédé à quelle donnée et quand ? Obligatoire pour la conformité (RGPD, Sarbanes-Oxley).
Logs d’erreurs : Surveillance des alertes système (disque plein, corruption de page).

2. Analyse du Plan d’Exécution (`EXPLAIN`)

L’outil principal pour comprendre pourquoi une requête est lente.

La commande EXPLAIN SELECT ... montre le “chemin” emprunté par l’optimiseur.
Signe de bonne santé : Utilisation d’un “Index Scan”.
Signe de problème : “Full Table Scan” sur une grande table.

3. Statistiques

Le SGBD collecte des statistiques sur la distribution des données (combien de lignes, valeurs distinctes). Si ces stats sont obsolètes, l’optimiseur peut choisir un mauvais plan. Le DBA doit planifier des mises à jour des statistiques automatiques ou manuelles.

a. Implantation physique des données

L’implantation physique concerne la manière dont les données sont stockées sur les supports. Cela inclut la définition de la structure de stockage, le choix des systèmes de fichiers, et l’organisation des données pour optimiser l’accès et la performance.

Exemple (SSD NVMe vs SATA) : Les disques SSD NVMe permettent de réduire significativement les temps d’accès. Pour les sauvegardes, les SSD SATA offrent un meilleur rapport coût/taille, mais la longévité et la redondance (RAID) restent un enjeu majeur d’où l’importance des technologies SSD (SLC, MLC, TLC) avec l’évaluation des “Terabytes Written” (TBW).

b. Structure de fichiers et index

La structure de fichiers désigne l’organisation des données sur le disque. Les index sont des structures de données (référentiels) qui améliorent la vitesse des opérations de recherche, car ils permettent d’accéder très rapidement à des enregistrements spécifiques (ex: via un arbre B-Tree) sans parcourir toutes les données séquentiellement (ce qu’on appelle un Full Table Scan).

c. Contrôle des accès concurrents

C’est ce qui permet à plusieurs utilisateurs d’accéder aux données simultanément sans les compromettre. L’utilisation de mécanismes de verrouillage (verrous de lignes, verrous de tables) et de transactions d’isolation évite les conflits, afin d’assurer que des opérations bancaires simultanées n’écrasent pas les actions isolément validées.

d. Résistance aux pannes

Cela reflète la capacité d’un système à continuer de fonctionner malgré des défaillances. Stratégies possibles :

Redondance des données (Clusters, Réplication).
Sauvegardes régulières.
Systèmes de récupération après sinistre (Disaster Recovery).

Un SGBD bien conçu restaure un état cohérent très rapidement après une panne globale en rejouant des journaux et logs de transaction récents.

e. Protection et sécurité des données

Préoccupation majeure de l’administration, la sécurité passe par :

L’implémentation de contrôles d’accès stricts et de rôles.
Le cryptage des données au repos et en transit (SSL/TLS).
Les audits de sécurité réguliers, la conformité légale et les mises à jour récurrentes pour parer les vulnérabilités identifiées.

f. Paramétrage, démarrage, arrêt, sauvegarde et restauration

Le paramétrage du SGBD ajuste les caches (RAM), les buffers et gère les ressources CPU pour des performances maximales.
Le démarrage et l’arrêt demandent un processus propre pour vider les caches persistants sur les disques sans corrompre le système.
Les sauvegardes régulières (SQL Dumps ou copies bit à bit) et les protocoles de restauration complets doivent être éprouvés par de véritables tests de scénario de perte de données.

g. Bases de données réparties et traitement réparti

Les bases de données réparties stockent la donnée sur un ensemble de serveurs géographiquement distribués pour une robustesse et une haute disponibilité maximales. Le traitement réparti scinde alors l’exécution des requêtes sur plusieurs nœuds pour des performances optimales et une scalabilité horizontale parfaite.

h. Audit et optimisation

L’audit : Examiner les logs, l’historique et la trace complète du système pour détecter les anomalies de sécurité ou les requêtes erratiques.
L’optimisation : Ajustement en continu du schéma métadonnées, réparation/création d’index, refonte des requêtes SQL. L’utilisation d’outils de plan d’exécution (comme EXPLAIN sur une requête SQL lente) est la clé de voûte pour identifier les bottlenecks (goulets d’étranglement) avec précision.