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.





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 : 



2

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.









 

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 Taille maximale des objets blob Performances de diffusion en continu des objets blob Sécurité Coût par Go

3

Serveur de fichiers/ Système de fichiers Taille du volume NTFS

Solution de stockage SQL Server (avec varbinary(max) 2 Go – 1 octet

Excellente

Médiocre

Excellente

Listes de contrôle d'accès manuelles Faible

Intégrées

Intégrées + automatiques Faible

Élevé

FILESTREAM Taille du volume NTFS

Solution de stockage Point de Serveur de fichiers/ SQL Server FILESTREAM comparaison Système de fichiers (avec varbinary(max) Simplicité de gestion Difficile Intégrée Intégrée Intégration avec les Difficile Cohérence au niveau Cohérence au niveau données structurées des données des données Développement et Plus complexes Plus simples Plus simples déploiement d'applications Récupération suite Excellente Médiocre Excellente à la fragmentation des données Performances de Excellentes Modérées Médiocres petites mises à jour fréquentes 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.

4

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 : 



5

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 sousjacentes 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 TransactSQL, implémentées comme UPDATE.Write(), ne peuvent pas être utilisées pour effectuer des mises à jour partielles des données FILESTREAM.



Lorsqu'une ligne qui contient des données FILESTREAM est supprimée ou une table qui contient des données FILESTREAM est supprimée ou tronquée, les données BLOB sous-jacentes dans le système de fichiers sont également supprimées. La suppression physique réelle des fichiers FILESTREAM est un processus en arrière-plan asynchrone, tel que l'explique la section « Configuration du garbage collection FILESTREAM » plus loin dans ce document.

Pour plus d'informations et des exemples d'utilisation de Transact-SQL pour accéder aux données FILESTREAM, consultez la rubrique « Gestion des données FILESTREAM avec Transact-SQL » de la documentation en ligne de SQL Server 2008 (http://msdn.microsoft.com/fr-fr/library/cc645962.aspx). Accès en continu Win32 Pour autoriser l'accès du système de fichiers transactionnel aux données FILESTREAM, une nouvelle fonction intrinsèque, GET_FILESTREAM_TRANSACTION_CONTEXT(), fournit le jeton qui représente la transaction actuelle à laquelle la session est associée. La transaction doit avoir été démarrée mais pas encore validée ou annulée. En obtenant un jeton, l'application lie les opérations de diffusion en continu du système de fichiers FILESTREAM avec une transaction commencée. La fonction retourne NULL si aucune transaction n'est explicitement commencée. Un jeton doit être obtenu pour accéder aux fichiers FILESTREAM. Dans FILESTREAM, le moteur de base de données contrôle l'espace de noms du système de fichiers physique BLOB. Une nouvelle fonction intrinsèque, PathName, fournit le chemin UNC logique de l'objet blob qui correspond à chaque champ FILESTREAM dans la table. L'application utilise ce chemin logique pour obtenir le descripteur Win32 et opérer sur les données BLOB en utilisant des interfaces de système de fichiers Win32 ordinaires. La fonction retourne NULL si la valeur de la colonne FILESTREAM est NULL. Elle souligne le fait qu'un fichier FILESTREAM doit être précréé pour pouvoir être accessible au niveau Win32. Cette opération est réalisée de la façon décrite précédemment. La prise en charge de la diffusion en continu Win32 fonctionne dans le contexte d'une transaction SQL Server. Après avoir obtenu un jeton de transaction et un chemin d'accès, l'API Win32 OpenSqlFilestream permet d'obtenir un descripteur de fichier Win32. Sinon, l'API managée SqlFileStream peut être utilisée. Ce descripteur peut ensuite être utilisé par les interfaces de diffusion en continu Win32, telles que ReadFile() et WriteFile(), afin d'accéder au fichier et de le mettre à jour au moyen du système de fichiers. De nouveau, notez que les fichiers FILESTREAM ne peuvent pas être directement supprimés et ne peuvent pas être renommés dans le système de fichiers. Sinon la cohérence au niveau du lien sera perdue entre la base de données et le système de fichiers (c.-à-d., la base de données est endommagée). L'accès au système de fichiers FILESTREAM modélise une instruction Transact-SQL en utilisant l'ouverture et la fermeture de fichier. L'instruction démarre lorsqu'un descripteur de fichier est ouvert et se termine lorsque le descripteur est fermé. Par exemple, lorsqu'un descripteur d'écriture est fermé, tout déclencheur AFTER possible enregistré sur la table est activé comme si une instruction UPDATE était exécutée. Pour plus d'informations et des exemples d'utilisation des API Win32 pour accéder aux données FILESTREAM, consultez la rubrique « Gestion des données FILESTREAM avec Win32 » de la documentation en ligne de SQL Server 2008 (http://msdn.microsoft.com/fr-fr/library/cc645940.aspx).

6

Sémantique transactionnelle Tous les descripteurs de fichiers doivent être fermés avant que la transaction ne soit validée ou annulée. Si un descripteur est laissé ouvert lorsqu'une validation de transaction se produit, la validation échoue et les lectures et écritures supplémentaires sur le descripteur provoquent une erreur, comme prévu. La transaction doit être restaurée. De même, si la base de données ou l'instance du moteur de base de données s'arrête, tous les descripteurs ouverts sont invalidés. Lorsqu'un fichier FILESTREAM est ouvert pour une opération d'écriture, un nouveau fichier de longueur nulle est créé et la valeur des données FILESTREAM mise à jour complète est écrite. 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. Avec FILESTREAM, lors de la validation, le moteur de base de données garantit la durabilité des transactions pour les données BLOB FILESTREAM modifiées à partir de l'accès en continu au système de fichiers. Cette opération est réalisée en utilisant le journal FILESTREAM indiqué précédemment et un vidage explicite du contenu du fichier FILESTREAM sur le disque. Sémantique d'isolation La sémantique d'isolation est gouvernée par les niveaux d'isolation des transactions du moteur de base de données. Lorsque les données FILESTREAM sont accessibles via des API Win32, seul le niveau d'isolation validé en lecture est pris en charge. L'accès Transact-SQL autorise également les niveaux d'isolation sérialisable et de lecture renouvelée. En outre, avec l'accès Transact-SQL, les lectures erronées sont autorisées par le niveau d'isolation non validé en lecture, ou l'indicateur de requête NOLOCK, mais cet accès n'affiche pas les mises à jour en cours d'exécution des données FILESTREAM. Les opérations d'ouverture d'accès au système de fichiers n'attendent pas de verrous. Au lieu de cela, les opérations d'ouverture échouent immédiatement si elles ne peuvent pas accéder aux données à cause de l'isolation des transactions. Les appels API de diffusion en continu échouent avec ERROR_SHARING_VIOLATION si l'opération d'ouverture ne peut se poursuivre à cause de la violation d'isolation. Mises à jour partielles Pour permettre les mises à jour partielles, l'application peut publier un contrôle FS de périphérique (FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT) afin d'extraire l'ancien contenu dans le fichier auquel le descripteur ouvert fait référence. Il est également possible d'utiliser l'API managée SqlFileStream à l'aide de l'indicateur ReadWrite. Cela déclenchera une copie de l'ancien contenu côté serveur, tel que cela est indiqué précédemment. Pour de meilleures performances d'application et afin d'éviter des dépassements de délais d'attente potentiels lorsque vous travaillez avec de très grands fichiers, vous devez utiliser des E/S asynchrones. Si le FSCTL est publié après l'écriture dans le descripteur, la dernière opération d'écriture persistera et les écritures antérieures effectuées dans le descripteur seront perdues. Pour plus d'informations sur les mises à jour partielles, consultez la rubrique « FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT » de la documentation en ligne de SQL Server 2008 (http://technet.microsoft.com/fr-fr/library/cc627407.aspx).

7

Double écriture à partir de clients distants L'accès du système de fichiers distant aux données FILESTREAM est activé sur le protocole SMB (Block Server Message). Si le client est distant, la mise en cache des opérations d'écriture dépend des options spécifiées et de l'API utilisée. Par exemple, la valeur par défaut pour les API en code natif consiste à exécuter la double écriture, alors que pour les API managées, la valeur par défaut consiste à utiliser la mise en mémoire tampon. Cette différence reflète les commentaires des clients sur les différentes API et leur utilisation dans les versions CTP (Community Technology Preview) de SQL Server 2008. Nous recommandons que les applications qui s'exécutent sur des clients distants consolident les petites opérations d'écriture (par la mise en mémoire tampon) afin d'effectuer moins d'opérations d'écriture avec une taille de données supérieure. En outre, si la mise en mémoire tampon est utilisée, un vidage explicite doit être effectué par le client avant que la transaction ne soit validée. La création de vues mappées en mémoire (E/S mappées en mémoire) à l'aide d'un descripteur FILESTREAM n'est pas prise en charge. Si le mappage mémoire est utilisé pour les données FILESTREAM, le moteur de base de données ne peut pas garantir la cohérence et la durabilité des données, ni l'intégrité de la base de données.

À quel moment utiliser FILESTREAM Bien que la technologie FILESTREAM dispose de nombreuses fonctionnalités attrayantes, elle n'est pas optimale dans toutes les situations. Comme indiqué précédemment, la taille des données BLOB et les modèles d'accès sont des facteurs importants lorsque vous décidez si vous allez stocker les données BLOB entièrement dans la base de données ou en utilisant FILESTREAM. La taille affecte les éléments suivants : 



Efficacité avec laquelle les données BLOB sont accessibles à l'aide de l'un des mécanismes de stockage. Comme indiqué précédemment, l'accès en continu des données BLOB est plus efficace avec FILESTREAM, mais les mises à jour partielles sont (potentiellement) plus lentes. Efficacité de sauvegarde des données combinées structurées et BLOB à l'aide de l'un des mécanismes de stockage. Une sauvegarde qui associe les fichiers de base de données SQL Server et un grand nombre de fichiers FILESTREAM sera plus lente qu'une sauvegarde des fichiers de base de données SQL Server de taille totale équivalente. Cela est causé par la charge supplémentaire de sauvegarde de chaque fichier NTFS (un pour chaque valeur de données FILESTREAM). Cette surcharge est plus notable lorsque les fichiers FILESTREAM sont plus petits (car la surcharge de temps représente un plus grand pourcentage de la durée totale de la sauvegarde par Mo de données).

Par exemple, le graphique suivant indique le débit relatif des lectures locales de différentes tailles de données BLOB à l'aide de varbinary (max), FILESTREAM dans Transact-SQL et FILESTREAM dans NTFS. Vous constatez (sur la ligne bleue) que l'accès Win32 des données FILESTREAM devient plusieurs fois plus rapide que l'accès Transact-SQL des données varbinary (max) à mesure que la taille des données augmente. Notez que les mesures de débit sont en mégabits par seconde (Mbit/s).

8

Illustration 1 : Performances de lecture de différentes tailles de BLOB Les chiffres NTFS incluent le temps nécessaire pour démarrer une transaction, pour extraire le chemin d'accès et le contexte de transaction de SQL Server et pour ouvrir un descripteur Win32 dans les données FILESTREAM. Chaque test a été effectué sur le même ordinateur avec quatre cœurs de processeur et un pool de mémoires tampons SQL Server à chaud. L'autre facteur à prendre en considération consiste à savoir si le client ou le niveau intermédiaire peut être écrit (ou modifié) de façon à utiliser les API de diffusion en continu Win32, ainsi qu'un accès normal à SQL Server. Si ce n'est pas le cas, FILESTREAM n'est pas nécessaire, car les meilleures performances sont obtenues avec les API de diffusion en continu Win32.

Configuration de Windows pour FILESTREAM Comme avec tout autre déploiement, avant de déployer une application qui utilise FILESTREAM, il est important de préparer le serveur Windows qui va héberger la base de données SQL Server et les conteneurs de données FILESTREAM associés. Cette section explique comment configurer le matériel de stockage et le système de fichiers NTFS en prévision de l'utilisation de FILESTREAM. Elle explique ensuite comment activer FILESTREAM au niveau Windows.

Sélection du matériel et configuration Une des causes les plus courantes de charge de travail qui ne fonctionne pas correctement est une configuration matérielle qui n'est pas adaptée. Parfois, la mémoire est insuffisante, ce qui entraîne une surexploitation dans le pool de mémoires tampons de SQL Server, et parfois cela est simplement dû au fait que le matériel de stockage ne possède pas la fonction de débit d'E/S demandée par la charge de travail. Pour les applications qui utilisent FILESTREAM pour la diffusion en continu hautes performances de données BLOB à l'aide des API Win32, le choix et la configuration du matériel de stockage sont essentiels.

9

Les sections suivantes décrivent certaines des meilleures pratiques de sélection et disposition du stockage. Pour une présentation plus approfondie, consultez le livre blanc TechNet relatif à la conception du stockage de base de données physique (http://www.microsoft.com/technet/prodtechnol/sql/2005/ physdbstor.mspx). Après avoir conçu une disposition optimale, il est prudent de réaliser des tests de charge pour valider les performances du sous-système d'E/S. Ce sujet est abordé en détail dans l'article TechNet sur les meilleures pratiques SQL Server intitulé « Meilleures pratiques pour les E/S de prédéploiement » (http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx).

Disposition du stockage physique Veillez à prendre en compte la charge de travail anticipée sur un conteneur de données FILESTREAM lorsque vous déterminez son emplacement, ainsi que les charges de travail sur tous les conteneurs de données colocalisés ou fichiers SQL Server. Chaque conteneur de données FILESTREAM devra peutêtre se trouver sur son propre volume, car le fait d'avoir plusieurs conteneurs de données avec un nombre élevé de charges de travail sur un seul volume peut provoquer une contention. Le point à retenir ici est que sans tenir compte des charges de travail impliquées, le fait de tout placer sur un seul volume peut entraîner des problèmes de performances. Le degré de séparation nécessaire varie d'un client à l'autre. Il est également possible de créer un schéma de table dans SQL Server, qui permet l'équilibrage de la charge brute des données FILESTREAM entre plusieurs volumes. Cette opération est décrite dans la section « Équilibrage de la charge des données FILESTREAM ».

Option de niveau RAID Les avantages liés à l'utilisation de la technologie RAID sont connus, et on a déjà beaucoup écrit sur la sélection d'un niveau RAID adapté aux besoins de l'application. C'est pourquoi, ce livre blanc n'essaie pas de reprendre toutes ces informations. Comme mentionné précédemment, le livre blanc « Conception du stockage de base de données physique » comporte une excellente section sur les niveaux RAID et la sélection du niveau RAID. Ce qui suit est une simple présentation des facteurs à prendre en compte. Les niveaux RAID diffèrent de plusieurs façons, plus particulièrement en termes de performances de lecture/écriture, de résilience aux défaillances et de coût. Par exemple, RAID 5 est relativement faible coût, il peut gérer l'échec d'un seul lecteur de la baie RAID, et peut ne pas être adapté aux charges de travail avec un nombre d'écritures élevé. En revanche, RAID 10 fournit d'excellentes performances en lecture et en écriture. Ce niveau permet de gérer plusieurs échecs de lecteur (selon le degré de mise en miroir impliqué), mais il est plus coûteux, étant donné qu'au moins la moitié des lecteurs de la baie RAID sont redondants. Voici les trois facteurs principaux impliqués dans la sélection d'un niveau RAID. Le choix du niveau RAID peut être différent pour le volume sur lequel chaque base de données utilisateur est stockée, et peut même être différent entre le volume de stockage des fichiers de données et le volume de stockage des fichiers journaux d'une seule base de données. Si la charge de travail comporte de la diffusion en continu hautes performances de données FILESTREAM, le choix immédiat peut être de faire en sorte que le conteneur de données FILESTREAM utilise le niveau RAID qui offre les performances de lecture les plus élevées. Toutefois, ce choix peut ne pas fournir un niveau élevé de résilience aux défaillances. Par ailleurs, le choix immédiat peut être d'utiliser le même niveau RAID que pour les autres volumes qui stockent les données de la base de données. Cependant, il est possible qu'il ne fournisse pas les niveaux de performances requis par la charge de travail. Pour ce livre blanc, le point à souligner est qu'un choix de niveau RAID conscient doit être effectué pour les volumes de conteneur de données FILESTREAM après avoir évalué les compromis impliqués. Il ne faut pas prendre une décision en fonction d'un seul facteur.

10

Choix de l'interface du lecteur Dans les bases de données classiques liées à des données BLOB, la taille totale des données BLOB peut être plusieurs fois supérieure à la taille totale des données structurées. Lors de l'implémentation d'une solution impliquant des données FILESTREAM stockées dans des volumes distincts, vous souhaiterez peut-être utiliser le stockage le moins cher, tel que IDE ou SATA (appelé simplement « SATA » ci-après), plutôt qu'un stockage SCSI plus onéreux. Avant de faire ce choix, vous devez comprendre les compromis impliqués. Cette section fournit une présentation des différentes caractéristiques de SCSI et IDE/SATA pour prendre une décision en toute connaissance de cause en fonction des performances, de la fiabilité et du coût. Capacité et performances Les lecteurs SATA sont susceptibles d'avoir une capacité plus élevée que les lecteurs SCSI, mais ils ont une vitesse de rotation (tr/min) moins élevée que les lecteurs SCSI. Il existe des lecteurs SATA à 10 000 tr/min, mais la plupart sont à 5 400 ou 7 200 tr/min. Des lecteurs SCSI hautes performances sont disponibles de 10 000 à 15 000 tr/min. Bien que le tr/min puisse être une mesure de comparaison utile, les deux chiffres qui doivent réellement être utilisés pour effectuer une comparaison sont la latence (le délai d'attente jusqu'à ce que la tête du disque soit à la position appropriée sur la surface du disque) et les taux de transfert moyens (volume de données pouvant être transféré vers/à partir de la surface du disque par seconde). Il est également important que les lecteurs soient en mesure de traiter des modèles complexes d'E/S efficacement. Lorsque vous choisissez des lecteurs, vérifiez que les lecteurs SATA prennent en charge NCQ (Native Command Queue) et les lecteurs SCSI prennent en charge CTQ (Command Tag Queue), ce qui leur permet de traiter plusieurs E/S entrelacées pour offrir de meilleures performances. En résumé, les lecteurs SCSI ont généralement une meilleure latence et des taux de transfert plus élevés et fournissent ainsi de meilleures performances de diffusion en continu, mais à un coût plus élevé. Fiabilité SQL Server s'appuie sur un tri des écritures et une durabilité garantis pour fournir fiabilité et récupération via son mécanisme de journal WAL (Write-Ahead Log). Pour plus d'informations sur ces spécifications d'E/S, consultez le livre blanc TechNet « Notions de base sur les E/S SQL Server » (http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx). Pour la fiabilité, SCSI est généralement plus adapté que SATA, car cette technologie prend en charge l'écriture forcée des données sur le disque contrairement à SATA. Cette opération s'effectue par prise en charge de la double écriture, où les données à écrire ne sont pas du tout mises en cache, ou par prise en charge du vidage de force du contenu du cache sur le disque. L'absence de l'une ou l'autre peut avoir un impact sur la récupération après une défaillance matérielle, logicielle ou une panne d'alimentation. Tous les types d'interfaces prennent en charge le remplacement à chaud pour autoriser des réparations tout en préservant la disponibilité. La fonctionnalité FILESTREAM repose sur les garanties de tri des écritures et de durabilité :  

11

Durabilité des données lors de la validation des transactions Journalisation WAL pour la création et la suppression de fichiers FILESTREAM

La durabilité des données est obtenue par le pilote du système de fichiers FILESTREAM qui effectue un vidage explicite des fichiers modifiés avant de valider des transactions (les détails du mécanisme dépassent l'objet de ce livre blanc). Cela garantit qu'en cas de panne de courant, tous les disques qui n'ont pas de cache protégé par batterie suffisant ne sont pas validés, mais les données FILESTREAM non vidées seront perdues. Si les lecteurs SATA ne prennent pas en charge une opération de vidage forcé, la récupération peut être affectée et des données peuvent être perdues. La journalisation WAL repose sur la cohérence des métadonnées NTFS. Elle-même dépend de la fiabilité des disques sous-jacents. Il n'y a aucun problème avec SCSI, mais si les lecteurs SATA ne prennent pas en charge le vidage forcé, certaines modifications apportées aux métadonnées NTFS peuvent être perdues en cas de panne d'alimentation. Cela peut provoquer un certain nombre de scénarios :  



Impossible de récupérer NTFS et de monter le volume (c.-à-d., le conteneur de données FILESTREAM est essentiellement hors connexion). NTFS récupère, mais les modifications apportées aux métadonnées NTFS sont perdues et SQL Server ne sait pas restaurer une transaction non validée qui effectue une insertion de données FILESTREAM (c.-à-d., les données FILESTREAM sont perdues). NTFS récupère, mais les modifications apportées aux métadonnées NTFS sont perdues et SQL Server ne sait pas restaurer une transaction non validée qui effectue une suppression de données FILESTREAM (c.-à-d., les données FILESTREAM sont perdues).

Notez que ces trois scénarios ne sont pas pires que si les données BLOB étaient stockées en dehors de la base de données sur un volume NTFS avec des lecteurs SATA sous-jacents qui ne prennent pas en charge l'application forcée des données sur le disque. Dans ce cas, l'utilisation de FILESTREAM sur un volume avec des lecteurs SATA est plus adaptée que le stockage des données BLOB dans des fichiers NTFS bruts sur le même volume, car la cohérence au niveau du lien de FILESTREAM fournit un mécanisme pour détecter à quel moment ces corruptions se sont produites (via l'exécution de DBCC CHECKDB sur la base de données). Pour résumer, les données FILESTREAM peuvent être stockées de manière fiable sur des volumes avec stockage SATA sous-jacent, tant que les lecteurs SATA prennent en charge l'application forcée des données sur le disque via le vidage du cache.

Configuration NTFS Même le sous-système d'E/S, conçu avec le plus de soin et exécuté sur du matériel hautes performances, peut ne pas fonctionner comme prévu si le système de fichiers (dans ce cas NTFS) n'est pas configuré correctement. Cette section décrit certaines options de configuration qui peuvent affecter une charge de travail impliquant des données FILESTREAM. Pour une présentation plus complète de NTFS, consultez les articles de la bibliothèque TechNet intitulés « Guide de référence technique sur NTFS » (http://technet.microsoft.com/fr-fr/library/cc758691.aspx) et « Utilisation des systèmes de fichiers » (http://technet.microsoft.com/fr-fr/library/bb457112.aspx). Optimisation des performances NTFS Par défaut, NTFS n'est pas configuré pour gérer une charge de travail hautes performances avec des dizaines de milliers de fichiers d'un répertoire du système de fichiers spécifique (c. à-d., le scénario FILESTREAM). Deux options NTFS doivent être configurées pour faciliter les performances FILESTREAM. Il est très important de définir ces options correctement avant d'entreprendre tous les tests de performances ; sinon, les résultats ne seront pas représentatifs des performances FILESTREAM réelles.

12

La première option de configuration consiste à désactiver la génération de noms 8.3 lorsque de nouveaux fichiers sont créés (ou renommés). Ce processus génère un nom secondaire pour chaque fichier, qui est uniquement destiné à la compatibilité descendante avec les applications 16 bits. L'algorithme génère un nouveau nom 8.3, puis doit analyser les noms de fichiers 8.3 existants dans le répertoire pour vérifier que le nouveau nom est unique. Comme le nombre de fichiers du répertoire devient important (généralement plus de 300 000), ce processus prend de plus en plus de temps. Le temps de création d'un fichier augmente et les performances diminuent. Par conséquent, le fait de désactiver ce processus peut accroître les performances de façon significative. Pour désactiver ce processus, tapez la commande suivante à l'invite de commandes, puis redémarrez l'ordinateur : fsutil behavior set disable8dot3 1 Remarque : cette option désactive la génération de noms 8.3 sur tous les volumes NTFS sur le serveur. Si des volumes sont utilisés par les applications 16 bits, ils peuvent rencontrer des problèmes après que vous avez modifié ce comportement. La deuxième option de désactivation consiste à mettre à jour le dernier temps d'accès d'un fichier lorsqu'il est accessible. Si la charge de travail accède brièvement à plusieurs fichiers, un temps disproportionné est consacré à la mise à jour du dernier temps d'accès de chaque fichier. Le fait de désactiver cette option peut aussi augmenter considérablement les performances. Pour désactiver ce processus, tapez la commande suivante à l'invite de commandes, puis redémarrez l'ordinateur : fsutil behavior set disablelastaccess 1 Taille de cluster Tous les systèmes de fichiers Windows possèdent le concept de « cluster », qui est l'unité d'affectation lorsque l'espace disque est alloué. Comme un cluster correspond à la plus petite quantité d'espace disque pouvant être allouée, si un fichier est très petit, une partie du cluster peut être inutilisée (essentiellement gaspillée). La taille de cluster est, par conséquent, généralement assez petite afin que les petits fichiers ne gaspillent pas l'espace disque. Plusieurs clusters peuvent être alloués aux fichiers volumineux, ou la taille des fichiers peut évoluer dans le temps et des clusters peuvent leur être alloués à mesure que leur taille augmente. Si un fichier se développe beaucoup, mais par petits segments, les clusters alloués sont susceptibles de ne pas être contigus sur le disque (c.-à-d., ce sont des « fragments »). Cela signifie que plus les clusters sont petits et plus un fichier se développe, plus il sera fragmenté. La taille de cluster est donc un compromis entre le gaspillage de l'espace disque et la réduction de la fragmentation. Plus d'informations sur les différentes tailles de cluster des systèmes de fichiers Windows sont disponibles dans l'article de la Base de connaissances « Taille de cluster par défaut pour FAT et NTFS » (http://support.microsoft.com/kb/140365). La recommandation pour l'utilisation de FILESTREAM est que les unités individuelles de données BLOB aient une taille minimale de 1 Mo. Dans ce cas, il est recommandé que la taille de clusters NTFS du volume du conteneur de données FILESTREAM soit définie à 64 Ko pour réduire la fragmentation. Cette opération doit être effectuée manuellement, car la valeur par défaut pour les volumes NTFS jusqu'à 2 téraoctets (To) est de 4 Ko. Cela peut être accompli en utilisant l'option /A de la commande format. Par exemple, à l'invite de commandes, tapez : format F: /FS:NTFS /V:MyFILESTREAMContainer /A:64K

13

Ce paramètre doit être associé à des tailles de mémoires tampons volumineuses, comme le décrit la section « Considérations relatives au réglage des performances et aux tests » plus loin dans ce livre blanc. Gestion de la fragmentation Comme décrit précédemment, lorsque plusieurs fichiers se développent sur un volume, ils sont fragmentés. Cela signifie que la collection de clusters allouée au fichier n'est pas contiguë. Lorsque le fichier est lu séquentiellement, les têtes de disque sous-jacentes doivent lire tous les clusters dans l'ordre, ce qui peut signifier qu'ils doivent lire différentes parties du disque. Même si les fichiers ne se développent pas une fois créés, s'ils ont été créés sur un volume où l'espace disponible n'est pas dans un seul segment contigu, ils peuvent être fragmentés immédiatement, car les clusters nécessaires pour les contenir ne sont pas disponibles en contigu. La fragmentation affecte les performances de lecture séquentielle contrairement à lorsqu'il n'y a pas ou peu de fragmentation. Le problème est très semblable à celui de la fragmentation d'index dans une base de données qui ralentit les performances d'analyse d'étendue de requête. Il est, par conséquent, essentiel que la fragmentation soit régulièrement supprimée à l'aide d'un outil de défragmentation de disque de façon à conserver les performances de lecture séquentielle. En outre, si le volume qui va être utilisé pour héberger le conteneur de données FILESTREAM a été précédemment utilisé, ou s'il contient d'autres données, le niveau de fragmentation doit être vérifié et modifié si nécessaire. Compression Les données stockées dans NTFS peuvent être compressées pour économiser de l'espace disque, mais au détriment d'UC supplémentaire pour compresser et décompresser les données lorsqu'elles sont écrites ou lues, respectivement. La compression n'est pas utile si les données sont essentiellement incompressibles. Par exemple, les données aléatoires, les données chiffrées ou les données qui ont déjà été compressées ne vont pas bien se compresser, mais devront être transmises via l'algorithme de compression NTFS et entraîneront une surcharge du processeur. C'est pourquoi, l'activation de la compression n'a de sens que lorsque les données peuvent être fortement compressées, et lorsque l'UC supplémentaire nécessaire n'entraîne pas la diminution des performances de charge de travail. Notez également que la compression ne peut être activée que lorsque la taille maximale de cluster NTFS est de 4 096 octets. La compression peut être activée sur le volume du conteneur de données FILESTREAM lorsqu'il est formaté, avec l'option /C de la commande format. Par exemple : format F: /FS:NTFS /V:MyFILESTREAMContainer /A:4096 /C Un volume existant peut également être activé pour la compression à l'aide des étapes suivantes : 1. Dans Poste de travail ou l'Explorateur Windows, cliquez avec le bouton droit sur le volume à compresser ou à décompresser. 2. Cliquez sur Propriétés pour afficher la boîte de dialogue Propriétés. 3. Dans l'onglet Général, activez ou désactivez la case à cocher Compresser le lecteur pour augmenter l'espace disque disponible, puis cliquez sur OK. 4. Dans la boîte de dialogue Confirmation des modifications d’attributs, indiquez si la compression s'applique au volume entier ou au dossier racine. C'est ce que montre l'illustration suivante.

14

Illustration 2 : Compression d'un volume existant à l'aide de l'Explorateur Windows Gestion de l'espace Bien que plusieurs conteneurs de données FILESTREAM puissent être placés sur un seul volume NTFS, il existe des raisons pour lesquelles il faut disposer d'un mappage 1:1 entre les conteneurs de données et les volumes NTFS. Outre le risque potentiel de contention dépendante de la charge de travail, il est impossible de gérer l'espace du conteneur de données FILESTREAM dans SQL Server. C'est pourquoi, l'utilisation de quotas de disque NTFS est nécessaire, le cas échéant. Les quotas de disque sont suivis par utilisateur, par volume. Ainsi, le fait de posséder plusieurs conteneurs de données FILESTREAM sur un seul volume rend difficile de déterminer quel conteneur de données utilise le plus d'espace disque. Notez que tous les fichiers FILESTREAM sont créés sous le compte de service SQL Server. Si ce paramètre est modifié, de l'espace disque est chargé dans le nouveau compte de service. Il existe un seul pilote de filtre du système de fichiers FILESTREAM pour chaque volume NTFS qui comprend un conteneur de données FILESTREAM, et il en existe également un pour chaque version de SQL Server qui a un conteneur de données FILESTREAM sur le volume. Chaque pilote de filtre est chargé de gérer les conteneurs de données FILESTREAM pour ce volume, pour toutes les instances qui utilisent une version particulière de SQL Server. Par exemple, un volume NTFS qui héberge trois conteneurs de données FILESTREAM, un pour chacune des trois instances de SQL Server 2008, n'aura qu'un pilote de filtre du système de fichiers FILESTREAM SQL Server 2008.

15

Sécurité Il existe deux critères de sécurité pour l'utilisation de la fonctionnalité FILESTREAM. Premièrement, SQL Server doit être configuré pour la sécurité intégrée. Deuxièmement, si l'accès distant est utilisé, le port SMB (445) doit être activé via tous les systèmes de pare-feu. Il s'agit du même port que celui qui est nécessaire pour l'accès standard au partage distant. Pour plus d'informations, consultez l'article de la Base de connaissances « Présentation des services et des exigences de ports réseau pour Windows Server System » (http://support.microsoft.com/kb/832017).

Considérations relatives à l'antivirus L'antivirus est omniprésent dans l'environnement actuel. FILESTREAM ne peut pas empêcher le logiciel antivirus d'analyser les fichiers dans le conteneur de données FILESTREAM (cela créerait des problèmes de sécurité). Le logiciel possède généralement un paramètre de stratégie indiquant ce qu'il faut faire lorsqu'un fichier est suspecté d'être contaminé par un virus : supprimer le fichier ou restreindre son accès (opération appelée « mise en quarantaine » du fichier). Dans les deux cas, l'accès aux données BLOB du fichier affecté sera empêché et, dans SQL Server, le fichier semblera avoir été supprimé. Il est recommandé que l'antivirus soit défini de façon à mettre les fichiers en quarantaine, et non pas les supprimer. DBCC CHECKDB peut être utilisé dans SQL Server afin de découvrir quels fichiers semblent être manquants. Ensuite, l'administrateur Windows peut corréler les noms de fichiers par rapport au journal de l'antivirus et effectuer l'action corrective nécessaire.

Activation de FILESTREAM dans Windows FILESTREAM est une fonctionnalité hybride qui requiert que l'administrateur Windows et les administrateurs SQL Server effectuent des actions avant que la fonctionnalité ne soit activée. Cela est nécessaire pour conserver la séparation des fonctions entre les administrateurs, surtout si l'administrateur SQL Server n'est pas aussi l'administrateur Windows. L'activation de FILESTREAM au niveau Windows installe un pilote de filtre du système de fichiers, opération pour laquelle seul un administrateur Windows dispose des privilèges nécessaires. Au niveau Windows, FILESTREAM est activé pendant l'installation de SQL Server 2008 ou en exécutant le Gestionnaire de configuration SQL Server. Voici les étapes à suivre : 1.

2. 3. 4. 5. 6. 7.

8.

9.

16

Dans le menu Démarrer, pointez successivement sur Tous les programmes, Microsoft SQL Server 2008, Outils de configuration, puis cliquez sur Gestionnaire de configuration SQL Server. Dans la liste de services, cliquez avec le bouton droit sur Services SQL Server, puis cliquez sur Ouvrir. Dans le composant logiciel enfichable Gestionnaire de configuration SQL Server, recherchez l'instance de SQL Server sur laquelle vous souhaitez activer FILESTREAM. Cliquez avec le bouton droit sur l'instance, puis cliquez sur Propriétés. Dans la boîte de dialogue Propriétés de SQL Server, cliquez sur l'onglet FILESTREAM. Activez la case à cocher Activer FILESTREAM pour l'accès Transact-SQL . Si vous souhaitez lire et écrire des données FILESTREAM à partir de Windows, cliquez sur Activer FILESTREAM pour l'accès en continu aux E/S de fichier. Entrez le nom du partage Windows dans la zone Nom de partage Windows. Si des clients distants doivent accéder aux données FILESTREAM stockées sur ce partage, sélectionnez Autoriser les clients distants à avoir un accès en continu aux données FILESTREAM. Cliquez sur Appliquer.

L'illustration suivante montre l'onglet FILESTREAM tel que le décrit la procédure.

Illustration 3 : Configuration de FILESTREAM avec le Gestionnaire de configuration SQL Server Cette procédure doit être exécutée pour chaque instance SQL Server qui va utiliser la fonctionnalité FILESTREAM avant de pouvoir être utilisée par SQL Server. Notez qu'il n'y a aucune spécification du conteneur de données FILESTREAM à ce stade. Cette opération est réalisée lorsqu'un groupe de fichiers FILESTREAM est créé dans une base de données après que FILESTREAM a été activé dans SQL Server. Notez qu'il est possible de désactiver l'accès FILESTREAM au niveau Windows même lorsque SQL Server l'a activé. Dans ce cas, une fois l'instance SQL Server redémarrée, toutes les données FILESTREAM sont indisponibles. L'avertissement suivant s'affiche :

Illustration 4 : Avertissement affiché lors de la désactivation de FILESTREAM avec le Gestionnaire de configuration SQL Server

17

Configuration de SQL Server pour FILESTREAM Chaque instance SQL Server qui va utiliser la fonctionnalité FILESTREAM doit être configurée séparément, au niveau Windows et au niveau SQL Server. Lorsque FILESTREAM est activé, une base de données doit être configurée pour stocker les données FILESTREAM, et uniquement ensuite des tables comportant des colonnes FILESTREAM peuvent être définies. Cette section explique comment configurer FILESTREAM au niveau SQL Server et comment créer des bases de données et des tables compatibles avec FILESTREAM. Elle explique également comment FILESTREAM interagit avec d'autres fonctionnalités de SQL Server 2008.

Considérations relatives à la sécurité FILESTREAM requiert l'utilisation de la sécurité intégrée (authentification Windows). Lorsqu'une application utilisant Win32 tente d'accéder aux données FILESTREAM, l'utilisateur Windows est validé par SQL Server. Si l'utilisateur dispose d'un accès Transact-SQL aux données FILESTREAM, l'accès est également accordé au niveau Win32, tant que le jeton de transaction est obtenu dans le contexte de sécurité de l'utilisateur Windows qui exécute l'ouverture de fichier. La spécification de l'authentification Windows provient de la nature des API d'E/S de fichier Windows. La seule façon de passer l'identité du client de l'application cliente à SQL Server pendant une opération d'E/S de fichier consiste à utiliser le jeton Windows associé au thread du client. Lorsque le conteneur de données FILESTREAM est créé, il est automatiquement protégé afin que le compte de service SQL Server et les membres du groupe builtin/Administrators aient accès à l'arborescence de répertoires du conteneur de données. Vous devez éviter que le contenu du conteneur de données soit modifié, excepté via des méthodes transactionnelles prises en charge, car les modifications apportées via d'autres méthodes entraîneront la corruption du conteneur.

Activation de FILESTREAM dans SQL Server La deuxième étape d'activation de FILESTREAM s'effectue dans l'instance SQL Server 2008. Cette opération ne doit pas être effectuée tant que FILESTREAM n'est pas activé au niveau Windows, et le volume NTFS qui va stocker les données FILESTREAM n'a pas été correctement préparé (tel que le décrit la section « Configuration de Windows pour FILESTREAM » plus haut). L'accès FILESTREAM est contrôlé dans SQL Server à l'aide de sp_configure pour affecter un des trois paramètres à l'option de configuration filestream_access_level. Les valeurs possibles sont :   

0 : désactiver la prise en charge de FILESTREAM pour cette instance 1 : activer FILESTREAM pour l'accès Transact-SQL uniquement 2 : activer FILESTREAM pour l'accès en continu Transact-SQL et Win32

L'exemple suivant montre comment activer FILESTREAM pour l'accès en continu Transact-SQL et Win32.

EXEC sp_configure filestream_access_level, 2; GO RECONFIGURE; GO

18

L'instruction RECONFIGURE est nécessaire pour que la valeur récemment configurée prenne effet. Notez que si FILESTREAM n'a pas été activé au niveau Windows, il n'est pas activé au niveau SQL Server lors de l'exécution du code précédent. Vous trouverez la valeur actuelle configurée à l'aide du code suivant.

EXEC sp_configure filestream_access_level; GO

Si FILESTREAM n'est pas configuré au niveau Windows, « config_value » dans la sortie de sp_configure est différent (c.-à-d., 0) de « run_value » après exécution de l'instruction RECONFIGURE.

Création d'une base de données compatible avec FILESTREAM Une fois que FILESTREAM est activé aux niveaux Windows et SQL Server, un conteneur de données FILESTREAM peut être défini. Cette opération s'effectue en définissant un groupe de fichiers FILESTREAM dans une base de données. Il existe un mappage 1:1 entre les groupes de fichiers FILESTREAM et les conteneurs de données FILESTREAM. Un groupe de fichiers FILESTREAM peut être défini lorsqu'une base de données est créée, ou il peut être créé séparément à l'aide d'une instruction ALTER DATABASE. L'exemple suivant crée un groupe de fichiers FILESTREAM dans une base de données existante.

ALTER DATABASE Production ADD FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM; GO

La clause CONTAINS FILESTREAM est nécessaire pour différencier le nouveau groupe de fichiers des groupes de fichiers ordinaires de base de données. Si la fonctionnalité FILESTREAM est désactivée, cette instruction échoue avec l'erreur suivante.

Msg 5591, Niveau 16, État 3, Ligne 1 La fonctionnalité FILESTREAM est désactivée.

En supposant que FILESTREAM est activé aux niveaux Windows et SQL Server, le groupe de fichiers est créé. À ce stade, le conteneur de données FILESTREAM est défini en ajoutant un seul fichier au groupe de fichiers. Le chemin d'accès spécifié est le chemin d'accès du répertoire qui va être créé en tant que racine du conteneur de données. Le chemin d'accès complet jusqu'au nom du répertoire final, mais

19

qui ne le comprend pas, doit déjà exister. L'exemple suivant définit le conteneur de données pour le groupe de fichiers FileStreamGroup1 créé précédemment.

ALTER DATABASE Production ADD FILE ( NAME = FSGroup1File, FILENAME = 'F:\Production\FSDATA') TO FILEGROUP FileStreamGroup1; GO

À ce stade, le répertoire FSDATA va être créé. Il est vide en dehors de deux éléments :  

Le fichier filestream.hdr. Il s'agit des métadonnées FILESTREAM du conteneur de données. Le répertoire $FSLOG. Il s'agit de l'équivalent FILESTREAM du journal des transactions d'une base de données.

Notez qu'une base de données peut contenir plusieurs groupes de fichiers FILESTREAM. Cela peut être utile pour distinguer le stockage d'objets blob de plusieurs tables de la base de données.

Création d'une table pour le stockage de données FILESTREAM Une fois que la base de données possède un groupe de fichiers FILESTREAM, il est possible de créer des tables qui contiennent des colonnes FILESTREAM. Comme indiqué précédemment, une colonne FILESTREAM est définie comme colonne varbinary (max) qui a l'attribut FILESTREAM. Le code suivant crée une table avec une seule colonne FILESTREAM.

USE Production; GO CREATE TABLE DocumentStore ( DocumentID INT IDENTITY PRIMARY KEY, Document VARBINARY (MAX) FILESTREAM NULL, DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID ()) FILESTREAM_ON FileStreamGroup1; GO

20

21

Une table peut avoir plusieurs colonnes FILESTREAM, mais les données de toutes les colonnes FILESTREAM d'une table doivent être stockées dans le même groupe de fichiers FILESTREAM. Si la clause FILESTREAM_ON n'est pas spécifiée, le groupe de fichiers FILESTREAM défini comme étant le groupe de fichiers par défaut est utilisé. Cela peut ne pas être la configuration voulue et peut provoquer des problèmes de performances. Une fois la table créée, le conteneur de données FILESTREAM doit contenir un autre répertoire, correspondant à la table, avec un sous-répertoire qui correspond à la colonne FILESTREAM dans la table. Ce sous-répertoire contiendra les fichiers de données une fois les données entrées dans la table. La structure du répertoire varie en fonction du nombre de colonnes FILESTREAM dont dispose une table et selon que la table est partitionnée ou non. Notez que pour qu'une table possède plusieurs colonnes FILESTREAM, elle doit également avoir une colonne avec le type de données uniqueidentifier qui a l'attribut ROWGUIDCOL. Cette colonne ne doit pas autoriser les valeurs Null et doit avoir une contrainte de colonne unique de type UNIQUE ou PRIMARY KEY . La valeur du GUID de la colonne doit être fournie par une application lors de l'insertion de données ou par une contrainte DEFAULT qui utilise la fonction NEWID() (ou NEWSEQUENTIALID() si la réplication de fusion est configurée, comme indiqué dans la section « Combinaisons de fonctionnalités et restrictions » plus loin dans ce document). Pour plus d'informations sur les détails et les restrictions sur le schéma de table et les options nécessaires, consultez la rubrique « CREATE TABLE (Transact-SQL) » de la documentation en ligne de SQL Server 2008 (http://msdn.microsoft.com/fr-fr/library/ms174979.aspx).

Configuration du garbage collection FILESTREAM Les fichiers de données FILESTREAM dans le conteneur de données FILESTREAM ne peuvent pas être partiellement mis à jour. Cela signifie que toute modification apportée aux données BLOB de la colonne FILESTREAM crée un nouveau fichier de données FILESTREAM. L'ancien fichier doit être conservé jusqu'à ce qu'il ne soit plus nécessaire à des fins de récupération. Les fichiers qui représentent les données FILESTREAM supprimées, les insertions restaurées de données FILESTREAM, sont conservés. Les fichiers qui ne sont plus nécessaires sont supprimés par le processus de garbage collection. Ce processus est automatique, contrairement aux services Windows SharePoint®, où le garbage collection doit être implémenté manuellement sur le magasin d'objets blob externe. Toutes les opérations de fichier FILESTREAM sont mappées à un numéro séquentiel dans le journal des transactions de la base de données. Tant que le journal des transactions est tronqué après le LSN d'opération FILESTREAM, le fichier n'est plus nécessaire et ne peut pas être récupéré par le garbage collector. Par conséquent, tout ce qui peut empêcher la troncation du journal des transactions peut également empêcher la suppression physique d'un fichier FILESTREAM. Exemples :   

22

Les sauvegardes de journal n'ont pas été effectuées, en mode de récupération FULL ou BULK_LOGGED. Il existe une transaction active de longue durée. Le travail du lecteur du journal de réplication n'a pas été exécuté.

Le garbage collection FILESTREAM est une tâche en arrière-plan qui est déclenchée par le processus de point de contrôle de base de données. Un point de contrôle est automatiquement exécuté lorsque le journal des transactions a été généré. Pour plus d'informations, consultez la rubrique « CHECKPOINT et la partie active du journal » dans la documentation en ligne de SQL Server 2008 (http://msdn.microsoft.com/fr-fr/library/ms189573.aspx). Étant donné que les opérations de fichier FILESTREAM font l'objet d'une journalisation minimale dans le journal des transactions de la base de données, quelques instants peuvent s'écouler avant que le nombre d'enregistrements générés dans le journal des transactions déclenche un processus de point de contrôle et qu'une opération de garbage collection se produise. Si cela devient un problème, forcez le garbage collection à l'aide de l'instruction CHECKPOINT.

Considérations relatives au partitionnement Si la table qui contient des données FILESTREAM est partitionnée, la clause FILESTREAM_ON doit être incluse et doit spécifier un schéma de partitionnement de groupes de fichiers FILESTREAM et reposer sur la fonction de partitionnement de la table. Cela est nécessaire, car le schéma standard de partitionnement impliquera des groupes de fichiers ordinaires qui ne peuvent pas être utilisés pour stocker des données FILESTREAM. La définition de la table (dans une instruction CREATE TABLE ou CREATE CLUSTERED INDEX … WITH DROP_EXISTING) spécifie les deux schémas de partitionnement. Le schéma de partitionnement FILESTREAM peut indiquer que toutes les partitions sont mappées à un seul groupe de fichiers, mais cela n'est pas recommandé, car cela peut entraîner des problèmes de performances. L'exemple suivant (forcé) illustre cette syntaxe :

CREATE PARTITION FUNCTION DocPartFunction (INT) AS RANGE RIGHT FOR VALUES (100000, 200000); GO

CREATE PARTITION SCHEME DocPartScheme AS PARTITION DocPartFunction TO (Data_FG1, Data_FG2, Data_FG3); GO

CREATE PARTITION SCHEME DocFSPartScheme AS PARTITION DocPartFunction TO (FS_FG1, FS_FG2, FS_FG3); GO

CREATE TABLE DocumentStore (

23

DocumentID INT IDENTITY PRIMARY KEY, Document VARBINARY (MAX) FILESTREAM NULL, DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID () ON Data_FG1) ON DocPartScheme (DocumentID) FILESTREAM_ON DocFSPartScheme; GO

Notez que pour utiliser la colonne DocumentID comme colonne de partitionnement, l'index non cluster sous-jacent qui applique la contrainte UNIQUE sur le DocGUID doit être explicitement placé dans un groupe de fichiers afin que DocumentID puisse être la colonne de partitionnement. Cela signifie que le basculement de partition n'est possible que si les contraintes UNIQUE sont désactivées avant d'effectuer le basculement de partition, car il s'agit d'index non alignés, puis sont réactivées après. Suite de l'exemple précédent, le code suivant crée une table, puis tente un basculement de partition.

CREATE TABLE NonPartitionedDocumentStore ( DocumentID INT IDENTITY PRIMARY KEY, Document VARBINARY (MAX) FILESTREAM NULL, DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID ()); GO

ALTER TABLE DocumentStore SWITCH PARTITION 2 TO NonPartitionedDocumentStore; GO

Le basculement de partition échoue avec le message suivant. Msg 7733, Niveau 16, État 4, Ligne 1 Échec de l'instruction 'ALTER TABLE SWITCH'. La table « FileStreamTestDB.dbo.DocumentStore » est partitionnée alors que l'index « UQ_Document_8CC1617F60ED59 » n'est pas partitionné.

24

La désactivation de l'index unique dans la table source et une nouvelle tentative donne le code suivant.

ALTER INDEX [UQ__Document__8CC331617F60ED59] ON DocumentStore DISABLE; GO

ALTER TABLE FileStreamTest3 SWITCH PARTITION 2 TO NonPartitionedFileStreamTest3; GO

Le basculement échoue également, avec le message suivant. Msg 4947, Niveau 16, État 1, Ligne 1 Échec de l'instruction ALTER TABLE SWITCH. Il n'y a pas d'index en double dans la table source « FileStreamTestDB.dbo.DocumentStore » pour l'index « UQ_NonParti_8CC3316103317E3D » dans la table cible « FileStreamTestDB.dbo.NonPartitionedDocumentStore ». Les index uniques dans les tables partitionnées et non partitionnées doivent être désactivés avant de poursuivre le basculement.

ALTER INDEX [UQ__NonParti__8CC3316103317E3D] ON NonPartitionedDocumentStore DISABLE; GO

ALTER TABLE DocumentStore SWITCH PARTITION 2 TO NonPartitionedDocumentStore; GO

ALTER INDEX [UQ__NonParti__8CC3316103317E3D] ON NonPartitionedDocumentStore REBUILD WITH (ONLINE = ON); ALTER INDEX [UQ__Document__8CC331617F60ED59] ON NonPartitionedDocumentStore REBUILD WITH (ONLINE = ON); GO

25

Des informations supplémentaires sur les données FILESTREAM de partitionnement seront incluses dans le prochain livre blanc consacré au partitionnement dans SQL Server 2008.

Équilibrage de charge des données FILESTREAM Il est également possible d'utiliser le partitionnement pour créer un schéma de table, qui permet l'équilibrage de la charge brute des données FILESTREAM entre plusieurs volumes. Cela peut être souhaitable pour diverses raisons, notamment des limitations au niveau matériel ou pour autoriser le stockage des zones réactives d'une table sur différents volumes. Le code suivant illustre une fonction et un schéma de partitionnement en fonction de la colonne uniqueidentifier qui répartit efficacement les données FILESTREAM sur 16 volumes, tout en agrégeant les données structurées dans deux groupes de fichiers.

USE master; GO

-- Créer la base de données CREATE DATABASE Production ON PRIMARY (NAME = 'Production', FILENAME = 'E:\Production\Production.mdf'), FILEGROUP DataFilegroup1 (NAME = 'Data_FG1', FILENAME = 'F:\Production\Data_FG1.ndf'), FILEGROUP DataFilegroup2 (NAME = 'Data_FG2', FILENAME = 'G:\Production\Data_FG2.ndf'), FILEGROUP FSFilegroup0 CONTAINS FILESTREAM (NAME = 'FS_FG0', FILENAME = 'H:\Production\FS_FG0'), FILEGROUP FSFilegroup1 CONTAINS FILESTREAM (NAME = 'FS_FG1', FILENAME = 'I:\Production\FS_FG1'), FILEGROUP FSFilegroup2 CONTAINS FILESTREAM (NAME = 'FS_FG2', FILENAME = 'J:\Production\FS_FG2'), FILEGROUP FSFilegroup3 CONTAINS FILESTREAM (NAME = 'FS_FG3', FILENAME = 'K:\Production\FS_FG3'), FILEGROUP FSFilegroup4 CONTAINS FILESTREAM (NAME = 'FS_FG4', FILENAME = 'L:\Production\FS_FG4'),

26

FILEGROUP FSFilegroup5 CONTAINS FILESTREAM (NAME = 'FS_FG5', FILENAME = 'M:\Production\FS_FG5'), FILEGROUP FSFilegroup6 CONTAINS FILESTREAM (NAME = 'FS_FG6', FILENAME = 'N:\Production\FS_FG6'), FILEGROUP FSFilegroup7 CONTAINS FILESTREAM (NAME = 'FS_FG7', FILENAME = 'O:\Production\FS_FG7'), FILEGROUP FSFilegroup8 CONTAINS FILESTREAM (NAME = 'FS_FG8', FILENAME = 'P:\Production\FS_FG8'), FILEGROUP FSFilegroup9 CONTAINS FILESTREAM (NAME = 'FS_FG9', FILENAME = 'Q:\Production\FS_FG9'), FILEGROUP FSFilegroupA CONTAINS FILESTREAM (NAME = 'FS_FGA', FILENAME = 'R:\Production\FS_FGA'), FILEGROUP FSFilegroupB CONTAINS FILESTREAM (NAME = 'FS_FGB', FILENAME = 'S:\Production\FS_FGB'), FILEGROUP FSFilegroupC CONTAINS FILESTREAM (NAME = 'FS_FGC', FILENAME = 'T:\Production\FS_FGC'), FILEGROUP FSFilegroupD CONTAINS FILESTREAM (NAME = 'FS_FGD', FILENAME = 'U:\Production\FS_FGD'), FILEGROUP FSFilegroupE CONTAINS FILESTREAM (NAME = 'FS_FGE', FILENAME = 'V:\Production\FS_FGE'), FILEGROUP FSFilegroupF CONTAINS FILESTREAM (NAME = 'FS_FGF', FILENAME = 'W:\Production\FS_FGF'); GO

USE Production; GO

-- Créer une fonction de partition basée sur les 6 derniers octets du GUID

27

CREATE PARTITION FUNCTION LoadBalance_PF (UNIQUEIDENTIFIER) AS RANGE LEFT FOR VALUES ( CONVERT (uniqueidentifier, '00000000-0000-0000-0000-100000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-200000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-300000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-400000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-500000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-600000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-700000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-800000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-900000000000'), CONVERT (, « uniqueidentifier 00000000-0000-0000-0000-a00000000000 »), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-b00000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-c00000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-d00000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-e00000000000'), CONVERT (uniqueidentifier, '00000000-0000-0000-0000-f00000000000')); GO

-- Créer un schéma de partitionnement FILESTREAM qui permet le mappage à 16 groupes de fichiers FILESTREAM CREATE PARTITION SCHEME LoadBalance_FS_PS AS PARTITION LoadBalance_PF TO ( FSFileGroup0, FSFileGroup1, FSFileGroup2, FSFileGroup3, FSFileGroup4, FSFileGroup5, FSFileGroup6, FSFileGroup7, FSFileGroup8, FSFileGroup9, FSFileGroupA, FSFileGroupB, FSFileGroupC, FSFileGroupD, FSFileGroupE, FSFileGroupF); GO

28

-- Créer un schéma de partitionnement de données dans une méthode de tourniquet (round robin) entre deux groupes de fichiers CREATE PARTITION SCHEME LoadBalance_Data_PS AS PARTITION LoadBalance_PF TO ( DataFileGroup1, DataFileGroup2, DataFileGroup1, DataFileGroup2, DataFileGroup1, DataFileGroup2, DataFileGroup1, DataFileGroup2, DataFileGroup1, DataFileGroup2, DataFileGroup1, DataFileGroup2, DataFileGroup1, DataFileGroup2, DataFileGroup1, DataFileGroup2); GO

-- Créer la table partitionnée CREATE TABLE DocumentStore ( DocumentID INT IDENTITY, Document VARBINARY (MAX) FILESTREAM NULL, DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT NEWID (), CONSTRAINT DocStorePK PRIMARY KEY CLUSTERED (DocGUID), CONSTRAINT DocStoreU UNIQUE (DocGUID) ON LoadBalance_Data_PS (DocGUID) FILESTREAM_ON LoadBalance_FS_PS ; GO

L'équilibrage de charge peut facilement être testé à l'aide du code suivant.

SET NOCOUNT ON; GO

29

-- Insérer 10000 lignes pour tester l'équilibrage de charge DECLARE @count INT = 0; WHILE (@count < 10000) BEGIN INSERT INTO DocumentStore DEFAULT VALUES; SET @count = @count + 1; END; GO

-- Vérifier la distribution SELECT COUNT ($PARTITION.LoadBalance_PF (DocGUID)) FROM DocumentStore GROUP BY $PARTITION.LoadBalance_PF (DocGUID); GO

Les résultats d'un exemple de série de test étaient 631, 641, 661, 640, 649, 637, 618, 618, 576, 608, 595, 645, 640, 616, 602 et 623 lignes dans chacun des groupes de fichiers FILESTREAM FS_FG0 à FS_FGF.

Combinaisons de fonctionnalités et restrictions Étant donné que la fonctionnalité FILESTREAM stocke des données dans le système de fichiers, il existe certaines restrictions et considérations à prendre en compte lorsque FILESTREAM est associé à d'autres fonctionnalités SQL Server. Cette section fournit une présentation des combinaisons de fonctionnalités à connaître. Pour plus d'informations, consultez la rubrique « Utilisation de FILESTREAM avec d'autres fonctionnalités SQL Server » dans la documentation en ligne de SQL Server 2008 (http://msdn.microsoft.com/fr-fr/library/bb895334.aspx). Réplication La réplication transactionnelle et la réplication de fusion prennent en charge les données FILESTREAM, mais il existe de nombreux éléments à en prendre en compte, notamment : 



30

Lorsque la topologie de réplication comprend des instances utilisant différentes versions de SQL Server, il existe des limitations sur la taille des données qui peuvent être envoyées aux instances de bas niveau. Les options de filtre de réplication déterminent si l'attribut FILESTREAM est répliqué ou n'utilise pas la réplication transactionnelle.

 

La taille maximale des données varbinary (max) qui peuvent être répliquées dans la réplication transactionnelle sans répliquer l'attribut FILESTREAM est de 2 Go. Lorsque la réplication de fusion est utilisée, celle-ci ainsi que FILESTREAM requièrent une colonne uniqueidentifier. Soyez vigilant avec le schéma de la table lorsque vous utilisez la réplication de fusion afin que les GUID soient séquentiels (c.-à-d., utilisez NEWSEQUENTIALID() plutôt que NEWID()).

Mise en miroir de bases de données La mise en miroir de bases de données ne prend pas en charge FILESTREAM. Un groupe de fichiers FILESTREAM ne peut pas être créé sur le serveur principal. La mise en miroir de bases de données ne peut pas être configurée pour une base de données qui contient des groupes de fichiers FILESTREAM. Chiffrement Les données FILESTREAM ne peuvent pas être chiffrées à l'aide de méthodes de chiffrement SQL Server. Si le chiffrement transparent des données est activé, les données FILESTREAM ne sont pas chiffrées. Clustering de basculement FILESTREAM est entièrement pris en charge par le clustering de basculement. FILESTREAM doit être activé pour tous les nœuds du cluster au niveau Windows, et les conteneurs de données FILESTREAM doivent être placés sur un stockage partagé afin que les données soient disponibles dans tous les nœuds. Pour plus d'informations, consultez la documentation en ligne de SQL Server 2008 : « Procédure : configurer FILESTREAM sur un cluster de basculement » (http://msdn.microsoft.com/frfr/library/cc645886.aspx). Texte intégral L'indexation de texte intégral fonctionne avec une colonne FILESTREAM de la même façon qu'avec une colonne varbinary (max). La table doit contenir une colonne supplémentaire qui contient l'extension de nom de fichier pour les données BLOB stockées dans la colonne FILESTREAM. Instantanés de base de données SQL Server ne prend pas en charge les instantanés de base de données pour les conteneurs de données FILESTREAM. Si un fichier de données FILESTREAM est inclus dans une clause CREATE DATABASE ON, l'instruction échoue et une erreur est levée. Si une base de données contient des données FILESTREAM, un instantané de base de données des groupes de fichiers ordinaires peut être créé. Dans ce cas, un message d'avertissement est retourné et les groupes de fichiers FILESTREAM sont marqués comme étant hors ligne dans l'instantané de base de données. Les requêtes fonctionnent comme prévu sur l'instantané de base de données à moins qu'elles tentent d'accéder aux données FILESTREAM. Si cela se produit une erreur est générée. Un instantané de base de données ne peut pas être rétabli si la base de données contient des données FILESTREAM, car il n'existe aucun moyen de déterminer l'état des données FILESTREAM au moment représenté par l'instantané de base de données. Vues, index, statistiques, déclencheurs et contraintes

31

Les colonnes FILESTREAM ne peuvent pas faire partie d'une clé d'index ou être spécifiées comme colonne INCLUDE dans un index non cluster. Il est possible de définir une colonne calculée qui référence une colonne FILESTREAM, mais la colonne calculée ne peut pas être indexée. Il est impossible de créer des statistiques sur des colonnes FILESTREAM.

Il est impossible de créer des contraintes PRIMARY KEY, FOREIGN KEY, et UNIQUE sur des colonnes FILESTREAM. Les vues indexées ne peuvent pas contenir de colonnes FILESTREAM ; contrairement aux vues non indexées. Les déclencheurs Instead-of ne peuvent pas être définis sur des tables qui contiennent des colonnes FILESTREAM. Niveaux d'isolation Lorsque les données FILESTREAM sont accessibles via des API Win32, seul le niveau d'isolation validé en lecture est pris en charge. L'accès Transact-SQL autorise également les niveaux d'isolation sérialisable et de lecture renouvelée. En outre, avec l'accès Transact-SQL, les lectures erronées sont autorisées par le niveau d'isolation non validé en lecture, ou l'indicateur de requête NOLOCK, mais cet accès n'affiche pas les mises à jour en cours d'exécution des données FILESTREAM. Sauvegarde et restauration FILESTREAM fonctionne avec tous les modes de récupération et toutes les formes de sauvegarde et de restauration (complète, différentielle et des journaux). Dans une situation d'urgence, si l'option CONTINUE_AFTER_ERROR est spécifiée sur une option BACKUP ou RESTORE, il est possible que les données FILESTREAM ne soient pas récupérées sans perte (semblable à la récupération des données standard lorsque CONTINUE_AFTER_ERROR est spécifiée). Sécurité L'instance SQL Server doit être configurée pour utiliser la sécurité intégrée si l'accès Win32 aux données FILESTREAM est requis. Copie des journaux de transaction La copie des journaux de transaction prend en charge FILESTREAM. Les serveurs principaux et secondaires doivent exécuter SQL Server 2008 ou une version ultérieure, et FILESTREAM doit être activé au niveau Windows. SQL Server Express SQL Server Express prend en charge FILESTREAM. La limite de taille de base de données de 4 Go n'inclut pas le conteneur de données FILESTREAM. Toutefois, si les données FILESTREAM sont envoyées à/à partir de l'instance SQL Server Express avec Service Broker, soyez vigilant, car Service Broker ne prend pas en charge le stockage des données comme FILESTREAM dans les files d'attente de transmission ou cibles. Cela signifie que si une file d'attente est créée, la limite de taille de base de données de 4 Go peut être atteinte. Une autre solution consiste dans ce cas à utiliser un schéma où la conversation Service Broker produit les notifications dont les données FILESTREAM ont besoin pour être envoyées ou reçues.

32

La transmission réelle des données FILESTREAM s'effectue à l'aide de l'accès à distance et via le partage FILESTREAM du conteneur de données FILESTREAM de l'instance SQL Server Express.

33

Considérations relatives au réglage des performances et aux tests Il existe plusieurs éléments importants à prendre en compte lors du paramétrage d'une charge de travail FILESTREAM :      

Vérifiez que le matériel est configuré correctement pour FILESTREAM. Vérifiez que la génération de noms 8.3 est désactivée dans NTFS. Vérifiez que le suivi du dernier temps d'accès est désactivé dans NTFS. Vérifiez que le conteneur de données FILESTREAM n'est pas sur un volume fragmenté. Vérifiez que la taille des données BLOB est appropriée pour le stockage avec FILESTREAM. Vérifiez que les conteneurs de données FILESTREAM ont leurs propres volumes dédiés.

Un facteur important à préciser est la taille du tampon utilisé par le protocole SMB pour la mise en mémoire tampon des lectures de données FILESTREAM. Dans le test avec le système d'exploitation Windows Server® 2003, les mémoires tampons de plus grande taille sont susceptibles d'obtenir un meilleur débit, avec des tailles de mémoire tampon d'un multiple de 60 Ko. De plus grandes tailles de mémoire tampon peuvent être plus efficaces sur d'autres systèmes d'exploitation. Il existe des éléments supplémentaires à prendre en compte lors de la comparaison d'une charge de travail FILESTREAM par rapport à d'autres options de stockage (une fois la charge de travail FILESTREAM paramétrée) :   

Vérifiez que le matériel de stockage et le niveau RAID est identique pour les deux. Vérifiez que le paramètre de compression de volume est identique pour les deux. Notez si FILESTREAM effectue la double écriture en fonction de l'API utilisée et des options spécifiées.

Considérations relatives à la migration des données Un scénario courant avec SQL Server 2008 est la migration des données BLOB existantes dans le stockage FILESTREAM. Alors que la mise à disposition d'un outil ou d'un ensemble de codes complet pour effectuer ces migrations dépasse l'objet de ce livre blanc, voici un exemple simple de flux de travail à suivre :  

      

34

Passez en revue les considérations de taille des données avec FILESTREAM pour vérifier que la taille moyenne des données impliquées est telle que le stockage FILESTREAM est approprié. Vérifiez les informations disponibles sur les combinaisons de fonctionnalités et sur les limitations pour vous assurer que le stockage FILESTREAM fonctionne avec toutes les autres spécifications de l'application. Suivez les recommandations de la section « Considérations relatives à l'optimisation des performances et aux tests » plus haut. Vérifiez que l'instance SQL Server utilise la sécurité intégrée et que FILESTREAM est activé aux niveaux Windows et SQL Server. Assurez-vous que l'emplacement cible du conteneur de données FILESTREAM dispose de suffisamment d'espace disque pour stocker les données BLOB migrées. Créez les groupes de fichiers FILESTREAM requis. Dupliquez les schémas de table impliqués, en modifiant les colonnes BLOB requises de façon à ce qu'elles soient des colonnes FILESTREAM. Migrez toutes les données de type non blob vers le nouveau schéma. Migrez toutes les données BLOB dans les nouvelles colonnes FILESTREAM.

Meilleures pratiques pour l'utilisation de FILESTREAM Cette section regroupe les recommandations qui ont émergé de l'utilisation de FILESTREAM au cours des tests internes et publics de la version préliminaire de la fonctionnalité. Comme avec toutes les recommandations, ce sont des considérations d'ordre général et il est possible qu'elles ne s'appliquent pas à toutes les situations et tous les scénarios. Voici les meilleures pratiques, sans ordre spécifique : 







Évitez si possible les petits ajouts dans un fichier FILESTREAM, car chaque ajout crée un fichier FILESTREAM. Cela peut s'avérer très gourmand en ressources pour les fichiers FILESTREAM volumineux. Si possible, regroupez plusieurs ajouts dans une colonne varbinary (max), puis dans la colonne FILESTREAM lorsqu'un seuil de taille est atteint. Avec une forte charge de travail d'écriture multithread, envisagez de définir le paramètre AllocationSize des API OpenSqlFilestream ou SqlFilestream. Les tailles supérieures initiales d'allocation limiteront le risque de fragmentation au niveau du système de fichiers, surtout lorsqu'elles sont associées à une taille de clusters NTFS comme décrit précédemment. Si les fichiers FILESTREAM sont volumineux, évitez les mises à jour Transact-SQL qui ajoutent des données dans un fichier. Cela met (généralement) les données en attente danstempdb et dans un nouveau fichier physique, ce qui affecte les performances. Lorsque vous lisez une valeur FILESTREAM, considérez les éléments suivants : o Si les lectures requièrent uniquement la lecture des premiers octets, prenez en compte la fonctionnalité de sous-chaîne. o Si le fichier complet doit être lu, pensez à l'accès Win32. o Si des parties aléatoires du fichier doivent être lues, envisagez d'ouvrir le descripteur de fichier avec SetFilePointer. o Lors de la lecture d'un fichier complet, spécifiez l'indicateur FILE_SEQUENTIAL_ONLY. o Utilisez des tailles de mémoire tampon qui sont des multiples de 60 Ko (comme décrit plus haut).

La taille d'un fichier FILESTREAM peut être obtenue sans avoir à ouvrir un descripteur de fichier en ajoutant une colonne calculée persistante à la table qui stocke la taille de fichier FILESTREAM. La colonne calculée est mise à jour lorsque le fichier est déjà ouvert pour les opérations d'écriture.

Conclusion Ce livre blanc a 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. Pour conclure, il utile de rappeler les principaux points examinés dans ce document. Le stockage FILESTREAM n'est pas approprié dans tous les cas. Selon une étude précédente et le comportement de la fonctionnalité FILESTREAM, les données BLOB d'une taille de 1 Mo et supérieure qui ne sont pas accessibles via Transact-SQL sont mieux adaptées au stockage des données FILESTREAM. Vous devez également tenir compte de la charge de travail de mise à jour, car toute mise à jour partielle d'un fichier FILESTREAM génère une copie complète des fichiers. Avec une charge de travail de mise à jour particulièrement importante, les performances peuvent être telles que FILESTREAM n'est pas approprié.

35

Les détails des combinaisons de fonctionnalités doivent être étudiés pour garantir la réussite du déploiement. Par exemple, dans SQL Server 2008 version finale, la mise en miroir de bases de données ne peut pas s'appliquer à des données FILESTREAM utilisées, ni à une version de l'isolement d'instantané. La plupart des autres combinaisons de fonctionnalités sont prises en charge, mais d'autres peuvent présenter des limitations (telles que la réplication). Ce livre blanc ne fournit pas une taxonomie exhaustive des fonctionnalités et de leur interaction. Consultez les sections les plus récentes de la Documentation en ligne de SQL Server avant le déploiement, plus particulièrement car certaines limitations sont susceptibles d'être soulevées dans les versions ultérieures. Enfin, si FILESTREAM est déployé sans configurer Windows et SQL Server correctement, les niveaux de performances anticipés peuvent ne pas être atteints. Les recommandations et les détails de configuration décrits ci-dessus doivent être utilisés pour éviter les problèmes de performances.

Pour plus d'informations : http://www.microsoft.com/sqlserver/ : Site Web SQL Server http://technet.microsoft.com/fr-fr/sqlserver/ : TechCenter SQL Server http://msdn.microsoft.com/fr-fr/sqlserver/ : Centre de développement SQL Server

Avez-vous trouvé ce document utile ? Nous apprécions vos commentaires. Sur une échelle de 1 (faible) à 5 (excellent), quelle note donneriez-vous à ce document ? Expliquez pourquoi. Par exemple :  

Avez-vous attribué une bonne note car le document fournit de bons exemples, contient des captures d'écran très utiles, est clairement rédigé, ou pour d'autres raisons ? Avez-vous attribué une mauvaise note car le document fournit de mauvais exemples, contient des captures d'écran pas claires ou est mal rédigé ?

Vos commentaires nous aident à améliorer la qualité des livres blancs que nous publions. Envoyez vos commentaires.

36