FILESTREAM (SQL Server)

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, sur le système de fichiers. Les applications peuvent utiliser les API de diffusion en continu enrichies et les 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 à un système de fichiers NTFS ou ReFS en stockant les données varbinary(max) binary large object (BLOB) en tant que 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 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 Moteur de base de données performances. Le pool de mémoires tampons SQL Server n’est pas utilisé ; par conséquent, cette mémoire est disponible pour le traitement des requêtes.

FILESTREAM n’est pas activé automatiquement lorsque vous installez ou mettez à niveau SQL Server. Vous devez activer FILESTREAM à l’aide de 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. Une fois ces tâches terminées, 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 un niveau 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 2 Go de taille de fichier 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 provoque le Moteur de base de données stocker toutes les données de cette colonne sur 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 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 non 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é.
• Les groupes de fichiers FILESTREAM peuvent être sur des volumes compressés.

Gestion intégrée

Étant donné que FILESTREAM est implémenté en tant que colonne varbinary(max) et intégré directement dans le Moteur de base de données, la plupart des outils et fonctions de gestion SQL Server fonctionnent sans modification 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 des données FILESTREAM avec des 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 comme d’autres données : en accordant des autorisations aux niveaux de table ou de colonne. Si un utilisateur dispose de l'autorisation pour la colonne FILESTREAM dans une table, il peut ouvrir les fichiers associés.

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

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

Après avoir stocké des données dans une colonne FILESTREAM, vous pouvez accéder aux fichiers à l’aide de transactions Transact-SQL ou à l’aide d’API Win32.

Accès Transact-SQL

À l’aide de 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 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 de diffusion en continu au système de fichiers

La prise en charge de la 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.

Étant donné que les opérations de fichier sont transactionnelles, vous ne pouvez pas supprimer ou renommer des fichiers FILESTREAM via le système de fichiers.

Modèle d’instruction

Le système de fichiers FILESTREAM accède à un modèle d’instruction Transact-SQL à l’aide d’un fichier ouvert et fermé. L'instruction démarre lorsqu'un descripteur de fichier est ouvert et se termine lorsque le descripteur est fermé. Par exemple, lorsqu’un handle d’écriture est fermé, tout déclencheur AFTER possible inscrit sur la table se déclenche comme si une instruction UPDATE est terminée.

espace de noms Stockage

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 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 transactionné

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 handle est laissé ouvert au-delà de l’étendue de la transaction, des lectures supplémentaires sur le handle provoquent un échec ; des écritures supplémentaires sur le handle réussissent, mais les données réelles 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 handles 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 BLOB FILESTREAM modifiées à partir de l’accès en continu du système de fichiers.

Sémantique d’isolation

La sémantique d’isolation est régie par Moteur de base de données niveaux d’isolation des transactions. Le niveau d’isolation en lecture validée est pris en charge pour l’accès au système de fichiers et Transact-SQL. Les opérations de lecture reproductibles, sérialisables et instantané niveaux d’isolation sont pris en charge. La lecture incorrecte 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 ouvertes échouent immédiatement si elles ne peuvent pas accéder aux données en raison de l’isolation des transactions. Les appels d’API de streaming échouent avec ERROR_SHARING_VIOLATION si l’opération ouverte ne peut pas continuer en raison d’une 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 ancienne copie de contenu côté serveur. Pour améliorer les performances de l’application et éviter de rencontrer des délais d’attente potentiels lorsque vous travaillez avec des fichiers très volumineux, 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 peut 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 présente les résultats possibles pour deux transactions qui accèdent au même fichier.

Écriture directe à 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 vous recommandons que les applications qui s’exécutent sur des clients distants consolident de petites opérations d’écriture en opérations de taille supérieure. 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 handle FILESTREAM n’est pas prise en charge. Si le mappage de 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 ou l’intégrité de la base de données.

Recommandations et instructions pour améliorer les performances de FILESTREAM

La fonctionnalité FILESTREAM SQL Server vous permet de stocker les données d’objet volumineux binaires 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 :

• Altitude case activée 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 filtres

  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 : Nom de clé : 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 : Nom de clé : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
  Nom : NtfsDisable8dot3NameCreation
  Type : REG_DWORD
  Valeur : 1

• Vérifiez que les conteneurs de répertoires FILESTREAM ne disposent pas du chiffrement du système de fichiers ou de la compression du système de fichiers activés, car ils peuvent introduire un niveau de surcharge lors de l’accès à ces 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 n’ont 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 les versions ultérieures, vous pouvez avoir plusieurs conteneurs ou fichiers sous un groupe de fichiers FILESTREAM, et un schéma d’allocation de tourniquet s’applique. Par conséquent, le nombre de fichiers NTFS par répertoire est 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 facilite les opérations. 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 répartition 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.

Tâches associées

• Activer et configurer FILESTREAM
• Créer une base de données compatible FILESTREAM
• Créer une table pour le stockage de données FILESTREAM
• Accéder aux données FILESTREAM avec Transact-SQL
• Créer des applications clientes pour les données FILESTREAM
• Accéder à des données FILESTREAM avec OpenSqlFilestream
• Effectuer des mises à jour partielles de données FILESTREAM
• Éviter les conflits avec les opérations de base de données dans les applications FILESTREAM
• Déplacer une base de données COMPATIBLE FILESTREAM
• Configurer FILESTREAM sur un cluster de basculement
• Configurer un pare-feu pour l’accès FILESTREAM

Contenu associé

• Compatibilité FILESTREAM avec d’autres fonctionnalités SQL Server
• Vues de gestion dynamique FILESTREAM et FileTable (Transact-SQL)
• Vues de catalogue FILESTREAM et FileTable (Transact-SQL)
• Procédures stockées système FILESTREAM et FileTable (Transact-SQL)