FileTable in SQL Server “Denali”–Dateizugriff auf SQL Server Filestream Daten von jeder Anwendung

Eines der spannenden neuen Features von SQL Server 2008 war Filestream – die Möglichkeit, BLOBs wie Dokumente, Videos oder Bilder unter SQL Server Kontrolle im Dateisystem abzulegen. Mit Filestream kann man gleichzeitig die Datenbankdateien klein halten, die Performance beim Zugriff auf große BLOBs erhöhen und volle Konsistenz zwischen BLOB-Daten und relationalen Daten gewährleisten, sowohl in Transaktionen als auch im Backup.

Der offensichtliche Nachteil von Filestream in SQL Server 2008 ist aber, dass man mit normalen Anwendungen, die nichts von SQL Server wissen nicht darauf zugreifen kann. Der Zugriff auf Filestream BLOB-Daten musste entweder über T-SQL oder über ein spezielles WIN32 oder .NET API erfolgen. Viele Kunden haben daher nach einer Lösung gefragt, die traditionellen Dateizugriff aus Anwendungen wie Word oder dem Windows Explorer mit SQL Server Features wie vollständige Backups und Volltextsuche kombiniert. Daher gibt es in SQL Server Denali das neue Konzept “Filetable”

Eine Filetable ist kurz gesagt eine Tabelle mit einem festen Schema, die Dokumente speichert, welche über normalen Windows-Dateizugriff von jedem Windows-Programm aus zugegriffen werden können. Dieselben Dokumente sind also einerseits aus T-SQL als Zeilen einer Tabelle verfügbar, andererseits aus dem Dateisystem als normale Dateien.

Wenn man Filetables nutzen will muss man zuerst wie gewohnt Filestream aktivieren und eine Filestream-Dateigruppe in der Datenbank anlegen. Diese Filestream-Dateigruppe zeigt auf ein Verzeichnis und legt den physischen Speicherplatz für die Dateien der Filetable fest – nicht jedoch den logischen, über den später aus dem Explorer zugegriffen wird.

Als nächstes muss man in der Datenbank “nicht-transaktionalen Zugriff” zulassen, entweder komplett oder read only. Das geht in T-SQL:

ALTER DATABASE SemanticSearch
       SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'SematicsDir')

oder in der Oberfläche:

Die Bezeichnung “nicht-transaktionaler Zugriff” besagt schon worum es geht: Alle Zugriffe auf die Filetable, die aus dem Dateisystem erfolgen (über den Explorer oder über Anwendungen) sind nicht transaktional. Sie können also weder durch ein Rollback rückgängig gemacht werden noch ist ein exakt konsistentes Point-in-Time Restore garantiert. Wichtig ist: Auch bei aktiviertem nicht-transaktionalen Zugriff gilt das nur, wenn die Daten über den Windows Fileshare zugegriffen werden. Jeglicher Zugriff über T-SQL oder das Filestream API ist voll transaktional.

Nun kann man Filetables anlegen. Das geht derzeit nur in T-SQL (wobei Management Studio eine Vorlage ausgibt bei der man nur noch die Parameter ausfüllen muss):

CREATE TABLE dbo.MeineFiletable AS FILETABLE

  WITH

  (

    FILETABLE_DIRECTORY = 'MeineFTDir',

    FILETABLE_COLLATE_FILENAME = database_default

  )

GO

Wie man sieht ist kein Schema der Tabelle angegeben. Das Schema einer Filetable ist fest, es können weder Spalten hinzugefügt noch geändert oder gelöscht werden. Aber es können zum Beispiel Trigger hinzugefügt werden – und Volltextindizes, aber dazu mehr im nächsten Artikel. Im Filetable Schema sind die Windows Dateieigenschaften (Readonly, Archiv usw) als Spalten vorhanden. Der Pfad (relativ zum Wurzelverzeichnis, das \\maschinenname\instanzname\DIRECTORY_NAME\FILETABLE_DIRECTORY ist) findet sich im Feld path_locator (Typ Hierarchyid), die eigentliche Datei im Feld file_stream. Im Management Studio befinden sich Filetables in einem eigenen Ordner in der Datenbank:

Von hier aus kann man auch einfach den Dateisystem-Ordner öffnen:

In diesen Ordner kann man nun ganz normal Dateien hineinkopieren, sie bearbeiten, löschen usw. Aus T-SQL Sicht sieht jede Datei und jeder Unterordner aus wie eine Zeile:

SELECT name, file_stream.GetFileNamespacePath(), is_directory FROM MeineFiletable

Den vollständigen Pfad der Dateien erhält man so:

SELECT FileTableRootPath() + file_stream.GetFileNamespacePath() AS FilePath FROM MeineFiletable

Man kann auch über T-SQL Die Einträge bearbeiten, z.B. neue Verzeichnisse anlegen oder Dateien Read Only setzen:

INSERT INTO MeineFiletable(name, is_directory) VALUES ( 'Analysis Services', 1)

UPDATE MeineFiletable
SET is_readonly = 1 WHERE name = 'QuoVadis.pptx'

Zum Verschieben von Dateien muss man sich aber schon ein wenig mit Hierarchyid-Funktionen auskennen (Danke an Georg Urban):

UPDATE MeineFiletable

SET path_locator = path_locator.GetReparentedValue( path_locator.GetAncestor(1),

       (SELECT path_locator FROM MeineFiletable WHERE name = 'Cloud' AND is_directory = 1))

WHERE (name = 'QuoVadis.pptx')

Und natürlich kann man auch die eigentliche Datei aus T-SQL bearbeiten.

Insgesamt bieten Filetables in SQL Server “Denali” eine mächtige Kombination aus T-SQL und klassischem Dateisystem-Zugriff und erlauben die Nutzung von datenbankbasierten Dokumenten von jedem Windows-Programm aus. Filetables werden in der kommenden CTP von SQL Server “Denali” enthalten sein.

Gruß,
Steffen