Na potrzeby tego przykładu załóżmy, że budujemy witrynę, która będzie przechowywać dużą liczbę obrazów. Aby zoptymalizować przechowywanie danych, wykorzystany będzie mechanizm FileStream, który pozwala by dane przechowywane w polach typu varbinary(max) były fizycznie zapisywane w postaci pliku na dysku. Warto dodać, że jest to chyba najwygodniejszy sposób przechowywania danych binarnych w SQL Server.
Odblokowanie Filestream
W trakcie instalacji serwera SQL na jednej z zakładek można wybrać czy domyślnie FileStream będzie udostępniony (i w jaki sposób). Po zainstalowaniu można włączać/wyłączać ten mechanizm używając procedury składowanej sp_configure, o następującej składni:
EXEC sp_configure 'filestream_access_level', '[poziom]'
RECONFIGURE
Parametr poziom określa sposób dostępu:
|
0
|
Zablokowane. Wartość domyślna.
|
|
1
|
Odblokowane. Dostęp tylko przez T-SQL.
|
|
2
|
Odblokowane. Dostęp przez T-SQL i przez system plików
|
Aby zobaczyć czy mechanizm FileStream jest odblokowany, można uruchomić konsole i wykonać polecenie NET SHARE. Polecenie NET SHARE pozwala podejrzeć udziały udostępniane na danej maszynie – w tym udziały wynikające z używania FileStream:
Nazwę udziału można także sprawdzić odwołując się do ustawień serwera:
SELECT SERVERPROPERTY ('FilestreamShareName')
Opcje można także ustawić z poziomu Sql Server Configuration Manager, wchodząc we właściwości danej instancji SQL Server i wybierając zakładkę FILESTREAM:
Deklarowanie bazy danych
Tworząc bazę danych, która będzie wykorzystywała mechanizm FileStream należy założyć oddzielną grupę plików (FileGroup) przeznaczonego dla tego typu informacji. Potem w ramach takiego FileGroup definiuje się konkretny plik – w tym przypadku folder na dysku.
CREATE DATABASE [Pictures_FileStream] ON PRIMARY
( NAME = N'Pictures_FileStream', FILENAME = N'C:\SQLEXPRESS\Pictures_FileStream.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [FSFileGroup] CONTAINS FILESTREAM DEFAULT
( NAME = N'Pictures_FileStreamFS', FILENAME = N'C:\SQLEXPRESS\Pictures_FileStreamFS' )
LOG ON
( NAME = N'Pictures_FileStream_log', FILENAME = N'C:\SQLEXPRESS\Pictures_FileStream_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
Oczywiście tych plików (czy grup plików) może być więcej niż jeden – w ten sposób (analogicznie jak przy tworzeniu np. partycji) można rozkładać fizyczne obciążenie pomiędzy różne dyski / macierze itp. Oczywiście – wygodnie jest by jedna z grup była grupą "domyślną".
Tworząc tabelę, należy także pamiętać, że jedną z kolumn musi być typ uniqueidentifier. Kolumnę należy dodatkowo oznaczyć atrybutem ROWGUIDCOL. Może ona być także kluczem głównym w tabeli, ale nie musi. Na przykład, tabela może mieć postać:
CREATE TABLE [dbo].[Pictures](
[gid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[picture] [varbinary](max) FILESTREAM NULL,
CONSTRAINT [PK_Pictures] PRIMARY KEY CLUSTERED
(
[gid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Ogólny schemat operacji
Na wstępie warto pamiętać, że wszystkie "klasyczne" operacje T-SQL używane do pracy z typami BLOB mogą być także stosowane do pracy z danymi w FileStream (także te opisane w tym poście). Z tego punktu widzenia, poza sposobem przechowywania niczym nie różni się on od normalnego varchar(max). Ale – można wykorzystać
Procedura zapisu danych ze strony aplikacji klienckiej musi składać się z kilku etapów:
- Otworzenie transakcji (READ_COMMITED)
- Stworzenie pustego wpisu (by powstał plik na dysku)
- Wykonanie operacji na bazie (np. zapis metadanych)
- Pobranie ścieżki do pliku – metoda .PathName() (na przykład picture.PathName())
- Pobranie kontekstu transakcyjnego – funkcja GET_FILESTREAM_TRANSACTION_CONTEXT() (wszystkie operacje na FileStream są wykonywane w kontekście transakcyjnym – za spójność odpowiada motor bazodanowy)
- Utworzenie obiektu SqlFileStream z odpowiednimi opcjami (do odczytu, zapisu albo równocześnie do odczytu/zapisu). Przy tworzeniu obiektu trzeba przekazać ścieżkę oraz kontekst transakcyjny.
- Odczyt/zapis danych z/do strumienia
- Zamknięcie transakcji
Wygodnie jest zdefiniować kilka procedur pomocniczych. Dla naszego przykładu z obrazkami będą to dwie procedury:
- procedura dodająca nowy "pusty" rysunek i zwracająca jego identyfikator (tu – GUID):
CREATE PROCEDURE [dbo].[spAddEmptyPicture]
AS
BEGIN
SET NOCOUNT ON;
declare @gid as uniqueidentifier = newid()
INSERT INTO Pictures (gid,picture)
VALUES
(@gid,cast('' as varbinary(max)))
select @gid
END
- procedura zwracająca ścieżkę do pliku oraz kontekst transakcyjny dla danego identyfikatora:
CREATE PROCEDURE [dbo].[tkGetPathForPictureGid]
@gid uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
select picture.PathName(),GET_FILESTREAM_TRANSACTION_CONTEXT()
from Pictures where gid=@gid
END
GO
Zapis i odczyt danych
Załóżmy, że do wysłania pliku na serwer wykorzystujemy zwykłą kontrolkę FileUpload z ASP.NET
[…]
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Wyślij do bazy"
onclick="Button1_Click" /></div>
<asp:HyperLink ID="MyHyperlink" runat="server">LinkDoPliku</asp:HyperLink>
</form>
Wtedy, sama operacja zapisu ma postać:
protected void Button1_Click(object sender, EventArgs e) {
Database db = DatabaseFactory.CreateDatabase();
DbConnection cnn = db.CreateConnection();
cnn.Open();
DbTransaction tx = cnn.BeginTransaction();
Guid gid = (Guid)db.ExecuteScalar(tx,"spAddEmptyPicture");
IDataReader dr = db.ExecuteReader(tx, "spGetPathForPictureGid", gid);
dr.Read();
string path = dr.GetString(0);
SqlFileStream fs = new SqlFileStream(path, (byte[])dr.GetValue(1), FileAccess.Write);
fs.Write(FileUpload1.FileBytes, 0, FileUpload1.FileBytes.Length);
fs.Close();
dr.Close();
tx.Commit();
cnn.Close();
MyHyperlink.NavigateUrl = "file.axd?gid=" + gid.ToString();
}
W nawiasach transakcyjnych dodajemy pusty rysunek, pobieramy ścieżkę i kontekst transakcyjny, po czym zapisujemy plik i potwierdzamy transakcję. Na koniec ustawiany jest link pozwalający podejrzeć dodany rysunek.
Analogicznie należy postępować przy odczycie informacji. W tym przypadku zdefiniowana jest dodatkowa biblioteka (HandlerLib) która zawiera zaimplementowany IHttpHandler "zwracający" obrazek.
public class FileHandlerSqlStream : IHttpHandler {
const int BufferSize = 1024;
public bool IsReusable { { return false; } }
public void ProcessRequest(HttpContext context) {
Guid gid = new Guid(context.Request.QueryString["GID"].ToString());
context.Response.Clear();
context.Response.ContentType = "image/JPEG";
Database db = DatabaseFactory.CreateDatabase();
DbConnection cnn = db.CreateConnection();
cnn.Open();
DbTransaction tx = cnn.BeginTransaction();
IDataReader dr = db.ExecuteReader(tx, "spGetPathForPictureGid", gid);
dr.Read();
string path = dr.GetString(0);
SqlFileStream fs = new SqlFileStream(path, (byte[])dr.GetValue(1), FileAccess.Read);
byte[] buf = new byte[BufferSize];
int read = 0;
while ((read = fs.Read(buf, 0, BufferSize)) > 0) {
context.Response.OutputStream.Write(buf, 0, read);
}
fs.Close();
dr.Close();
cnn.Close();
}
}
Po pobraniu z kontekstu http identyfikatora (GUID), który wskazuje, jaki plik ma odczytany, pobierana jest ścieżka i kontekst transakcyjny. Pozostałe operacje to po prostu odczyt danych ze strumienia i zapis do Response.OutputStream (by dane zostały wysłane do przeglądarki klienta).
Uwaga! Nie należy zapomnieć o rejestracji handlera, tu pod nazwą file.axd (klient chcąc zobaczyć obrazek, wchodzi na adres URL […]\file.axd?GID=<tu guid>) :
<httpHandlers>
[…]
<add verb="*" path="file.axd" type="HandlerLib.FileHandlerSqlStream,HandlerLib"/>
</httpHandlers>
Oczywiście, zamiast SqlFileStream można skorzystać z funkcji natywnej OpenSqlFilestream i potem przekazać uchwyt (typ Handle WinAPI) do np. API.NET FileStream. Dokładnie to samo robi używana tutaj klasa SqlFileStream.
Backup
Wykonanie kopii zapasowej bazy wykorzystującej FileStream nie różni się niczym od "normalnego" backupu SQL Server.
BACKUP DATABASE [Pictures_FileStream] TO DISK = N'[…]Pictures_FileStream.bak' WITH NOFORMAT, NOINIT, NAME = N'Pictures_FileStream-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Odzyskiwanie:
RESTORE DATABASE [Pictures_FileStream] FROM DISK = N'[…]Pictures_FileStream.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
Jeżeli spróbujemy odzyskać poszczególne FileGroup, to po wejściu w Restore Files and FileGroup zobaczymy (w tym przypadku) 2 strumienie:
Inne informacje
Chcą przeczytać więcej o Filestream, warto sięgnąć do publikacji FILESTREAM Storage in SQL Server 2008, autorstwa Paul S. Randal (SQLskills.com)) opublikowanej na stronach MSDN.
Sql Server 2008 Express Edition pozwala wykorzystać mechanism FileStream a dane przechowywane w tej postaci nie "wliczają się" do limitu 4GB / bazę danych.
Wydajność FileStream przeanalizowali pracownicy SQL Skills. Wyniki badań zostały opublikowane pod tym adresem: http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-FILESTREAM-performance.aspx. Warto zwrócić uwagę na jeden aspekt – dla małych obiektów (w przypadku tego badania – do ok. 1MB) operacje "klasyczne", wykorzystujące API T-SQL były szybsze niż manipulacja przy użyciu strumieni. Dodatkowo, w każdym przypadku klasyczny sposób przechowywania BLOB-ów, w plikach mdf, był wolniejszy niż FileStream.
Przykład jest dostępny do ściągnięcia tutaj:
BlobInFileStream.zip (622,89 kb)
Własny typ .NET
Na koniec warto chwilę się zastanowić – czy nie opłaca się definiować "dużych" typów .NET do przechowywania BLOB-ów. Tu warto podkreślić, że zaletą tej technologii jest to, że można po stronie serwera SQL pracować z metodami i funkcjami .NET na danych. Czyli można napisać typ, który np. opakuje tablicę i pozwoli z poziomu T-SQL odwołać się do poszczególnych składników (np. bajtów). Ale – już operacje po stronie klienckiej wymagają pobrania (a potem aktualizacji) całego pola – co zwykle nie będzie rozwiązaniem optymalnym.