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 :

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 :

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  * * * * *