Filestream in SQL Server 2008

Fino a SQL Server 2005, quando era necessario salvare file binari direttamente all'interno di un database, l'unica soluzione possibile consisteva nell'utilizzare campi di tipo VARBINARY(MAX). Questo approccio, però, ha una serie di svantaggi, primo fra tutti il degrado delle prestazioni: ad esempio, nel caso si streaming, l'accesso ai dati memorizzati all'interno di SQL Server è di gran lunga più lento rispetto all'accesso diretto al file system.

Inoltre, le informazioni devono prima essere scritte nella memoria del DBMS, e solo a questo punto sono disponibili all'applicazione ne ha fatto richiesta. Al contrario, memorizzando i dati su file system, questi possono essere letti e inviati direttamente al client, senza la "mediazione" di un buffer gestito dal database engine.

In questo contesto si inserisce FILESTREAM, funzionalità introdotta con SQL Server 2008. Essa permette alle applicazioni basate su SQL Server di archiviare file, ovvero dati non strutturati come documenti e immagini, nel file system.

non ci si deve più preoccupare del limite dei 2 GB per la memorizzazione dei file binari (limite intrinseco del tipo VARBINARY(MAX)).

Caratteristiche e supporto

Innanzi tutto, precisiamo che il FILESTREAM non è propriamente un nuovo tipo di dati, bensì un attributo del tipo VARBINARY(MAX). Esso modifica la modalità di salvataggio delle informazioni inserite in un campo di questo tipo, andandole a scrivere in un file su disco piuttosto che nel database (come avviene per i dati VARBINARY "normali").

Dal momento che si tratta di una funzionalità integrata direttamente nel motore del database, la maggior parte degli strumenti e delle funzioni di gestione di SQL Server supporta anche i dati FILESTREAM. Ad esempio, è possibile utilizzare tutti i modelli di backup e di recupero con i dati FILESTREAM, di cui viene eseguito il backup come qualunque altro tipo di dati presente nel database.

I dati FILESTREAM, inoltre, sono protetti come tutti gli altri tipi di informazioni, grazie alle autorizzazioni a livello di tabella o colonna. Se un utente dispone delle autorizzazioni per la colonna FILESTREAM in una tabella, può aprire i file associati. È bene ricordare, però, che la crittografia non è supportata sui dati FILESTREAM. Inoltre, solo l'account con cui viene eseguito il servizio SQL Server ha le autorizzazioni NTFS sul contenitore FILESTREAM, ovvero la directory che contiene i dati binari memorizzati nelle colonne FILESTREAM.

Il FILESTREAM è supportato in tutte le versioni di SQL Server, compresa l'edizione Express: in questo caso, il limite massimo di 4 GB per un database non tiene conto di eventuali dati FILESTREAM. Tale funzionalità, inoltre, è compatibile con la replica di database e il Log shipping , mentre non è possibile creare snapshot o configurare il mirroring su database che contengono FILESTREAM.

Abilitare il supporto al FILESTREAM su SQL Server 2008

Il supporto al FILESTREAM può essere abilitato in fase di installazione oppure in un qualunque momento successivo, utilizzando l'interfaccia del Management Studio o la stored procedure sp_configure. Durante il setup, nel passaggio Database Engine Configuration, portarsi nella scheda FILESTREAM e porre il segno di spunta alle opzioni Enable FILESTREAM for Transact-SQL Access e Enable FILESTREAM for file I/O streaming access:

Figura 1. Attivare FILESTREAM in fase di setup

Come i nomi fanno intuire, la prima consente di utilizzare i comandi T-SQL per accedere ai dati FILESTREAM, mentre la seconda abilita l'accesso anche attraverso le API di Windows (e quindi da un qualsiasi linguaggio di programmazione che le sfrutti, come .NET). L'ultima opzione, Allow remote client to have streaming access to FILESTREAM data, attiva il FILESTREAM anche per i computer remoti che si collegano al server.

Se, invece, si vogliono modificare le impostazioni relative a FILESTREAM dopo l'installazione, nel Management Studio fare clic con il tasto destro del mouse sul nome del server da configurare, quindi selezionare il comando Properties e visualizzare la scheda Advanced:

Figura 2. L'impostazione del FILESTREAM dal Management Studio

La terza strada per configurare il FILESTREAM è tramite la stored procedure sp_configure:

EXEC sp_configure 'filestream access level', 2
GO
RECONFIGURE
GO

I valori possibili sono 3:

Archiviare i dati FILESTREAM

La gestione del FILESTREAM è implementata all'interno di una colonna VARBINARY(MAX) in cui, però, i dati sono archiviati come BLOB (Binary Large Objects) nel file system. Le dimensioni dei dati così memorizzati sono limitate solo dalla dimensione del volume del file system: in altre parole, il limite dei 2 GB per le colonne VARBINARY(MAX) non si applica ai BLOB salvati nel file system.

I dati FILESTREAM sono archiviati in opportuni filegroup di tipo FILESTREAM, che contengono il riferimento ad una directory su disco in cui sono effettivamente memorizzate le informazioni. Per indicare che una colonna contenere dati FILESTREAM, è sufficiente specificare l'attributo FILESTREAM nella dichiarazione di una colonna VARBINARY(MAX) (Come vedremo meglio tra poco): così facendo, il motore di SQL Server archivierà tutti i dati di quella colonna direttamente nel file system e non nel file del database.

Quando si utilizza l'archiviazione FILESTREAM, si devono considerare i seguenti aspetti:

• Ogni tabella che contiene una colonna FILESTREAM deve avere una chiave univoca con valore diverso da NULL
• I contenitori di dati FILESTREAM non possono essere nidificati
• Nel caso di un'architettura basata su failover clustering, i filegroup FILESTREAM devono risiedere su un disco condiviso
• I filegroup FILESTREAM possono essere memorizzati su volumi compressi, ma la crittografia non è supportata

Creare un database con supporto FILESTREAM

Affinché un database sia in grado di ospitare dati FILESTREAM, deve contenere almeno un filegroup per cui è specificata la clausola CONTAINS FILESTREAM. Questa attività può essere realizzata utilizzando la finestra di dialogo New Database, selezionando la pagina Filegroups e utilizzando il pulsante Add della sezione Filestream.

Figura 3. Creazione di un nuovo Filegroup contenente Filestream

In alternativa, si può scrivere direttamente uno script T-SQL come questo:

CREATE DATABASE TestFilestream 
ON
PRIMARY (NAME = Test, FILENAME = 'C:DataTest.mdf'),
        FILEGROUP FileStreamGroup 
        CONTAINS FILESTREAM (
           NAME = Filestream1,
           FILENAME = 'C:DataFilestream')

LOG ON ( NAME = LogTest, FILENAME = 'C:DataTest.ldf')
GO

Questo script presuppone l'esistenza della cartella C:DATA e al suo interno inserisce i file del database di nome TestFilestream, composto da due filegroup e un file di LOG. In particolare, FileStreamGroup è il filegroup al cui interno sarà inserito il filestream vero e proprio: per memorizzare questi dati, nella cartella C:DATAFILESTREAM vengono creati l'archivio filestream.hdr , contenente i metadati del contenitore, e la directory $FSLOG, l'equivalente su file system del log delle transazioni del database.

Creare una tabella per archiviare dati FILESTREAM

Dopo aver definito il Filestream, dobbiamo creare una tabella in grado di contenere questo tipo di dati. Come detto in precedenza, una colonna Filestream è definita come una colonna di tipo VARBINARY(MAX) in cui si specifica l'attributo FILESTREAM. Ad esempio:

CREATE TABLE Documents (
    ID    INT IDENTITY PRIMARY KEY,
    Title NVARCHAR(255) NOT NULL,
    DocID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID(),
    Data  VARBINARY(MAX) FILESTREAM NULL)

FILESTREAM_ON FileStreamGroup
GO

Una tabella può avere più colonne FILESTREAM, ma tutte devono fare riferimento allo stesso filegroup. L'esecuzione di questo script aggiunge una nuova cartella al contenitore FILESTREAM, corrispondente alla tabella Documents, al cui interno è a sua volta inserita una sottodirectory relativa alla colonna Data, che conterrà tutti i dati binari in essa memorizzati.

Per creare una tabella con colonne FILESTREAM, è necessario che essa contenga anche una colonna di tipo UNIQUEINDENTIFIER con l'attributo ROWGUIDCOL. Tale colonna non deve ammettere i valori NULL e deve essere UNIQUE o PRIMARY KEY. Tipicamente, nella dichiarazione si aggiunge anche la clausola DEFAULT NEWID (come nell'esempio), in modo che non sia necessario fornire esplicitamente il GUID per ogni nuovo inserimento.

Gestione dei dati FILESTREAM mediante Transact-SQL

L'esempio sotto riportato mostra come utilizzare un'istruzione INSERT standard per creare su disco un file contenente dati:

INSERT INTO Documents(Title, Data)
VALUES ('Inserimento di test',
        CAST ('Primo documento inserito nel FILESTREAM' AS VARBINARY(MAX)));
GO

Nella colonna Data memorizziamo una stringa di testo convertita in VARBINARY(MAX). Poiché essa è stata creata con l'attributo FILESTREAM, SQL Server crea un file nel contenitore FILESTREAM (quindi in opportune cartelle nella posizione C:DATAFILESTREAM) e al suo interno inserisce il valore specificato. Provando ad eseguire una SELECT sulla tabella Documents, si ottiene un risultato analogo al seguente:

In particolare, notiamo la colonna Data, il cui contenuto è la rappresentazione binaria del file che in realtà il database engine ha memorizzato su disco.

L'aggiornamento parziale dei dati FILESTREAM non è supportato. Questo significa che ogni modifica delle informazioni contenute in una colonna FILESTREAM in realtà crea un nuovo file su disco. I vecchi file sono comunque conservati per garantire eventuali operazioni di recupero dei dati.

Gestione dei dati FILESTREAM da un'applicazione .NET

Sebbene SQL Server 2008 permetta di memorizzare dati FILESTREAM ricorrendo direttamente ad istruzioni T-SQL di tipo INSERT, l'utilizzo più comune consiste nel salvataggio attraverso un'applicazione, che ad esempio legge un file lo invia al database engine. Vediamo come realizzare questo meccanismo con Visual Studio 2008 (o 2010). È importante che il Service Pack 1 sia stato installato, poiché il supporto a FILESTREAM è stato aggiunto solo a partire da tale release; se non presente nel sistema, può essere scaricato gratuitamente dal sito Microsoft. Avviamo l'ambiente di sviluppo e creiamo una nuova applicazione Console con C#, a cui assegniamo il nome Filestream. All'interno del file Program.cs aggiungiamo il seguente metodo:

private static void SaveFileStream(string connectionString, string fileToSave, string title)
{
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
    connection.Open();
    SqlTransaction tran = connection.BeginTransaction(IsolationLevel.ReadCommitted);
    // Esegue una INSERT nel database in modo che SQL Server crei
    // il file su disco
    SqlCommand command =  new SqlCommand("INSERT INTO Documents(Title, Data)
                                          VALUES (@title, CAST('' AS VARBINARY(MAX)));
                                          SELECT SCOPE_IDENTITY();", connection);
    command.Parameters.AddWithValue("@title", title);
    command.Transaction = tran;
    int id = Convert.ToInt32(command.ExecuteScalar());
    // Recupera il percorso del file e il contesto della transazione
    // corrente
    command.CommandText = "SELECT Data.PathName(),
                           GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Documents WHERE ID=@id";
    command.Parameters.Clear();
    command.Parameters.AddWithValue("@id", id);
    using (SqlDataReader reader = command.ExecuteReader())
    {
      if (reader.Read())
      {
        // Recupera il puntatore al file
        string path = reader.GetString(0);
        byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
        // Crea l'oggetto SqlFileStream
        SqlFileStream fileStream = new SqlFileStream(path,
                                                     transactionContext,
                                                     FileAccess.Write,
                                                     FileOptions.SequentialScan,
                                                     0);
        // Legge il file specificato e lo salva nel database
        byte[] buffer = File.ReadAllBytes(fileToSave);
        fileStream.Write(buffer, 0, buffer.Length);
        fileStream.Close();
      }
    }
    tran.Commit();
  }
}

In sintesi, per utilizzare il FILESTREAM si ricorre ai classici meccanismi che .NET fornisce per l'accesso ai dati: anche in questo caso abbiamo a che fare con oggetti di tipo SqlConnection, SqlCommand e SqlTransaction. In particolare, le operazioni su FILESTREAM, sia in lettura sia in scrittura, devono sempre eseguite all'interno di una transazione.

Innanzi tutto, eseguiamo una INSERT "tradizionale", indicando un contenuto vuoto per il FILESTREAM, in modo che SQL Server crei un file su disco in cui andremo ad inserire i dati veri e propri. Subito dopo, recuperiamo il percorso in cui il DBMS ha memorizzato tale file, utilizzando la funzione PathName() sulla colonna Data. Analogamente, con la funzione GET_FILESTREAM_TRANSACTION_CONTEXT() otteniamo il contesto della transazione corrente.

Arriviamo così alla creazione dell'oggetto SqlFileStream, che utilizzeremo per salvare il file nel database. Esso richiede alcuni parametri, i più importanti dei quali sono i primi due, ovvero il percorso logico del file (recuperato con Data.PathName) e il contesto della transazione. Leggiamo poi il contenuto del file passato al metodo SaveFileStream e, infine, lo scriviamo nel filestream con l'istruzione SqlFileStream.Write, che si occuperà di inviare il file al database ed effettuare l'inserimento delle informazioni nella tabella.

Speculare a questa è la funzione che va a leggere i dati contenuti in una colonna FILESTREAM e li rende disponibili all'applicazione come array di byte:

private static byte[] ReadFileStream(string connectionString, string title)
{
  byte[] buffer = null;
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
    connection.Open();
    SqlTransaction tran = connection.BeginTransaction(IsolationLevel.ReadCommitted);
    // Recupera il file di cui è stato specificato il titolo
    SqlCommand command = new SqlCommand("SELECT Data.PathName(),
                                         GET_FILESTREAM_TRANSACTION_CONTEXT()
                                         FROM Documents WHERE Title=@title", connection);
    command.Parameters.AddWithValue("@title", title);
    command.Transaction = tran;
    using (SqlDataReader reader = command.ExecuteReader())
    {
      if (reader.Read())
      {
        // Recupera il puntatore al file
        string path = reader.GetString(0);
        byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
        // Crea l'oggetto SqlFileStream
        SqlFileStream fileStream = new SqlFileStream(path,
                                                     transactionContext,
                                                     FileAccess.Read,
                                                     FileOptions.SequentialScan, 0);
        // Legge il contenuto della colonna
        buffer = new byte[fileStream.Length];
        fileStream.Read(buffer, 0, buffer.Length);
        fileStream.Close();
      }
    }
    tran.Commit();
  }
  return buffer;
}

Anche in questo caso recuperiamo il percorso del file e il contesto della transazione, quindi li utilizziamo per creare un oggetto SqlFileStream, questa volta in lettura (parametro FileAccess.Read), da cui preleviamo l'interno contenuto, salvandolo nella variabile buffer.

L'utilizzo di questi metodi è semplice. Ad esempio, nel metodo Main del file Program.cs possiamo inserire le seguenti istruzioni, con cui salviamo un file nel database e, subito dopo, lo diamo a rileggere:

static void Main(string[] args)
{
  string CONN_STRING = "Data Source=.;Initial Catalog=TestFilestream; Integrated Security=True";
  // Salva il file impostando il suo titolo su "Test"
  SaveFileStream(CONN_STRING, @"D:FilestreamTestMessaggio.mp3", "Test");
  // Recupera il file con titolo "Test"
  byte[] file = ReadFileStream(CONN_STRING, "Test");
  // Salva il file in una nuova posizione su disco
  File.WriteAllBytes(@"D:FromDB.mp3", file);
}

Per utilizzare i FILESTREAM da un'applicazione, è necessario che nella stringa di connessione sia specificata l'opzione Integrated Security=True, dal momento che l'accesso al contenitore FILESTREAM è possibile solo se si utilizza l'autorizzazione integrata di Windows: infatti, poiché i dati sono effettivamente memorizzati in una directory su disco, la connessione deve essere eseguita da un account che ha i diritti di accesso a tale cartella. In caso contrario, si otterrebbe un'eccezione nel momento in cui si tenta di creare l'oggetto SqlFileStream.

Ricordiamo infine che, per il corretto funzionamento di questa applicazione, è necessario configurare il FILESTREAM per lo streaming di file, come specificato sopra.

Tutti gli script mostrati in questo articolo e il codice sorgente dell'applicazione di esempio sono disponibili per il download.

Quando utilizzare FILESTREAM

Con l'introduzione del FILESTREAM, gli amministratori di basi di dati devono innanzi tutto decidere se continuare ad usare colonne di tipo VARBINARY(MAX) "standard" oppure passare al nuovo sistema. La scelta dipende prima di tutto dalla dimensione e dall'utilizzo dei dati: in generale, se gli oggetti che si stanno archiviando sono mediamente più grandi di 1 MB e si vuole un'alta velocità di accesso in lettura, si consiglia di utilizzare il FILESTREAM. Quando, invece, le informazioni da memorizzare hanno dimensione inferiori al MB, il classico VARBINARY(MAX) spesso garantisce prestazioni migliori.

Conclusioni

In questo articolo abbiamo introdotto il FILESTREAM, una nuova funzionalità di SQL Server 2008 che permette di salvare dati binari su disco, mantenendo la possibilità di accedervi come se fossero memorizzati all'interno del database.

Questa soluzione garantisce ottime prestazioni, perché l'accesso ai dati FILESTREAM è realizzato attraverso le streaming API di NTFS. Inoltre, trattandosi di una funzionalità integrata direttamente in SQL Server, anche i FILESTREAM sono gestiti in un contesto transazionale, quindi la consistenza delle informazioni è assicurata. Questo strumento rappresenta la soluzione ideale quando si ha la necessità di memorizzare dati binari di una certa dimensione all'interno del database e la velocità di accesso è un fattore cruciale.

Su MSDN è disponibile un interessante articolo di approfondimento (in lingua inglese).