Akademia SQL - Część 6: Atrybut FILESTREAM

Autor: Damian Widera

Opublikowano: 26 września 2008

Zawartość strony

	Atrybut FILESTREAM – informacje podstawowe
	Atrybut FILESTREAM – laboratorium
	Atrybut FILESTREAM – referencje

Atrybut FILESTREAM – informacje podstawowe

Znaczna część danych tworzonych obecnie, takich jak dokumenty, zdjęcia, pliki wideo, z punktu widzenia serwera baz danych ma niezorganizowaną strukturę.

Dane tego typu mogą być przechowywane na dwa sposoby:

1. Poza serwerem SQL zapisany jest plik a ścieżka (wskaźnik) zapisywany jest w tablicy,
2. W tablicy wewnątrz serwera SQL

Obydwa wymienione powyżej sposoby powodują jednak problemy nie tylko podczas administrowania serwerem, ale także dla użytkowników końcowych (aplikacji). W pierwszym przypadku, gdy dane są przechowywane poza serwerem baz danych, powstaje konieczność odpowiedniego zarządzania takimi obiektami. Nie ma w zasadzie żadnej możliwości, aby z poziomu serwera SQL kontrolować dostęp do tych plików, więc użytkownik może dowolnie zmieniać ich zawartość, a w najgorszym przypadku usunąć je. Kolejnym ważnym problemem jest wykonywanie kopii zapasowej bazy danych i odtwarzanie jej na innym serwerze, ponieważ wtedy administrator musi pamiętać o przeniesieniu plików na drugi serwer oraz utworzeniu odpowiednich ścieżek do nich, co nie zawsze jest możliwe. Pozostaje jeszcze do rozwiązania problem związany z równoczesnym dostępem wielu użytkowników do tego typu zasobu.

W drugim przypadku, czyli przechowując duże obiekty binarne w bazie danych, administrator ma do rozwiązania dwa problemy. Pierwszym z nich jest konieczność zarządzania obiektów, których rozmiar przekracza 2GB. Drugim istotnym czynnikiem będzie szybkość odczytu danych zawartych w dużym obiekcie binarnym, która jest bardzo mała w tej sytuacji.

Użycie atrybutu FILESTREAM rozwiązuje wszystkie wspomniane powyżej problemy. W serwerze SQL 2008 duże obiekty binarne mogą być przechowywane w tradycyjny sposób, jako dane typu varbinary(max) lub obiekty typu varbinary(max)FILESTREAM, co spowoduje zapisanie ich w systemie plików NTFS. Generalnie, o użyciu atrybutu FILESTREAM decyduje ilość danych, które należy przechować oraz sposób, w jaki będą używane. FILESTREAM powinien być zatem użyty w trzech przypadkach, gdy:

1. Przechowywany obiekt ma (średnio) powyżej 1MB
2. Istotny jest szybki odczyt danych
3. Środkowa warstwa aplikacji klienckiej, a więc warstwa logiki aplikacji w modelu trójwarstwowym, korzysta z dużych obiektów binarnych

Dla mniejszych obiektów przechowywanie ich w bazie danych w większości przypadków poprawia wydajność ich odczytu.

Konfiguracja obsługi funkcjonalności FILESTREAM sprowadza się do włączenia tej opcji dla instancji serwera baz danych. Można to zrobić na trzy sposoby:

1. W trakcie instalacji instancji serwera SQL 2008
2. Wykonując procedurę składowaną sp_configure
3. Zmieniając ustawienia na zakładce Advanced w oknie dialogowym właściwości serwera w konsoli SSMS

Po skonfigurowaniu instancji do pracy z funkcjonalnością FILESTREAM można przystąpić do konfigurowania bazy danych i tablic do użycia nowej funkcjonalności. Baza danych, która korzysta z funkcjonalności FILESTREAM, musi posiadać co najmniej jedną grupę plików, dla której zostanie określona klauzula CONTAINS FILESTREAM. Ta grupa plików posłuży jako kontener dla wszystkich dużych obiektów binarnych składowanych w bazie danych.

Istnieje możliwość nadania użytkownikowi odpowiednich uprawnień na poziomie dostępu do tabeli czy kolumny tak, aby mógł korzystać z funkcjonalności FILESTREAM. Jeżeli użytkownik uzyskał dostęp na poziomie tabeli, to może otwierać pliki zachowane w kolumnie z atrybutem FILESTREAM.

SQL Server 2008 ogranicza dostęp do plików także dla użytkowników systemu operacyjnego. Tylko konto, na którym uruchomiony została usługa SQL Server ma nadane prawa NTFS do kontenera danych. Rekomendacja firmy Microsoft określa, iż uprawnienia te nie powinny być przenoszone na innych użytkowników czy konta.

Dostęp do danych FILESTREAM, dla bazy danych będącej w trybie online, jest możliwy tylko z poziomu transakcji T-SQL lub API OpenSqlFilestream. Gdy baza danych zostaje zamknięta to katalog zawierający dane FILESTREAM przestaje być kontrolowany w sposób omówiony powyżej. Z tego powodu kontener danych powinien być dodatkowo zabezpieczony tak, aby nie była możliwa przypadkowa ingerencja użytkownika, co mogłoby spowodować utratę danych.

Nie jest możliwe użycie szyfrowania dla danych wykorzystujących atrybut FILESTREAM.

 Do początku strony

Atrybut FILESTREAM – laboratorium

Laboratorium składa się z dwóch zadań. Celem pierwszego z nich będzie odpowiednie skonfigurowanie instancji do pracy z atrybutem FILESTREAM. W drugim ćwiczeniu zostanie pokazany przykład, w którym wykonana zostanie baza danych oraz tablica zawierająca dane z atrybutem FILESTREAM.

Zadanie 1

  1.  Uruchom konsolę SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

  2.  W oknie eksploratora obiektów zaznacz serwer Evaluation i z menu kontekstowego wybierz opcję Properties, jak na poniższym rysunku:

  3.  W oknie właściwości serwera przejdź na zakładkę Advanced i w sekcji Filestream wybierz odpowiędnią opcję:

        a.    Disabled - ustawienie domyślne, po zainstalowaniu serwera SQL 2008 korzystanie z funkcjonalności FILESTREAM jest wyłączone

        b.    Transact-SQL access enabled - opcja oznacza, że dostęp do plików umieszczonych w systemie operacyjnych a zarządzanych przez SQL Server 2008 będzie możliwy tylko za pomocą kodu T_SQL

        c.    Full access enabled oznacza, że kontrolowanie plików będzie zarówno z poziomu bazy danych, jak i aplikacji klienckich, które będą wykorzystywały Win32 API - oczywiście pod warunkiem posiadania odpowiednich uprawnień.

Przykładowa konfiguracja została pokazana na rysunku poniżej.

  4.  Ustawienie konfiguracji serwera może być również wykonane przy pomocy procedury sp_configure. Wykonaj poniższy fragment kodu, wstawiając odpowiednia wartość parametru @p:

      0 — funkcjonalność FILESTREAM jest wyłączona

      1 — dostęp do plików jest tylko z poziomu T-SQL

      2 — dostęp jest zarówno z poziomu kodu T-SQL jak i za pomocą funkcji Win32 API.

DECLARE @p int

EXEC sp_configure filestream_access_level, @p

RECONFIGURE

GO

  5.  W celu sprawdzenia konfiguracji funkcjonalności FILESTREAM na serwerze bazy danych możesz wykonać jedno z dwóch zapytań:

    a)  wywołanie procedury sp_configure

EXEC sp_configure filestream_access_level

Wynik może być następujący:

name                                minimum     maximum     config_value run_value

----------------------------------- ----------- ----------- ------------ -----------

filestream access level             0           2           1            1

    b)  wywołanie zapytania, które sprawdza ustawienia za pomocą systemowej funkcji SERVERPROPERTY:

SELECT SERVERPROPERTY ('FilestreamShareName') as [Nazwa zasobu sieciowego]

      ,SERVERPROPERTY ('FilestreamConfiguredLevel') as  [Skonfigurowany poziom dostepu]

      ,SERVERPROPERTY ('FilestreamEffectiveLevel') as [Aktualny poziom dostepu]]

Wynik wykonania tego zapytania może być następujący:

Nazwa zasobu sieciowego  Skonfigurowany poziom dostepu  Aktualny poziom dostepu

-----------------------  -----------------------------  ------------------------ 

MSSQLSERVER              2                              1

Zwróć uwagę, że jeżeli aktualny poziom dostępu jest różny od skonfigurowanego poziomu dostępu to system zaproponuje restart usługi SQL Server.

Zadanie 2

W tym zadaniu zaprezentowany zostanie praktyczny sposób na korzystanie z funkcjonalności FILESTREAM. W tym celu wykonana zostanie baza danych oraz tablica zawierająca dane z atrybutem FILESTREAM.

  1.  Uruchom konsolę SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

  2.  Poniższy kod tworzy bazę danych udostępnioną dla funkcjonalności FILESTREAM. Zauważ, że baza danych posiada grupę plików z klauzulą CONTAINS FILESTREAM. Ta grupa plików jest kontenerem dla danych typu FILESTREAM.

use master

GO



CREATE DATABASE BazaDaychFileStream ON PRIMARY

  ( NAME = BazaDaychFileStream_data, 

    FILENAME = N'D:\TECHNET\BazaDaychFileStream_data.mdf',

    SIZE = 10MB,

    MAXSIZE = 50MB),

FILEGROUP GrupaFileStream CONTAINS FILESTREAM

  ( NAME = KatalogFileStream, 

    FILENAME = N'D:\TECHNET\FileStreamCatalog')

LOG ON 

  ( NAME = 'BazaDaychFileStream_log', 

    FILENAME = N'D:\TECHNET\BazaDaychFileStream_log.ldf',

    SIZE = 5MB, 

    MAXSIZE = 25MB, 

    FILEGROWTH = 5MB);

  3.  Dla grupy plików FILESTREAM atrybut FILENAME wskazuje miejsce, w którym będą przechowywane pliki. Ścieżka powyżej ostatniego folderu musi istnieć (D:\Technet), a ostatni folder (FileStreamKatalog) nie może z kolei istnieć w momencie tworzenia bazy danych. Systemowe katalogi, w których będą istniały dane FILESTREAM nazwane zostały kontenerami danych. Po utworzeniu bazy danych SQL Server utworzy także katalog FileStreamCatalog, w którym znajdzie się plik filestream.hdr oraz katalog $FSLOG. Plik filestream.hdr jest ważnym plikiem systemowym i nie powinno się go otwierać i modyfikować.

  4.  Każda baza danych, która posiada grupę plików utworzoną dla funkcjonalności FILESTREAM, może zawierać tablice, które będą korzystały z tej funkcjonalności. W tablicy, w której planujemy skorzystać z możliwości określonych przez atrybut FILESTREAM należy:

    a.  utworzyć kolumnę typu varbinary(max) z atrybutem FILESTREAM

    b.  utworzyć kolumnę typu RUWGUIDCOL, co pozwala jednoznacznie identyfikować wiersze.

Przykład ilustrujący utworzenie tabeli, w której przechowywane będą duże obiekty binarne znajduje się poniżej:

USE BazaDaychFileStream;

GO

IF OBJECT_ID('dbo.autorzy', 'U') IS NOT NULL

  DROP TABLE dbo.autorzy;

GO

CREATE TABLE dbo.autorzy

(

    id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, 

    Imie varchar(25),

    Nazwisko varchar(25),

    Bio varbinary(max) FILESTREAM

);

  5.  Po utworzeniu odpowiedniej tabeli można rozpocząć wstawianie wierszy. W przypadku wykonywania operacji INSERT do kolumny FILESTREAM można wstawić wartość NULL bądź varbinary(MAX). W pierwszym przypadku w systemie plików nie zostanie utworzony nowy plik.

INSERT INTO dbo.autorzy 

VALUES(NEWID(),'Adam','Mickiewicz',NULL)

Można również wstawić łańcuch o zerowej długości, co jest przydatne do uzyskania uchwytu do pliku z poziomu aplikacji klienckiej używającej Win32 API, o czym napisaliśmy w dalszej części tego rozdziału.

INSERT INTO dbo.autorzy 

VALUES(NEWID(),'Juliusz','Slowacki',CAST('' as varbinary(max)))

Ostatni przykład pokazuje, w jaki sposób stworzyć plik z danymi. Silnik bazy danych zamienia podany łańcuch tekstowy na typ varbinary(max), tworzy plik (o ile jest potrzebny) i wstawia do niego dane.

INSERT INTO dbo.autorzy 

VALUES(NEWID(),'Jan','Kochanowski',CAST('tutaj moze byc zdjecie poety lub jego wiersze' as varbinary(max)))

  6.  Kolumna zawierająca dane przechowywane w systemie plików może być aktualizowana poleceniem UPDATE. Należy jednak pamiętać, iż nie jest to wskazana operacja w przypadku dużej ilości danych.

UPDATE dbo.autorzy

SET Bio = CAST('biografia wieszcza' as varbinary(max))

WHERE Nazwisko = 'Mickiewicz'

  7.  Usuwanie wiersza z tablicy powoduje również usunięcie pliku. Jest to również jedyny sposób umożliwiający usunięcie pliku, do którego dostęp jest kontrolowany przez serwer baz danych.

DELETE FROM dbo.autorzy

WHERE Nazwisko = 'Slowacki'

  8.  Przy pobieraniu danych z kolumny FILESTREAM należy pamiętać, iż trzeba za każdym razem pobrać kolumnę posiadającą atrybut ROWGUIDCOL, co pozwala na deterministyczne zidentyfikowanie każdego wiersza, jak pokazano na przykładzie poniżej.

SELECT Nazwisko, Bio, id

FROM dbo.autorzy

Poniższy przykład pobrania danych zakończy się niepowodzeniem, ponieważ w zapytaniu nie ma kolumny ID, która zawiera atrybut ROWGUIDCOL.

SELECT Nazwisko, Bio

FROM dbo.autorzy

 Do początku strony

Atrybut FILESTREAM – referencje

Dodatkowe informacje na temat atrybutu FILESTREAM zdarzeń można znaleźć w Internecie:

[1] SQL Server 2008 – FILESTREAM, czyli przechowywanie dużych obiektów binarnych (BLOB) w systemie plików

[2] Co nowego w silniku bazodanowym SQL Server 2008 November CTP

[3] Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008

[4] White Paper: Managing Unstructured Data with SQL Server 2008

[5] Getting Traction with SQL Server 2008 Filestream - blog Roberta Doherty

Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”. Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.

Do początku strony