FILESTREAM (SQL Server)

S’applique à :SQL Server sur Windows

FILESTREAM permet aux applications SQL Server de stocker des données non structurées, telles que des documents et des images, dans le système de fichiers. Les applications peuvent utiliser la richesse des API de streaming et les performances du système de fichiers, tout en maintenant la cohérence transactionnelle entre les données non structurées et les données structurées correspondantes.

FILESTREAM intègre le Moteur de base de données de SQL Server aux systèmes de fichiers NTFS ou ReFS en stockant les données d’objets binaires volumineux (BLOB) varbinary(max) sous forme de fichiers sur le système de fichiers. Les instructions Transact-SQL peuvent insérer, mettre à jour, interroger, rechercher et sauvegarder des données Filestream. Les interfaces du système de fichiers Win32 fournissent un accès en continu aux données.

FILESTREAM utilise le cache système NT pour mettre en cache les données de fichiers. La mise en cache des fichiers dans le cache système permet de réduire tout impact que les données FILESTREAM peuvent avoir sur les performances du moteur de base de données. Le pool de tampons SQL Server n’est pas utilisé ; par conséquent, cette mémoire est disponible pour le traitement de requête.

FILESTREAM n’est pas activé automatiquement lorsque vous installez ou mettez à niveau SQL Server. Vous devez activer FILESTREAM à l’aide du Gestionnaire de configuration SQL Server et de SQL Server Management Studio. Pour utiliser FILESTREAM, vous devez créer ou modifier une base de données de sorte qu'elle contienne un type spécial de groupe de fichiers. Ensuite, créez ou modifiez une table afin qu’elle contienne une colonne varbinary(max) avec l’attribut FILESTREAM. Après avoir effectué ces tâches, vous pouvez utiliser Transact-SQL et Win32 pour gérer les données FILESTREAM.

À quel moment utiliser FILESTREAM

Dans SQL Server, les objets blob peuvent être des données varbinary(max) standard qui stockent les données dans des tables, ou des objets varbinary(max) FILESTREAM qui stockent les données dans le système de fichiers. La taille et l'utilisation des données déterminent si vous devez utiliser du stockage de base de données ou du stockage de système de fichiers. Si les conditions suivantes sont remplies, vous devez envisager d'utiliser FILESTREAM :

  • La taille des objets stockés est, en moyenne, supérieure à 1 Mo.
  • L'accès en lecture rapide est important.
  • Vous développez des applications qui utilisent une couche intermédiaire pour la logique d’application.

Pour les plus petits objets, le stockage des objets blob varbinary(max) dans la base de données procure souvent de meilleures performances de diffusion en continu.

Stockage FILESTREAM

Le stockage FILESTREAM est implémenté en tant que colonne varbinary(max) dans laquelle les données sont stockées comme objet blob dans le système de fichiers. Les tailles des objets blob sont limitées uniquement par la taille de volume du système de fichiers. La limitation varbinary(max) standard de tailles de fichiers de 2 Go ne s’applique pas aux objets blob stockés dans le système de fichiers.

Pour spécifier qu’une colonne doit stocker des données dans le système de fichiers, spécifiez l’attribut FILESTREAM sur une colonne varbinary(max) . Cet attribut fait en sorte que le moteur de base de données stocke toutes les données pour cette colonne dans le système de fichiers, mais pas dans le fichier de base de données.

Les données FILESTREAM doivent être stockées dans des groupes de fichiers FILESTREAM. Un groupe de fichiers FILESTREAM est un groupe de fichiers spécial qui contient des répertoires de système de fichiers au lieu des fichiers eux-mêmes. Ces répertoires de système de fichiers portent le nom de conteneurs de données. Les conteneurs de données sont l’interface entre le stockage du moteur de base de données et le stockage du système de fichiers.

Lorsque vous utilisez le stockage FILESTREAM, considérez les éléments suivants :

  • Lorsqu’une table contient une colonne FILESTREAM, chaque ligne doit avoir un ID de ligne unique n’acceptant pas la valeur Null.
  • Plusieurs conteneurs de données peuvent être ajoutés à un groupe de fichiers FILESTREAM.
  • Les conteneurs de données FILESTREAM ne peuvent pas être imbriqués.
  • Lorsque vous utilisez le clustering de basculement, les groupes de fichiers FILESTREAM doivent se trouver sur des ressources de disque partagées.
  • Les groupes de fichiers FILESTREAM peuvent être sur des volumes compressés.

Gestion intégrée

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. Par exemple, vous pouvez utiliser tous les modes de récupération et de sauvegarde avec les données FILESTREAM, et les données FILESTREAM sont sauvegardées avec les données structurées dans la base de données. Si vous ne souhaitez pas sauvegarder les données FILESTREAM avec les données relationnelles, vous pouvez utiliser une sauvegarde partielle pour exclure les groupes de fichiers FILESTREAM.

Sécurité intégrée

Dans SQL Server, les données FILESTREAM sont sécurisées tout comme les autres données : en accordant des autorisations au niveau des tables ou des colonnes. Si un utilisateur dispose de l'autorisation pour la colonne FILESTREAM dans une table, il peut ouvrir les fichiers associés.

Remarque

Le chiffrement n’est pas pris en charge sur les données FILESTREAM.

Seul le compte sous lequel le compte de service SQL Server s’exécute dispose des autorisations sur le conteneur FILESTREAM. Nous recommandons de n’accorder des autorisations sur le conteneur de données à aucun autre compte.

Remarque

Les connexions SQL ne fonctionnent pas avec les conteneurs FILESTREAM. Seule l’authentification NTFS ou ReFS fonctionne avec les conteneurs FILESTREAM.

Accès aux données BLOB avec Transact-SQL et accès en continu au système de fichiers

Après avoir stocké des données dans une colonne FILESTREAM, vous pouvez accéder aux fichiers en utilisant des transactions Transact-SQL ou en utilisant des API Win32.

Accès Transact-SQL

Grâce à Transact-SQL, vous pouvez insérer, mettre à jour et supprimer des données FILESTREAM :

  • Vous pouvez utiliser une opération d'insertion pour préremplir un champ FILESTREAM avec une valeur NULL, une valeur vide ou des données inline relativement courtes. Toutefois, une grande quantité de données est transférée plus efficacement en continu vers un fichier utilisant les interfaces Win32.
  • Lorsque vous mettez à jour un champ FILESTREAM, vous modifiez les données BLOB sous-jacentes dans le système de fichiers. Lorsqu’un champ FILESTREAM est défini sur NULL, les données BLOB associées au champ sont supprimées. Vous ne pouvez pas utiliser une mise à jour Transact-SQL segmentée, implémentée en tant que UPDATE.**Write(), pour effectuer des mises à jour partielles des données.
  • Lorsque vous supprimez une ligne ou supprimez ou tronquez une table qui contient des données FILESTREAM, vous supprimez les données d'objet blob sous-jacentes dans le système de fichiers.

Accès en continu au système de fichiers

La prise en charge du streaming Win32 fonctionne dans le cadre d’une transaction SQL Server. Dans une transaction, vous pouvez utiliser des fonctions FILESTREAM pour obtenir un chemin d'accès de système de fichiers UNC logique d'un fichier. Vous utilisez ensuite l’API OpenSqlFilestream pour obtenir un descripteur de fichier. Ce descripteur peut ensuite être utilisé par des interfaces de diffusion de fichiers en continu Win32, telles que ReadFile() et WriteFile(), afin d’accéder au fichier et de le mettre à jour par le biais du système de fichiers.

Les opérations de fichiers étant transactionnelles, vous ne pouvez pas supprimer ou renommer des fichiers FILESTREAM par le biais du système de fichiers.

Avertissement

Le conteneur FILESTREAM est un dossier géré par SQL Server. N’ajoutez pas ou supprimez des fichiers dans le dossier FILESTREAM manuellement ou via d’autres applications. Si vous le faites, cela entraîne des erreurs de sauvegarde et d’incohérence. Pour plus d’informations, consultez MSSQLSERVER_3056, MSSQLSERVER_7908 et MSSQLSERVER_7906.

Modèle de déclaration

L’accès au système de fichiers FILESTREAM modèle 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 éventuel AFTER enregistré sur la table se déclenche comme si une instruction UPDATE était terminée.

Espace de noms de stockage

Dans FILESTREAM, le Moteur de base de données gère l’espace de noms du système de fichiers physique des BLOB. Une nouvelle fonction intrinsèque, PathName, fournit le chemin UNC logique de l’objet blob qui correspond à chaque cellule FILESTREAM dans la table. L’application utilise ce chemin logique pour obtenir le handle Win32 et manipuler les données BLOB à l’aide des interfaces standard du système de fichiers Win32. La fonction retourne NULL si la valeur de la colonne FILESTREAM est NULL.

Accès transactionnel au système de fichiers

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 abandonnée ou validée. En obtenant un jeton, l'application lie les opérations de diffusion en continu de système de fichiers FILESTREAM avec une transaction commencée. La fonction retourne NULL en cas de non-démarrage explicite de transaction.

Tous les descripteurs de fichiers doivent être fermés avant que la transaction ne soit validée ou abandonnée. Si un descripteur est laissé ouvert au-delà de l’étendue de transaction, les lectures supplémentaires contre le descripteur provoquent un échec ; les écritures supplémentaires contre le descripteur réussissent, mais les données effectives ne sont pas écrites sur le disque. 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.

Durabilité transactionnelle

Avec FILESTREAM, lors de la validation d’une transaction, le Moteur de base de données garantit la durabilité transactionnelle des données BLOB FILESTREAM modifiées via l’accès en continu du système de fichiers.

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. Le niveau d’isolation Read Committed est pris en charge pour l’accès à Transact-SQL et au système de fichiers. Les opérations de lecture renouvelables, ainsi que les isolements de capture instantanée sérialisables, sont pris en charge. La lecture incohérente n’est pas prise en charge.

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 d’API de streaming échouent avec ERROR_SHARING_VIOLATION si l’opération d’ouverture ne peut pas se poursuivre en raison d’une violation d’isolation.

Pour permettre des mises à jour partielles, l'application peut émettre une commande de contrôle du système de fichiers du périphérique (FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT) afin de récupérer l'ancien contenu dans le fichier référencé par le handle ouvert. Cela déclenche une copie de l’ancien contenu côté serveur. 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, nous vous recommandons d’utiliser des E/S asynchrones.

Si le FSCTL est émis après qu'une écriture a été effectuée sur le descripteur, la dernière opération d'écriture sera conservée, et les écritures antérieures effectuées sur le descripteur seront perdues.

API du système de fichiers et niveaux d’isolation pris en charge

Lorsqu’une API du système de fichiers ne parvient pas à ouvrir un fichier en raison d’une violation d’isolation, une exception ERROR_SHARING_VIOLATION est retournée. Cette violation d'isolation se produit lorsque deux transactions essaient d'accéder au même fichier. Le résultat de l’opération d’accès dépend du mode dans lequel le fichier a été ouvert et de la version de SQL Server sur laquelle la transaction s’exécute. Le tableau suivant décrit les résultats possibles pour les deux transactions qui accèdent au même fichier.

Transaction 1 Transaction 2 Résultat sur SQL Server 2008 (10.0.x) Résultat sur SQL Server 2008 R2 (10.50.x) et les versions ultérieures
Ouvert pour la lecture. Ouvert pour la lecture. Les deux réussissent. Les deux réussissent.
Ouvert pour la lecture. Ouvert pour l'écriture. Les deux réussissent. Les opérations d’écriture sous la transaction 2 n’affectent pas les opérations de lecture effectuées dans la transaction 1. Les deux réussissent. Les opérations d’écriture sous la transaction 2 n’affectent pas les opérations de lecture effectuées dans la transaction 1.
Ouvert pour l'écriture. Ouvert pour la lecture. L'ouverture de la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION. Les deux réussissent.
Ouvert pour l'écriture. Ouvert pour l'écriture. L'ouverture de la transaction 2 échoue avec l'exception ERROR_SHARING_VIOLATION. L'ouverture de la transaction 2 échoue en générant l'exception ERROR_SHARING_VIOLATION.
Ouvert pour la lecture. Ouvert pour SELECT. Les deux réussissent. Les deux réussissent.
Ouvert pour la lecture. Disponible pour UPDATE ou DELETE. Les deux réussissent. Les opérations d’écriture sous la transaction 2 n’affectent pas les opérations de lecture effectuées dans la transaction 1. Les deux réussissent. Les opérations d’écriture sous la transaction 2 n’affectent pas les opérations de lecture effectuées dans la transaction 1.
Ouvert pour l'écriture. ouvert pour SELECT. La transaction 2 est bloquée jusqu'à ce que la transaction 1 valide ou mette fin à la transaction, ou jusqu'à ce que le délai d'attente du verrou de transaction expire. Les deux réussissent.
Ouvert pour l'écriture. Disponible pour UPDATE ou DELETE. La transaction 2 est bloquée jusqu'à ce que la transaction 1 valide ou mette fin à la transaction, ou jusqu'à ce que le délai d'attente du verrou de transaction expire. La transaction 2 est bloquée jusqu'à ce que la transaction 1 valide ou mette fin à la transaction, ou jusqu'à ce que le délai d'attente du verrou de transaction expire.
Ouvert pour SELECT. Ouvert pour la lecture. Les deux réussissent. Les deux réussissent.
Ouvert pour SELECT. Ouvert pour l'écriture. Les deux réussissent. Les opérations d’écriture sous la transaction 2 n’affectent pas la transaction 1. Les deux réussissent. Les opérations d’écriture sous la transaction 2 n’affectent pas la transaction 1.
Disponible pour UPDATE ou DELETE. Ouvert pour la lecture. L’opération d’ouverture sur la transaction 2 échoue avec l’exception ERROR_SHARING_VIOLATION. Les deux réussissent.
Disponible pour UPDATE ou DELETE. Ouvert pour l'écriture. L’opération d’ouverture sur la transaction 2 échoue avec l’exception ERROR_SHARING_VIOLATION. L'opération d'ouverture de la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION.
Ouvert pour SELECT avec lecture répétable. Ouvert pour la lecture. Les deux réussissent. Les deux réussissent.
Ouvert pour SELECT avec lecture répétable. Ouvert pour l'écriture. L'opération d'ouverture sur la transaction n° 2 échoue avec une exception ERROR_SHARING_VIOLATION. L’opération d’ouverture sur la transaction 2 échoue en générant l’exception ERROR_SHARING_VIOLATION.

Écriture immédiate depuis des clients distants

L’accès distant au système de fichiers pour les données FILESTREAM est activé via le protocole SMB (Server Message Block). Si le client est distant, aucune opération d'écriture n'est mise en cache par le côté client. Les opérations d'écriture seront toujours envoyées au serveur. Les données peuvent être mises en cache du côté serveur. Nous recommandons que les applications qui s’exécutent sur des clients distants consolident les petites opérations d’écriture en opérations de plus grande taille. L’objectif est d’effectuer moins d’opérations d’écriture.

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.

Recommandations et instructions pour améliorer les performances de FILESTREAM

La fonctionnalité FILESTREAM de SQL Server vous permet de stocker les données d’objets blob varbinary(max) en tant que fichiers dans le système de fichiers. Si des conteneurs FILESTREAM, qui fournissent le stockage sous-jacent pour les colonnes FILESTREAM et les FileTables, contiennent un grand nombre de lignes, vous pouvez vous retrouver avec une grande quantité de fichiers dans le volume du système de fichiers. Pour obtenir de meilleures performances lors du traitement des données intégrées à partir de la base de données et du système de fichiers, il est important de s’assurer que le système de fichiers est optimisé de manière optimale. Voici quelques-unes des options de réglage disponibles du point de vue du système de fichiers :

  • Vérification de l’altitude pour le pilote de filtre FILESTREAM de SQL Server (par exemple, rsfx0100.sys). Évaluez tous les pilotes de filtre chargés pour la pile de stockage associée à un volume dans lequel la fonctionnalité FILESTREAM stocke les fichiers et vérifiez que le pilote rsfx se trouve en bas de la pile. Vous pouvez utiliser le programme de contrôle FLTMC.EXE pour énumérer les pilotes de filtre pour un volume spécifique. Voici un exemple de sortie de l’utilitaire FLTMC : C:\Windows\System32>fltMC.exe filtres

    Nom du filtre Nb d’instances Altitude Cadre
    Sftredir 1 406000 0
    MpFilter 9 328000 0
    luafv 1 135000 0
    FileInfo 9 45000 0
    RsFx0103 1 41001.03 0

  • Vérifiez que la propriété « last access time » est désactivée pour les fichiers sur le serveur. Cet attribut de système de fichiers est conservé dans le registre : Nom de clé : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem Nom : NtfsDisableLastAccessUpdate Type : REG_DWORD Valeur : 1

  • Vérifiez que la convention de nommage 8.3 est désactivée sur le serveur. Cet attribut de système de fichiers est conservé dans le registre : Nom de clé : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem Nom : NtfsDisable8dot3NameCreation Type : REG_DWORD Valeur : 1

  • Vérifiez que les fonctionnalités de chiffrement et de compression du système de fichiers ne sont pas activées pour les conteneurs de répertoires FILESTREAM, celles-ci pouvant introduire un certain niveau de surcharge lors de l’accès aux fichiers.

  • À partir d’une invite de commandes avec élévation de privilèges, exécutez les instances fltmc et vérifiez qu’aucun pilote de filtre n’est attaché au volume dans lequel vous essayez d’effectuer la restauration.

  • Vérifiez que les conteneurs de répertoires FILESTREAM ne comptent pas plus de 300 000 fichiers. Vous pouvez utiliser les informations de la vue catalogue sys.database_files pour identifier les répertoires du système de fichiers qui stockent des fichiers FILESTREAM-related. Pour éviter cela, vous pouvez utiliser plusieurs conteneurs (pour plus d’informations, lisez le point suivant.)

  • Avec un seul groupe de fichiers FILESTREAM, tous les fichiers de données sont créés sous le même dossier. La création de fichiers d’un très grand nombre de fichiers peut être affectée par de grands index NTFS, qui peuvent également devenir fragmentés.

    • Le fait d’avoir plusieurs groupes de fichiers est généralement utile (l’application utilise le partitionnement ou dispose de plusieurs tables, chacune ayant son propre groupe de fichiers).

    • Avec SQL Server 2012 (11.x) et versions ultérieures, vous pouvez avoir plusieurs conteneurs ou fichiers sous un groupe de fichiers FILESTREAM, et un schéma d’allocation par tourniquet (round robin) s’applique. Le nombre de fichiers NTFS par répertoire devient donc plus petit.

  • La sauvegarde et la restauration peuvent être plus rapides avec plusieurs conteneurs FILESTREAM, si plusieurs volumes stockant des conteneurs sont utilisés.

    SQL Server 2012 (11.x) prend en charge plusieurs conteneurs par groupe de fichiers et peut faciliter les choses. Aucun schéma de partitionnement compliqué n’est nécessaire pour gérer un plus grand nombre de fichiers.

  • Lorsqu’il existe un très grand nombre de conteneurs FILESTREAM dans une instance SQL, le démarrage des bases de données avec de nombreux conteneurs FILESTREAM peut prendre beaucoup de temps pour les inscrire dans le pilote de filtre FILESTREAM. La diffusion de ces volumes dans plusieurs volumes permet d’améliorer le temps de démarrage de la base de données.

  • Le MFT NTFS peut devenir fragmenté, ce qui peut entraîner des problèmes de performances. La taille réservée de la MFT dépend effectivement de la taille du volume ; vous pourriez donc y être confronté, ou non.

    • Vous pouvez vérifier la fragmentation de la table MFT avec defrag /A /V C: (en remplaçant C: par le nom réel du volume).

    • Vous pouvez réserver plus d’espace MFT en utilisant fsutil behavior set mftzone 2.

    • Les fichiers de données FILESTREAM doivent être exclus de l’analyse antivirus.

      Remarque

      Windows Server 2016 active automatiquement Windows Defender. Vérifiez que Windows Defender est configuré pour exclure les fichiers FILESTREAM. Sinon, les performances des opérations de sauvegarde et de restauration risquent d’être affectées.

      Pour plus d’informations, consultez Configurer et valider des exclusions pour les analyses de l’Antivirus Windows Defender.

Contenu connexe

  • Last updated on