Legacy-Daten importieren: Konvertierung, Unicode-Support und Sicherheit bei einem Zwei-Server-System

• » Home
• »SQL-Praxis
• » Upload von Legacy-Daten

Im Rahmen der Entwicklung des Hauptprojektes Server-Daten: Die Web-Datenbank für erstklassige Unternehmen stellte sich die Frage, wie Daten aus anderen Systemen in das hiesige System übernommen werden können. Auf den ersten Blick ist das Anbieten einer solchen Import- und Export-Möglichkeit zwingend, um Nutzern von Altsystemen, bei welchen eine Internet-Anbindung nicht realisierbar ist, einen Umstieg zu ermöglichen, ohne daß alle Daten neu eingegeben werden müßten. Eine genauere Betrachtung lehrte jedoch, daß bei einem unter dem Gesichtspunkt der Sicherheit zwingenden Zwei-Server-System eine Reihe von Problemen auftreten. Ein solcher Import sollte die folgenden Anforderungen erfüllen:

1. Altsysteme bieten manchmal nur sehr eingeschränkte Exportmöglichkeiten. So mag als Dezimaltrennzeichen entweder Komma oder Punkt unveränderbar eingestellt sein, die Währungsangabe wird in der Form 'DM 00,00' oder '00.00 $' ausgegeben. Eine Importlösung sollte Werte mit Währungsangaben korrekt als Dezimalzahlen einlesen können. Analog gilt dies für Datumsangaben, die bsp. in der deutschen Form Tag.Monat.Jahr oder in der amerikanischen Form Monat/Tag/Jahr vom Altsystem exportiert werden.
2. Greift der Webserver beim interaktiven Hinzufügen neuer Zeilen aus Sicherheitsgründen nur über gespeicherte Prozeduren auf die Datenbank zu, so wäre die Performance inakzeptabel, falls mit derselben Technik tausende von Zeilen importiert werden würden: Der Webserver müßte für jede Zeile die Parameter der gespeicherten Prozedur neu belegen und den Befehl ausführen, dieser würde für jede Zeile eine erneute Berechtigungsprüfung durchführen. Ein Designziel besteht folglich darin, die Sicherheitsprüfung nur einmal zu Beginn auszuführen und die Eingabe nicht zeilenweise, sondern in größeren Blöcken zu verarbeiten. Gleichwohl muß die Größe der Blöcke limitiert sein, damit nicht ein einziger Upload den gesamten Arbeitsspeicher des Webservers blockiert.
3. Enthalten die Upload-Daten Unicode, so sollten diese korrekt verarbeitet werden.
4. Unter dem Gesichtspunkt der Sicherheit darf weder der Datenbankserver auf die Festplatte des Webservers noch umgekehrt der Webserver auf die Festplatte des Datenbankservers Zugriff erhalten. Würde ein solcher Zugriff erlaubt werden, so müßte die Sicherheit zwischen beiden Servern konfiguriert werden und einem Konto Leserecht auf die andere Festplatte erteilt werden.
5. Es soll keine eigene Routine derart implementiert werden, daß mit Regular Expressions - Anweisungen der Input in Zeilen zerlegt und versucht wird, den korrekten Datentyp zu bestimmen. Ebenso soll keine eigene Routine entwickelt werden, die - bei bekannten Datentypen für jede Spalte - zeilenweise die gesamte Datei einliest und jede Zeile in Zellen zerlegt. Denn jede eigene Lösung auf diesem basalen Niveau bedeutet zum einen einen großen Implementierungsaufwand, zum anderen ist davon auszugehen, daß jede eigene Lösung langsamer ist als die Nutzung vorhandener Import- und Exportwerkzeuge.

Innerhalb der .NET-Programmierumgebung bzw. des als Backend genutzten MS-SqlServers existieren Werkzeuge, die für die Entwicklung einer solchen Logik nutzbar sind. Im einzelnen sind dies:

1. Ansprechen einer Textdatei per OleDb: Jedes Verzeichnis mit Textdateien stellt eine Datenbank im Textformat dar und kann mit dem OleDb-Provider 'Microsoft.Jet.OleDb.4.0' direkt angesprochen werden. Dieser Provider wird zwar üblicherweise genutzt, um auf Access-Datenbanken zuzugreifen. Wird jedoch der ConnectionString um 'Extended Properties=Text;' ergänzt, so kann auf eine Textdatenbank per Sql zugegriffen werden. Befindet sich im Verzeichnis eine Datei 'schema.ini', so kann diese sowohl Hinweise zu Spalten- und Zeilentrennern (Komma, Semikolon, Tabulator) als auf Festlegungen über Dezimalzeichen, das Währungssymbol sowie dessen Position (DM 50.00 versus 50,00 €) enthalten. Sind diese Werte korrekt belegt, so werden die Datentypen in der Regel zuverlässig erkannt. Im Normalfall, etwa beim Textimport-Assistenten für Access, genügt eine Vorschau, um die korrekten Zeilen- und Spaltentrenner zu identifizieren bzw. zu korrigieren (näheres zu den Optionen der schema.ini ist unter Schema.ini File - Text File Driver zu finden).

    

   Diese Technik kann sowohl vom Webserver als auch vom Datenbankserver (mit Ms-SqlServer als DBMS) genutzt werden. Beim Webserver genügt es, eine OleDb-Connection zu erstellen. Über diese können entweder Informationen zu den Spalten abgefragt werden oder die Daten werden in ein DataSet geladen und von der Quelle getrennt. Das folgende Beispiel liest die Datei 'C:\Temp\upload-12345\Artikel.txt' ein (die Aufteilung in mehrere Zeilen erfolgt lediglich aus Gründen der Html-Darstellung):

   Dim oConn As New OleDbConnection( _
   	"Provider=Microsoft.Jet.OleDb.4.0;" & _
   	"Data Source=""C:\Temp\upload-12345"";" & _
   	"Extended Properties=Text;"), _
   	dT As DataTable, _
   	oCmd As New OleDbCommand( _
   		"Select * From [Artikel#txt]", oConn), _
   	oDA As New OleDbDataAdapter(oCmd), _
   	ds As New DataSet()
   
   oConn.Open()
   
   dT = oConn.GetOleDbSchemaTable( _
   	OleDbSchemaGuid.Columns, _
   	New Object() {Nothing, Nothing, _
   	"Artikel#txt", Nothing})
   
   'dT enthält nun die Metainformationen zu den
   '	Spalten der Tabelle Artikel.txt
   
   oDA.Fill(ds, "Artikel")
   
   'ds enthält nun die Tabelle Artikel
   

   Für die Ausführung vom Datenbankserver her kann entweder ein Verbindungsserver erstellt oder, da es sich um ein temporäres Verzeichnis mit einmaligem Zugriff handelt, eine einmalige Verbindung mit der OpenRowSet- oder der OpenDataSource-Funktion geöffnet werden. Einer der drei folgenden Befehle kopiert die Daten bsp. direkt in eine temporäre Tabelle (entfernen Sie vor der Verwendung des mittleren Beispiels die Zeilenumbrüche im Parameter):

   Select * Into #temp_Table_ORS
   From OpenRowSet('Microsoft.Jet.OleDb.4.0',
   	'Text;Database=C:\Temp\upload-Dir-12345;',
   	'Select * From [Artikel#txt]')
   
   
   Select * Into #temp_Table_ODS
   From OpenDataSource('Microsoft.Jet.Oledb.4.0',
   	-- die folgenden drei Zeilen zusammenfassen
   	'Data Source="C:\Temp\upload-Dir-12345";
   	User Id=Admin;
   	Password=;Extended Properties=Text')...[Artikel#txt]
   
   oder
   
   Select * Into #temp_Table_ODS
   From OpenDataSource('Microsoft.Jet.Oledb.4.0',
   	'Text;Database="C:\Temp\upload-Dir-12345"')...[Artikel#txt]
   	

   Beachten Sie, daß der Zugriff mit der OpenRowSet-Funktion zwar komplexer erscheint und die einzelnen Parameter sehr fehleranfällig sind. Die erste Abfrage wird jedoch auf Textdateien ungefähr doppelt so schnell wie die zweite Abfrage ausgeführt. Die Überprüfung mit der 'Set ShowPlan_Text On' - Option ergibt, daß im zweiten Fall zunächst das Schema abgefragt und ein detaillierter Sql-String mit den einzelnen Tabellenspalten erstellt wird. Dieser wird anschließend lokal ausgeführt. Die OpenRowSet-Funktion übergibt dagegen die Sql-Anweisung als Sql-Pass-Through-Abfrage vollständig an den Provider und erhält das gesamte Recordset zurück. Da für den Zugriff aus Sicherheitsgründen ein eigener Prozeß erstellt wird, ist die hierfür notwendige Interprozeßkommunikation weitaus geringer als im Fall der lokalen Ausführung mit der OpenDataSource-Funktion. Beim Einlesen einer 50-KB-Textdatei werden mit der OpenDataSource-Funktion jeweils etwa 50% der Gesamtzeit für die Kompilierung und für die Ausführung benötigt, die OpenRowSet-Funktion bewältigt die Ausführung in einer etwas kürzeren Zeit, die Kompilierungszeit ist zu vernachlässigen. Analoge Performance-Unterschiede können bei Verbindungsservern beobachtet werden, bei welchen Tabellen entweder über den vierteiligen Namen oder mittels der OpenQuery-Funktion (Select * From OpenQuery('Verbindungsserver', 'Select * From Artikel')) eingelesen werden.
2. Der Vorteil dieser Technik besteht darin, daß beliebige ODBC-Formatdefinitionen, wie sie in der Systemsteuerung länderspezifisch festgelegt werden können, unterstützt werden. Damit ist für den Import kaufmännischer Daten mit Währungsangaben diese Methode erste Wahl. Zwei Nachteile sind offenkundig: Zum einen unterstützt diese Technik noch kein Unicode, so daß nur 8-Bit-Codierungen eingelesen werden können. Zum anderen ist ein Zugriff auf eine Datei, also auf die Festplatte notwendig. Erfolgt der Zugriff vom Webserver, so sind die Daten anschließend nur im Arbeitsspeicher des Webservers, noch nicht in der Datenbank. Wird der Befehl direkt vom Sql-Server her ausgeführt, so greift dieser auf die Festplatte des Webservers zu oder die Daten wurden zuvor vom Webserver-Prozeß auf der Festplatte des DB-Servers gespeichert. Beide Zugriffe widersprechen dem Prinzip der minimalen Rechte unmittelbar.
3. Import mit dem Dienstprogramm Bcp.exe bzw. Bulk Insert: Diese Technik gestattet es, eine durch beliebige Trennzeichen gegliederte Datei zu importieren, sie erlaubt ein Überspringen von Spalten und kann Unicode-Input korrekt verarbeiten. Sie erfordert jedoch die vorherige Erstellung einer exakten Formatdatei, welche die Datentypen festlegt. Allen Datentypen (Zahlen, Währungen) ist ein genaues Format zugewiesen, so daß auch Dezimalzahl-Trenner und Währungszeichen nicht optional sind. Diese Methode kann also nur eingesetzt werden, falls die Datentypen der Quelle bereits bekannt sind und diese eine genau bestimmte Form haben. Ferner ist der Zugriff des ausführenden Prozesses auf die Festplatte notwendig. Bulk Insert ermöglicht ausschließlich den Datenimport und kann innerhalb einer gespeicherten Prozedur gestartet werden, die Daten werden nur prozeßintern bewegt. Für Bcp.exe muß ein zusätzlicher Betriebssystemprozeß gestartet werden, hiermit gelingt sowohl der Import als auch der Export von Daten (näheres zu beiden Tools siehe Importing and Exporting Bulk Data, dort die beiden Unterseiten).

Zusammenfassend können die folgenden Anforderungen genannt werden:

• Verarbeitung beliebiger Währungs- und Datumsformate
• Einlesen von Unicode-Spalten
• einmalige Sicherheitsüberprüfung pro Upload
• kein zeilenweises Versenden vom Web- zum Datenbankserver
• Kein Zugriff von einem der beiden Serverprozesse auf die Festplatte des anderen Servers

Die implementierte Lösung im Detail

Die inzwischen implementierte Lösung kombiniert all diese Techniken. Sie erscheint zwar auf den ersten Blick redundant oder unnötig komplex. Jede einfachere Lösung würde jedoch bedeuten, mindestens eines der Designziele aufgeben zu müssen. Beim Upload werden die folgenden Schritte durchlaufen:

1. Nach dem Upload der Daten durch den Benutzer werden diese zunächst temporär auf dem Webserver gespeichert, so daß der benötigte Arbeitsspeicher freigegeben ist. Der Zugriff per OleDb auf die etwa 20 ersten Zeilen ermöglicht eine Identifikation der Spalten. Ist das Ergebnis nicht korrekt, so können Kriterien (Spalten- und Dezimaltrenner, Währungszeichen, Währungsformat, Datumsformat) explizit festgelegt und im Hintergrund in eine Schema.ini geschrieben werden. Alle in der Schema.ini definierbaren Optionen stehen damit Nutzern zur Verfügung. Ferner kann für einzelne Textspalten eine Unicode-Option gewählt werden.
2. Ist das Ergebnis zufriedenstellend, so speichert der Nutzer das Ergebnis ab und startet hierdurch den eigentlichen Import. Im Hintergrund wird der gesamte Input in größere Blöcke zerlegt und jeder Block in eine temporäre nText-Zelle, also eine Zelle für größere Unicode-Daten, geschrieben. Zusätzlich werden eine passende Schema.ini sowie, falls die Unicode-Option für eine Spalte gewählt wurde, eine geeignete Formatdatei als String erzeugt und ebenfalls in Zellen gespeichert. Damit minimiert sich der Datentransfer zwischen Web- und Datenbankserver, insbesondere erfolgt keine zeilenweise Übertragung. Anschließend wird, gemäß dem Prinzip der minimalen Rechte, ein Job angestoßen, welcher die eigentliche Zerlegung auf dem Datenbankserver erledigt (zur Technik siehe Sql-Befehl als SysAdmin ).
3. Der Job startet einen externen Betriebssystemaufruf, um mit dem Bcp.exe die Zellen als einzelne Blöcke in ein temporäres Verzeichnis auf die Festplatte zu speichern. Dies gelingt deshalb problemlos, da das Bcp.exe mit der Option -queryout eine Sql-Abfrage als Quelle erlaubt. Letztere nutzt die Id des aktuell verarbeiteten Blocks. Da hierbei der ganze Block eine Einheit bildet und nicht zerlegt wird, ist die Performance dieser Aktion akzeptabel. Der Befehl sieht in etwa wie folgt aus:

   Set @cmd = 'bcp "Select A.Upload_Text From ' +
   	'_tbl_Uploads As A Where A._tbl_UploadsId = ' +
   	Cast(@_tbl_UploadsId as nvarchar(5)) +
   	'" queryout "C:\Temp\upload-54321\myOutput_' +
   	Cast(@_tbl_UploadsId as nvarchar(5)) + '.txt' +
   	'" -c -C RAW -T'
   
   Execute (@cmd)
   

   Der Parameter -c bedeutet, daß die Standardwerte für Zeilen- und Spaltentrenner genutzt werden. Da nur eine einzige Zelle zu speichern ist, werden nicht wirklich Zeichen hinzugefügt. -C RAW bewirkt eine Speicherung ohne eine Codepage-Übersetzung, -T erzwingt eine vertraute Verbindung. Nach demselben Muster werden die Schema.ini sowie, falls notwendig, die Formatdatei (.fmt) in dasselbe Verzeichnis geschrieben. Es schreibt also weder der Webserver-Prozeß Daten auf die Festplatte des Datenbankservers noch greift der Datenbankserver-Prozeß lesend auf die Festplatte des Webservers zu.
4. Anschließend kann über einen OleDb-Zugriff der Block direkt in eine temporäre Tabelle importiert werden. Die Schema.ini enthält die vom Benutzer festgelegten Werte für Dezimaltrenner, Währungszeichen und ähnliches. Allerdings werden die Spalten mit Unicode-Daten hierbei fehlerhaft interpretiert, da der Zugriff auf Textdaten Unicode nicht unterstützt. Gibt es solche Spalten nicht, so ist der Import an dieser Stelle abgeschlossen, es kann die eigentliche Zieltabelle mit passenden Metadaten erzeugt und die Daten in diese kopiert werden.
5. Für die Verarbeitung der Unicode-Spalten hat sich ein zweiter Bcp-Export als notwendig erwiesen. Diese verwendet den folgenden Befehl:

   Set @cmd = 'bcp "Select A.Upload_Text From ' +
   	'_tbl_Uploads As A Where A._tbl_UploadsId = ' +
   	Cast(@_tbl_UploadsId as nvarchar(5)) +
   	'" queryout "C:\Temp\upload-54321\myWOutput_' +
   	Cast(@_tbl_UploadsId as nvarchar(5)) + '.txt' +
   	'" -w -T'

   Der einzigste Unterschied liegt darin, daß die Daten mit der -w - Option geschrieben werden und die RAW-Option fehlt. Dies erstellt die Ausgabe im UTF-16-Format, so daß der Output doppelt so groß ist wie die erste Version.
6. Die Formatdatei bcp.fmt, hier gezeigt für die Tabelle 'Artikel' aus dem Sql-Tutorial, enthält für jede Unicode-Spalte (hier: A_Name) eine spezielle Zeile:

   8.0
   3
   1 SQLCHAR 0 0 "\t\0"	1	[A_NR]		""
   2 SQLCHAR 0 0 "\t\0"	2	[A_NAME]	""
   3 SQLCHAR 0 0 "\r\0\n\0"	0	[A_PREIS]	""

   Als Datentyp wird SQLCHAR, nicht, wie vielleicht erwartet, SQLNCHAR angegeben. Ferner wird der vom Benutzer festgelegte Spaltentrenner, mit welchem der Output mit der -w - Option geschrieben wird, hier direkt in der internen UTF-16-Codierung als Kombination aus dem Hauptzeichen sowie #x0 angegeben. Das obige Beispiel gilt, falls der Nutzer den Tabulator als Spaltentrenner gewählt hat. Nach demselben Muster ist für das Zeilenende "\r\0\n\0" anstelle des für ASCII-Dateien gültigen "\r\n" codiert. Da für die dritte Spalte keine Serverspalte angegeben ist, wird diese übersprungen. Nun können die ID-Spalte und alle Unicode-Spalten in eine zuvor erstellte weitere Tabelle eingelesen werden, die nur diese Spalten enthält. Dafür kann direkt Bulk Insert genutzt werden, ein Umweg über einen Betriebssystemaufruf ist nicht mehr notwendig:

   Set @cmd = 'Bulk Insert __temp_Table_1' +
   	' From ''C:\Temp\upload-54321\myWOutput_' +
   	Cast(@_tbl_UploadsId as nvarchar(5)) +
   	'.txt'' With (DataFileType = ''widechar'', ' +
   	'FirstRow = 2, ' +
   	'FormatFile = ''C:\Temp\upload-54321\bcp.fmt'')'
   Execute (@cmd)
   	

   Dieser Ladebefehl legt als Datentyp 'widechar', also Unicode fest, überspringt für den ersten Block die erste Zeile, falls diese die Spaltennamen enthält und nutzt ansonsten die Informationen aus der bcp.fmt. Diese legt durch die Informationen in der drittletzten Spalte fest, daß nur die ID-Spalte sowie die Spalten mit Unicode-Codierung importiert werden. Anschließend wird auf der ersten temporären Tabelle durch eine Verknüpfung mit der neuen Tabelle ein Update durchgeführt, welches die Unicode-Daten kopiert.

Betrachtet man nach diesen Einzelausführungen die gesamte Lösung, so fällt auf, daß weder die Sicherheit noch die Geschwindigkeit zu sehr eingeschränkt sind. Einerseits ist die Performance der Gesamtlösung durch die Nutzung der Systemroutinen sowie deren Verarbeitung von Blöcken akzeptabel. Aufgrund des Aufrufs über einen Job steht das Ergebnis, die neue Tabelle, ohnehin nicht interaktiv, sondern erst verzögert zur Verfügung. Andererseits muß kein zusätzlicher Kommunikationskanal zwischen Web- und Datenbank-Server eingerichtet werden. Die Daten werden als ganz gewöhnliche nText-Felder vom Webserver zum Datenbank-Server bewegt, letzterer schreibt den Block am Stück aus und liest ihn zeilen- bzw. schließlich zellweise wieder ein.

 

Auf den ersten Blick scheint noch eine andere Lösung denkbar: Der Webserver könnte die Daten als Unicode-Stream auf seiner eigenen Festplatte speichern und anschließend einen eigenen Bcp.exe-Prozeß starten. Dieser würde sich mit derselben AspNet-Kennung, mit welcher auch der Webserver auf den Datenbankserver zugreift, an letzterem anmelden und die Daten importieren. Diese Lösung erfordert jedoch das Recht für den AspNet-Nutzer, in einer gesonderten Datenbank geeignete Tabellen zu erstellen sowie die Berechtigung für den Massenimport von Daten. Eine solche Architektur würde dem Prinzip der minimalen Rechte (Principle of least Privilege, siehe Zur Architektur von Datenmanagement - Systemen ) unmittelbar widersprechen. Bei der tatsächlichen Implementierung erhält der AspNet-Nutzer, also der Webserver, nur das Recht, diese Datenpakete zu speichern und den Verarbeitungsprozeß anzustoßen. Der Unterschied wird am Beispiel eines gehackten Webservers deutlich. Hier könnten beide Rechte - Erstellen von Tabellen in einer gesonderten Datenbank sowie das Recht, einen Massenkopier-Batch zu starten - rasch zu Problemen führen.

 

Neu im Dezember 2006: Der Microsoft Sql-Server 2005

Bei der Umstellung des Backends vom MS-Sql-2000 auf den MS-Sql-2005 muß der Datentyp in der bcp.fmt-Datei auf

SQLNCHAR

geändert werden. Ferner schien das Einlesen mittels des Microsoft.OleDb.Jet.4.0-Treibers zunächst nicht zu funktionieren, obwohl alle Sicherheitsregeln beachtet worden waren. Wiederholt wurden Fehler der Form

Meldung 7303, Ebene 16, Status 1, Server D80-237-183-226\SQL2005, Zeile 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OleDb.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OleDb.4.0" for linked server "(null)" returned
message "Unbekannter Fehler" (Unknown Error).

ausgegeben. Schließlich stellte sich heraus, daß es sich derzeit um einen sehr versteckten Bug handelt. Um solche Ad-Hoc-Queries auszuführen, muß der Schlüssel

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\Interner Instanz-Name
	\Providers\Microsoft.Jet.OLEDB.4.0

seit einem ServicePack des Sql-2000 einen Schlüssel

DisallowAdHocAccess

mit dem Wert 0 enthalten. Laut der Ansicht im Sql-Management-Studio war dieser Schlüssel mit dem korrekten Wert gesetzt. Eine manuelle Kontrolle der Registrierung ergab jedoch, daß ein Aushaken dazu führt, daß das Sql-Management-Studio den Schlüssel löscht, anstatt ihn auf 0 zu setzen. Ein fehlender Schlüssel wird jedoch als

DisallowAdHocAccess = 1

interpretiert und verweigert die Ausführung von OpenRowset und ähnlichen Ad-Hoc-Abfragen für diesen Datenprovider.

 

Lösung, falls Sie bei sich dasselbe Problem beobachten:

• Haken Sie den Wert an, dies erzeugt den Schlüssel.
  Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.
• Schließen Sie das Sql-Management-Studio, öffnen Sie die Registrierung und suchen Sie den Schlüssel.
  Close the Sql-Management-Studio, open RegEdit and search the key. If you have more than one instance, use the right internal name (MSSQL.1 / MSSQL.2 etc.).
• Ändern Sie den Wert auf 0.
  Change the value to 0
• Zur Übernahme ist ein Neustart des MS-SqlServers notwendig.
  Restart your MS-SqlServer. Using Reconfigure doesn't help.

Die Inhalte wurden auf englisch ergänzt, da es diverse Foreneinträge zu diesem Thema gibt.