Dokumenten Verwaltung mit Filestream und der Volltextsuche

In diesem Artikel geht es um die Speicherung von Dateien und dem Auffinden dieser im SQL Server. Um Dateien nicht komplett in der Datenbank zu speichern, bietet sich Filestream an. Bei dieser Technologie werden lediglich Meta Daten innerhalb der Datenbank gespeichert und die eigentliche Datei befindet sich in einem File-Share und werden dort physikalisch als Datei abgelegt. So spart man sich den Speicherplatz innerhalb der Datenbank.
Um diese Dateien aber auch wiederzufinden, bzw. nach dem Inhalt zu suchen, kann die Volltext-Indizierung eingesetzt werden.

Folgendes Fallbeispiel stellt sich:
Wir verfügen über verschiedenste Dokumente, bspw. im DOC, TXT und RTF Format die innerhalb der Datenbank im Filestream gespeichert und auch vollständig durchsucht werden sollen

Zu erst muss Filestream über den SQL Server Configuration Manager aktiviert werden.
Siehe hierfür BOL:
http://msdn.microsoft.com/de-de/library/cc645923.aspx

Zuerst brauchen wir eine Datenbank:
CREATE DATABASE [DMS] ON PRIMARY
( NAME = N'DMS',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\DATA\DMS.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'DMS_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\DATA\DMS_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
GO

Nun wird das Filestream Feature auf der Datenbank mit dem Access Level 2 (Zugriff auch von externen Ressourcen, wie bspw. .NET) aktiviert:
EXEC sys.sp_configure N'filestream access level', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

Nun wird eine weitere Dateigruppe für die Speicherung der Filestream Daten angelegt mit der entsprechenden Ressource. Hier wird auch konfiguriert, wo sich der Filestream Ordner im Netzwerk oder Lokal befindet:
ALTER DATABASE [DMS] ADD FILEGROUP [dateien] CONTAINS FILESTREAM
GO
ALTER DATABASE [DMS] ADD FILE ( NAME = N'Dateien', FILENAME = N'C:\filestream\Dateien' ) TO FILEGROUP [dateien]
GO

Nun benötigen wir noch eine Tabelle in der die Daten gespeichert werden sollen:
CREATE TABLE Dokumente (
uid uniqueidentifier default newid() not null primary key ROWGUIDCOL,
name varchar(255) not null,
erweiterung varchar(255) not null,
datei varbinary(MAX) FILESTREAM NOT NULL,
hinzugefuegt datetime default getdate() not null)
ON [PRIMARY] FILESTREAM_ON [dateien];
GO

Um Filestream Daten speichern zu können, ist die Angabe von ROWGUIDCOL als Index notwendig.

Nun werden noch 3 Dateien über OPENROWSET hinzugefügt:
INSERT INTO dbo.Dokumente(name, erweiterung, datei)
SELECT
'Mein RTF Dokument' AS Name,
'rtf' AS Erweiterung,
* FROM OPENROWSET(BULK N'C:\temp\dokument.rtf', SINGLE_BLOB) AS datei;
GO

INSERT INTO dbo.Dokumente(name, erweiterung, datei)
SELECT
'Word 2003 Dokument' AS Name,
'doc' AS Erweiterung,
* FROM OPENROWSET(BULK N'C:\temp\beschreibung.doc', SINGLE_BLOB) AS datei;
GO

INSERT INTO dbo.Dokumente(name, erweiterung, datei)
SELECT
'Mein TXT Dokument' AS name,
'txt' AS erweiterung,
* FROM OPENROWSET(BULK N'C:\temp\test.txt', SINGLE_BLOB) AS datei
GO

Nun sind diese 3 Dateien im Filestream Container enthalten.
Mit folgender Query kann der Pfad ermittelt werden:
SELECT name, datei.PathName() FROM dbo.Dokumente

Ergebnis:

Die RTF Datei die eingefügt wurde, sieht folgendermaßen aus:

Nun ist das nächste Ziel, die Datei wiederzufinden indem nach „Welt“ gesucht wird.
Dafür benötigen wir den Volltext Index (Achtung: Dieser muss installiert sein!)

Zuerst wird ein Volltext Katalog benötigt:
CREATE FULLTEXT CATALOG FS_Datei_FT_Katalog AS DEFAULT;
GO

Nun wird der eigentliche Volltext Index erstellt. Dieser geht über die Felder Name, Datei und Erweiterung innerhalb der Tabelle damit diese durchsucht werden können. Da das Feld „datei“ Filestream Daten enthält, wird dieses mit dem Feld Erweiterung als Typ gebunden. So kann der Volltext Index unterscheiden, um was für Daten es sich handelt.
CREATE FULLTEXT INDEX ON [dbo].[Dokumente] KEY INDEX [PK__Dokument__DD7012647F60ED59]
ON ([FS_Datei_FT_Katalog]) WITH (CHANGE_TRACKING AUTO)
GO
ALTER FULLTEXT INDEX ON [dbo].[Dokumente] ADD ([datei] TYPE COLUMN [erweiterung])
GO
ALTER FULLTEXT INDEX ON [dbo].[Dokumente] ADD ([erweiterung])
GO
ALTER FULLTEXT INDEX ON [dbo].[Dokumente] ADD ([name])
GO
ALTER FULLTEXT INDEX ON [dbo].[Dokumente] ENABLE
GO

Um zu prüfen welche Erweiterungen der Volltext Index unterstützt, kann dies mit folgender Abfrage überprüft werden:
SELECT * FROM sys.fulltext_document_types;

Weitere Treiber können bei Bedarf nachinstalliert werden. Siehe hier (bspw. für Office 2007 oder 2010 Dokumente):
http://support.microsoft.com/default.aspx?scid=kb;en-us;945934

Nun kann der Volltext Index die Dateien durchsuchen:
select name, datei.PathName() from dbo.dokumente where FREETEXT(datei, '"*welt*"')

Ergebnis:

Weitere Informationen:
Volltext Filter: http://technet.microsoft.com/en-us/library/ms142571.aspx
Filestream: http://technet.microsoft.com/en-us/library/bb933993.aspx

Das wars.

Server Trigger: Logon Trigger

In diesem Blog Eintrag geht es um Logon-Trigger. Analog zu den Einstell-Möglichkeiten im Microsoft Active Directory, wo man Benutzern verschiedene Zeiten voreinstellen kann, wann eine Anmeldung möglich ist, kann dies auch im SQL Server nachvollzogen werden.

Hierfür ist lediglich eine kleine Infrastruktur notwendig. Eine Verwaltungsdatenbank in der die Benutzer gespeichert werden, wie auch eine Tabelle in der die Zeiten definiert werden.

In folgendem Beispiel wird die Verwaltungsdatenbank erstellt mit einer Benutzertabelle in der die Benutzernamen gespeichert werden, eine Gruppentabelle um Benutzer zu Gruppen hinzuzufügen die gleichzeitig die Zeiten definieren.

Mit diesem Code wird die Benutzer Tabelle erzeugt. Hier werden lediglich die Benutzernamen gespeichert:
create table dbo.benutzer (
uid uniqueidentifier default newid() primary key not null,
benutzername varchar(255) not null unique);
go

Hier noch 2 Benutzer:
insert into benutzer (benutzername) values ('Herta');
insert into benutzer (benutzername) values ('Kerrin');
go

Um das Beispiel testen zu können, empfiehlt es sich auch diese Benutzer als SQL Authentifizierte Accounts auf dem Server anzulegen und der Datenbank zuzuordnen.

Nun wird die Gruppen Tabelle erstellt. Über die Felder Beginn- und Endzeit wird die Zeit definiert, wo eine Anmeldung möglich ist.
create table dbo.gruppen (
uid uniqueidentifier default newid() primary key not null,
gruppenname varchar(255) not null unique,
beginnzeit time not null,
endzeit time not null);
go

Nun werden noch 3 Gruppen angelegt:
insert into gruppen (gruppenname, beginnzeit, endzeit) values ('Vormittag', '07:00', '12:30');
insert into gruppen (gruppenname, beginnzeit, endzeit) values ('Nachmittag', '12:30', '18:30');
insert into gruppen (gruppenname, beginnzeit, endzeit) values ('Vollzeit', '07:00', '18:30');
go

Zuletzt wird noch die Zuordnungstabelle erstellt:
(Für die Übersicht habe ich hier die Referentiellen Integritäten nicht erstellt)
create table bennutzer_gruppen (
benutzer_uid uniqueidentifier not null,
gruppen_uid uniqueidentifier not null);
go

Nun werden die Benutzer noch jeweils einer Gruppe zugeordnet:
(Beim Test müssen die ID’s angepasst werden)
insert into bennutzer_gruppen values ('E4727227-7C64-46E0-8233-CE0A249AAAB5', 'D319D6EA-D280-4E65-8AC6-823100270667');
insert into bennutzer_gruppen values ('ED3EEA47-E4BA-4E82-A6E7-D5A69E991C95', 'F5D9E222-98BE-455A-B5B5-62096CA254EE');
go

Nun wird noch eine View erstellt, die die Benutzer zu den jeweligen Gruppen ausgibt:
create view benutzerInGruppen as
SELECT     benutzer.benutzername, gruppen.gruppenname, gruppen.beginnzeit, gruppen.endzeit
FROM         bennutzer_gruppen INNER JOIN
benutzer ON bennutzer_gruppen.benutzer_uid = benutzer.uid INNER JOIN
gruppen ON bennutzer_gruppen.gruppen_uid = gruppen.uid
go

Ergebnis:

Für den Trigger wird hier noch eine weitere View erstellt, die prüfen soll, ob es den Benutzer auch gibt:
create view benutzernamen as
SELECT benutzername FROM benutzer WHERE benutzername = SUSER_NAME()
go

Damit die Benutzer beim Login auch prüfen können, ob ein Login möglich ist, wird noch eine Rolle erstellt die auf die beiden Sichten berechtigt und die Benutzer werden dieser Rolle hinzugefügt:
create role benutzer;
grant select on benutzerInGruppen to benutzer
grant select on benutzernamen to benutzer
exec sp_addrolemember benutzer, herta;
exec sp_addrolemember benutzer, kerrin;
go

Ob die Datenbank Infrastruktur stimmt, kann mit folgenden Code überprüft werden:
EXECUTE AS LOGIN = 'kerrin'
SELECT * FROM benutzernamen
REVERT

Hiermit wird die Abfrage unter dem angegeben Benutzerkontext ausgeführt.

Nun wird der Servertrigger erstellt. Server Trigger befinden sich im SSMS hier:

Der Trigger soll folgende Voraussetzungen erfüllen:

  • Benutzer die nicht in der Tabelle definiert sind, sollen vom Trigger ausgenommen werden
  • Nur Benutzer die sich im Zeitfenster befinden dürfen sich anmelden

Nun der Code:
CREATE trigger benutzerAnmeldung on ALL SERVER
for LOGON
as
begin
DECLARE @result INT
IF((SELECT COUNT(*) from LogonTime.dbo.benutzernamen) > 0)
BEGIN
select @result = COUNT(*) from LogonTime.dbo.benutzerInGruppen
where benutzername = SUSER_SNAME()
AND (
beginnzeit <= CONVERT(time, GETDATE()) AND endzeit >= CONVERT(time, GETDATE()))
IF(@result = 0)
BEGIN
ROLLBACK;
END
END
END;
go

Nun kann man den Logon mit einem Benutzernamen testen. Befindet man sich in dem Zeitfenster funktioniert eine Anmeldung, ansonsten wird man durch folgende Meldung begrüßt:

TDE: Datenbank Verschlüsselung

Durch die in SQL Server 2008 eingeführte Funktion TDE (Transparent Data Encryption) werden die Datenbank Dateien physikalisch auf dem Dateisystem verschlüsselt. Hierbei handelt es sich nicht um die Verschlüsselung der Daten auf logischer Ebene, also beim Lesen aus den Tabellen o.ä., sondern die Dateien können nicht ohne eine Entschlüsselung auf einem anderen Server wiederhergestellt werden. Weiterhin können die Dateien auch nicht via einem HEX Editor o.ä. ausgelesen werden, da auch der komplette Inhalt verschlüsselt ist.

TDE kostet überings laut Microsoft keinerlei Performance. Voraussetzung hierfür ist, dass entsprechend CPU Zeit zur Verfügung steht, da beim Lesen von Seiten die CPU bei der Entschlüsselung mehr als sonst gefragt ist.

In diesem Beispiel wird beschrieben wie eine Datenbank mit TDE verschlüsselt und dann auf einem anderen Server wiederhergestellt wird:

Zuerst wird auf der Instanz ein Master Schlüssel erzeugt, mit dem dann die Zertifikate verschlüsselt werden:
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Kennwort1!'
GO

Nun wird das Zertfikat erstellt:
USE master
GO
CREATE CERTIFICATE TDE WITH SUBJECT = 'Zertifikat fuer die TDE Datenbank'
GO

Zur Vollständigkeit des Beispiels wird noch die Datenbank „TDE“ erstellt, die geschützt werden soll:
CREATE DATABASE TDE
GO
USE TDE
GO
CREATE TABLE dbo.Daten (wert varchar(100));
GO
INSERT INTO dbo.Daten VALUES ('Daten');
GO

Nun wird ein Verschlüsselungsschlüssel erzeugt, der die Datenbank später verschlüsselt.
Unterstützt werden: AES 128, AES 192, AES 256, TRIPLE DES
USE TDE
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE
GO

Nun sollte zuerst das Zertifikat gesichert werden:
USE master
GO
BACKUP CERTIFICATE TDE TO FILE = 'c:\TDE_certificate'
WITH PRIVATE KEY ( FILE = 'c:\TDE_certificate_key' ,
ENCRYPTION BY PASSWORD = 'Kennwort1!' )
GO

Dabei werden 2 Dateien erzeugt: Die eigentliche Zertifikatsdatei und der Schlüssel in der zweiten Datei. Diese Datei wird durch ein Kennwort geschützt.

Verschlüsselung anwenden:
ALTER DATABASE TDE SET ENCRYPTION ON;
GO

Nun ist die Datenbank verschlüsselt und kann physikalisch nicht ohne Schlüssel ausgelesen werden. Die Verschlüsselung wirkt sich nicht auf die Datensicherung (Voll-, Differentiell- oder Transaktionslogsicherung) aus.

Nun wird eine Vollsicherung der Datenbank durchgeführt, die später auf einem anderen Server wiederhergestellt werden soll:
backup database TDE to disk = 'C:\temp\TDE.bak' WITH INIT

Nun kann ein neuer Master-Schlüssel auf dem anderen Server erzeugt werden. Das Kennwort muss nicht identisch mit dem Kennwort vom vorherigen Server sein.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Kennwort2!';
GO

Nun wird das Zertifikat wiederhergestellt. Hier kann ein beliebiger neuer Name ebenfalls gewählt werden:
CREATE CERTIFICATE TDE_SERVER2 FROM FILE = 'c:\TDE_certificate'
WITH PRIVATE KEY ( FILE = 'c:\TDE_certificate_key' ,
DECRYPTION BY PASSWORD = 'Kennwort1!' )
GO

Nun wo das Zertifikat wierhergestellt ist, kann auch die Datensicherung widerhergestellt werden:
RESTORE DATABASE [TDE_RESTORE] FROM DISK = N'C:\temp\TDE.bak' WITH FILE = 1, MOVE N'TDE' TO N'F:\TDE_RESTORE.mdf', MOVE N'TDE_log' TO N'F:\TDE_RESTORE_1.LDF', NOUNLOAD, STATS = 10
GO

Fazit:
TDE eignet sich hervorragend zum Schutz der Datendateien vor unbefugten Zugriff und kostet, in meinen Tests, keinerlei zusätzliche Ressourcen oder Zeit beim Zugriff auf die Datenbank.
Jedoch bleibt immer zu beachten, dass man Zugriff auf das Kennwort wie auch auf das Zertifikat benötigt wenn eine Datensicherung wiederhergestellt werden muss. Weiterhin bleibt zu beachten, dass es sich hier um ein Enterprise Feature handelt.

CDC – Zeilen Versionierung

In diesem Blog Eintrag geht es um Change Data Capture (CDC). Dieses Werkzeug im Microsoft SQL Server 2008 (verfügbar (leider) ab der Enterprise Edition) dient nicht für das Autiting von Benutzern sondern um Daten zu versionieren und so Veränderungen oder Bewegungen auswerten oder wiederherstellen zu können. Das bedeutet, jede Tabelle in der CDC aktiviert ist, wird entsprecht mit protokolliert und die Änderungen werden in einer eigenen Systemtabelle pro Objekt gespeichert. Wie man zu erst vermuten möchte, handelt es sich nicht um typische DML Trigger sondern um eine Technologie, die direkt vom Transaktionslog liest und somit sicher die Datenbewegungen registriert.

Als Fallbeispiel wird eine Buchungstabelle „überwacht“:
CREATE TABLE dbo.buchungen (
uid uniqueidentifier default newid() not null primary key,
buchungsid int not null,
buchungswert money not null,
buchungsdatum datetime not null);
GO

Über folgenden Befehl wird CDC in der Datenbank aktiviert:
EXEC sys.sp_cdc_enable_db
GO

Nun wird CDC für die Tabelle aktiviert:
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'buchungen',
@role_name     = NULL,
@supports_net_changes = 1
GO

Nun fügen wir einen Datensatz in die Buchungstabelle ein:
INSERT INTO dbo.buchungen (buchungsid, buchungswert, buchungsdatum)
VALUES (1, 200, GETDATE());
GO

Zuletzt verändern wir diesen Datensatz nochmal:
UPDATE dbo.buchungen SET buchungswert = 17300 WHERE buchungsid = 1
GO

Mit folgender Abfrage kann die Datenveränderung dargestellt werden mit den jeweiligen Statis:
SELECT
CASE __$operation
WHEN 1 THEN 'DELETED'
WHEN 2 THEN 'INSERTED'
WHEN 3 THEN 'BEFORE UPDATED'
WHEN 4 THEN 'AFTER UPDATED'
END 'Aktion', buchungsid, buchungswert, buchungsdatum FROM cdc.dbo_buchungen_CT

Ergebnis:

Weitere Informationen zu den Statis gibt es hier:
http://technet.microsoft.com/en-us/library/bb522511.aspx

Sobald CDC aktiviert wird, werden folgende Tabellen erstellt:

  • cdc.change_tables
    Enthält alle CDC überwachten Tabellen
  • cdc.captured_columns
    Enthält die Felder der CDC überwachten Tabellen
  • cdc.dbo_buchungen_CT
    Eine solche Tabelle wird für jedes CDC Objekt erstellt und speichert die Datenänderungen
  • cdc.ddl_history
    Speichert Änderungen an der CDC überwachten Tabelle
  • cdc.index_columns
    Enthält die Index Felder der CDC Tabellen
  • cdc.lsn_time_mapping
    Über diese Tabelle kann nachvollzogen werden, wann welche Änderung durchgeführt wurde. Dies kann anhand der LSN mit der Benutzertabelle verglichen werden.

Weitere Informationen zu den Tabellen gibt es hier: http://technet.microsoft.com/de-de/library/bb500353.aspx

Weiterhin erstellt CDC noch zwei SQL Agent Jobs:

  1. cdc.CDC_capture
    Dieser Job steuert CDC für die jeweiligen konfigurierten Objekte die für CDC aktiviert worden sind
  2. cdc.CDC_cleanup
    Entfernt abgelaufene, bzw. deaktivierte CDC Objekte die sonst vom Capture Job ausgeführt werden

Hinweis:
Das Wiederherstellungsmodell für CDC ist nicht relavant. CDC liest lediglich aus dem Aktiven Teil des Transaktionslog und somit wir auch das Einfache Wiederherstellungsmodell unterstützt.

Fazit:
CDC eignet sich hervorragend für Auswertungszwecke um Datenveränderungen zu speichern und darzustellen. Eine Speicherung von Benutzernamen etc. ist nicht vorgesehen und könnte max. selber durch DML Trigger hinzugeführt werden.