Home > Articoli > FileTable in SQL Server Code-Named “Denali” (CTP3)

FileTable in SQL Server Code-Named “Denali” (CTP3)

Alcuni di voi avranno sicuramente apprezzato (e utilizzato) l’attributo FILESTREAM introdotto dalla versione 2008 di SQL Server. FILESTREAM rappresenta il punto di contatto tra il mondo del file system e il mondo del database, concilia i vantaggi delle due tecnologie con una struttura dati gestita da SQL Server, ma appoggiata sul file system.

Abbiamo illustrato l’utilizzo del supporto FILESTREAM (su SQL Server 2008) nell’articolo: FILESTREAM Attribute e per non lasciare nulla al caso, sul portale BEIT è stato pubblicato anche uno screencast sull’argomento: SQL Server 2008 R2: FILESTREAM Attribute.

Lo svantaggio evidente del supporto FILESTREAM è rappresentato dal fatto che le applicazioni tradizionali, che non sanno nulla di SQL Server, non possono accedervi. L’accesso al flusso di dati, ad esempio di un file BLOB, doveva essere eseguito utilizzando T-SQL oppure attraverso speciali API .NET Win32.

Molti clienti hanno quindi chiesto l’implementazione del tradizionale accesso ai file, come avviene nelle applicazioni Office o con Esplora Risorse di Windows, mantenendo però i vantaggi della tecnologia FILESTREAM ovvero la possibilità di includere nei backup i file memorizzati nelle colonne VARBINARY(MAX) con attributo FILESTREAM e di poter eseguire ricerche con il servizio full-text search. La risposta di Microsoft è arrivata con SQL Server "Denali", che implementa il concetto di FileTable(s).

FileTable(s) è una delle novità di SQL Server "Denali" ed è stata annunciata già nella CTP1 durante lo scorso PASS Summit 2010. Rohan Kumar (Principal Group Program Manager SQL Server RDBMS) ne ha parlato durante la key-note di apertura della conferenza come illustrano le slide di figura 1.

  

Figura 1 – Slide KeyNote Day 1, PASS Summit 2010 (Live Streaming Keynote)

FileTable(s) permette, ad una applicazione, di integrare la memorizzazione dei propri file all’interno di SQL Server dove sarà possibile eseguire ricerche full-text e semantiche anche su dati e metadati non strutturati. In altre parole sarà possibile memorizzare file e documenti in speciali tabelle di SQL Server chiamate FileTables; le applicazioni Windows potranno accedere a tali file come se fossero memorizzati (solo) nel file system senza implementare alcuna modifica.

La nuova caratteristica FileTable è basata sulla tecnologia FILESTRAM; una FileTable è una tabella speciale, con uno schema fisso, creata dall’utente e utilizzata per la memorizzazione di dati FILESTREAM (documenti, file o cartelle).

 

Prerequisiti

Per utilizzare FileTable è necessario aver attivato il supporto FILESTREAM, dapprima a livello di sistema operativo e poi a livello d’istanza. Vedremo tra poco che sarà inoltre necessario aggiungere al DB, su cui vogliamo usare FileTable, un filegroup dedicato.

Per maggiori informazioni sull’attivazione del supporto FILESTREAM consultate l’articolo FILESTREAM Attribute oppure lo screencast SQL Server 2008 R2: FILESTREAM Attribute citati in precedenza.

 

FileTable Setup

Procediamo con il setup del database di prova FileTablesDB, il seguente frammento di codice in linguaggio T-SQL implementa la creazione del DB FileTablesDB. Osserviamo la definizione del filegroup aggiuntivo FileStreamGroup dedicato al supporto FILESTREAM.

————————————————————————
— Setup database
————————————————————————

— Drop database if exists

use [master];
go

if DB_ID(‘FileTablesDB’) is not null
begin
  alter database FileTablesDB
    set single_user with rollback immediate;

  drop database FileTablesDB;
end;
go

— Create database

create database FileTablesDB on primary
(
  name = FileTablesDB_Data
  ,filename = N’C:\Program Files\Microsoft SQL Server\MSSQL11.SSDENALI_CTP3\MSSQL\DATA\FileTablesDB_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\MSSQL11.SSDENALI_CTP3\MSSQL\DATA\’ must exists on the file system
  — This subfolder ‘\FileTablesDB’ is created by SQL Server
  name = FileTablesDB_FileStream
  ,filename = N’C:\Program Files\Microsoft SQL Server\MSSQL11.SSDENALI_CTP3\MSSQL\DATA\FileTablesDB_FileStream’
)
LOG ON
(
  name = FileTablesDB_Log
  ,filename = N’C:\Program Files\Microsoft SQL Server\MSSQL11.SSDENALI_CTP3\MSSQL\DATA\FileTablesDB_Log.ldf’
  ,size = 3MB
  ,maxsize = 10MB
  ,filegrowth = 3MB
);
go

Dopo aver creato il database che ospiterà FileTables, modifichiamo l’opzione NON_TRANSACTED_ACCESS sul database FileTablesDB. Il valore FULL assegnato a NON_TRANSACTED_ACCESS indica a SQL Server che tutti gli accessi a FileTable, derivanti dal file system, non saranno transazionali; non sarà quindi possibile ripristinare i dati di FileTable in un punto preciso (point-in-time restore). La non transazionalità delle modifiche è applicata solo agli accessi che avvengono tramite la condivisione file di Windows, qualsiasi altro accesso tramite T-SQL o API sarà completamente transazionale, anche se attiva l’opzione NON_TRANSACTED_ACCESS.

I valori supportati da NON_TRANSACTED_ACCESS sono:

  • FULL
  • READONLY
  • OFF

Il seguente frammento di codice T-SQL imposta l’accesso non transazionale (FULL) per FileTables sul database di prova FileTablesDB.

————————————————————————
— Set NON_TRANSACTED_ACCESS to FULL
————————————————————————

— Change database context

use [FileTablesDB];
go

— Alter database
alter database FileTablesDB
  set filestream (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’Projects’);
go

E’ possibile ottenere lo stesso risultato anche da SSMS impostando il valore desiderato all’opzione "Accesso FILESTREAM non in transazioni" dalla finestra Proprietà database (scheda Opzioni), come illustrato in figura 2.

Figura 2 – Scheda Opzioni della finestra Proprietà database su SSMS "Denali"

 

Creazione di una FileTable

Procediamo ora con la creazione di una FileTable (letteralmente "tabella di file") nel database di prova FileTablesDB, il seguente frammento di codice T-SQL implementa la creazione della tabella dbo.ProjectsStore:

————————————————————————
— Setup FileTable
————————————————————————

create table dbo.ProjectsStore as FileTable
  with (
         FileTable_Directory = ‘DocumentTable’
        ,FileTable_Collate_Filename = database_default
       );
go

Una delle particolarità che possiamo osservare nel precedente statement è l’assenza di definizione dello schema; la tabella dbo.ProjectsStore è stata creata con lo schema predefinito per FileTable, che potete consultare sui BOL di SQL "Denali" [link].

Come descritto in precedenza, l’aggiunta o la rimozione di colonne allo schema fisso di un oggetto FileTable non è consentita. Il seguente frammento di codice T-SQL sarà quindi respinto da SQL Server:

— Alter FileTable schema… does not work
alter table dbo.ProjectsStore add test int null;
go

In output, il messaggio di errore sarà:

Messaggio 33422, livello 16, stato 1, riga 2
Impossibile aggiungere la colonna ‘test’ alla tabella ‘dbo.ProjectsStore’ perché si tratta di un oggetto FileTable. L’aggiunta di colonne allo schema fisso di un oggetto FileTable non è consentita.

Nello schema della tabella dbo.ProjectsStore troviamo colonne che descrivono le proprietà dei file di Windows (attributo di sola lettura, archivio, file temporaneo, file di sistema, ecc…), troviamo il path relativo rispetto alla root directory, memorizzato nella colonna path_locator di tipo HIERARCHYID; il file effettivo, invece, si trova nella colonna file_stream di tipo VARBINARY(MAX).

La figura 3 illustra la struttura della tabella dbo.ProjectsStore consultata dal nuovo SSMS di SQL "Denali", si osservi il ramo dedicato a FileTable, posto allo stesso livello di quello dedicato alle tabelle di sistema.

Figura 3 – Schema predefinito per le tabelle FileTable

Per esplorare il contenuto della directory associata alla tabella dbo.ProjectsStore è sufficiente eseguire un click destro del mouse sulla FileTable e selezionare la voce "Esplora directory FileTable" dal menù contestuale. La figura 4 descrive graficamente l’operazione da eseguire.

Figura 4 – Menù click desto su un oggetto FileTable

L’output di questa operazione è illustrato in figura 5.

Figura 5 – Contenuto della directory associata alla FileTable dbo.ProjectsStore

La cartella è vuota (così come la tabella dbo.ProjectsStore), osserviamo che il suo path ha come origine \\Win-riaf9y5g02g\ssdenali_ctp3\ ossia il nome della condivisione Windows specificata durante la configurazione del supporto FILESTREAM.

Eseguiamo ora la copia di tutti i file (file e sotto-cartelle) contenuti in C:\sgovoni\SQL\Projects, specificando come cartella di destinazione la directory associata alla FileTable dbo.ProjectsStore; il seguente comando XCOPY permette di eseguire l’attività di copia:

Figura 6 – Copia file da C:\sgovoni\SQL\Projects a… FileTable

Trattandosi di un’operazione di copia, nella cartella di destinazione ci aspettiamo di trovare tutti i file copiati, ma nella tabella (FileTable) dbo.ProjectsStore, cosa ci aspettiamo di trovare?

Il seguente frammento di codice T-SQL interroga il contenuto della FileTable dbo.ProjectsStore:

select
  name as [file_name]   ,file_type
  ,is_directory
  ,last_access_time
  ,file_stream.GetFileNamespacePath() as spacePath
from
  dbo.ProjectsStore;
go

L’output è illustrato in figura 7:

Figura 7 – Contenuto della tabella dbo.ProjectsStore

L’operazione di copia dei file dalla cartella C:\sgovoni\SQL\Projects alla directory collegata alla FileTable ha eseguito anche l’inserimento di un record, per ogni file copiato, nella tabella dbo.ProjectsStore memorizzando, di fatto, tali file nel database FileTablesDB, grazie al supporto FILESTREAM.

Con FileTable sono state rilasciate anche alcune funzioni che permettono di gestire e interrogare i dati attraverso il linguaggio T-SQL, un esempio è rappresentato dalla funzione FileTableRootPath() che fornisce il path di origine del file salvato su FileTable. Con T-SQL è inoltre possibile:

  • Creare nuove cartelle
  • Aggiornare gli attributi di un file
  • Spostare i file da una cartella a un’altra (con le funzioni del tipo HIERARCHID)
  • Modificare il file vero e proprio

 

Conclusioni

FileTable, una delle novità di SQL Server "Denali", completa la tecnologia FILESTREAM e rappresenta una potente combinazione tra il linguaggio T-SQL e l’accesso classico al file system da parte delle applicazioni Windows che ora possono salvare con estrema facilità i propri documenti all’interno di SQL Server.

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Unit testing: Come scrivere la tua prima unit test!

Nell’articolo precedente, il secondo di questa serie, abbiamo descritto come installare il framework tSQLt, il …