Wstęp

Microsoft wraz z kolejną odsłoną swojego produktu bazodanowego wprowadza szereg ułatwień i unowocześnień. Jednym z nich jest FILETABLE , czyli mechanizm pozwalający na przechowywanie plików binarnych w specjalnie do tego celu przeznaczonych tabelach w taki sposób by można było uzyskać do nich dostęp zarówno z poziomu T-SQL jak i WINDOWS.

SQL Server 2008 posiadał możliwość przechowywania danych poza plikiem bazy. Opcja ta została nazwana FILESTREAM i pozwalała na zapis wartości binarnych bezpośrednio w systemie plików NTFS. Dostęp z poziomu systemu operacyjnego do tych wartości był jednak utrudniony. SQL Server 2012 wprowadza w tym zakresie znaczne udogodnienie - FILETABLE.

Mechanizm FILETABLE jest zbudowany na bazie FILESTREAM i umożliwia udostępnianie danych binarnych za pomocą Windows API w sposób pozwalający na:

• Utworzenie przestrzeni nazw dla przechowywanych plików
• Wstawianie, uaktualnianie i usuwanie plików za pomocą mechanizmów windowsowych
• Przechowywanie atrybutów plików, takich jak np. data utworzenia czy modyfikacji

Aby zobrazować działanie FILETABLE z punktu widzenia sytemu operacyjnego zamieszczam zrzut ekranu ukazujący ten mechanizm podczas pracy:

Jak widać, dostęp do przechowywanych w ten sposób dokumentów jest łudząco podobny w działaniu do serwera plików udostępniającego swoją zawartość.

Z poziomu SQL Server oba pliki są widoczne jako rekordy w specjalnie do tego celu utworzonej tabeli:

 Wymagania wstępne

 Zanim będzie możliwe skorzystanie z FILETABLE należy spełnić następujące wymagania:

• włączyć FILESTRAM dla instancji
• stworzyć FILESTREAM FILEGROUP  dla wybranej bazy danych
• ustawić odpowiedni poziom dla nietransakcyjnego dostępu do danych
• ustawić nazwę lokalizacji dostępowej do danych FILETABLE z poziomu windows

Włączenie FILESTREAM dla instancji

Zacznijmy od włączenia FILESTRAM dla instancji. Aby tego dokonać należy przejść do narzędzia  SQL Server Configuration Manager znajdującego się w Menu Start\Microsoft SQL Server Denali\ Configuration Tools.

Po włączeniu Configuration Manager-a powinno się zaznaczyć wybraną usługę SQL Server, rozwinąć menu kontekstowe dostępne pod prawym przyciskiem myszki oraz wybrać opcję "Właściwości".

 W tym momencie powinno pojawić się nowe okno z dostępnymi opcjami ustawień, gdzie należy wybrać zakładkę "FILESTREAM". W zakładce tej należy włączyć dostęp do danych FILESTREAM z poziomu T-SQL jak oraz systemu wejścia/wyjścia systemu operacyjnego, określić nazwę udziału windowsowego oraz pozwolić zdalnym klientom na dostęp do danych. Okno z zaznaczonymi opcjami przedstawiam poniżej:

Ostatnim krokiem jest przejście do SQL Server Management Studio i włączenie dostępu do FILESTREAM wewnątrz usługi SQL za pomocą następującego kodu:

 EXEC sp_configure 'filestream access level', 2

RECONFIGURE

Po dokonaniu wymaganych ustawień należy zrestartować usługę SQL Server.

 Stworzenie FILESTREAM FILEGROUP  dla wybranej bazy danych

Po skonfigurowaniu instancji należy utworzyć FILESTEAM FILEGROUP w bazie danych, która będzie korzystać z mechanizmu FILETABLE, oraz określić miejsce przechowywania plików na dysku twardym serwera z zainstalowaną usługą SQL. W przypadku, gdy chcemy użyć FILETABLE w nowej bazie danych możemy stworzyć tę bazę wraz z odpowiednią grupą plików FILESTREAM za pomocą kodu podobnego do poniższego: 

CREATE DATABASE FileTableDatabase
ON PRIMARY (
       NAME = 'FileTableDatabase_prim',
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.CTP3\MSSQL\DATA\FileTableDatabase_prim.mdf',
       SIZE = 250,
       FILEGROWTH = 250
), FILEGROUP FG_FILESTREAM CONTAINS FILESTREAM (

       NAME = 'FileTableDatabase_filestream',
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.CTP3\MSSQL\DATA\FileTableDatabase_filestream'
) LOG ON (
       NAME = 'FileTableDatabase_log',
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.CTP3\MSSQL\DATA\FileTableDatabase_log.ldf',
       SIZE = 250,
       FILEGROWTH = 250
)

Dla istniejącej bazy danych kod tworzący FILESTREAM FILEGROUP oraz określający miejsce przechowywania plików binarnych będzie wyglądał następująco:

ALTER DATABASE IstniejacaBaza
ADD FILEGROUP FG_FILESTREAM CONTAINS FILESTREAM
GO

ALTER DATABASE IstniejacaBaza
ADD FILE (
       NAME = 'IstniejacaBaza_filestream',
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.CTP3\MSSQL\DATA\IstniejacaBaza'
) TO FILEGROUP FG_FILESTREAM
GO

Ustawienie odpowiedniego poziomu nietransakcyjnego dostępu do danych

Jak pokazano we wstępie do tego artykułu mechanizm FILETABLE pozwala na łatwy dostęp do informacji binarnych przechowywanych w bazie danych za pomocą mechanizmów windowsowych. Zanim zaczniemy używać tej możliwości powinniśmy określić zasady tego dostępu.

SQL Server posiada trzy opcje konfiguracyjne w tym zakresie:

• brak dostępu (NONE)
• dostęp tylko do odczytu (READ_ONLY)
• dostęp do odczytu i zapisu (FULL)

Kod T-SQL umożliwiający pełen dostęp do zasobów za pomocą systemu operacyjnego będzie wyglądał następująco: 

ALTER DATABASE FileTableDatabase
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL)

 Ustawienie nazwy lokalizacji dostępowej do danych FILETABLE

Ostatnim z wymagań jest ustawienie lokalizacji dostępowej WINDOWS do danych zawartych w FILETABLE. Jednym ze sposobów spełnienia tego wymogu jest napisanie instrukcji T-SQL  wyglądającej podobnie do kodu przedstawionego poniżej: 

ALTER DATABASE FileTableDatabase
SET FILESTREAM (DIRECTORY_NAME = 'ZasobFilestream')

Użycie mechanizmu FILETABLE

Po spełnieniu wszystkich wymagań wstępnych skorzystanie z mechanizmu FILETABLE polega na utworzeniu odpowiedniej tabeli oraz używaniu jej za pomocą konstrukcji języka T-SQL lub mechanizmów windowsowych.

Stworzenie tabeli przechowującej pliki

Utworzenie odpowiedniej tabeli polega na napisaniu instrukcji CREATE TABLE pozbawionej definicji kolumn i ograniczeń wraz z dodaną klauzulą AS FILETABLE. Przykładowy kod będzie wyglądać w następujący sposób:

CREATE TABLE dbo.TabelaFileTable
AS FILETABLE

Tabela ta posiada z góry ustaloną ilość kolumn, ich nazwy oraz typy i ograniczenia, tak więc nie musieliśmy tych danych umieszczać w instrukcji CREATE. Jeżeli rozwiniemy utworzoną tabelę  w okienku  Obejct Explorer SQL Server Management Studio lub odpytamy odpowiedni widok katalogowy to zauważymy, że kolumn tych jest dokładnie siedemnaście.

Użycie FILETABLE z poziomu T-SQL

Korzystanie z możliwości FILETABLE polega wpisywaniu, aktualizowaniu i kasowaniu rekordów we wcześniej stworzonej tabeli.

Przykładowy kod wstawiający nowy wiersz wygląda następująco:

INSERT INTO dbo.TabelaFileTable(file_stream, name)
VALUES (CAST('Ala ma kota' AS varbinary(max)), 'przykład.txt')

Wraz z wprowadzeniem FILETABLE zostały stworzone odpowiednie widoki, procedury i funkcje T-SQL, które pozwalają nam zarządzać przechowywanymi w ten sposób informacjami. Jedną z takich funkcji jest FileTableRootPath, która zwraca ścieżkę UNC wybranej tabeli. Dzięki niej można łatwo określić jaki adres należy wpisać w Exploratorze Windows by odnieść się do zapisanych danych z poziomu systemu operacyjnego. Przykład użycia tej funkcji przedstawiam poniżej:

SELECT FiletablerootPath('dbo.TabelaFileTable')

W przypadku zadeklarowanej tabeli lokalizacja UNC, która jest  z nią związana ma następującą postać: \\test-PC\CTP3\ZasobFilestream\TabelaFileTable

Użycie FILETABLE z poziomu systemu operacyjnego

Dostęp za pomocą mechanizmów WINDOWS do danych zapisanych w tabeli używającej FIELTABLE polega na otworzeniu lokalizacji sieciowej i w zależności od ustawionej opcji  dostępu nietransakcyjnego przeczytaniu, edycji, wstawianiu lub kasowaniu wybranych plików za pomocą ogólnodostępnych narzędzi. Jak widać na załączonym poniżej obrazku, wstawienie rekordu z poziomu bazy poskutkowało udostępnieniem pliku o odpowiedniej nazwie w zadeklarowanej lokalizacji sieciowej.

Plik ten możemy edytować np. za pomocą narzędzia wordpad.

Podsumowanie

Powyższy artykuł przybliża pokrótce mechanizm FILETABLE, który zostanie wprowadzony wraz z nadejściem SQL Server 2012. Pokazuje on jego możliwości oraz przedstawia w jaki sposób można z niego skorzystać.

Dalsze źródła informacji: http://msdn.microsoft.com/en-us/library/ff929144(v=sql.110).aspx