Introduction à FILESTREAM avec SQL Server 2008

À l'origine les bases de données étaient conçues pour stocker des données discrètes. Cependant la variété des données utilisées dans les entreprises a fait que le besoin de consolider ces données dans un seul type de stockage est devenu important. On parle ici de mutualisation de stockage. SQL Server 2008 répond à cette problématique en proposant une nouvelle fonctionnalité : FILESTREAM.

Tout d'abord il est important de préciser que FILESTREAM n'est pas un type de données, mais une propriété associée à une colonne.

Avant SQL Server 2008, les données non structurées comme les fichiers ou les images étaient stockées avec un type de données tel que VARBINARY(MAX), VARCHAR(MAX) ou bien NVARCHAR(MAX). Par conséquent la taille maximum de stockage était de 2 Go. Les fichiers au-delà de cette taille étaient alors stockés directement sur le système de fichiers et un lien était inséré dans la base de données, ce qui pouvait poser des problèmes d'intégrité des données, car aucun mécanisme de vérification n'existait.

À partir de SQL Server 2008 le stockage des données FILESTREAM se fait par l'intermédiaire d'un type de données VARBINARY(MAX). Les fichiers ne sont plus stockés dans la base, mais directement sur le système de fichiers. Un pointeur de 16 octets vers ce même fichier est stocké dans la base de données. Un premier avantage est que la taille des fichiers n'est plus limitée par le type de données VARBINARY(MAX), mais directement par le système de fichiers lui-même. Un deuxième avantage est que le cache système de Windows est utilisé à la place du cache des données du moteur SQL limitant ainsi l'impact direct sur les performances du serveur SQL. Cela implique également que les performances liées à FILESTREAM sont directement dépendants du sous-système disque sur lequel il réside.

Parlons rapidement du cache système Windows. Celui-ci contribue à l'amélioration des performances disque en gardant en mémoire les fichiers les plus récemment consultés. Il est également lié au gestionnaire de mémoire et possède deux modes de fonctionnement :
- Le mode par défaut qui consiste à limiter la croissance du cache à 8MB. Ce mode convient parfaitement pour les stations de travail où seuls quelques applications et fichiers non volumineux sont en jeux (par défaut activé sur windows XP).
- L'autre mode qui permet au cache de s'agrandir jusqu'à occuper toute la mémoire du système. Il est possible de limiter l'accroissement à une taille maximum. Ce mode convenant plus aux serveurs dans le cas d'utilisation de fichiers volumineux comme FILESTREAM (par défaut activé sur Windows Server 2003).

Par défaut FILESTREAM est désactivé lors de l'installation de SQL Server. Il est possible d'activer cette fonctionnalité de plusieurs façons :

• à l'installation classique de SQL Server ;
• pendant une installation silencieuse par script ;
• après l'installation de SQL Server. Dans ce cas l'activation du stockage FILESTREAM demandera un peu plus de paramétrages.

La 1re étape consiste à activer FILESTREAM par le gestionnaire de configuration de SQL Server en allant dans les propriétés du service SQL Server ou par la commande TSQL sp_filestream_configure. Il existe plusieurs niveaux d'accès :

• Activer FILESTREAM pour l'accès TSQL : ce niveau d'accès permet la gestion des données FILESTREAM directement par le langage TSQL ;
• Activer FILESTREAM pour l'accès en continu aux E/S de fichier : ce niveau d'accès permet la gestion des données FILESTREAM et par TSQL et par l'API Win32, mais en local sur le serveur. Un nom partage Windows sera également configuré ;
• Autoriser les clients distants à avoir un accès en continu aux données FILESTREAM : ce dernier niveau d'accès permet l'accès aux fichiers par TSQL et par l'API Win32 pour les clients distants.

Activation de FILESTREAM par le gestionnaire de configuration :

… ou par la commande TSQL sp_filestream_configure :

EXEC sp_filestream_configure 
    @enable_level = 3, 
    @share_name = "MSSQLSERVER";
RECONFIGURE

Dans le cas d'une activation FILESTREAM postinstallation, il faut également configurer les niveaux d'accès au niveau de l'instance SQL Server soit par la console SSMS dans les propriétés du serveur soit directement par la commande TSQL sp_configure.

EXEC sp_configure 'filestream_access_level', 2;
GO
RECONFIGURE
GO

Les valeurs que peut prendre l'option « filestream access level » sont :

• 0 : (FILESTREAM désactivée) ;
• 1 : (FILESTREAM activé pour TSQL) ;
• 2 : (FILESTREAM activé pour TSQL et Win32).

Pour utiliser FILESTREAM, il faut créer et allouer un groupe de fichiers spécial. Celui-ci peut être créé à la création de la base de données ou ajouté si la base de données existe déjà. La clause CONTAINS FILESTREAM doit être utilisée lors de la création du groupe de fichiers pour FILESTREAM.

CREATE DATABASE [filestream_database] 
ON  PRIMARY 
( NAME = N'filestream_data', FILENAME = N'E:\MSSQL\DATA\filestream_data.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
 FILEGROUP [FILESTREAM_GRP] CONTAINS FILESTREAM  DEFAULT 
( NAME = N'filestream_ds', FILENAME = N'E:\MSSQL\FILESTREAM' )
 LOG ON 
( NAME = N'filestream_log', FILENAME = N'E:\MSSQL\DATA\filestream.ldf' , SIZE = 51200KB , MAXSIZE = 2048GB , FILEGROWTH = 20480KB )
GO

Pour un groupe de fichiers FILESTREAM, FILENAME fait référence à un chemin d'accès. Tous les dossiers constituant le chemin doivent exister sauf le dernier. Dans le cas de l'exemple ci-dessus, le dossier FILESTREAM ne doit pas exister.

La requête suivante permet de vérifier la création du groupe de fichiers de stockage FILESTREAM_GRP pour la base de données filestream_database :

USE filestream_database;
GO
 
SELECT 
    m.file_id,
    m.type_desc,
    g.name AS [filegroup_name],
    m.name AS [file_name],
    m.physical_name
FROM sys.master_files m
INNER JOIN sys.filegroups g
ON m.data_space_id = g.data_space_id
WHERE database_id = DB_ID();

et qui donne le résultat suivant :

De plus, une structure contenant un ensemble de dossiers et de fichiers est créée.

Le fichier filestream.hdr est un fichier système important, car il contient les informations d'en-têtes du stockage FILESTREAM. Il ne doit pas être supprimé. Le dossier $FSLOG est, quant à lui, l'équivalent du journal des transactions d'une base de données pour FILESTREAM. Cette nouvelle fonctionnalité supporte et gère la notion de transaction appliquée aux mêmes données relationnelles.

Une fois que le stockage FILESTREAM est défini à un groupe de fichiers, il faut ensuite créer une table qui stockera les données. Pour illustrer l'article, une table [documents] sera créée dans laquelle divers documents seront stockés (Documents office, images et NTEXT).

CREATE TABLE [dbo].[Documents](
    [DocID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [DocNumber] [varchar](20) NULL,
    [DocName] [varchar](50) NULL,
    [DocDescription] [varchar](50) NOT NULL,
    [DocExtension] [varchar](50) NOT NULL,
    [Doc] [varbinary](max) FILESTREAM  NULL
    UNIQUE NONCLUSTERED ([DocID] ASC)
) 
ON [PRIMARY] 
FILESTREAM_ON [FILESTREAM_GRP];

Plusieurs remarques sont à faire sur la création de cette table :

• colonne [DocID] : les données de cette colonne sont de type uniqueidentifier. Ceci est nécessaire pour l'utilisation de FILESTREAM avec l'API Win32 ;
• colonne [DocExtension] : cette colonne contiendra les extensions d'un document inséré. Elle est utile pour la recherche FullText (voir plus loin dans l'article) ;
• colonne [Doc] : cette colonne stockera les documents. Elle est de type VARBINARY(MAX) et possède la propriété FILESTREAM ;
• utilisation de la clause FILESTREAM_ON qui définit le stockage des documents sur le groupe de fichiers dédié que l'on a créé précédemment.

Il faut maintenant insérer des documents dans la table [Documents]. Il s'agit de trois documents de la suite Microsoft Office .

Document Office Word 2007 avec pour contenu la phrase « Document office Word »

-- Variable de type image
DECLARE @img AS VARBINARY(MAX)
 
-- Chargement du document (chemin c:\)
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK 'C:\Word.docx', SINGLE_BLOB) AS x
 Insertion des documents dans la table
 Document word 2007          
INSERT INTO dbo.Documents (DocID, DocNumber, DocName, DocDescription, DocExtension, Doc)
VALUES (NEWID(), 'doc1','word','Document office word','.docx', @img);

Document Office Excel 2007 avec pour contenu la phrase « Document office Excel 2007 »

-- Chargement du document (chemin c:\)
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
 BULK 'C:\Excel.xlsx', SINGLE_BLOB) AS x
 Insertion des documents dans la table
 Document excel 2007          
INSERT INTO dbo.Documents (DocID, DocNumber, DocName, DocDescription, DocExtension, Doc)
VALUES (NEWID(), 'doc2','excel','Document office excel','.xlsx', @img);

Document Office Excel 2003 avec pour contenu la phrase « Document office Excel 2003 »

-- Chargement du document (chemin c:\)
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
 BULK 'C:\Excel.xls', SINGLE_BLOB) AS x
-- Insertion des documents dans la table
-- Document excel 2003         
INSERT INTO dbo.Documents (DocID, DocNumber, DocName, DocDescription, DocExtension, Doc)
VALUES (NEWID(), 'doc3','excel 2003','Document office excel 2003','.xls', @img);

Image jpeg :

-- Chargement du document (chemin c:\)
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK 'C:\image.jpg', SINGLE_BLOB) AS x
--Insertion des documents dans la table Image jpeg          
INSERT INTO dbo.Documents (DocID, DocNumber, DocName, DocDescription, DocExtension, Doc)
VALUES (NEWID(), 'img1','image1','Image Jpeg','.jpg', @img);

Il est également possible de créer un document directement en utilisant la commande INSERT :

INSERT INTO dbo.Documents (DocID, DocNumber, DocName, DocDescription, DocExtension, Doc)
VALUES (NEWID(), 'text1','text','ntext type','', CAST('test type ntext' AS VARBINARY));

Pour sélectionner les données de la table [Documents] à l'aide de TSQL il suffit d'exécuter une simple requête SQL :

SELECT * FROM dbo.Documents;

Voici le résultat :

Cependant la manipulation des données FILESTREAM à l'aide du langage TSQL est limitée. Il permet de manipuler des données FILESTREAM en utilisant les ordres DML classiques (INSERT, UPDATE, DELETE).

SQL Server fournit également deux autres méthodes utilisables directement via l'API Win32 pour la manipulation des fichiers par une application cliente :

• PathName() : avant d'accéder au fichier, une application cliente doit connaître le chemin d'accès de ce fichier. Cette méthode retourne le chemin d'accès en tant que jeton à un objet blob directement utilisable par l'application cliente.

SELECT 
    DocName,
    Doc.PathName() AS DocPath
FROM dbo.Documents;

donnera :

• GET_FILESTREAM_TRANSACTION_CONTEXT() : L'accès aux fichiers se fait par la classe SQLFileStream de l'API Win32. Il est possible de manipuler un fichier dans le contexte d'une transaction. Cela peut être utile pour lancer un ROLLBACK en cas de problème sur un fichier. Pour pouvoir accéder au système de fichiers dans le contexte d'une transaction, il faut utiliser la méthode GET_FILESTREAM_TRANSACTION_CONTEXT(). Celle-ci retourne un jeton qui représente le contexte de transaction actuel d'une session. Le jeton est utilisé par l'application Win32 pour lier des opérations de diffusion en continu de système de fichiers FILESTREAM à la transaction.

Extrait d'un code client permettant la récupération d'un fichier avec FILESTREAM

Imports System
Imports System.IO
Imports System.Text
 
'Chaine de connexion à la base
Dim ConStr As String
ConStr = "Data Source=SRV-SQL2008;Initial Catalog=filestream_database;Integrated Security=True"
 
Dim con As New SqlConnection(ConStr)
con.Open()
 
' Sélection du document Word 2007
Dim sqlCommand As New SqlCommand()
sqlCommand.Connection = con
sqlCommand.CommandText = "SELECT Doc FROM dbo.Documents WHERE DocID = 'doc1'"
 
Dim buffer As Byte() = sqlCommand.ExecuteScalar()
 
' Utilisation de la mémoire comme magasin de stockage du fichier
Dim ms As MemoryStream = New MemoryStream(buffer)
 
' ... Traitement du document  
 
'Fermeture connexion
con.Close()

La recherche FULL TEXT avec SQL Server 2008 prend en charge l'indexation et la recherche des fichiers du stockage FILESTREAM. Cette fonctionnalité est intéressante, car elle permet la recherche de mots clés à l'intérieur d'un document. Le but de cet article n'étant pas d'expliquer comment fonctionne la recherche FULL TEXT, on s'attachera simplement à expliquer comment effectuer une recherche. Les deux exemples suivants illustrent l'utilisation des méthodes FREETEXT et CONTAINS.

SELECT *
FROM dbo.Documents 
WHERE FREETEXT (Doc, 'Excel')

donne le résultat suivant :

SELECT * 
FROM dbo.Documents 
WHERE CONTAINS (Doc, '"FILESTREAM" AND "WORD"')

donne le résultat suivant :

Tous les types de sauvegarde et tous les modes de récupération utilisés avec SQL Server fonctionnent également pour FILESTREAM. Les données FILESTREAM sont sauvegardées au même titre que les données relationnelles dans les fichiers relatifs aux sauvegardes. Il en va de même pour les opérations de restauration. Les données sont restaurées à la fois dans la base de données et sur le système de fichiers hôte.

• FILESTREAM ne peut pas être utilisé en mirroring, ce qui peut être regrettable pour des environnements de haute disponibilité.
• Des topologies de réplication ou de log shipping avec FILESTREAM nécessitent l'utilisation d'une version SQL Server 2008 pour tous les serveurs et/ou ordinateurs concernés.
• Dans un environnement cluster, le groupe de fichiers de stockage pour FILESTREAM doit se trouver sur un disque partagé.
• FILESTREAM ne fonctionne pas avec les snapshots de bases de données. L'accès aux données causera une erreur dans ce cas.
• L'instance SQL Server doit être configurée avec la sécurité intégrée Windows pour FILESTREAM si le niveau d'accès configuré autorise la manipulation des données via l'API Win32.

Microsoft, dans sa documentation, préconise certaines bonnes pratiques à adopter concernant la configuration et la maintenance des volumes NTFS pour FILESTREAM. Voici les plus importantes :

• désactiver les noms courts (ou noms 8.3) ;
• défragmenter régulièrement les volumes contenant les données FILESTREAM ;
• utiliser de préférence des clusters NTFS de 64Ko ;
• désactiver l'indexation des fichiers Windows ;
• désactiver l'analyse antivirale ou adopter des règles d'exclusion sur les fichiers FILESTREAM ;
• choisir un sous système disque performant (Technologie RAID5 ou RAID 1+0 par exemple).

SQL Server 2008 avec FILESTREAM propose de nouvelles fonctionnalités quant à la gestion des données non relationnelles. Cette nouvelle technologie apporte des avantages non négligeables comme la capacité de stockage de données hétérogènes, la gestion des fichiers directement par le cache système Windows pour plus de performances ou encore la garantie d'intégrité des données par une gestion transactionnelle de ces fichiers. FILESTREAM ouvre donc la voie à de futurs développements d'applications intéressants !!