SQL Server 2012 - FileTable e la musica

In questo post parleremo di una delle novità di SQL Server 2012, FileTable. Possiamo definirla come una evoluzione della funzionalità  FILESTREAM, presente nella versione precedente di SQL Server.

Cosa è FileTable ?

E’ una tabella di files e cartelle che può essere creata su SQL Server per le applicazioni che intendono utilizzare i database come storage per i documenti.

Perchè è utile ?

Perchè è compatibile con le API di Windows per l’accesso non transazionale, la memorizzazione degli attributi dei files e la gestione del contenuto della FileTable. E’ ovviamente anche compatibile con gli strumenti di management di SQL Server.

Facciamo una prova

La nostra prova consiste nel creare un database che contenga al suo interno la nostra library musicale per poi poterla ascoltare con un player multimediale. Tempo di esecuzione : 5 minuti!

Come prima cosa abilitiamo FILESTREAM da SQL Server Configuration Manager. Per questa dimostrazione useremo SQL Server 2012 RC0 installato su Windows 2008 R2 Server Core. Se siete interessati a fare il setup di SQL su Server Core potete leggere questo post. Per usare SQL Server Configuration Manager dobbiamo passare per MMC da un computer dedicato e aggiungere lo snap-in Computer Management.   A quel punto nella tab FILESTREAM abilitiamo la feature.

Creiamo un database abilitato per il FILESTREAM e creiamo una FileTable

 --
 sp_configure 'show advanced options', 1
 Go
 RECONFIGURE
 GO
 sp_configure 'xp_cmdshell', 1
 GO
 RECONFIGURE
 GO
 sp_configure 'filestream access level', 2
 GO
 RECONFIGURE
 GO
  
 --creo una cartella per il database
 EXECUTE xp_cmdshell 'md C:\_FileTable', no_output
  
 --creo un database abilitato per il filestream
 create database TestFileTable
     on primary (name = TestFileTable_File, filename = N'C:\_FileTable\TestFileTable_File.mdf'),
     filegroup FileSystem
         contains filestream
             (name = TestFileTable_FILESTREAM, filename = N'C:\_FileTable\TestFileTable_FS')
         with filestream
             (non_transacted_access = full, directory_name = N'_FileTable')
  
 --creo una FileTable
 CREATE TABLE TestFileTable..Documenti AS FileTable WITH (filetable_directory = N'Documenti')

Facciamo refresh su SSMS per vedere lo schema (non modificabile) della tabella creata all’interno del nostro database. Per avere un dettaglio dei singoli campi potete fare riferimento a questo documento.

A questo punto prendiamo un po’ di musica e la copiamo all’interno della nostra FileTable. Non dobbiamo scrivere nessun client per fare questo, semplicemente apriamo Windows Explorer. Per recuperare il path della FileTable basta eseguire questo script

 --recuperiamo il path alla document library
 USE TestFileTable
 GO
 SELECT FileTableRootPath() AS 'Path'
 GO

Ora copiamo la nostra musica all’interno della FileTable ed il gioco è fatto!

Facciamo una query sulla FileTable per capire se i nostri MP3 sono stati copiati regolarmente.

 --facciamo un check per verificare i dati nella filetable
 SELECT stream_id, name, file_type,
 (
 SELECT REPLACE(N'File:\\'+FileTableRootPath()+file_stream.GetFileNamespacePath(), N' ', N'%20'
 )
 FROM TestFileTable..Documenti T2
 WHERE T2.stream_id = T1.stream_id FOR XML PATH(''), TYPE) AS FilePath,
     creation_Time, last_write_time, last_access_time, is_directory, is_offline,
     is_hidden, is_readonly, is_archive, is_system, is_temporary
 FROM TestFileTable..Documenti T1
 ORDER BY name ASC

Ultimo passo  è ascoltare la musica direttamente dalla FileTable, per esempio con Windows Media Player . Settiamo la Library Location che contiene i due album dei Radiohead che sto usando per la demo e come vedete dagli screenshot seguenti Media Player riconosce i due album e li aggiunge alla library musicale.

Per il backup della nostra musica ora possiamo chiedere aiuto a TSQL lanciando semplicemente un BACKUP DATABASE, che conterrà al suo interno anche la nostra FileTable (la nostra musica).

 --BACKUP
 backup database TestFileTable to disk='c:\musica.bak'

Per aggiungere, eliminare musica nella nostra library possiamo tranquillamente continuare ad usare Windows Explorer ed il nostro caro copia-incolla.

In un prossimo post vedremo alcune interessanti possibilità per usare FileTable anche con i documenti, per il momento buon ascolto!

Francesco