FILESTREAM für Microsoft SQL Server aktivieren

combit_Webmaster · 2. Juli 2013 um 00:00

Einleitung

Dieser Artikel unterstützt Sie bei der Auslagerung von eingebetteten Dateien Ihrer Microsoft SQL Server-Datenbank in das Dateisystem mittels FILESTREAM. Diese Vorgehensweise erlaubt es Ihnen, die Datenbankgröße gering zu halten, da die Dateien vom SQL Server aus dem Dateisystem gestreamt werden und die Größenbeschränkung der Datenbank (bei der Microsoft SQL Server Express-Edition) somit weniger relevant erscheint. Eine Anwendung dieser Methode könnte sich bereits lohnen, wenn die zu speichernden Objekte im Durchschnitt größer als 1 Megabyte sind und ein schneller Lesezugriff wichtig ist.

Weitere Informationen hierzu finden Sie auch unter FILESTREAM (SQL Server) - SQL Server | Microsoft Learn

Hinweis: FILESTREAM-Daten können nicht verschlüsselt werden.

Durchführung der Umstellung

Die folgenden Schritte werden anhand der combit CRM-Datenbank combit_Large13 erklärt und die SQL-Abfragen beziehen sich auf deren Tabellen (wenn Sie eine andere Datenbank einsetzen, ersetzen Sie diese an allen Stellen einfach). combit CRM sollte während der Durchführung nicht aktiv sein und kein Anwender sollte in der Datenbank arbeiten. Bitte erstellen Sie vor der Durchführung der folgenden Schritte eine komplette Sicherung Ihrer Datenbanken und der Solution.

Aktivierung von FILESTREAM in der verwendeten SQL Server-Instanz

Damit FILESTREAM verwendet und konfiguriert werden kann, muss es vorher in den Eigenschaften der verwendeten SQL Server-Instanz aktiviert werden. Folgende Schritte sind dafür notwendig:

Öffnen Sie zunächst den SQL Server-Konfigurations-Manager. Öffnen Sie dazu eine neue Windows-Eingabeaufforderung (z. B. über die Suche im Windows-Startmenü). Geben Sie in dieser nun den folgenden Befehl ein: C:\Windows\SysWOW64\SQLServerManager16.msc

Hinweis: Die 16 am Ende bestimmt dabei die SQL Server-Version. Weitere Informationen hierzu finden Sie auf der Microsoft-Website zum SQL Server-Konfigurations-Manager.
Führen Sie in der linken Leiste einen Rechtsklick auf SQL-Server Dienste aus und wählen Sie anschließend Öffnen.
Markieren Sie nun auf der rechten Seite die entsprechende SQL Server-Instanz, auf welcher FILESTREAM aktiviert werden soll und wählen Eigenschaften aus dem Rechtsklick-Kontextmenü.
Wechseln Sie im Eigenschaften-Dialog nun auf die Registerkarte FILESTREAM und aktivieren Sie die Option FILESTREAM für Transact-SQL-Zugriff aktivieren.
Optional: Wenn ein Lesen und Schreiben der FILESTREAM-Daten über Windows erforderlich sein sollte, dann aktivieren Sie zusätzlich die Option FILESTREAM für E/A-Dateizugriff aktivieren. Geben Sie anschließend den Windows-Freigabenamen ein.

Hierbei ist es noch erforderlich, den FILESTREAM-Zugriffslevel anzupassen. Öffnen Sie dazu im SQL Server Management Studio eine neue Abfrage und geben die folgenden Zeilen ein:
```
-- Change FILESTREAM Access Level to 2 - Default: 0
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
```
Führen Sie die Abfrage anschließend über Ausführen für die Datenbank master aus.
Optional: Falls Remoteclients auf FILESTREAM-Daten dieser Freigabe zugreifen müssen, aktivieren Sie zusätzlich die Option Zugriff von Remoteclients auf FILESTREAM-Daten zulassen.
Bestätigen Sie die vorgenommen Änderungen nun mit OK.
Wechseln Sie nun zurück in den SQL Server-Konfigurations-Manager und starten Sie den SQL Server-Dienst neu.

Sicherstellen der benötigten RowGuid-Feldeigenschaft

Standardmäßig ist dies in der combit_Large13-Datenbank bereits korrekt eingestellt. Um dies ggf. zu überprüfen, öffnen Sie im SQL Server Management Studio in der Datenbank combit_Large13 die Tabelle Activities über Entwerfen im Kontextmenü. Markieren Sie dort die Spalte ID und stellen Sie sicher, dass die Eigenschaft RowGuid auf Ja steht.

Aktivierung der AlwaysOn-Verfügbarkeitsgruppen

Für das Erzeugen der Dateigruppe im nächsten Schritt müssen zunächst die AlwaysOn-Verfügbarkeitsgruppen in der SQL Server-Instanz aktiviert werden. In der SQL Server Express Edition ist dies leider nicht über den SQL Server-Konfigurations-Manager möglich, wie in anderen Editionen, sondern muss über die Windows-Registrierung gemacht werden. Öffnen Sie dazu den Windows-Registrierungs-Editor über die Windows-Suche, navigieren zu folgendem Schlüssel und setzen dort den Binärwert HADR_Enabled auf 1.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQLServer\HADR

Hinweis: Der im o. g. Schlüssel verwendete Teilpfad „MSSQL16.SQLEXPRESS“ bestimmt dabei den Namen Ihrer SQL Server-Instanz. Passen Sie diesen ggf. an.

Wechseln Sie nun zurück in den SQL Server-Konfigurations-Manager und starten Sie den SQL Server-Dienst neu. Beenden Sie nun den SQL Server-Konfigurations-Manager.

Alternativ ist dies auch über PowerShell möglich, weitere Informationen hierzu finden sich auf der Microsoft-Website Aktivieren oder Deaktivieren der Verfügbarkeitsgruppenfunktion - SQL Server Always On | Microsoft Learn.

Erzeugen einer neuen Dateigruppe

Für FILESTREAM benötigt die SQL Server-Datenbank eine sogenannte Dateigruppe, welcher eine Datei zugeordnet wird. Legen Sie dazu zunächst einen beliebigen neuen Ordner an, in unserem Beispiel ist dieser Ordner C:\data. Achten Sie darauf, dass auf dem gewählten Laufwerk, auch in Zukunft, ausreichend Speicherplatz vorhanden ist, da in diesem die Dateien ausgelagert werden.

Öffnen Sie dazu im SQL Server Management Studio eine neue Abfrage und geben die folgenden Zeilen ein. Passen Sie dabei den Pfad zu FILENAME = 'C:\data\filestream' entsprechend an den zuvor angelegten neuen Ordner an; dabei ist es wichtig, den Unterordner \filestream im letzten Teil des Pfads beizubehalten (dieser darf aber nicht bereits im Dateisystem vorhanden sein und wird durch die Abfrage erstellt).

-- Add a file group
ALTER DATABASE combit_Large13
ADD FILEGROUP DocumentsGroup CONTAINS FILESTREAM
GO

-- Add a file for storing database documents to DocumentsGroup
ALTER DATABASE combit_Large13
ADD FILE
(
	NAME = 'DocumentsFile',
	FILENAME = 'C:\data\filestream'
)
TO FILEGROUP DocumentsGroup
GO

Führen Sie die Abfrage anschließend über Ausführen aus.

Nach der Ausführung der SQL-Abfrage sollten die Datei filestream.hdr und der Ordner $FSLOG im Ordner C:\data\filestream angezeigt werden. Die Datei filestream.hdr ist eine FILESTREAM-Container-Headerdatei, die eine wichtige Systemdatei ist, die nicht entfernt oder geändert werden darf.

Weitere Informationen hierzu finden sich auf der Microsoft-Website Erstellen einer FILESTREAM-aktivierten Datenbank - SQL Server | Microsoft Learn.

Erstellen eines neuen FILESTREAM-Felds

Nun muss in der Datenbank combit_Large13 in der Tabelle Activities die neue Spalte Document_Filestream mit dem Datentyp varbinary(MAX) und der FILESTREAM-Eigenschaft erstellt werden. Öffnen Sie dazu im SQL Server Management Studio eine neue Abfrage und geben die folgenden Zeilen ein:

-- Add Field 'Document_Filestream' with field type and arguments
ALTER TABLE Activities
ADD Document_Filestream VARBINARY(MAX) FILESTREAM NULL

Führen Sie die Abfrage anschließend über Ausführen für die Datenbank combit_Large13 aus.

Abgleichen des BLOB-Felds mit dem FILESTREAM-Feld

Hinweis: Dieser Schritt ist nicht notwendig, wenn in der Datenbank keine eingebetteten Dokumente enthalten sind oder diese ggf. nicht mehr benötigt werden und damit auch nicht übertragen werden müssen.

Damit nun die Inhalte aus dem bisherigen BLOB-Feld Document_Embedded in das neue FILESTREAM-Feld Document_Filestream übertragen und die eingebetteten Dokumente ausgelagert werden können, müssen die Felder miteinander abgeglichen werden. Öffnen Sie dazu im SQL Server Management Studio eine neue Abfrage und geben die folgenden Zeilen ein:

UPDATE Activities
SET Document_Filestream = Document_Embedded

Führen Sie die Abfrage anschließend über Ausführen für die Datenbank combit_Large13 aus.

Achtung: Wenn die Tabelle bereits in einer Replikation verwendet wird, dann muss diese vorher aus der Replikation entfernt und nach Abschluss wieder hinzugefügt werden. Ein erneutes Initialisieren des Snapshots ist notwendig!

Anpassen des combit CRM-Projekts und der Eingabemaske

Öffnen Sie nun in combit CRM die Ansichteneigenschaften für die Ansicht Aktivitäten und setzen in der Lasche Felder den internen Feldtyp des soeben neu angelegten Felds Document_Filestream auf den Wert Eingebettete Datei. Anschließend wechseln Sie auf die Lasche Dokumenteninfos und vergeben die Feldzuordnungen analog zum bestehenden Feld Document_Embedded. Wechseln Sie nun noch einmal zurück auf die Lasche Felder und setzen den Feldtyp des bisher verwendeten Felds Document_Embedded auf Unbekannt (dieses Feld wird in einem nachfolgenden Schritt abschließend entfernt werden). Bestätigen Sie nun den Dialog mit OK und speichern Sie das Projekt.

Öffnen Sie nun die Ansicht Aktivitäten und wechseln in den Eingabemaskendesigner. Ersetzen Sie dort das Eingabefeld Document_Embedded mit dem Feld Document_Filestream. Ersetzen Sie außerdem in allen Objekten den Feldnamen Document_Embedded durch Document_Filestream; dies sind mind. die Schaltflächen für Hinzufügen, Öffnen, Bearbeiten und Löschen von Dokumenten. Speichern Sie die abschließend die Eingabemaske ab.

Löschen des nicht mehr benötigten BLOB-Felds

Wenn Sie nun sichergestellt haben, dass die bisherigen Inhalte (sofern vorhanden) des BLOB-Felds Document_Embedded korrekt in das neue FILESTREAM-Feld Document_Filestream übernommen werden konnten, kann das nun nicht mehr benötigte BLOB-Feld Document_Embedded gelöscht werden. Öffnen Sie dazu im SQL Server Management Studio eine neue Abfrage und geben die folgenden Zeilen ein:

ALTER TABLE Activities
DROP COLUMN Document_Embedded

Führen Sie die Abfrage anschließend über Ausführen für die Datenbank combit_Large13 aus.

Schließen Sie nun eine ggf. geöffnete Instanz von combit CRM und starten Sie diesen erneut. Um das nun nicht mehr vorhandene BLOB-Feld Document_Embedded auch aus dem Projekt zu entfernen, öffnen Sie nochmals die Ansichteneigenschaften für die Ansicht Aktivitäten, bestätigen Sie den Dialog mit OK und speichern Sie das Projekt abschließend.

Freigabe des belegten Speicherplatzes

Um den durch die ehemals eingebetteten Dokumente belegten Speicherplatz in der SQL-Datenbank wieder freizugeben, öffnen Sie im SQL Server Management Studio eine neue Abfrage und geben die folgenden Zeilen ein:

DBCC CLEANTABLE (combit_Large13, 'Activities', 0)
GO

Führen Sie die Abfrage anschließend über Ausführen aus.

Im Anschluss muss auch die entsprechende Datendatei der Datenbank selbst reduziert werden. Öffnen Sie dazu im SQL Server Management Studio eine neue Abfrage und geben die folgenden Zeilen ein:

DBCC SHRINKFILE (combit_Large13);
GO

Führen Sie die Abfrage anschließend über Ausführen aus.

Hinweis: Bitte beachten Sie, dass dieser Vorgang, je nach Größe des belegten Speicherplatzes, entsprechend Zeit in Anspruch nehmen kann.

IDKBAD001258 KBAD001258