Una delle novità più interessanti di SQL Server 2008 è la possibilità di utilizzare il file system per fare storage dei nostri dati.
Ovvero utilizzare il nuovo tipo di dati FILESTREAM per memorizzare large binary data come parte integrante del nostro database con la stessa consistenza transazionale!
SQL Server 2008 fornisce, infatti, il nuovo tipo di storage: VARBINARY(MAX) FILESTREAM.
Le applicazioni sono quindi in grado di utilizzare le "normali" istruzioni T-SQL SELECT/INSERT/UPDATE/DELETE per interrogare e modificare i dati FILESTREAM.
La cosa che forse potrebbe interessare ancora di più è che la versione express mantiene il limite (per database) dimensionale di 4Gb, supporta l'utilizzo di FILESTREAM ma, attenzione, attenzione, il size limit *non* include il FILESTREAM data container!
Vantaggi
- fare storage di BLOB su file system, su filegroup dedicato, in contesto transazionale
- accessibile attraverso T-SQL e streaming API NTFS (ottime performance!)
- limite di storage = limite del volume NTFS
- gestione integrata in SQL Server
Svantaggi / Punti di attenzione
- non posso configurare il db mirroring su db con dati FILESTREAM
- snapshot del db non fanno foto dei dati FILESTREAM
- i dati FILESTREAM non possono essere cifrati nativamente da SQL Server
- non è supportato su tabelle che utilizzano table-valued parameters
Vediamo in pratica...
Tre sono le cose che dobbiamo fare:
1. abilitare FILESTREAM sull'istanza
2. creare un db ed una tabella per memorizzare dati FILESTREAM
3. utilizzare i dati :-)
Per prima cosa andiamo ad abilitarne l'utilizzo. Prima di farlo visualizzo però, su shell DOS, il comando NET SHARE, questo il risultato:
use Master
go
EXEC sp_filestream_configure
@enable_level = 3,
@share_name = "myStorageFileStream";
/*
@enable_level = livello 3
Ovvero:
Enabled for Transact-SQL, local file system access, and remote file system access
Gli altri valori:
0 = off; 1 = T-SQL; 2 = T-SQL + File
*/
Comando NET SHARE:
Creo il database:
USE Master
GO
CREATE DATABASE myDocumentDB ON PRIMARY
( NAME = myDocumentDB_data,
FILENAME = N'C:\data\myDocumentDB_data.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%),
FILEGROUP myDocumentDBFileStream CONTAINS FILESTREAM
( NAME = myDocumentDBCatalog,
FILENAME = N'C:\data\myDocumentDBCatalog')
LOG ON
( NAME = 'myDocumentDB_log',
FILENAME = N'C:\data\myDocumentDB_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
);
Fate attenzione al filegroup "CONTAINS FILESTREAM", necessario per fare storage del tipo.
Sul file system avremo quindi:
Creo la tabella che andrà a contenere i dati FILESTREAM:
USE myDocumentDB
GO
CREATE TABLE dbo.catalog
(
idRecord int primary key identity(1,1),
CatalogName varchar(100),
Catalog varbinary(max) FILESTREAM,
CatalogID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
);
GO
Attenzione alla presenza di una colonna UNIQUEIDENTIFIER con ROWGUIDCOL (è indispensabile!).
/* semplici insert di prova */
INSERT dbo.catalog (catalogName, catalog, catalogID)
VALUES ('test catalog 1', CAST('my text ...' AS VARBINARY(MAX)), newid());
INSERT dbo.catalog (catalogName, catalog, catalogID)
VALUES ('test catalog 2', CAST('my text ...' AS VARBINARY(MAX)), newid());
INSERT dbo.catalog (catalogName, catalog, catalogID)
VALUES ('test catalog 3', CAST('my text ...' AS VARBINARY(MAX)), newid());
/* vedo la tabella */
select * from dbo.catalog
Se volessimo scrivere due righe di codice (C#) e provare ad utilizzare l'API OpenSqlFilestream, potremmo fare un'applicazione console simile a:
using System;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.Win32.SafeHandles;
namespace consoleFileStreamSQL2008Test
{
class Program
{
const int blockSize = 1024 * 512;
static void Main()
{
/* !!! impostazione parametri per i file !!! */
String filePath = @"C:\test\";
String fileName = @"immagine.jpg";
/* !!! impostazione parametri per i file !!! */
/* !!! impostazione parametri per db !!! */
SqlConnectionStringBuilder mySQLConnectionString = new SqlConnectionStringBuilder();
mySQLConnectionString["Data Source"] = "TORONTO\\SQL008";
mySQLConnectionString["Integrated Security"] = true;
mySQLConnectionString["Initial Catalog"] = "myDocumentDB";
/* !!! impostazione parametri per db !!! */
Console.WriteLine("Apertura connessione SQL Server");
SqlConnection mySQLConnection = new SqlConnection(mySQLConnectionString.ConnectionString);
mySQLConnection.Open();
Console.WriteLine("Apertura transazione");
/* Start della transazione */
SqlTransaction mySQLTransaction = mySQLConnection.BeginTransaction();
/* Creo il GUID che verrà utilizzato per l'insert */
Guid catalogID = Guid.NewGuid();
Console.WriteLine("GUID: " + catalogID.ToString());
/* Inserisco una riga */
Console.WriteLine("Insert riga");
SqlCommand cmd = new SqlCommand("INSERT dbo.catalog (catalogName, catalogID, catalog) VALUES( @fileName, @catalogID, CAST('.' AS VARBINARY(MAX)));", mySQLConnection, mySQLTransaction);
cmd.Parameters.Add("@fileName", SqlDbType.VarChar, 100).Value = fileName;
cmd.Parameters.Add("@catalogID", SqlDbType.UniqueIdentifier).Value = catalogID;
cmd.ExecuteNonQuery();
cmd = new SqlCommand("SELECT catalog.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(), catalogName FROM dbo.catalog WHERE [catalogID] = @catalogID;", mySQLConnection, mySQLTransaction);
cmd.Parameters.Add("@catalogID", SqlDbType.UniqueIdentifier).Value = catalogID;
SqlDataReader dr;
dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
dr.Read();
SqlString sqlFilePath = dr.GetSqlString(0);
SqlBinary transactionToken = dr.GetSqlBinary(1);
SqlString catalogName = dr.GetSqlString(2);
dr.Close();
/* Recupero un file handle per poter scrivere il filestream */
SafeFileHandle handle = OpenSqlFilestream(
sqlFilePath.Value,
DESIRED_ACCESS_WRITE,
0,
transactionToken.Value,
(UInt32)transactionToken.Value.Length,
new LARGE_INTEGER_SQL(0));
/* Apertura dello stream - destinazione e sorgente */
FileStream destBlob = new FileStream(handle, FileAccess.Write);
FileStream sourceFile = new FileStream(String.Concat(filePath, fileName), FileMode.Open, FileAccess.Read);
byte[] buffer = new byte[blockSize];
int bytesRead;
while ((bytesRead = sourceFile.Read(buffer, 0, buffer.Length)) > 0)
{
destBlob.Write(buffer, 0, bytesRead);
}
/* Commit transazione e chiusura */
Console.WriteLine("Commit e chiusure");
destBlob.Close();
sourceFile.Close();
mySQLTransaction.Commit();
mySQLConnection.Close();
Console.WriteLine("Operazioni terminate.");
Console.ReadLine();
}
public const UInt32 DESIRED_ACCESS_READ = 0x00000000;
public const UInt32 DESIRED_ACCESS_WRITE = 0x00000001;
public const UInt32 DESIRED_ACCESS_READWRITE = 0x00000002;
[DllImport("sqlncli10.dll", SetLastError = true, CharSet = CharSet.Unicode)]
public static extern SafeFileHandle OpenSqlFilestream(
string FilestreamPath,
UInt32 DesiredAccess,
UInt32 OpenOptions,
byte[] FilestreamTransactionContext,
UInt32 FilestreamTransactionContextLength,
LARGE_INTEGER_SQL AllocationSize);
[StructLayout(LayoutKind.Sequential)]
public struct LARGE_INTEGER_SQL
{
public Int64 QuadPart;
public LARGE_INTEGER_SQL(Int64 quadPart) { QuadPart = quadPart; }
}
[DllImport("kernel32.dll", SetLastError = true)]
public static extern UInt32 GetLastError();
}
}
Curiosità: ad oggi, tramite la stored procedure sp_helpdb il filegroup relativo al FILESTREAM non è ancora visibile...
Ultima cosa: su sistemi operativi Windows Vista o Windows XP Service Pack 2, se si utilizza il livello di accesso 2 or 3, la porta SMB (server message block) deve essere aperta nel firewall. Vedi "sp_filestream_configure" nei Books Online di SQL Server 2008.
Come al solito tenete d'occhio codeplex, ad esempio qui.