Des informations supplémentaires sur les données

Article technique SQL Server
Auteur : Paul S. Randal (SQLskills.com)
Relecteur technique : Alexandru Chirica, Arkadi Brjazovski, Prem Mehra, Joanna Omel,
Mike Ruthruff, Robin Dhamankar
Date de publication : octobre 2008
S'applique à : SQL Server 2008
Résumé : Ce livre blanc décrit la fonctionnalité FILESTREAM de SQL Server 2008, qui permet
le stockage et l'accès aux données BLOB en associant SQL Server 2008 et le système de
fichiers NTFS. Il couvre les options de stockage d'objets BLOB, la configuration Windows et
SQL Server pour utiliser des données FILESTREAM, les considérations relatives à l'association
de FILESTREAM avec d'autres fonctionnalités et des informations sur l'implémentation, telles
que le partitionnement et les performances.
Ce livre blanc est destiné aux architectes, aux professionnels de l'informatique, ainsi qu'aux
administrateurs de bases de données chargés d'évaluer ou d'implémenter FILESTREAM.
Il suppose que le lecteur est familiarisé avec Windows et SQL Server et possède au moins
une connaissance rudimentaire des concepts de base de données, tels que les transactions.
Introduction
Dans la société actuelle, les données sont générées à des taux incroyables et doivent souvent être
enregistrées et accessibles de manière contrôlée et efficace. Il existe différentes technologies pour cela
et le choix de la technologie dépend souvent de la nature des données qui sont stockées – structurées,
semi-structurées ou non structurées :
 Les données structurées sont des données qui peuvent facilement être enregistrées dans
un schéma relationnel, tel que celui qui représente les données de ventes pour une société.
Elles peuvent être stockées dans une base de données avec une table d'informations pour les
produits vendus par la société, une autre avec des informations sur les clients, ainsi que d'autres
informations sur les ventes des produits aux clients. Les données sont accessibles et manipulées
à l'aide d'un langage de requête complet tel que Transact-SQL.

2
 Les données semi-structurées sont des données conformes à un schéma faible, mais ne se
prêtent pas au stockage dans un ensemble de tables de base de données, telles que les
données où chaque point de données peut avoir des attributs radicalement différents. Ces
données sont souvent stockées avec le type de données XML du logiciel de base de données
Microsoft® SQL Server® et accessibles à l'aide d'un langage de requête basé sur les éléments,
tel que XQuery.
 Les données non structurées peuvent ne pas avoir de schéma du tout (comme une partie des
données chiffrées) ou peuvent représenter un volume important de données binaires (plusieurs
Mo ou Go) qui peuvent sembler ne pas avoir de schéma, mais qui ont en réalité un schéma très
simple lié à elles, par exemple les fichiers image, les flux vidéo ou les clips audio. Les données
binaires dans ce cas signifient que les données peuvent avoir n'importe quelle valeur, et non pas
uniquement celles qui peuvent être entrées au clavier. Ces valeurs de données sont
généralement appelées objets blob.
Ce livre blanc décrit la fonctionnalité FILESTREAM de SQL Server 2008, qui permet le stockage et
l'accès aux données BLOB en associant SQL Server 2008 et le système de fichiers NTFS. Il couvre la
fonctionnalité FILESTREAM, les options de stockage d'objets BLOB, la configuration du système
d'exploitation Windows® et de SQL Server pour utiliser des données FILESTREAM, les considérations
relatives à l'association de FILESTREAM avec d'autres fonctionnalités et des informations sur
l'implémentation, telles que le partitionnement et les performances.
Options de stockage d'objets BLOB
Lorsque des données structurées et semi-structurées peuvent facilement être stockées dans une base de
données relationnelle, le choix de l'emplacement de stockage des données non structurées ou des données
BLOB est plus compliqué. Lorsque vous décidez de l'emplacement de stockage des données BLOB,
prenez connaissance des spécifications suivantes :
 Performances : la façon dont les données vont être utilisées est un facteur essentiel. Si l'accès
en continu est nécessaire, le stockage des données dans une base de données SQL Server peut
être plus lent que le stockage externe dans un emplacement tel que le système de fichiers NTFS.
Avec le stockage du système de fichiers, les données sont lues dans le fichier et transmises
à l'application cliente (directement ou avec mise en mémoire tampon supplémentaire). Lorsque
les données blob sont stockées dans une base de données SQL Server, elles doivent d'abord
être lues dans la mémoire de SQL Server (le pool de mémoires tampons), puis être retransmises
via une connexion cliente à l'application cliente. Cela signifie non seulement que les données
passent par une étape de traitement supplémentaire, mais également que la mémoire de
SQL Server est inutilement « polluée » par des données BLOB, ce qui peut entraîner d'autres
problèmes de performances pour les opérations SQL Server.
 Sécurité : les données sensibles dont l'accès doit être étroitement géré peuvent être stockées
dans une base de données et la sécurité peut être contrôlée en utilisant les contrôles d'accès
SQL Server habituels. Si les mêmes données sont stockées dans le système de fichiers,
différentes méthodes de sécurité, telles que les listes de contrôle d'accès (ACL), doivent être
implémentées.

3
 Taille des données : selon l'étude mentionnée plus loin dans ce livre blanc, les objets blob d'une
taille inférieure à 256 kilo-octets (Ko) (tels que des icônes de widget) sont mieux adaptés au
stockage dans une base de données, et les objets blob d'une taille supérieure à 1 Mo sont plus
adaptés au stockage en dehors de la base de données. Pour ceux dont la taille est comprise
entre 256 Ko et 1 Mo, la solution de stockage la plus efficace dépend du rapport lecture/écriture
des données et de la fréquence de « remplacement ». Le stockage des données BLOB uniquement
dans la base de données (par exemple, en utilisant le type de données varbinary (max)) est
limité à 2 gigaoctets (Go) par BLOB.
 Accès du client : le protocole utilisé par le client pour accéder aux données SQL Server,
tel qu'ODBC, peut ne pas être adapté aux applications telles que les fichiers volumineux de
flux vidéo. Cela peut nécessiter le stockage des données dans le système de fichiers.
 Sémantique transactionnelle : si les données BLOB possèdent des données structurées
associées qui sont stockées dans la base de données, les modifications apportées aux données
BLOB doivent respecter la sémantique transactionnelle de façon à ce que les deux ensembles de
données restent synchronisés. Par exemple, si une transaction crée des données BLOB et une
ligne dans une table de base de données, puis annule l'opération, la création des données BLOB
doit être annulée, ainsi que la création de la ligne dans la table. Cela peut s'avérer très complexe
si les données BLOB sont stockées dans le système de fichiers sans lien vers la base de données.
 Fragmentation des données : les mises à jour et remplacements fréquents entraînent le
déplacement des objets blob, dans les fichiers de base de données SQL Server ou dans le
système de fichiers, en fonction de l'emplacement de stockage des données. Dans ce cas, si les
objets blob sont volumineux, ils peuvent être fragmentés. (c.-à-d., ne pas être stockés dans une
partie contiguë du disque). Cette fragmentation est plus aisément traitée à l'aide du système de
fichiers qu'à l'aide de SQL Server.
 Simplicité de gestion : une solution utilisant différentes technologies qui ne sont pas intégrées
sera plus complexe et coûteuse à gérer qu'une solution intégrée.
 Coût : le coût de la solution de stockage varie selon la technologie utilisée.
Les explications ci-dessus concernant la taille et la fragmentation reposent sur le document Microsoft
Research intitulé Utiliser ou non des objets blob : stockage d'objets blob dans une base de données ou
dans un système de fichiers ? (en anglais) (Gray, Van Ingen et Sears). Le document comporte davantage
d'informations sur les compromis impliqués et peut être téléchargé à l'adresse suivante :
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45
Il existe une multitude de solutions pour le stockage d'objets blob, chacune ayant des avantages et des
inconvénients en fonction des spécifications ci-dessus. Le tableau suivant compare trois options
communes pour stocker des données blob, notamment FILESTREAM, dans SQL Server 2008.
Point de
comparaison
Solution de stockage
Serveur de fichiers/
Système de fichiers
SQL Server
(avec varbinary(max)
FILESTREAM
Taille maximale des
objets blob
Taille du volume NTFS
2 Go – 1 octet
Taille du volume NTFS
Performances de
diffusion en continu
des objets blob
Excellente
Médiocre
Excellente
Sécurité
Listes de contrôle
d'accès manuelles
Intégrées
Intégrées +
automatiques
Coût par Go
Faible
Élevé
Faible

4
Point de
comparaison
Solution de stockage
Serveur de fichiers/
Système de fichiers
SQL Server
(avec varbinary(max)
FILESTREAM
Simplicité de gestion
Difficile
Intégrée
Intégrée
Intégration avec les
données structurées
Difficile
Cohérence au niveau
des données
Cohérence au niveau
des données
Développement et
déploiement
d'applications
Plus complexes
Plus simples
Plus simples
Récupération suite
à la fragmentation
des données
Excellente
Médiocre
Excellente
Performances de
petites mises à jour
fréquentes
Excellentes
Modérées
Médiocres
Tableau 1 : Comparaison des technologies de stockage d'objets blob avant SQL Server 2008
FILESTREAM est la seule solution qui assure la cohérence transactionnelle des données structurées et
non structurées, ainsi que la gestion intégrée, la sécurité, un faible coût et d'excellentes performances de
diffusion en continu. Cela est possible en stockant les données structurées dans les fichiers de base de
données et les données BLOB non structurées dans le système de fichiers, tout en maintenant la cohérence
transactionnelle entre les deux banques de données. Des informations supplémentaires sur l'architecture
FILESTREAM sont disponibles dans la section « Présentation de FILESTREAM » plus loin dans ce
document.
Présentation de FILESTREAM
FILESTREAM est une nouvelle fonctionnalité de SQL Server 2008. Elle permet le stockage des données
structurées dans la base de données et des données non structurées associées (c.-à-d., des données
BLOB) directement dans le système de fichiers NTFS. Vous accédez ensuite aux données BLOB via les
API de diffusion en continu Win32® hautes performances, au lieu de devoir subir la diminution des
performances liée à l'accès aux données via SQL Server.
FILESTREAM assure la cohérence transactionnelle entre les données structurées et non structurées
à tout moment, en permettant même la récupération jusqu'à une date et heure des données FILESTREAM
à l'aide des sauvegardes de fichiers journaux. La cohérence est gérée automatiquement par SQL Server
et ne requiert pas de logique personnalisée dans l'application. Le mécanisme FILESTREAM y parvient en
conservant l'équivalent d'un journal des transactions de la base de données, qui possède de nombreuses
conditions de gestion identiques (décrites plus en détail dans la section « Configuration du garbage collection
FILESTREAM » plus loin dans ce document). La combinaison du journal des transactions de la base de
données et du journal des transactions FILESTREAM permet aux données FILESTREAM et aux données
structurées d'être récupérées d'un point de vue transactionnel.
Au lieu d'être d'un type de données entièrement nouveau, FILESTREAM est un attribut de stockage du
type de données varbinary (max) existant. FILESTREAM conserve la majorité du comportement du type
de données varbinary (max). Cette fonctionnalité modifie la façon dont les données BLOB sont stockées;
dans le système de fichiers plutôt que dans les fichiers de données SQL Server. FILESTREAM étant
implémenté en tant que colonne varbinary (max) et intégré directement dans le moteur de base de
données, la plupart des fonctions et outils d'administration SQL Server fonctionnent sans changement
pour les données FILESTREAM.

5
Il est à noter que le comportement du type de données régulier varbinary (max) demeure inchangé dans
SQL Server 2008, y compris la limite de taille de 2 Go. L'ajout de l'attribut FILESTREAM rend une
colonne varbinary (max) essentiellement illimitée en taille (en réalité la taille est limitée à celle du volume
NTFS sous-jacent).
Les données FILESTREAM sont stockées dans le système de fichiers dans un ensemble de répertoires
NTFS appelés conteneurs de données, qui correspondent aux groupes de fichiers spéciaux dans la base
de données. L'accès transactionnel aux données FILESTREAM est contrôlé par SQL Server et un pilote
de filtre du système de fichiers qui est installé dans le cadre de l'activation de FILESTREAM au niveau
Windows. L'utilisation d'un pilote de filtre du système de fichiers permet également d'avoir accès
à distance aux données FILESTREAM dans un chemin d'accès UNC. SQL Server gère un lien des tris
de lignes de la table dans les fichiers FILESTREAM associés. Cela signifie que la suppression ou
l'affectation d'un nouveau nom à des fichiers FILESTREAM directement via le système de fichiers
provoque l'endommagement de la base de données.
L'utilisation de FILESTREAM requiert plusieurs modifications de schéma apportées aux tables de données
(le plus souvent la condition que chaque ligne doit posséder un ID de ligne unique) ; elle comprend
également des restrictions lorsqu'elle est combinée avec d'autres fonctionnalités (telles que l'incapacité de
chiffrer des données FILESTREAM). Elles sont toutes décrites en détail dans la section « Configuration de
SQL Server pour FILESTREAM » plus loin dans ce livre blanc.
Les données FILESTREAM sont accessibles et manipulées de deux manières : avec le modèle de
programmation standard Transact-SQL ou via les API de diffusion en continu Win32. Les deux
mécanismes sont entièrement compatibles avec les transactions et prennent en charge la plupart des
opérations DML, notamment insertion, mise à jour, suppression et sélection. Les données FILESTREAM
sont également prises en charge pour les opérations de maintenance, telles que la sauvegarde, la
restauration et le contrôle de cohérence. L'exception principale est que les mises à jour partielles des
données FILESTREAM ne sont pas prises en charge. Toute mise à jour d'une valeur de données
FILESTREAM se traduit par la création d'une nouvelle copie du fichier de données FILESTREAM.
L'ancien fichier est supprimé de façon asynchrone, tel que le décrit la section « Configuration du garbage
collection FILESTREAM » plus loin dans ce document.
Modèle de programmation double pour accéder aux données BLOB
Une fois les données stockées dans une colonne FILESTREAM, elles sont accessibles en utilisant des
transactions Transact-SQL ou des API Win32. Cette section fournit des informations générales sur les
modèles de programmation et leur utilisation.
Accès Transact-SQL
À l'aide de Transact-SQL, les données FILESTREAM peuvent être insérées, mises à jour et supprimées,
comme suit :
 Les champs FILESTREAM peuvent être préremplis à l'aide d'une opération d'insertion (avec une
valeur vide ou une petite valeur non NULL). Toutefois, une grande quantité de données est
diffusée en continu plus efficacement à l'aide des interfaces Win32.
 Lorsque vous mettez à jour des données FILESTREAM, vous modifiez les données BLOB sous-
jacentes dans le système de fichiers. Lorsqu'un champ FILESTREAM a la valeur NULL, les
données BLOB associées au champ sont supprimées. Les mises à jour segmentées Transact-
SQL, implémentées comme UPDATE.Write(), ne peuvent pas être utilisées pour effectuer des
mises à jour partielles des données FILESTREAM.