Dateien im SQL Server speichern

Question

Hallo,

ich möchte verschiede Dokumente und Bilder im SQL Server speichern.

Auf Clientseite möchte ich das ADODB.Stream Object verwenden.

Die Dateigröße ist ca 100KB-100MB im Schnitt so 1-2MB, die Gesamtmenge ist derzeit ca 100GB

Versuchsweise habe ich einfach ein Feld vom Typ image angelegt und darin geschrieben und gelesen. Es hat ganz gut und schnell geklappt. Bei der weiteren Recherche bin ich auf die Dateigruppe Filestream gestossen und ich habe gesehen dass man zwischen  FILESTREAM, FileTables und Remote Blob Store wählen kann.

Was ist nun sinnvoll für mich?

Der SQL Server ist 2008R2 auf einer VM am SAN. Die Dateien werden einmal geschrieben möglicherweise noch 1-2 mal geändert und dann nur noch gelesen. Die Clients haben keinen Dateizugriff auf den Server! Die ältesten Clients sind noch VB6 mit ADO 2.8

Vielen Dank für Eure Antworten

Mit freundlichen Grüßen Dieter

Answer 1

Hallo Dieter,

welche zwingenden Gründe gibt es in deinem Fall, die Datei in der Datenbank zu speichern. In den allermeisten Fällen bringt das außer eine exorbitant großen Datenbank und einer erheblich schlechteren Performance rein gar nichts. Dass kein Dateizugriff auf den Server möglich ist, ist noch kein Grund, hierfür könnte man sich wahrscheinlich problemlos eines Webservers bedienen.

Die wenigen Fälle, in denen die Speicherung von Dateien in der Datenbank Sinn macht, kann ich hier noch nicht rauslesen. Daher wäre es (zumindest für mich) schon interessant, warum Du das machen willst.

File Table gibt es AFAICS erst ab SQL Server 2012. Für 2008 R2 bleibt daher nur Filestream. Lies hierzu aber bitte mal diese Artikel:

  http://www.insidesql.org/blogs/falkkrahl/2011/10/30/sql-server-2012-neue-funktionen

  http://msdn.microsoft.com/library/hh461480

  http://www.sqlskills.com/blogs/paul/sql-server-2008-filestream-performance/

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

Hallo Stefan,

die Dateien werden derzeit von einem FTP Server bereitgestellt, aber seit dem letzten Umbau des FTP Servers zickt der rum und bricht immer wieder ab. Auch in der Vergangenheit gab es immer wieder Probleme, z.B. mit Umlauten im Dateinamen, oder der Codierung wenn auch Daten über das Dateisystem in die FTP Root kamen.

Weiterhin möchte der Admin die Abhängigkeit SQL Server - FTP Server gerne auflösen. Das würde auch die Firewall einfacher machen.

Auch das bereitstellen einer Testumgebung oder die Wiederherstellung ist nicht einfach, da 2 Datenstände synchronisiert werden müssen.

Allerdings will ich auch nicht 'Not' gegen 'Elend' tauschen.

Ich habe mir die Dateien gerade nochmals angesehen. Ich schätze das 90% unter 256KB haben. Die größte Datei (mpeg was sonst) hat 131MB.

Der Server läuft auf Win2008R2 32GB

Meine bisherigen Test haben mir den Eindruck vermittelt, das ADODB.Stream stabiler und schneller als FTP ist. Deine verlinkten Artikel widersprechen dem.

Wie kann ich das verwalten der Dateien auf dem SQL Server sicher und einfach gestallten?

Gruß Dieter

Answer 3

Hallo Dieter,

ADODB.Stream sagt mir, dass ihr mit etwas ziemlich altem arbeitet? Falls ja, was genau verwendet ihr da?

FTP ist sicherlich nicht das gelbe vom Ei. Ich persönlich würde eher http für den Download verwenden, also einen Webserver (IIS oder ähnliches), der die Dateien ausliefert.

Bei derart vielen Dateien (100 GB, wenn 90% <= 256 KB sind) würde ich persönlich auf keinen Fall mit BLOBs, die direkt in der Datenbank gespeichert werden, arbeiten (also bspw. image, ... Spalten). Bei SQL Server 2012 (welches ihr ja aber nunmal [noch?] nicht einsetzt) käme Filetable evtl. in Frage. Ich muss gestehen, dass ich mich damit noch nicht allzusehr auseinandergesetzt habe. Wenn ich die Erfahrungen der Kollegen so lese, ist es aber wohl schon um einiges besser verwendbar als Filestream.

Aber wenn ich ganz ehrlich sein soll, würde ich das dennoch nicht machen. Dateien gehören für mich ins Dateisystem. Für den seltenen Fall, dass es zwingende Gründe für die Verwendung von Filestream und Co. gibt, muss man da zwar durch aber das sehe ich hier nicht.

Die Verwaltung ist natürlich nicht so einfach, da man, wie Du es ja auch geschrieben hast, 2 Datenquellen hat, die man miteinander verbinden muss. Allerdings ist das eigentlich problemlos, wenn man ein Auge darauf hat, wer was auf welche Art anpassen darf.

Erlaubt man bspw. "jedem", irgendwelche Dateien auf dem Webserver hinzuzufügen, zu ändern oder zu löschen, kann man das natürlich nicht auf einfachem Weg mit der Datenbank synchronisieren. Dasselbe gilt für den Fall, dass man direkt in der Datenbank arbeiten kann (bspw. über das SSMS). Klar geht das auch hier, bspw. über Trigger, FileSystemWatcher, ... einfacher wird es damit aber nicht^^

Wenn man über die Anwendung arbeitet, hast Du ja die Kontrolle über die Aktionen. Da sollte das Synchronisieren kein Problem sein.

Was dein Argument mit der Wiederherstellung angeht: Ich persönlich sehe da kein Problem, eine Datenbank- und eine Dateisystemsicherung zu erstellen und aus den Sicherungen im Havariefall auch wiederherzustellen. Allemal besser als sich die Datenbank extrem aufzublähen^^

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 4

Hallo Stefan,

ja, das ist was altes. VB6 / ADO Anwendung entwickelt unter SqlServer7.

Die Daten werden nur von Anwendungen bearbeitet, aber da gibt es mehrere die auf die gleichen Dateien zugreifen. Die 100GB verteilen sich auf mehrere Datenbanken. In der wichtigsten Datenbank befinden sich derzeit

20GB in 142.000 Dateien. die zweite hat

7,5GB in 34.000 Dateien und die dritte hat

12GB in 14.000 Dateien

Das ganze nochmals verdoppelt damit ich ein Test und ein Produktivsystem habe und ein wenig Reserve eingerechnet..

Es handelt sich um die Datenmenge von 12 produktiven Jahren.

Wie stellt sich die Lage nun bei diesen Fakten dar?

Danke für Deine Unterstützung

Gruß Dieter

Answer 5

Hallo Stefan,

der Admin ist sehr dafür, die Abhängigkeiten aufzulösen und alle Daten auf dem SQL Server zu haben. Ich schreibe gerade mal die 20GB (14000 Dateien) in eine Testdatenbank die nur aus einer Tabelle besteht.

CREATE TABLE[dbo].[xFtpDok](
[Pfad][nvarchar](200) NOT NULL,
[Datei] [nvarchar](250) NOT NULL,
[Daten] [image] NULL,
[Laenge] [int] NULL,
Datum und Anwender von Anlegen; Ändern; Letztes Lesen; Löschen;
Key auf Pfad+Datei

 Der Netzwerkadapter gelb = Datei Server lesen; rot = SQL Server schreiben, zeigt mir dass der SQL Server schneller als der File Server ist.

Der Server hat 32GB Speicher und stand vor dem Job bei 12GB in Benutzung. Jetzt nach ca 6GB und 45000 geschriebenen Dateien hat der Server 20,5GB Speicher in Benutzung. Er merkt sich also alles, aber ist ja noch Platz. Der Job dauert noch ein wenig, da auf langsamen Clientrechner und zwischen den Dateien immer eine kleine Pause 0,2 Sekunden.

Bisher läuft aber alles Fehlerfrei.

Auf welche Dinge muss ich besonders achten wenn ich diese Testumgebung einem Stresstest unterziehen will?

Gruß Dieter