Partage via

FILESTREAM (SQL Server)

  • Article

S’applique à : SQL Server - Windows uniquement

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 des API de diffusion et des performances du système de fichiers, et en même temps maintenir 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 SQL Server avec un système de fichiers NTFS ou ReFS en stockant les données d’objet blob varbinary (max) en tant que fichiers dans le système de fichiers. Les instructions Transact-SQL peuvent insérer, mettre à jour, interroger, rechercher et sauvegarder des données Filestream. Les interfaces de système de fichiers Win32 fournissent l'accès de diffusion 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 être 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 diffusée en continu plus efficacement dans un fichier qui utilise des interfaces Win32.
  • Lorsque vous mettez à jour un champ FILESTREAM, vous modifiez les données d'objet blob sous-jacentes dans le système de fichiers. Lorsqu'un champ FILESTREAM a la valeur NULL, les données d'objet blob associées au champ sont supprimées. Vous ne pouvez pas utiliser une mise à jour segmentée Transact-SQL , implémentée comme 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 de diffusion en continu au système de fichiers

La prise en charge de diffusion en continu Win32 fonctionne dans le contexte 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 ou ne supprimez pas 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 d’instruction

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 AFTER possible enregistré sur la table est activé comme si une instruction UPDATE était effectuée.

Espace de noms de stockage

Dans FILESTREAM, le moteur de base de données contrôle l’espace de noms du système de fichiers physique d’objet 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 descripteur Win32 et opérer sur les données d’objet 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.

Accès au système de fichiers traité

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 si aucune transaction n'est explicitement commencée.

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 des transactions, le moteur de base de données garantit la durabilité des transactions pour les données d’objet blob FILESTREAM modifiées à partir de l’accès en continu au 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 de lecture validée 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 erronée 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 diffusion en continu échouent avec ERROR_SHARING_VIOLATION si l’opération d’ouverture ne peut se poursuivre à cause de la violation d’isolation.

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

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. Réussite des deux transactions. Réussite des deux transactions.
Ouvert pour la lecture. Ouvert pour l'écriture. Réussite des deux transactions. Les opérations d’écriture sous la transaction 2 n’affectent pas les opérations de lecture effectuées dans la transaction 1. Réussite des deux transactions. 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'opération d'ouverture sur la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION. Réussite des deux transactions.
Ouvert pour l'écriture. Ouvert pour l'écriture. L'opération d'ouverture sur la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION. L'opération d'ouverture sur la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION.
Ouvert pour la lecture. Ouvert pour SELECT. Réussite des deux transactions. Réussite des deux transactions.
Ouvert pour la lecture. Ouvert pour UPDATE ou DELETE. Réussite des deux transactions. Les opérations d’écriture sous la transaction 2 n’affectent pas les opérations de lecture effectuées dans la transaction 1. Réussite des deux transactions. 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 se bloque jusqu'à ce que la transaction 1 valide ou termine la transaction, ou l'opération d'obtention d'un verrou pour la transaction se solde par une erreur de délai d'attente. Réussite des deux transactions.
Ouvert pour l'écriture. Ouvert pour UPDATE ou DELETE. La transaction 2 se bloque jusqu'à ce que la transaction 1 valide ou termine la transaction, ou l'opération d'obtention d'un verrou pour la transaction se solde par une erreur de délai d'attente. La transaction 2 se bloque jusqu'à ce que la transaction 1 valide ou termine la transaction, ou l'opération d'obtention d'un verrou pour la transaction se solde par une erreur de délai d'attente.
Ouvert pour SELECT. Ouvert pour la lecture. Réussite des deux transactions. Réussite des deux transactions.
Ouvert pour SELECT. Ouvert pour l'écriture. Réussite des deux transactions. Les opérations d’écriture sous la transaction 2 n’affectent pas la transaction 1. Réussite des deux transactions. Les opérations d’écriture sous la transaction 2 n’affectent pas la transaction 1.
Ouvert pour UPDATE ou DELETE. Ouvert pour la lecture. L'opération d'ouverture sur la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION. Réussite des deux transactions.
Ouvert pour UPDATE ou DELETE. Ouvert pour l'écriture. L'opération d'ouverture sur la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION. L'opération d'ouverture sur la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION.
Ouvert pour SELECT avec lecture renouvelable. Ouvert pour la lecture. Réussite des deux transactions. Réussite des deux transactions.
Ouvert pour SELECT avec lecture renouvelable. Ouvert pour l'écriture. L'opération d'ouverture sur la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION. L'opération d'ouverture sur la transaction 2 échoue avec une exception ERROR_SHARING_VIOLATION.

Double écriture à partir de clients distants

L'accès de système de fichiers distant aux données FILESTREAM est activé sur le protocole SMB (Block Server Message). 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’écritures.

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 optimiser les performances lors du traitement des données intégrées provenant de la base de données et du système de fichiers, il est important de vérifier que le système de fichiers est réglé 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 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 filters

    Nom du filtre Nb d’instances Altitude Frame
    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 : Key Name : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Nom : NtfsDisableLastAccessUpdate
    Type : REG_DWORD
    Valeur : 1

  • Vérifiez que le format de nommage 8.3 est désactivé. Cet attribut de système de fichiers est conservé dans le Registre : Key Name : 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 d’un très grand nombre de fichiers peut être impactée par des index NTFS volumineux, qui peuvent aussi 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.

  • La table MFT NTFS peut devenir fragmentée, ce qui peut entraîner des problèmes de performances. La taille réservée de la table MFT dépendant de la taille du volume, vous pouvez ou non être confronté à ce problème.

    • 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