SQL Server 2012 – Filestreaming und Filetables

In SQL Server 2008 stand erstmals das Feature Filestreaming zur Verfügung. Die Idee, Dateien im SQL Server abzulegen ist nicht alt, wurde aber nur ungern verwendet, da der Performanceverlust gegenüber dem Dateisystem schwerwiegender war, als die vereinfachte Verwaltung. (Backup, Security usw..alles aus einem Guß). Mit SQL Server 2008 wurde die alte Idee “WinFS” weiterentwickelt. Dateien können über Filestreaming Protokoll bezogen werden, Dokumente landen nicht in den Datenbankdateien, sondern auf dem Datenträger und dennoch bleiben diese integraler Bestandteil der Datenbank. Der Verwaltungsaufwand ist somit Aufgabe des SQL Administrators.

Die Optionen in SQL 2008 sahen schon recht vielversprechend aus: Windows Freigabe und Zugriff für Remoteclients erlauben. Jedoch lag man mir der Annahme falsch, dass auf diese Freigaben einfach mal so zugegriffen werden konnte. Diese Option steht lediglich per T-SQL oder Win32 Api zur Verfügung. Kurz gesagt: Nur Entwickler können Lösungen für das Filestreaming zur Verfügung stellen.  Mit SQL Server 2012 wurde nun diese Idee weiterverfolgt und vollendet. Die Freigaben sind nun tatsächlich mittels Windows Freigaben per Dateimanager erreichbar – ohne auch nur zu ahnen, ein SQL Server diese zur Verfügung stellt. Genau das ist die Funktion des neuen SQL Server 2012 Features Filetable.

 

Was muss man dazu tun..?

 

1. Aktivieren des Filestreaming

Das Filestreaming läßt sich entweder bereits beim Setup aktivieren oder auch später im SQL Server Konfigurationsmanager. (SQL Server Dienst >> Eigenschaften)

image

Der Windows Freigabename stellt für die gewählte SQL Server Instanz das Freigabe Wurzelverzeichnis dar. image

 

2. Aktivieren des Filestreaming Zugriffs im SQL Server Management Studio

Unter den Eigenschaften des SQL Servers findet man unter Punkt “Erweitert” die Möglichkeiten den Zugriff auf die Daten zu steuern: Nur T-SQL oder T-SQL und Windows-Freigabe Zugriffe erlauben. Für Windows Freigaben also die Option: Vollzugriff aktiviert wählen.

image

 

3. Filestream in Benutzerdatenbanken aktivieren und konfigurieren

 

Jede Datenbank, die Filestreaming anbieten möchte, bekommt unter der Windows Freigabe ein Unterverzeichnis zugeordnet. Die Filestreaming, sowie auch Filetable-Verzeichnisse dürfen nicht verschachtelt werden. Jede DB und jede Filetable besitzt ihr persönliches Verzeichnis. Diese Einstellung treffen wir in den Eigenschaften der Datenbank:

image

Der Punkt Nicht transaktionsgebundener Filestream-Zugriff bedeutet, dass jegliche Aktion in den Verzeichnissen über die Windows Freigabe nicht rückgängig gemacht werden kann. Sie sind eben nicht transaktionsgebunden. Aktionen per T-SQL oder Win32-Api bleiben natürlich transaktiongebunden und können deshalb ein Rollback vollziehen.

Nach dieser Einstellung findet man folgenden Pfad:  image

 

4. Filetables erstellen

Jede Datenbank kann eine oder mehrere Filetables besitzen. Jede Datenbank bzw. jede Filetable kann entschieden, wo Ihre Dateien physikalisch abgelegt werden sollen. Dies wird per Filestreaming Dateigruppen erreicht. Bevor man also Filetables erstellt, muss man also Filestreaming Dateigruppen anlegen.
(Filetables werden wie alle anderen Tabellen auch auf (Filestreaming-)Dateigruppen erstellt )

Als erstes also die (oder mehrere) Filestreaming Dateigruppe…

SNAGHTMLb05996b

und nun anschließend die Datei, die genauer gesagt das Verzeichnis vorgibt, in dem die Dateien physikalisch abgelegt werden.

image

 

Das war bereits die größte Arbeit.  Das Anlegen der Filetables ist relativ einfach. Das Schema ist vorgegeben, die Filetable unveränderlich. Effektiv würde sogar folgende Syntax ausreichen:

create table Vertrag as Filetable

Vernünftiger ist es natürlich zu bestimmen, in welche Dateigruppe die Werte sollen (Filestreaming Dateien und Metadatenspalten der Tabelle lassen getrennt angeben) und für das Verzeichnis, in dem die Dateien landen benannt werden sollte. Gibt man das Verzeichnis nicht an, wird der Name der Tabelle verwendet.

CREATE TABLE Vertraege AS FILETABLE on [PRIMARY] FILESTREAM_ON Contracts

  WITH

  (

    FILETABLE_DIRECTORY = 'verträge',

    FILETABLE_COLLATE_FILENAME =  database_default

  ) 

GO

Das Ergebnis ist folgende Verzeichnisstruktur:

image

Folgendes sollte man nun beachten:

  • Ab jetzt können Dateien per Drag ‘n Drop ausgetauscht werden.
  • Berechtigungen wird im SQL Server gesetzt. Logischerweise blieben über diesen Weg SQL Authentifizierte User aussen vor.
  • Backup und Restore werden in gewohnter Weise gehandhabt.
  • Der Zugriff auf die Dateien ist über den lokalen Pfad nur dem SQL Server Dienst vorbehalten.
  • Der Titel der Datei wird verschlüsselt, nicht dir Datei selbst.
  • Filestreaming lohnt sich in der Regel erst ab Dateien, die größer als 1 MB sind.
6. Die Filetable

Die Filetable gibt folgende Werte zurück:

image

eine genauere Liste der zurückgegebenen Spalten dazu:

Stream_ID

Uniqueidentifier RowGUID Spalte

Eindeutiger Kennzeichner

File_Stream

Varbinary(MAX) Filestream

BLOB Inhalt

Name

Nvarchar(255)

Name der Datei oder des Verzeichnis

Path_Locator

hierarchyid

Ort der Datei/Verzeichnis

Parent_path_locator

Hierarchyid, Berechnet

Übergeordnetes Verzeichnis

File_Type

Nvarchar(255), Berechnet

Dateityp

Cached_file_Size

Bigint, Berechnet

Größe der Datei

Last_write_time

Datetimeoffset(7)

Letzter Zugriff Schreiben

Last_write_time

Datetimeoffset(7)

Letzter Zugriff Lesend

Is_directory

Bit

Weitere Spalten für Dateiattribute wie ReadOnly, Archivbit, versteckt usw.

Fazit

Filetables sind ein deutlicher Fortschritt, um in SQL Server Dateien ablegen zu wollen Zwinkerndes Smiley. Der Umgang und die Verwaltung sind einfach umzusetzen. Filetables lassen sich natürlich auch per Volltextsuche und statistischer Semantik indzieren.
Schön wäre noch, um Dokumenten weitere Metadaten zuzuweisen, die Filetable erweitern zu können  anstatt zusätzliche Tabellen anlegen zu müssen. Aber was nicht ist, kann noch werden.. sagte man zumindest auch 2006 als WinFS eingestampft wurde.

Author: Fumus

3 thoughts on “SQL Server 2012 – Filestreaming und Filetables

  1. Ich habe recht lange nach einer wirklichen Beschreibung gesucht. Besten Dank, es ist klar geschrieben und gut verständlich. Warum kann dies Microsoft nicht?

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert