Une seule base de données ou plusieurs ?

Le débat entre faire une seule base de données pour différents clients ou bien autant de base que de client est récurent et légitime. Il y a des avantages et des inconvénients à l’un comme à l’autre. Cet article tente d’en montrer tous les aspects afin de faire votre choix en toute connaissance de cause…

0 – Le problème

Imaginons une application qui est utilisée en ASP (Application Service Provider en anglais, c’est à dire « fournisseur de service d’application »), par exemple un éditeur louant son application. Il existe alors deux façons de procéder : faire une seule base mélangeant les données de tous les clients ou bien faire autant de bases qu’il existe de clients.

1 – Une seule base

En procédant par la création d’une seule base mélangeant les données de tous les clients, on doit se poser la question de la manipulation des données spécifiques à chacun des clients.
La encore deux techniques peuvent être utilisées :

  • partitionner par données;
  • partitionner la base par schéma SQL.

Dans le premier cas, il s’agit d’introduire dans chaque clef de chaque table un discriminant, un seul schéma SQL peut être utilisé. Ceci sera le cas dans MySQL qui ne sait toujours pas gérer des bases multi schémas….

Dans le second cas, il suffit de créer autant de schéma SQL qu’il y a de clients concernés.


1.1 – Une seule base avec discriminant

Il suffit de rajouter dans toutes les clefs primaires de toutes les tables, à l’exception des tables communes, une colonne permettant de savoir d’où vient l’insertion. Pour cela il convient de rajouter une table des clients avec une colonne référençant les différents clients. Cette table pourrait avoir la forme suivante :


CREATE TABLE T_CLIENT_CLI
(CLI_ID          SMALLINT NOT NULL PRIMARY KEY,
 CLI_NOM_CLIENT  VARCHAR(64) NOT NULL UNIQUE);

Dés lors toutes les tables « primaires » qui ne présentent pas des données communes (comme par exemple la liste des codes postaux….) doivent être construites avec une clef primaire incorporant la colonne CLI_ID à titre de clef étrangère.
Une table primaire étant une table qui n’est pas cible d’une intégrité référentielle. Par exemple les tables factures et ligne de factures étant liées par l’intégrité référentielles l’une au client, l’autre aux factures, il n’y a pas lieu d’y ajouter cette colonne, car on sait l’origine des données par les jointures. En revanche une table des clients, comme une table des produits n’est généralement pas cible de l’IRD (intégrité référentielle déclarative).
Cette modification peut se faire à l’aide de SQL dynamique, la forme générale étant, pour une table :


ALTER TABLE <NomTable> DROP CONSTRAINT <NomContrainteClefPrimaire>;
ALTER TABLE <NomTable> ADD CLI_ID SMALLINT NOT NULL;
ALTER TABLE <NomTable> ADD CONSTRAINT <NomContrainteClefPrimaire> PRIMARY KEY (CLI_ID, <autresColonnesClef> );

Enfin, la manipulation des données sera simplifiée par la création de vues limitant la visibilité des données aux lignes spécifiques de chacun des clients. À ce niveau deux techniques existent :

  • créer autant de vue que de tables et de clients (vues spécifiques);
  • créer une seule vue « générique » par table en filtrant dynamiquement.

NOTA : contrairement à une idée largement répandue on peut parfaitement mettre à jour des données des tables à travers les vues en appliquant les ordres SQL INSERT, UPDATE ou DELETE directement sur ces vues. Voir les articles que j’ai écrit à ce sujet, en particulier :
http://sqlpro.developpez.com/SGBDR/ReglesCodd/
règle n°6 avec exemples et :
http://blog.developpez.com/sqlpro/p9259/ms-sql-server/exemple-d-utilisation-du-mapping-ro-dire/

1.1.1 – Une seule base, discriminant et vues spécifiques

La première solution (autant de vues que de tables et de clients) devient vite fastidieuse lorsqu’il y a de nombreux clients et multiplie le nombre de vues. De plus cela oblige à modifier l’application pour prendre en compte un nom de vue à chaque fois différent pour chaque client. Enfin, les différences des noms des vues induisent une perte de performance liée à la persistance dans le cache de procédure et ce point n’est pas à négliger !

1.1.2 – Une seule base, discriminant et vues génériques

La seconde solution (des vues génériques) est plus simple à mettre en œuvre à condition de gérer une bonne sécurité d’accès au serveur SQL en jouant sur la notion d’utilisateur SQL, ce qui est généralement une bonne chose !
Cela consiste à créer autant d’utilisateur SQL qu’il y a de client, chaque utilisateur SQL étant codé avec un nom incorporant son identifiant de client, par exemple sous la forme USER_xxxxx ou xxxxx représente le CLI_ID avec complétion par des zéros à gauche.
Voici par exemple comment créer directement tous les utilisateurs de la base sous cette forme à l’aide de SQL dynamique (Transact SQL – MS SQL Server) :


DECLARE @SQL VARCHAR(max);
 
SET @SQL = '';
 
WITH  
T0 AS
(SELECT CAST(CLI_ID AS VARCHAR(5)) AS I,  
        5 - LEN(CAST(CLI_ID AS VARCHAR(5))) AS L
 FROM   T_CLIENT_CLI)
SELECT @SQL = @SQL + 'CREATE USER U_' + REPLICATE('0', L) + I + ';'
FROM T0;
 
EXECUTE (@SQL);

On pourra d’ailleurs profiter de ce script pour donner aux utilisateurs tous les privilèges sur les tables ou les vues, par exemple en rajoutant dans le SELECT final :

+ 'GRANT ALL ON DATABASE::Mabase TO U_' + REPLICATE('0', L) + I + ';'

Chaque client étant connecté au serveur à l’aide de don nom SQL, dès lors le critère de filtrage des vues pourra être le suivant :
WHERE   CLI_ID = CAST(SUBSTRING(USER, 3, 5) AS SMALLINT)

Inconvénient de la solution "une seule base avec discriminant"

Le gros inconvénient de cette solution est que les données ne sont pas physiquement « cloisonnées » entre les différents clients, bien que ceci puisse se faire à l’aide du partitionnement de données. Et cela peut être une cause rédhibitoire pour certaines applications ou pour des problématiques de maintenance. Par exemple il n’est pas possible dans un tel cas de sauvegarder les données d’un seul client indépendamment ni, bien entendu, de le restaurer…

1.2 – Une seule base avec partitionnement pas schémas SQL

Le principe consiste à créer autant de schéma SQL qu’il y a de clients (pour ceux qui ne seraient pas familiarisé avec la notion de schéma SQL, lire : http://blog.developpez.com/sqlpro/p5835/langage-sql-norme/de-l-interet-des-schema-sql/). Chaque schéma comportant toutes les tables nécessaires au fonctionnement de l’application mais avec les données d’un seule client. Pour qu’un client n’utilise pas la table du voisin, on créé pour chaque client un utilisateur SQL dont c’est le schéma SQL par défaut, et l’on renforce la sécurité par les privilèges SQL comme pour la méthode précédente.

A nouveau ces schémas SQL peuvent être créés dynamiquement de la façon suivante avec les utilisateurs « autorisés » :


DECLARE @SQL VARCHAR(max);
SET @SQL = '';
 
WITH  
T0 AS
(SELECT CAST(CLI_ID AS VARCHAR(5)) AS I,  
        5 - LEN(CAST(CLI_ID AS VARCHAR(5))) AS L
 FROM   T_CLIENT_CLI)
SELECT @SQL = @SQL + 'CREATE USER U_' + REPLICATE('0', L) + I + ';'
                   + 'CREATE SCHEMA S_' + REPLICATE('0', L) + I  
                   + ' AUTHORIZATION U_' + REPLICATE('0', L) + I + ';'
FROM T0;
 
PRINT @SQL
EXECUTE (@SQL);

Reste à créer les objets (tables, vues…) dans toute la base.
Il suffit de créer un script SQL de rétro-ingénierie pour la création de tous les objets de la base originale et de lui changer son schéma. Par exemple dans MS SQL Server, le schéma par défaut étant dbo, il suffit de faire un remplacement de cette chaine de caractères dans le rétro script de création de tous les objets, avec le nom de schéma considéré, pour l’appliquer au nouveau schéma et créer ainsi tous les objets.
Mieux : ceci peut être automatisé lors de la création d’un schéma SQL, par un déclencheur DDL trappant l’événement « CREATE SCHEMA » et reproduisant le script de création des objets. Et cerise sur le gâteau, point n’est besoin de spécifier le nom de schéma en préfixe de tous les objets si cette création s’effectue directement dans le CREATE SCHEMA ! Dans ce dernier cas on terminera par la création de tous les utilisateurs SQL avec leur schéma par défaut et on en profitera pour leur donner tous les privilèges sur le schéma, par exemple avec la commande SQL :
GRANT ALL ON SCHEMA::S_00007 TO U_00007;

2 – Plusieurs bases

Le principe est on ne peut plus simple : créer autant de base que de client. Il faudra simplement penser que l’application se connecte à la bonne base en fonction du client qui accède à l’application.
Pour la création des objets dans chaque nouvelle base on en revient à la solution précédente (une seule base, plusieurs schémas SQL) avec l’avantage de ne pas devoir modifier le script initial.
Le seul problème de cette solution réside dans la multiplicité des bases. Chaque base ouverte publie en mémoire des informations de méta données et oblige à maintenir toujours en mémoire des descripteurs de fichiers pour les lectures physique comme pour les écritures. Multiplier le nombre de bases possède donc des limites qui jouent rapidement sur les performances. Si les serveurs peuvent généralement accepter plusieurs milliers de bases, en pratique il y va tout autrement : à plus de 100 bases, les effets négatifs sur les performances sont visibles. A plus de 10 bases, certaines techniques comme la gestion de la haute disponibilité par mirroring, deviennent fortement pénalisante.

3 – Données communes

On peut choisir de reproduire les données communes (par exemple les tables de codes postaux), comme de les partager.

La redondance des tables communes ne nécessite aucune modification particulière. Néanmoins elle consomme du volume ce qui nuit rapidement aux performances de manière globale (il faudra donc prévoir une augmentation significative de RAM). De plus elle peut conduire à des différences dans les mises à jour, sauf si l’on tente de les synchroniser par un mécanisme de réplication par exemple (mais c’est encore au détriment des performances du fait de la consommation importante des ressources pour la gestion de la réplication).

Partager les données communes peut se faire dans des tables communes d’accès uniformisé : dans la même base pour les bases sans schéma, dans le schéma par défaut du SGBDR pour la solution avec multi schémas (c’est dbo par exemple pour MS SQL Server) et enfin dans une base commune pour la solution multi base.
Il y a donc une évolution nécessaire du code des requêtes.
Cette solution est à préférer dans tous les cas, car elle limite le volume des données en plus d’offrir aucune redondance.

4 – Évolutions

Pour les bases monolithiques les évolutions de la structure sont rendues très simples. La modification d’une table est immédiatement utilisable.

Pour les bases multi schéma, il faut reproduire la modification de la table dans tous les schémas. Ceci peut être fait dynamiquement et de manière synchrone par le biais d’un déclencheur DDL.
Pour faciliter ce travail, il est intéressant de disposer d’un schéma type, dont le seul but est d’être l’origine des modifications de structure. Par exemple ce schéma pourrait s’intituler S_00000 et contenir un « modèle » de toutes les structures de données. Par ce biais, on peut ensuite appliquer les modifications à tous les schémas.
Voici un exemple de déclencheur DDL permettant un tel travail (MS SQL Server / Transact SQL) :


CREATE TRIGGER E_DDL_MODIFICATION_SCTRUCTURE_SCHEMA_00000
ON DATABASE  
FOR DDL_TABLE_VIEW_EVENTS
AS
 
   DECLARE @XMLEVENT XML,
           @SQLCMD   NVARCHAR(max),
           @SCHEMA   sysname;
   SET @XMLEVENT = EVENTDATA();
   SELECT @XMLEVENT
   IF @XMLEVENT.().value('(/EVENT_INSTANCE/SchemaName)[1]','sysname') <> 'S_00000'
      RETURN;
   SET @SQLCMD = @XMLEVENT.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');
   DECLARE C CURSOR  
   FORWARD_ONLY STATIC READ_ONLY
   FOR  
      SELECT schema_name from INFORMATION_SCHEMA.SCHEMATA
      WHERE  schema_name LIKE 'S?_%' ESCAPE '?';
   OPEN C;
   FETCH C INTO @SCHEMA;
   WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @SQLCMD = REPLACE(@SQLCMD, 'S_00000', @SCHEMA);
      EXECUTE (@SQLCMD);
      FETCH C INTO @SCHEMA;
   END;
   CLOSE C;
   DEALLOCATE C;
GO

Pour la solution multi bases, on peut adopter la même stratégie en proposant une base modèle et en répercutant les modifications apportée à la base modèle, non aux schémas, mais aux différentes bases, toujours par le biais d’un déclencheur DDL.

5 – Maintenance

Pour le cas ou les bases seraient très petites, c’est à dire au plus quelques dizaines de Go, la maintenance est favorable à la solution en base monolithique. Mais dès que le volume s’accroit, il faut penser à réaliser cela à différentes heures (notamment suivant les fuseaux horaires des clients en privilégiant les heures creuses), différentes fréquences (en fonction du taux de mise à jour), différentes techniques (sauvegarde complètes, différentielles, du journal de transactions….), voire en parallèle (lancer plusieurs opérations de maintenance en même temps ou multi threadées)… Ce qui n’est possible que dans le système multi bases ou bien si votre serveur le permet, en jouant sur les espaces de stockage et les schémas.

En particulier dans le modèle de base partitionné par schéma la maintenance peut se faire au choix, globalement (une seule procédure pour toute la base), ou bien par schéma pour la maintenance logique (défragmentation, vérification…) et par storage pour la maintenance physique (sauvegarde), laissant ainsi une grande latitude de maintenance.
Enfin, si le SSGBDR est apte, comme c’est le cas de SQL Server, on peut effectuer la sauvegarde d’une même base en parallèle sur différents supports, même hétérogènes (disques locaux, SAN, distant, bande…)

6 – Tableau récapitulatif :

Une seule base ou plusieurs - résumé

EXPLICATIONS :

  • Modification de la base originale : quel faut-il faire à la base originale, non conçue au départ pour de multiples client afin d’utiliser la solution (par exemple ajout de vues, d’utilisateurs….)
  • Évolution de la base originale : comment modifier les objets de la base en cas d’évolution de la structure (ajout de nouvelles tables ou de nouvelles colonnes par exemple).
  • Connexion depuis l’application cliente : que faut-il changer dans le mode de connexion de l’application cliente pour la solution envisagée.
  • Modification de l’application cliente : que faut-il modifier dans le code de l’application pour que la solution fonctionne
  • Mise en œuvre pratique : quel effort global faut-il faire pour la solution envisagée
    Stockage des données : comment peut-on répartir les données dans les « storages » (filegroups, tablespaces….) et les fichiers
  • Sauvegardes : quelle stratégie adopter pour faire les sauvegardes.
  • Maintenance : comment faut-il faire la maintenance de la base, notamment en matière de défragmentation des index, vérification de consistance des données ou encore import export
  • Évolutivité du serveur : comment faire en cas de nécessité de faire évoluer les ressources matérielles du serveur (scalability) afin d’assurer une bonne adéquation ressources/performances.
  • Haute disponibilité : de quelle manière assurer la haute disponibilité, c’est à dire le passage à une machine de secours en cas d’indisponibilité du serveur principal
  • Performances : comment sont affecté les performances en fonction de la solution adoptée
  • NOTE GLOBALE : elle est calculée en donnant une note de 1 à 3 à chaque item du tableau et en faisant la somme.

7 – Conclusion :

On voit assez clairement que le meilleur compromis entre souplesse, facilité de codage et maintenance repose sur une base unique avec un partitionnement par schéma. Ce système sera d’autant plus intéressant que le serveur possède la capacité d’une bonne gestion des espaces de stockage ainsi que la possibilité de faire des sauvegardes par fichiers ou « storage » y compris en mode complet, différentiel, voire avec parallélisation du stockage. Cela exclue d’emblée des SGBDR comme MySQL qui ne sont pas capable de gérer plusieurs schémas, ne permettent pas de réaliser des déclencheurs DDL, ni de jouer des sauvegardes partielles par storage !
Mais si les bases deviennent énormes (plusieurs centaines de Go chacune), alors la solution d’utiliser plusieurs bases, et par facilité plusieurs serveurs à terme, sera la solution la plus adaptée.


--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

9 réflexions au sujet de « Une seule base de données ou plusieurs ? »

  1. Avatar de kahlabourrikahlabourri

    Bonjour, je développe une application J2EE et j’ai adopté la première méthode de partionner par données car j’utilise Mysql mais j’ai un souci c’est lorsque un des utilisateurs vient d’insérer un nouveau enregistrement comment je fais pour récupérer son CLI_ID et l’insérer comme clé étrangère??

  2. Avatar de sqlprosqlpro Auteur de l’article

    Pour les données communes il y a effectivement deux niveaux :
    1) les données externes, qui sont communes et partagées.
    2) les données du référentiel interne qui peut être partagé ou spécifique

    Dans le premier cas, aucun problème : liste de codes postaux, nomenclature ISO…

    Dans le second cas, nous pouvons encore avoir des données strictement partagées, comme le sexe d’une personne ou encore des données semi partagées. Civilité en est un bon exemple :
    les M. Mme. et Mlle. peuvent être communs, mais il est possible de rajouter d’autres éléments (Monseigneur, Son Altesse….). Aussi dans les modèles que je met au point, toutes les tables de référence ont une colonne « BASE » de type booléen qui si elle vaut 1 indique que la données est immuable et sert au fonctionnement de la base.
    On peut donc étendre cette notion en faisant en sorte de modifier cette colonne en « SPECIFIQUE » et si elle vaut NULL être commune, sinon avoir le n° du client qui l’a saisie. Ce choix est plus pratique pour une base monolithique.
    Une autre manière de faire est de prévoir en sus du référentiel commun, des tables équivalentes et vide, dans chaque base, dont on fait l’union des données dans une vue spécifique au client. Ce dernier choix étant plus pratique dans des bases éclatées.

    Quand à la restauration, c’est un faux problème… C’est du même niveau de responsabilité que celui de l’hébergeur de site web face à une mutualisation des ressources. Encore faut-il que le client soit au courant !
    De toutes façon et en pratique il est rare que l’on restaure une base en totalité si il y a eu une petite corruption. Généralement on tente de recomposer les données manquantes en les extrayant de la sauvegarde pour les replacer dans la base de production. Ce qui n’est même pas nécessaire si les données manquantes sont celles d’un index non clustered.

  3. Avatar de zinzinetizinzineti

    Il me semble que la problématique de restauration de base de données n’est pas examinée pour les différents scénarios. Prenons le cas d’une corruption de base (page de données endommagées par exemple), et à la restauration de(s) base(s).
    Quel est le poids de cette problématique sur les différents choix ?

    A+

  4. Avatar de sevyc64sevyc64

    On parle de point de verite dans la literature mais qui possede la verite ? Le client de la base 1 ou le client de la base 2 ?
    Aucun d’entres eux, si on prend la decision de ne considérer communes que des données qui ne sont pas directement modifiables par les clients, comme par exemple, celles données en exemple de la table des code postaux, ou des pays, devises, etc…..
    On laisse le soin à un « administrateur », superviseur, modérateur, superclient, … de pouvoir modifier ces données-là.

  5. Avatar de mikedavemmikedavem

    On peut etendre encore la problematique sur le point 3 :

    Faire un « referentiel » des donnnes communes est une bonne chose comme tu l’as dit mais elle fait apparaitre la problematique suivante : Qui mettra a jour les donnees. On parle de point de verite dans la literature mais qui possede la verite ? Le client de la base 1 ou le client de la base 2 ? Il faudra certainement implementer des regles de gestion a ce niveau …

    ++

  6. Avatar de dbaffaleufdbaffaleuf

    Re-

    Je sors juste d’une mission chez un client qui a 3000 bases sur une seule instance. On avait fait un audit chez lui en 2007, on avait déjà préconisé de migrer sur des schémas. Coût de redéveloppement faramineux, donc ils sont restés sur le principe 1 client = 1 base.

    Le plus gros problème chez eux reste la maintenance, et notamment le backup. Pas de possibilité de passer les bases en FULL et générer 3000 backups logs 6 ou 8 fois par jour, donc toutes les bases en SIMPLE et 1 backup par nuit qui dure 8 ou 9 heures. Ils ont envisagé de passer par du VDI snapshot, mais je leur ai déconseillé (VDI créé 3 threads par base pour figer les IOs avant de générer le snapshot du volume, 3 threads x 3000 bases x 2Mb de stack en 64 bits, laisse tomber, tu peux descendre en salle machine et tirer la prise). Je leur ai écrit un petit backup.exe en C# sur 8 threads, ça roule comme ça.

    Donc les solutions qui impliquent du redéveloppement dans ce que j’en ai vu, c’est chaud. Les développeurs sont dans leur trucs, ils n’ont pas la vision de l’exploitation, du temps de backup ou de dbcc, etc… Il faut prendre la décision de passer par des schémas dès le départ sinon ça devient vite la galère.

    Profite bien de tes vacances, et salut à elsuket de ma part A+

    David B.

Laisser un commentaire