Press "Enter" to skip to content

Funzionalità FILESTREAM in SQL server

Fra le novità più importanti di SQL Server 2008, una è certamente la possibilità di utilizzare il file System per la memorizzazione dei dati di tipo BLOB. Pertanto se lavorate con Immagini, File binari ed altro che dovete memorizzare in un database SQL Server, è possibile utilizzare questa funzionalità, importante ad esempio per chi usa SQL Express, perché in questo modo, la zona per i BLOB non occupa spazio utile nei files del database, che sono limitati se non vado errata a 4GB.

Come si attiva questa funzionalità

  1. Primo passo, attivare la funzionalità a livello di protocolli del server, dal Menu Programmi > Microsoft SQL Server 2008 > Configuration Tools – lanciare SQL Server Configuration Manager (Icona con la cassetta attrezzi rossa).
    • Selezionare SQL Server Services nella treeview a sinistra
    • Sulla finestra di destra selezionare SQL Server (MSSQLSERVER) per l’istanza di default oppure l’istanza nominata.
    • Tasto destro proprietà
    • Sulla finestra c’è una serie di Tab, scegliere FILESTREAM attivare l’accesso per Transact SQL ed eventualmente l’accesso via API e l’accesso da client remoti ( non sono certa sia obbligatorio settarlo se si usa il TSQL ma non ho ancora effettuato test in merito.)
  2. Secondo passo, in SQL Management Studio, collegarsi al server con diritti da Sysadmin, sul Server SQL tasto destro Properties, Selezionare Advanced nella lista sulla sinistra e sulla prima riga della property grid a destra, attivare l’opzione Filestream In modalità Transact SQL o Full Access.
  3. Creare un database che vi permetta di memorizzare dati FILESTREAM:
      USE [master]
      GO
      
      /****** Object:  Database [Paperinik]    Script Date: 03/04/2010 18:09:07 ******/
      IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'Paperinik')
      BEGIN
      CREATE DATABASE [Paperinik] ON  PRIMARY 
      ( NAME = N'Paperinik', FILENAME = N'C:\SQL.DIR\Data\Test\Paperinik.mdf' , 
      SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [Binari] CONTAINS FILESTREAM DEFAULT ( NAME = N'Paperinik_Bin', FILENAME = N'c:\sql.dir\data\test\PaperinikFS\paperinik_bin' ) LOG ON ( NAME = N'Paperinik_log', FILENAME = N'C:\SQL.DIR\Data\Test\Paperinik_log.ldf' ,
      SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) END

      Questo esempio, fatto con il mio database preferito 😀 crea il database vero e proprio con il file Paperinik.mdf come master data file ed il file Paperinik_log.ldf come file di log. Aggiungo poi un Filegroup che chiamo Binari che definisce la zona relativa ai FILESTREAM, indicando la cartella dove memorizzare i dati, nel mio caso, c:\sql.dir\data\test\PaperinikFS\

  4. Creare una tabella che contenga un campo Varbinary(MAX) con l’opzione FILESTREAM
      USE [Paperinik]
      GO
      
      CREATE TABLE [dbo].[TbFiles](
          [IDFile] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
          [IDFileSerial] [int] NOT NULL,
          [DDFile] [nvarchar](255) NULL,
          [FileData] [varbinary](max) FILESTREAM  NULL,
       CONSTRAINT [PK_TbFiles] PRIMARY KEY NONCLUSTERED 
      (
          [IDFile] ASC
      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  =     OFF, 
          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
      ) ON [PRIMARY] FILESTREAM_ON [Binari]
      GO
      
      USE [Paperinik]
      /****** Object:  Index [UQ_TbFileSerial]    Script Date: 03/04/2010 17:23:33 ******/
      CREATE UNIQUE CLUSTERED INDEX [UQ_TbFileSerial] ON [dbo].[TbFiles] 
      (
          [IDFileSerial] ASC
      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
          SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
          DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
          ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] FILESTREAM_ON [Binari]
      GO

      In questo caso, ho creato una tabella per memorizzare dei file, anche se poi nell’esempio memorizzeremo qualcosa di più facile, per le cose più complesse farò ulteriori test in seguito. Per poter contenere un campo di tipo FILESTREAM è indispensabile che la tabella contenga un ID univoco di tipo GUID (Il campo IDFile) ho anche generato un campo univoco con codice numerico che può divenire un metodo “potabile” per cercare i record, e gli ho quindi assegnato un indice univoco clustered.

  5. Ora non ci resta che Inserire dei dati di test, questi dati possono essere creati in modo semplice usando uno script T-SQL di inserimento:
      INSERT INTO [Paperinik].[dbo].[TbFiles]
                 ([IDFile]
                 ,[IDFileSerial]
                 ,[DDFile]
                 ,[FileData])
           VALUES
                 (NEWID()
                 ,1
                 ,'Nullo'
                 ,null)
      GO
      
      INSERT INTO [Paperinik].[dbo].[TbFiles]
                 ([IDFile]
                 ,[IDFileSerial]
                 ,[DDFile]
                 ,[FileData])
           VALUES
                 (NEWID()
                 ,2
                 ,'Vuoto'
                 ,cast('' as varbinary(max)))
      GO
      
      INSERT INTO [Paperinik].[dbo].[TbFiles]
                 ([IDFile]
                 ,[IDFileSerial]
                 ,[DDFile]
                 ,[FileData])
           VALUES
                 (NEWID()
                 ,3
                 ,'Stringa'
                 ,cast('Questa stringa è binaria' as varbinary(max)))
      GO
      
      INSERT INTO [Paperinik].[dbo].[TbFiles]
                 ([IDFile]
                 ,[IDFileSerial]
                 ,[DDFile]
                 ,[FileData])
           VALUES
                 (NEWID()
                 ,4
                 ,'La quarta'
                 ,cast('Questa riga contiene questa stringa' as varbinary(max)))
      GO

      In questo caso, ho generato quattro righe, contenenti un campo binario nullo, un fantomatico file di testo vuoto, e due files di testo con una stringa al loro interno.

  6.   Cosa troveremo nel nostro file system dopo queste operazioni?
    Verrà generato un sistema di cartelle, sotto alla cartella ROOT  c:\sql.dir\data\test\PaperinikFS\ i cui nomi sono dei GUID, che saranno utilizzate per memorizzare i dati relativi ai nostri VarBinary (MAX) di tipo filestream. Se curiosate nelle sottocartelle, troverete dei file senza alcuna estensione con nomi in stile nnnnnnnn-nnnnnnnn-nnnn che aperti con notepad, visualizzeranno il contenuto del campo varbinary. Non ho ancora provato a memorizzare nella tabella un file binario e vedere cosa troverò, lascio ad un post futuro il compito di rivelarlo.

A che conclusioni ci porta questo piccolo esperimento? Se abbiamo bisogno di memorizzare dati non strutturati in un database, questa può essere una buona soluzione, perché SQL Server fornisce comunque le transazioni, il log delle modifiche e variazioni, con un purge che può essere configurato, la possibilità di attivare l’indicizzazione. La possibilità di fare il backup del filesystem che costruisce come qualsiasi altro filesystem. Ci toglie dall’imbarazzo per il problema del limite a 4GB dei database di SQL Express, ma attenzione, il formato del filesystem non è comunque direttamente accessibile dall’esterno, se si perdono i database, recuperare fisicamente i files binari è possibile, ma sapere cos’è ciascuno dei files recuperati NON è una cosa semplice.

Chi avesse bisogno di indicizzare su database dati destrutturati posti su file system che vuole comunque vedere e poter accedere anche usando il file system stesso, non deve usare questa funzionalità, ma costruire un gestore manuale del file system e memorizzarsi sul database i percorsi dei files che gestisce.