FILESTREAM in SQL Server

27.06.24 12:30 PM Comment(s)

In questo articolo discuteremo della funzionalità FILESTREAM in SQL Server: partiremo da una panoramica della tecnologia, vedremo come abilitare e configurare tale funzionalità e concluderemo poi con un esempio pratico.

Dati strutturati e dati non strutturati

Oltre ai classici dati strutturati che salviamo nelle tabelle, potremmo avere bisogno di archiviare dei dati non strutturati come documenti, immagini, audio o video. Per fare ciò è possibile usare i dati di tipo BLOB (Binary Large Object). Tra questi troviamo il data type varbinary(max), che permette di archiviare file e documenti all’interno di una tabella con un limite 2^31 -1 bytes (circa 2 GB).

Questo tipo di dato però rallenta le prestazioni del database perché tende ad essere grande e quindi ad usare molte risorse del buffer pool di SQL Server per attività di lettura e scrittura.

L'introduzione di FILESTREAM

Con SQL Server 2008 è stata introdotta la funzionalità FILESTREAM che integra il motore del database con il file system NTFS del sistema operativo e archivia i dati BLOB direttamente nel file system.

Questo approccio offre vantaggi in termini di ottimizzazione dello spazio su disco, prestazioni migliorate e gestione semplificata dei dati non strutturati, per i quali è garantita la coerenza transazionale con i dati strutturati.

FILESTREAM utilizza la cache del file system per memorizzare i dati dei file. Questo approccio consente di ridurre qualsiasi impatto dei dati non strutturati sulle prestazioni del motore di database. Il buffer pool di SQL Server non viene utilizzato e la memoria rimane disponibile per l’elaborazione delle query.

Un altro vantaggio è avere un limite di spazio superiore ai 2 GB che dipenderà dal volume disponibile nel file system.

Configurazione e abilitazione di FILESTREAM

FILESTREAM non viene abilitato automaticamente durante l'installazione o l'aggiornamento di SQL Server. È necessario abilitare manualmente FILESTREAM usando SQL Server Configuration Manager e SQL Server Management Studio.

Come prima cosa apriamo SQL Server Configuration Manager. Tra la lista dei servizi selezioniamo l’istanza che ci interessa, tasto destro, proprietà e selezioniamo la Tab FILESTREAM.

Mettiamo il flag su ‘Enable FILESTREAM for Transact/SQL access’ e ‘Enable FILESTREAM for file I/O access’.

A questo punto è necessario immettere un nome per la window share per permettere a SQL Server di interagire con il file system. Opzionale la voce ‘Allow remote clients access to FILESTRAM data’.

L’ultimo passaggio è quello di collegarci all’istanza tramite SQL Server Management Studio e abilitare l’accesso eseguendo la seguente query.

EXEC sp_configure filestream_access_level
	,2;

RECONFIGURE
WITH OVERRIDE;
	/*	   

  0 = Accesso Disabilitato 

  1 = Accesso per solo T-SQL  

  2 = Accesso per T-SQL e Windows Streaming 

*/

Una volta eseguito il comando riavviamo l’istanza e la configurazione ed abilitazione di FILESTREAM sarà completata.

Esempio pratico

Per utilizzare FILESTREAM, è necessario creare o modificare un database in modo che contenga un tipo speciale di filegroup e quindi creare o modificare una tabella in modo che contenga una colonna varbinary(max) con l'attributo FILESTREAM.

In questo esempio creeremo un database chiamato ‘FilestreamDemoDB’ con il filegroup speciale FILESTREAM chiamato 'FilestreamDemo' con il seguente comando.

CREATE DATABASE FilestreamDemoDB ON PRIMARY (
	NAME = N'FilestreamDemoDB_data'
	,FILENAME = N'C:\Temp\DATA\FilestreamDemoDB.mdf'
	)
	,FILEGROUP DemoFilestream CONTAINS FILESTREAM (
	NAME = N'FilestreamDemo'
	,FILENAME = 'C:\Temp\DATA\Filestreamdemo1'
	) LOG ON (
	NAME = N'FilestreamDemoDB_log'
	,FILENAME = N'C:\Temp\DATA\FilestreamDemoDB_log.ldf'
	)

Creato il database possiamo notare nella pagina delle proprietà del database il filegroup FILESTREAM.

Nel file system oltre ai classici file MDF e LDF verrà creata una cartella con la nomeclatura del filegroup FILESTREAM. Questa cartella conterrà altre cartelle in base al numero di tabelle che utilizzano FILESTREAM. Vengono chiamate contenitori di dati i quali sono l'interfaccia tra archiviazione del motore di database e del file system.

Continuiamo con l’esercizio creando una tabella nominata ‘DemoFilestreamTable’ la quale deve avere necessariamente due colonne, una di tipo ROWGUIDCOL che ha la funzione di identificatore univoco e l’altra di tipo varbinary(max) specificando l’attributo FILESTREAM. La terza colonna servirà all’utente per attribuire un’etichetta identificativa.

USE FilestreamDemoDB
GO

CREATE TABLE [DemoFileStreamTable] (
	[FileId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
	,[FileName] VARCHAR(25)
	,[File] VARBINARY(MAX) FILESTREAM
	);

Creata la tabella con il seguente script possiamo inserire un dato non strutturato a scelta come un pdf, un word o un’immagine.

In questo caso userò un file png che si trova nel percorso ‘C:\Temp’ e lo inserirò nella tabella ‘DemoFilestreamTable’ precedentemente creata.

DECLARE @File VARBINARY(MAX);

SELECT @File = CAST(bulkcolumn AS VARBINARY(max))
FROM OPENROWSET(BULK 'C:\Temp\datamaze_logo.png', SINGLE_BLOB) AS MyData;

INSERT INTO DemoFileStreamTable
VALUES (
	NEWID()
	,N'Datamaze logo'
	,@File
	)

In questo script viene dichiarata una variabile e con un inserimento bulk il file viene convertito in varbinary(max) e assegnato alla variabile creata. L’ultima operazione è un inserimento di valori nella tabella, tra i quali abbiamo il nostro file in formato binario.

Facciamo una SELECT per visualizzare i record della tabella e nella colonna ‘File’ possiamo vedere che il file png è stato convertito in un oggetto binario.

Una volta inserito il file nella tabella possiamo trovare il file nel contenitore di dati a file system.

Il file binario è identico in tutto e per tutto al file originario che abbiamo inserito nel FILESTREAM. Nel nostro caso si tratta di un’immagine e la possiamo aprire con un editor di immagine qualsiasi.

Concludendo, in questo articolo abbiamo visto una panoramica della funzionalità FILESTREAM e i vantaggi che può portare quando abbiamo a che fare con dati non strutturati. L’esempio visto è introduttivo e serve per capire come funziona il meccanismo a livello base. Ci sarà modo in altri articoli di approfondire tematiche legate per esempio al backup e restore di database con FILESTREAM attivo.

di Nabil El Merzouki, pubblicato il 27 giugno 2024

FILESTREAM in SQL Server

Dati strutturati e dati non strutturati

L'introduzione di FILESTREAM

Configurazione e abilitazione di FILESTREAM

Esempio pratico

Datamaze Srl