UGISS User Group Italiano SQL Server
Tra le numerose novità introdotte con SQL Server 2008 troviamo anche il nuovo attributo FILESTREAM che integra l’intelligenza del query processor di SQL Server con l’immediatezza del file system per la memorizzazione di file di grandi dimensioni.
Quante volte ci siamo chiesti dove fosse meglio memorizzare file di grandi dimensioni, come ad esempio file video, multimediali o semplicemente documenti nei formati Microsoft Office. Fondamentalmente due le possibili soluzioni: nel file system oppure all’interno del DB, in colonne di tipo BLOB (Binary Large Object). Da qui l’analisi dei vantaggi e degli svantaggi di ognuna delle due soluzioni, ma in entrambi i casi, comunque, abbiamo dovuto accettare gli svantaggi della soluzione scelta. Probabilmente, il più delle volte abbiamo scelto di tenere fuori dal DB i dati non strutturati per evitare ad esempio file di backup di dimensioni elevate, a discapito però della semplicità di gestione e accettando limitazioni nelle funzionalità di flusso.
FILESTREAM rappresenta il punto di contatto tra il mondo del file system ed il mondo del database, concilia i vantaggi delle due tecnologie (file system e database) con una struttura dati gestita da SQL Server ma appoggiata sul file system.
FILESTREAM Setup
L’attivazione del supporto FILESTREAM deve essere configurata dapprima a livello di sistema operativo e successivamente a livello di istanza/database. L’attivazione di FILESTREAM a livello di sistema operativo può essere eseguita attraverso l’utility SQL Server Configuration Manager, da cui, con un click destro del mouse sul servizio SQL Server si può accedere alla finestra Proprietà su cui è presente la scheda FILESTREAM illustrata in figura 1.
Figura 1 – Proprietà servizio SQL Server, scheda FILESTREAM
Selezionare, con un segno di spunta, la casella di controllo “Abilita FILESTREAM per l’accesso Transact-SQL” per abilitare il supporto.
Se si desidera leggere e scrivere dati FILESTREAM da Windows, fare clic su “Abilita FILESTREAM per l’accesso tramite il flusso di I/O dei file”. Nella casella “Nome condivisione di Windows” immettere il nome della condivisione Windows.
Se client remoti devono accedere ai dati FILESTREAM archiviati in tale condivisione, selezionare “Consenti ai client remoti l’accesso tramite flusso ai dati FILESTREAM”.
Per attivate FILESTREAM a livello di istanza è necessario utilizzare la stored procedure di sistema sp_configure come illustrato nel seguente frammento di codice T-SQL.
————————————————————————
— Setup FILESTREAM at instance level
————————————————————————
— Enable filestream support at instance level
— Configuration value & description
— 0 = Disabled (default value)
— 1 = Enabled only for T-SQL access
— 2 = Enabled for T-SQL and Win32 streaming access
exec sp_configure ‘filestream access level’, 2;
go
reconfigure;
go
La funzione SERVERPROPERTY() permette di verificare l’effettiva attivazione del supporto FILESTREAM, un esempio nel seguente frammento di codice T-SQL, che produce l’output illustrato in figura 2.
select
SERVERPROPERTY(‘FilestreamShareName’) as ShareName
,SERVERPROPERTY(‘FilestreamConfiguredLevel’) as ConfiguredLevel
,SERVERPROPERTY(‘FilestreamEffectiveLevel’) as EffectiveLevel;
go
Figura 2 – Verifica dell’effettiva abilitazione del supporto FILESTREAM
FILESTREAM è effettivamente attivo sia a livello di sistema operativo che a livello di istanza, per utilizzare il nuovo attributo è però necessario aggiungere al database un filegroup dedicato utilizzando, nel comando CREATE/ALTER DATABASE le parole chiave CONTAINS FILESTREAM. Ipotizziamo ora di voler creare il database VideoLibrary al cui interno verrà aggiunta la tabella dbo.videos che memorizza l’elenco dei video disponibili ed i relativi file multimediali.
Il seguente frammento di codice T-SQL permette di creare il database VideoLibrary a cui è associato il filegroup FileStreamGroup dedicato a contenere i metadati associati al supporto FILESTREAM. Viene creata anche la tabella dbo.videos avente una colonna di tipo VARBINARY(MAX) su cui viene applicato l’attributo FILESTREAM. Importante sottolineare che l’applicabilità dell’attributo FILESTREAM su una colonna VARBINARY(MAX) di una tabella è subordinata alla presenza di un campo di tipo UNIQUEIDENTIFIER con proprietà ROWGUID.
————————————————————————
— Setup database
————————————————————————
use [master];
go
if (DB_ID(‘VideoLibrary’) is not null)
begin
alter database VideoLibrary
set single_user with rollback immediate;
drop database VideoLibrary;
end;
go
create database VideoLibrary on primary
(
name = VideoLibrary_Data
,filename = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\VideoLibrary_Data.mdf’
,size = 5MB
,maxsize = 50MB
,filegrowth = 10%
),
— This filegroup contains filestream data
FILEGROUP FileStreamGroup CONTAINS FILESTREAM
(
— This folder
— ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\’ must exists on the file system
— This subfolder ‘\VideoLibrary_FileStream’ is created by SQL Server
name = VideoLibrary_FileStream
,filename = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\VideoLibrary_FileStream’
)
LOG ON
(
name = VideoLibrary_Log
,filename = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\VideoLibrary_Log.ldf’
,size = 3MB
,maxsize = 10MB
,filegrowth = 3MB
);
go
use [VideoLibrary];
go
————————————————————————
— Create e new table with varbinary(max) and FILESTREAM attribute
————————————————————————
create table dbo.videos
(
video_id uniqueidentifier not null rowguidcol primary key
,video_title nvarchar(256) not null
— FILESTREAM attribute for varbinary(max) column
,video_file varbinary(max) FILESTREAM default(0x)
);
go
Osserviamo che il filegroup FileStreamGroup non viene collegato a file di dati ma alla directory C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ che non sarebbe stata accessibile con le attuali credenziali, ma considerando che FILESTREAM utilizza la protezione integrata di SQL Server, il precedente comando viene eseguito correttamente (proprio perché è SQL Server che chiede di accedere al file system).
All’interno della directory C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA SQL Server ha creato la sottocartella VideoLibrary_FileStream (che non deve esistere nel momento in cui viene aggiunto il filegroup) in cui troveremo una sottocartella per ogni tabella del database che contiene una colonna VARBINARY(MAX) su cui è stato applicato l’attributo FILESTREAM. All’interno di ognuna di queste sottocartelle troveremo ulteriori directory, una per ogni colonna su cui è stato applicato l’attributo FILESTREAM.
Per la tabella dbo.videos, nell’esempio, ci aspettiamo la struttura illustrata in figura 3.
Figura 3 – Contenuto della cartella VideoLibrary_FileStream
FILESTREAM Programming
Il linguaggio T-SQL può essere utilizzato per inserimenti preliminari, Update, Query e Backup di strutture con supporto FILESTREAM, viene inoltre utilizzato per richiedere a SQL Server il token che rappresenta il contesto transazionale di una sessione. Infatti, il token restituito da GET_FILESTREAM_TRANSACTION_CONTEXT() può essere utilizzato da un’applicazione per associare le operazioni di flusso del file system (FILESTREAM) alla transazione corrente.
Vediamo ora un esempio di utilizzo del linguaggio T-SQL per l’inserimento preliminare di un record nella tabella dbo.videos. La colonna video_file, BLOB VARBINARY(MAX), viene per semplicità valorizzata con la stringa “The binary content of the stored video” convertita in VARBINARY(MAX).
insert into dbo.videos
(
video_id
,video_title
,video_file
)
values
(
NEWID()
,’SQL Server FILESTREAM support’
,CAST(‘The binary content of the stored video’ as varbinary(max))
);
go
Dopo l’inserimento, possiamo interrogare la tabella dbo.videos con la seguente SELECT, che produce l’output illustrato in figura 4.
select
CAST(v.video_file as varchar(max)) as video_file_varchar
,v.video_file.PathName() as pathname
,v.video_id
,v.video_title
from
dbo.videos as v;
go
Figura 4 – Contenuto della tabella dbo.videos
Particolare importanza riveste la funzione PathName() di SQL Server (PathName è case sensitive!!) che restituisce un percorso, come token, a un BLOB. Il token restituito potrà quindi essere utilizzato da un’applicazione per ottenere un handle Win32 e operare sui dati BLOB.
Per memorizzare un file multimediale all’interno della colonna video_file della tabella dbo.videos è necessario utilizzare l’API OpenSqlFilestream() da cui si ottiene un handle di file compatibile con Win32 per un oggetto BLOB (Binary Large Object) FILESTREAM archiviato nel file system. L’handle può essere passato a una delle seguenti API Win32: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile o FlushFileBuffers.
Ipotizziamo ora di voler inserire, nella tabella dbo.videos i files multimediali (con estensione .wmv) contenuti in una determinata directory. Utilizzeremo quindi il linguaggio T-SQL per l’inserimento preliminare di un record per ogni file multimediale che desideriamo memorizzare nella colonna video_file, successivamente chiederemo a SQL Server di restituirci il token relativo al contesto transazionale della sessione con la funzione GET_FILESTREAM_TRANSACTION_CONTEXT(). Una volta ottenuto il token potremo quindi utilizzare l’API OpenSqlFilestream() per ottenere un file handle compatibile con Win32 che verrà utilizzato per lo streaming, in transazione, di files multimediali.
Il seguente frammento di codice in linguaggio Microsoft Visual C#, tratto da SQL2K8 FILESTREAM-WPF-HTTP Sample di Roger Doherty e Zach Skyles Owens, fornisce un esempio per inserire nella tabella dbo.videos i files multimediali wake_up_cat.wmv e Robotica_720.wmv contenuti nella cartella ..\..\Media\.
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.Win32.SafeHandles;
using SqlFilestream;
namespace FilestreamLoader
{
class Program
{
// Sets the buffer size for reading and writing blobs.
// Used NTFS blocksize which is 512K.
const int blockSize = 1024*512;
static void Main()
{
// Setup file parameters.
String filePath = @"..\..\..\..\..\Media\";
Console.WriteLine("Loading videos…");
LoadVideoIntoDatabase(filePath, @"wake_up_cat.wmv", "Wake Up Cat");
LoadVideoIntoDatabase(filePath, @"Robotica_720.wmv", "Robotica");
Console.WriteLine("Done. Hit any key to exit.");
Console.ReadLine();
}
private static void LoadVideoIntoDatabase(String filePath, String fileName, String title)
{
#region Database connection
// Open a connection to the database.
SqlConnectionStringBuilder cxnString = new SqlConnectionStringBuilder();
cxnString["Data Source"] = "<server_name\instance_name>";
cxnString["Integrated Security"] = true;
cxnString["Initial Catalog"] = "VideoLibrary";
SqlConnection cxn = new SqlConnection(cxnString.ConnectionString);
cxn.Open();
#endregion Database connection
// Start up a database transaction.
SqlTransaction txn = cxn.BeginTransaction();
// Create a Guid to be used on insert.
Guid mediaId = Guid.NewGuid();
// Insert a row into the table to create a handle for streaming write.
SqlCommand cmd=new SqlCommand("INSERT [dbo].[videos]([video_id],[video_title]) VALUES(@mediaId, @title);", cxn, txn);
cmd.Parameters.Add("@mediaId", SqlDbType.UniqueIdentifier).Value = mediaId;
cmd.Parameters.Add("@title", SqlDbType.NVarChar, 256).Value = title;
cmd.ExecuteNonQuery();
// Get a filestream PathName token and filestream transaction context.
// These items will be used to open up a file handle against the empty blob instance.
cmd = new SqlCommand("SELECT [video_file].PathName(),GET_FILESTREAM_TRANSACTION_CONTEXT() FROM [dbo].[videos] WHERE [video_id]=@mediaId;",cxn,txn);
cmd.Parameters.Add("@mediaId", SqlDbType.UniqueIdentifier).Value = mediaId;
// Read in results of query.
SqlDataReader rdr;
rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
rdr.Read();
SqlString sqlFilePath = rdr.GetSqlString(0);
SqlBinary transactionToken = rdr.GetSqlBinary(1);
rdr.Close();
// Get a Win32 file handle to the empty blob instance using SQL Native Client call.
// This is required in order to write to the empty blob instance.
SafeFileHandle handle = SqlNativeClient.OpenSqlFilestream(
sqlFilePath.Value,
SqlNativeClient.DESIRED_ACCESS_WRITE,
0,
transactionToken.Value,
(UInt32)transactionToken.Value.Length,
new SqlNativeClient.LARGE_INTEGER_SQL(0));
// Open up a new stream to write the file to the blob.
FileStream destBlob = new FileStream(handle, FileAccess.Write);
// Open up a new stream to read the source media file.
FileStream sourceFile = new FileStream(String.Concat(filePath, fileName), FileMode.Open, FileAccess.Read);
// Loop through source file and write to FileStream handle
// ATTENTION: This is the power of FileStream!!!
byte[] buffer = new byte[blockSize];
int bytesRead;
while ((bytesRead = sourceFile.Read(buffer, 0, buffer.Length)) > 0)
{
destBlob.Write(buffer, 0, bytesRead);
}
#region Connection cleanup
// Commit transaction, cleanup connection.
destBlob.Close();
sourceFile.Close();
txn.Commit();
cxn.Close();
#endregion Connection cleanup
}
}
}
Conclusioni
FILESTREAM associa dati strutturati e non, rappresentando il punto di contatto tra il mondo del file system e il mondo del database, concilia i vantaggi offerti da entrambe le tecnologie. Sarà vantaggioso utilizzare il supporto FILESTREAM per trattare files di dimensioni maggiori di 1MB, per questi infatti la memorizzazione dei dati sul file system risulterà essere ottimale, i metadati rimarranno comunque memorizzati nel DB con le modalità illustrate in precedenza.
