Umstellung der Team4-eigenen SSIS
Werbung
FH Aachen, Abteilung Jülich Umstellung der Team4-eigenen SSIS-Komponenten auf den Standard-Protokoll-Mechanismus auf Basis von Microsoft SQL Server 2012 Johann Jansen y Alegret 23. Dezember 2011 Inhaltsverzeichnis 1. Einleitung 3 2. Grundlagen 2.1. Microsoft SQL Server Integration Services 2.1.1. Allgemeines . . . . . . . . . . . . . 2.1.2. Control Flow . . . . . . . . . . . . 2.1.3. Data Flow . . . . . . . . . . . . . . 2.1.4. Connection Manager . . . . . . . . 2.2. SSIS-Komponenten von Team4 . . . . . . 2.3. Neuerungen bei Microsoft SQL Server 2012 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 4 4 4 5 6 6 12 3. Aufgabenstellung: Machbarkeitsstudie zur Umstellung der SSIS-Komponenten von Team4 auf den Protokoll-Mechanismus von MS SQL Server 2012 (Proof of Concept) 14 4. Analyse 16 4.1. Team4-spezifisches Verfahren zur Speicherung des Protokolls . . . . . . . 16 4.2. Protokollverfahren in Microsoft SQL Server 2012 . . . . . . . . . . . . . . 17 4.3. Vergleich der Verfahren (GAP-Analyse) . . . . . . . . . . . . . . . . . . . 18 5. Realisierung: Notwendige Schritte zur Umstellung des Protokoll-Mechanismus 19 6. Ergebnisse und Schlussfolgerungen 24 7. Zusammenfassung und Ausblick 25 A. Literaturverzeichnis 26 B. Abkürzungsverzeichnis 27 2 1. Einleitung Die Firma Microsoft bietet ein SQL-Datenbanksystem mit dem Namen Microsoft SQL Server an. Die derzeit aktuelle Version ist MS SQL Server 2008R2. Im Jahr 2012 soll eine neue Version mit der Bezeichnung MS SQL Server 2012 herauskommen. Es wurden für Entwicklungs- und Testzwecke schon Vorabversionen veröffentlicht, die zuerst unter dem Codenamen „Denali“ und aktuell als „MS SQL Server 2012RC0“ bereitgestellt wurden. Anhand der bereitgestellten Dokumentation ist schon zu erkennen, dass viele Neuerungen geplant sind. Eine wesentliche Neuerung betrifft die Technologie „SQL Server Integration Services“ (SSIS), die bei bestimmten Version von MS SQL Server mitgeliefert wird. Diese Technologie zur graphischen Erstellung von ETL-Programmen (Extract Transform Load), die als Pakete bezeichnet werden, wird von Team4 viel verwendet. Es wird bei dieser Technologie die Möglichkeit geben, die Pakete in ein neu erschaffenen Katalog abzulegen, der eine spezielle Datenbank ist. Bei der Ausführung dieser Pakete im Katalog werden im Gegensatz zu früher automatisch Protokollierungen vorgenommen. Die Protokolleinträge werden ebenfalls im Katalog gespeichert. Für diese SSIS wurden von Team4 Komponenten entwickelt, mit denen man u. a. in „Lotus Notes“ und in „Microsoft Dynamics CRM“ Daten lesen und schreiben kann. Diese Komponenten erstellen bei der Ausführung ein Protokoll, dass in Datenbanktabellen gespeichert wird. Diese Tabellen sind in einer von Team4 angelegten Datenbankstruktur und werden von keinem anderen Hersteller verwendet. Dies hat den Nachteil, dass die Auswertung der Protokollierung nur mit Reporten von Team4 möglich ist und nicht mit standardisierten Reporten. Diese selbst entwickelten Komponenten sollen in Zukunft ihre Protokolleinträge stattdessen in den Katalog schreiben. Es wird im folgenden untersucht, ob diese Umstellung möglich ist. Dazu werden der alte und der neue Protokoll-Mechanismus verglichen und dokumentiert, was bei einer Umstellung zu berücksichtigen wäre. In dem Proof of Concept wird dann abschließend beurteilt, ob die Umstellung sinnvoll wäre, falls sie überhaupt möglich erscheint. 3 2. Grundlagen 2.1. Microsoft SQL Server Integration Services 2.1.1. Allgemeines Die SQL Server Integration Services (SSIS)von Microsoft sind ein Bestandteil von Microsoft SQL Server. Sie können für Datenintegrationszwecke verwendet werden. Man kann die Daten in Datenbanken damit bereinigen oder auch transformieren. Man kann diese Dateien auf verschiedene Art und Weise sich anliefern lassen und versenden, z.B. auch per FTP. Es können Benachrichtigungen von bestimmten Ereignisse per Email versendet werden. Komplexe Geschäftsprozesse können mit Hilfe der SSIS abgebildet werden.Microsoft (2011b) Es können Daten aus verschiedenen Quellen gelesen und in verschiedene Ziele geschrieben werden. Quellen und Ziele können alle möglichen Arten von Datenbanken sein, aber auch bestimmte Dateien können mit den SSIS verwendet werden. Mitgeliefert werden unter anderem Schnittstellen für Datenbanken, die OLE DB oder ADO.NET unterstützen. Auch sind Schnittstellen für Excel-Dateien, CSV-Dateien (Flat Files) und AccessDateien mitgeliefert. Die Liste der möglichen Datenbankentypen und Dateitypen lässt sich durch Programmierung eigener Komponenten erweitern, bzw. solche Komponenten werden von verschiedenen Firmen angeboten. Team4 bietet aktuell SchnittstellenKomponenten für Lotus Notes und Microsoft Dynamics CRM an. Diese Komponenten lassen sich in den Programmiersprachen VB.NET und C# entwickeln. Die dazu notwendigen Programmressourcen und Interface-Definitionen werden von Microsoft mit dem SQL Server mitgeliefert, bzw. stehen frei verfügbar im Internet zur Verfügung. Zur Erstellung der SSIS-Jobs werden Pakete (Packages) erzeugt, die mit einer graphischen Entwicklungsumgebung bearbeitet werden. Diese Entwicklungsumgebung ist ein Bestandteil des „Business Intelligence Development Studio“ und in „Microsoft Visual Studio“ eingebettet. Es stehen dort einem eine Vielzahl an Tools zur Verfügung. Auf der obersten Ebene ist der „Control Flow“. In diesen Control Flow können einzelne Task eingebunden werden. Diese Tasks sind individuell konfigurierbar. So gibt es z.B. Tasks die SQL-Befehle ausführen oder Skript-Tasks, in denen VB.NET oder C# Code verwendet werden kann. Eine besondere Task ist der „Data Flow“-Task. Dieser wird in der „Data Flow“-Ebene bearbeitet und besteht aus „Data Flow“-Komponenten, die auch individuell konfigurierbar sind. Es können mehrere „Data Flow“-Tasks in einem „Control Flow“ existieren. Im folgenden werde ich auf die beiden unterschiedlichen Ebenen „Control Flow“ und „Data Flow“ eingehen. 2.1.2. Control Flow Der „Control Flow“ ist eine Arbeitsfläche, in der sich Container oder Tasks befinden, die in der Toolbox ausgewählt und auf die Fläche gezogen werden. Container können 4 ein oder mehrere Tasks enthalten, Tasks können aber auch außerhalb eines Containers existieren. Mit dem Container werden Tasks gruppiert, auch ist es möglich, Container ineinander zu schachteln. Ein Task bildet einen einzelnen Prozess ab. Die Tasks sind miteinander über einen „Precedence Constraint“ verbunden. Dort wird bestimmt, ob der nächste Task ausgeführt wird, bzw. welcher Task als nächstes ausgeführt wird. So kann z.B. zwei unterschiedliche Tasks ausgeführt werden, je nachdem ob eine Bedingung erfüllt ist oder nicht. Vorgegebene mögliche Bedingungen sind Erfolg, Misserfolg und Beendigung des vorherigen Task. Tasks werden nacheinander ausgeführt und nicht gleichzeitig. Tasks, die in einem „For Loop Container“ oder in einem „Foreach Loop Container“ enthalten sind, werden so oft ausgeführt, wie es die dazugehörigen Bedingung verlangt. Es kann z.B. eine Liste in einer Variablen gespeichert werden. Die Listeneinträge können dann im Foreach Loop Container durchlaufen werden und dienen innerhalb des Containers als Parameter für die Tasks. 2.1.3. Data Flow Ein „Data Flow“-Task wird in einer eigenen Arbeitsfläche bearbeitet. Dort steht einem in der Toolbox eine Anzahl von Komponenten zur Verfügung, grob eingeteilt in „Data Flow Sources“, „Data Flow Transformation“ und „Data Flow Destination“. Diese werden auf die Arbeitsfläche gezogen und können mit einer „Data Flow Pipeline“ verbunden werden. Mit den Data Flow Source-Komponenten werden Daten aus einer Quelle gelesen, die mit dem Typ der Komponente übereinstimmen muss. Es gibt die mitgelieferte QuellKomponenten und die „Custom Components“. Mitgeliefert werden die Quell-Komponente für die schon im ersten Teil erwähnten Schnittstellen. Die Custom Components gehören zu den von Fremdfirmen entwickelten Schnittstellen-Erweiterungen für andere Dateiformate und Datenbanksysteme. Die Data Flow Destination-Komponente schreibt Daten in ein Ziel. Meistens existieren Ziel-Komponenten zu den gleichen Formaten und Datenbanksystemen wie zu den Quell-Komponenten. Sowohl die Ziel- als auch die Quell-Komponenten müssen meistens konfiguriert werden, z.B. welche Tabellen verwendet werden sollen. Die Verbindung zu der Datenbank bzw. Datei findet meistens über einen „Connection Manager“ statt. Diese werde ich im nächsten Abschnitt beschreiben. Als dritter Typ existieren die Data Flow Transformation-Komponenten. Mit ihnen werden die Daten transformiert. Sie haben einen Datenzulauf und einen Datenabfluss. Von ihnen sind eine große Anzahl mitgeliefert und man muss sie üblicherweise konfigurieren. Ein Data Flow-Task besteht üblicherweise aus einer oder mehreren Quell-Komponenten und einer oder mehreren Ziel-Komponenten. Dazwischen sind oft Transformations-Komponenten, von denen auch mehrere hintereinander sein können. Alle diese Komponenten werden über Data Flow Pipelines verbunden. Diese Pipelines transportieren die Daten von einer Komponente zur nächsten. Es gibt auch Transformations-Komponenten, die Daten von mehreren Pipelines zusammenführen oder die Daten in mehrere Pipelines verteilen. 5 Deswegen müssen die Anzahl der Datenquellen auch nicht mit denen der Datenziele übereinstimmen. 2.1.4. Connection Manager Üblicherweise existiert zu jeder Art von Datenbank oder Datei ein Typ von Connection Manager. Man kann in einem Paket mehrere Connection Manager vom gleichen Typ erstellen, falls mehrere Datenbanken/Dateien vom gleichen Typ verwendet werden. Diese Connection Manager werden sowohl von den Data Flow Source-Komponenten als auch von den Data Flow Destination-Komponenten verwendet. Es gibt auch Connection Manager, die von bestimmten passenden Control Flow-Tasks verwendet werden können. Z.B. kann ein OLE DB-Connection Mananger für den „Execute SQL Task“ verwendet werden. Darüber hinaus gibt es auch Connection Manager für allgemeine Verbindungen, in denen man z.B. Internetressourcen wie eine Website oder eine FTP-Verbindung angeben kann. 2.2. SSIS-Komponenten von Team4 Team4 hat eigene Custom Components für die SSIS entwickelt. Diese werden in Kundenprojekten verwendet, bei denen Microsoft SQL zum Einsatz kommt, aber auch einzeln vermarktet. Es gibt unter anderem den „Team4 Lotus Notes Connector“ und den „Team4 Microsoft Dynamics CRM Connector“. Außerdem gibt es noch einen „Team4 Log Task“. Der Team4 Microsoft Dynamics CRM Connector besteht aus einem Connection Manager für Microsoft Dynamics CRM-Systeme und dazugehörigen Data Flow Source und Data Flow Destination-Komponenten. Abbildung 1: ConnectionManager des Team4 Microsoft Dynamics CRM Connectors 6 Mit der Source kann man sogenannte Entitäten aus dem Microsoft Dynamics CRM (was in etwa einer Tabelle in SQL entspricht) oder das Ergebnis einer FetchXML-Abfrage auslesen. FetchXML ist eine XML-basierte Abfragesprache, die für das Microsoft Dynamics CRM-System verwendet werden kann und als Ergebnis Daten in Tabellenform liefert. Abbildung 2: Source-Komponente des Team4 Microsoft Dynamics CRM Connectors: Auswahl von Quelldatensätzen Aus diesen Daten kann man bestimmte Spalten (Columns) auswählen, die im Data Flow verwendet werden sollen. Abbildung 3: Source-Komponente des Team4 Microsoft Dynamics CRM Connectors: Auswahl von Quellattributen Die Destination-Komponente schreibt Daten in eine Entität. Es sind dort entweder Inserts, Updates oder Deletes möglich. 7 Abbildung 4: Destination-Komponente des Team4 Microsoft Dynamics CRM Connectors: Auswahl von Zielentität Dort muss eine Zuordnung der Eingangsdaten auf die Zielentität durchgeführt werden. Abbildung 5: Destination-Komponente des Team4 Microsoft Dynamics CRM Connectors: Auswahl von Zielattributen Der „Team4 Lotus Notes Connector“ besteht ebenfalls aus einem Connection Manager, Data Flow Source und Destination Komponenten. Lotus Notes ist ein dokumentenorientiertes Datenbanksystem, d.h. die Daten werden nicht in Tabellen, sondern in Dokumenten abgelegt. Jedes dieser Dokumente hat seine Daten in Feldern gespeichert, die in unterschiedlicher Anzahl vorhanden sein können. So ein Feld besteht jeweils aus einen Feldnamen, einen Datentyp und den Daten als Inhalt. Die Lotus Notes-Datenbankserver haben die Bezeichnung Domino. Im Connection Manager muss der Domino-Servername und der Pfad der Datenbank 8 angegeben werden. Es muss außerdem zur Authentifizierung eine Notes-ID und das zugehörige Passwort vorliegen, das ebenfalls im Connection Manager eingegeben wird. Abbildung 6: ConnectionManager des Team4 Lotus Notes Connectors Man kann mit der Source-Komponente aus Lotus Notes-Datenbanksystemen Dokumente auslesen. In der Source-Komponente muss man dann anhand bestimmter Kriterien auswählen, welche Dokumente man haben will. Man kann alle Dokumente, die im Feld FormName einen bestimmten Wert haben auswählen, oder auch eine Selektionsformel in der Lotus Notes-Formelsprache verwenden. Abbildung 7: Source-Komponente des Team4 Lotus Notes Connectors: Auswahl von Quelldatensätzen Man muss dann die Felder, die man haben will mit Namen und Zieldatentyp festlegen. Da bei Lotus Notes-Dokumenten Felder mit dem gleichen Feldnamen nicht zwangsläufig 9 den gleichen Typ haben, werden die Typen zu dem Zieldatentyp konvertiert, falls möglich, ansonsten wird eine Exception geworfen. Abbildung 8: Source-Komponente des Team4 Lotus Notes Connectors: Auswahl von Quellattributen Die Destination-Komponte kann entweder Updates, Insert oder Delete auf NotesDokumente in der Notes-Datenbank schreiben. Es müssen hier die Feldnamen und die Datentypen der Zielfelder festgelegt werden, falls Update oder Insert ausgewählt wurde. Bei Update und Delete müssen die jeweiligen Unique-Ids der Notes-Dokumente bekannt sein, die immer die Primärschlüssel der Notes-Dokumente sind. Sie sind bei allen NotesDokumenten vorhanden und es gibt auch keine andere Möglichkeit einen Primärschlüssel für Notes zu definieren. 10 Abbildung 9: Destination-Komponente des Team4 Lotus Notes Connectors: Auswahl von Zieldokumenten Der „Team4 Log Task“ kann im Control Flow verwendet werden. Er ist ausschließlich dafür konzipiert, eigene Meldungen in die Log-Datenbank zu schreiben. Man kann bei ihm den Typ der Meldung festlegen zwischen „Information“, „Warning“ und „Error“. Abbildung 10: Team4 Log Task Bisher wurde bei ihr wie auch bei den anderen Komponeten eine eigene von Team4 11 entwickelte Datenbankstruktur für Log-Meldungen verwendet worden. Es müssen für alle diese Team4-Komponenten insgesamt ein Connection Manager erzeugt werden vom Typ OLE DB, der auf die Protokolldatenbank zeigt. Diese soll durch den neuen Katalog von Microsoft SQL 2012 abgelöst werden. 2.3. Neuerungen bei Microsoft SQL Server 2012 Microsoft SQL 2012 wird gegenüber seinem Vorgänger MS SQL 2008R2 eine Vielzahl an Neuerungen bietenBachrach u. a. (2011). Die Einrichtung von Datenbankensystem mit Hochverfügbarkeit wird durch die Einführung neuer Technologien vereinfacht. Diese werden unter dem Oberbegriff „MS SQL Server AllwaysOn“ vermarktet. Teilweise gab es die Technologien schon vorher, doch es sind neue hinzugekommen. Zudem gibt es Neuerungen, die die Performance betreffen. Unter anderem wurde die Volltextsuche verbessert, auch gibt es jetzt die Möglichkeit, große Tabellen auf bis zu 15000 Partitionen aufzuteilen. Außerdem gibt es viele Neuheiten bei den Sicherheits- und Überwachungseinstellungen. Durch Datenbank-Authentifizierung kann man jetzt auf Datenbanken zugreifen, ohne eine Login für den SQL-Server zu haben. Die Audit-Funktionalität steht jetzt in allen Versionen von MS SQL 2012 zur Verfügung und wurde erweitert. Bei den SSIS von MS SQL 2012 ist jetzt eine neue Möglichkeit hinzugekommen, Pakete auf dem Server zu veröffentlichen: das „Project Deployment Model“ Ericson u. a. (2011). Es beinhaltet einige Änderungen zum vorherigen „Legacy Deployment Model“, das weiterhin verwendet werden kann. Folgende Unterschiede bestehen zwischen den beiden Modellen: • Anstatt von Konfigurationen werden Parameter verwendet, um die Eigenschaften (Properties) von Paketen zu bestimmen. • Es gibt Dateien (.ispac) für die Veröffentlichung, die das Paket und die Parameter enthällt, anstatt dass man die Paket-Dateien (.dtsx) und die Konfigurationsdateien (.dtsConfig) getrennt gespeichert veröffentlicht. • Das Projekt, das aus Paket und Parameter besteht, wird in einer speziellen neuen Datenbankstruktur, veröffentlicht, die mit dem neuen Modell eingeführt wurde. Diese wird als Katalog bezeichnet. Bei dem vorherigen Modell gab es die Möglichkeit, die Pakete im Dateisystem oder der Systemdatenbank „MSDB“ zu veröffentlichen. • Unterstützung der CLR (“Common Language Runtime“)ist im Gegensatz zu vorher auf Datenbankebene notwendig geworden. • Wichtigster Unterschied im Hinblick auf die Seminararbeit ist folgender: Beim der Ausführung von Paketen werden die Events automatisch erfasst und je nach Einstellung im Katalog gespeichert. Man kann diese dann mit SQL-Abfragen 12 auswerten. Beim vorherigen Model war es notwendig, einen „Log Provider“ dem Paket hinzuzufügen, um Events zu erfassen, sonst wurden diese nicht geloggt. 13 3. Aufgabenstellung: Machbarkeitsstudie zur Umstellung der SSIS-Komponenten von Team4 auf den Protokoll-Mechanismus von MS SQL Server 2012 (Proof of Concept) Die Team4 GmbH hat ein eigenes Customer Relationship Management System mit dem Namen „Lotus Notes CRM“ entwickelt. Viele Kunden dieser Software haben individuelle von Team4 angepasste Versionen dieses CRM-Systems gekauft. Auch gibt es Kunden, die Microsoft Dynamics CRM benutzen und individuelle Anpassungen bei Team4 beauftragen. Oft ist ein regelmäßiger automatischer Datenabgleich mit anderen Datenbanksystem und auch eine Aufbereitung der CRM-Daten für Reporte gewünscht. Bei beiden CRMSystemen gibt es integrierte Möglichkeiten zum Datenimport und Datenexport. Diese erfüllen jedoch nicht immer alle Anforderungen und sind nur eingeschränkt flexibel. Deswegen wird als Hilfsmittel oft ein Microsoft SQL Server mit den SSIS verwendet. Diese haben sich als sehr flexibel herausgestellt, so dass auch komplexe Transformationen relativ einfach umgesetzt werden können. Der Server fungiert dann als Schnittstelle zwischen allen Systemen. Er speichert die Daten zwischen und wird als „Datamart“ bezeichnet. Dieser Datamart führt regelmäßig ETL-Prozesse aus (Extract, Transform, Load). Die Daten werden aus dem Quellsystem geladen, gegebenenfalls transformiert und dann in das Zielsystem gespeichert. Bei den Kundenlösungen ist nicht immer eindeutig festgelegt, was das Quell- und was das Zielsystem ist. Das heißt, es kann auch bidirektionaler Datenaustausch stattfinden. Die ETL-Prozesse werden mit SSIS-Paketen realisiert. Dort kommen dann die Team4-Komponenten zum Einsatz, da ohne diese kein Zugriff der SSIS auf die CRM-Systeme von Microsoft und Team4 möglich ist. Diese Komponenten werden auch einzeln von Team4 angeboten, da sie auch von Fremdfirmen als Schnittstelle von SSIS zu Lotus Notes oder zu Microsoft Dynamics CRM verwendet werden kann. Bei der Ausführung von diesen SSIS-Paketen können einige Informationen anfallen, für die man sich im Nachhinein interessiert: War der Verbindungsaufbau erfolgreich, welche Entität wurde geladen, wie viele Datensätze wurden gelesen bzw. geschrieben, gab es Schwierigkeiten mit den Daten usw... . Für diese Informationen wurde eine eigene Datenbankstruktur erschaffen, in deren Tabellen diese Informationen protokolliert wurden. Man kann in diese Tabellen auch eigene Meldungen mit dem Team4-Log Task eintragen. Doch diese Datenbankstruktur ist nicht standardisiert und wird ausschließlich von Team4Komponenten verwendet. Darum besteht Interesse, diese Datenbankstruktur zu ersetzen. Es muss geprüft werden, ob und wie alle diese interessanten Information erfasst werden können durch den neuen Protokoll-Mechanismus von MS SQL Server 2012 im Project Deployment Model. Dann ist zu prüfen, ob sich relevante Unterschiede in der Verwendung ergeben und ob diese Unterschiede entscheidend für die Nutzbarkeit des neuen Mechanismus ist. 14 Protokolliert werden sollen: • Startzeitpunkt des Paketes • Name des Paketes • Text der Meldung • Ursprungskomponente der Meldung • Zeitpunkt der Meldung • Status der Meldung 15 4. Analyse 4.1. Team4-spezifisches Verfahren zur Speicherung des Protokolls Um Information zur Ausführung von Paketen dauerhaft festzuhalten setzen die Team4Komponenten einen Connection Manager zu einer OLE DB Datenbank voraus, die bei Team4 üblicherweise auf dem Datamart-Server liegt. In dieser Datenbank werden automatisch zwei Tabellen angelegt, falls diese noch nicht vorhanden sind: „T4LogBody“ und „T4LogHdr“. In der Tabelle „T4LogHdr“ stehen nur Informationen, welches Paket wann gestartet ist und wann endete, welche Laufzeit er hatte und ein Statusfeld. Dieses Statusfeld kann drei Werte haben: 0 für OK, 1 für Warning und 2 für Error. Der Status Warning wird gespeichert, wenn es mindestens eine Warnmeldung gegeben hat und keine Fehlermeldung. Error wird als Status gespeichert, wenn es mindestens eine Fehlermeldung gegeben hat. Ansonsten wird „OK“ als Status gespeichert. In der Tabelle „T4LogBody“ werden alle Meldungen gespeichert die von den Team4Komponenten protokolliert werden sollen. Diese enthält folgende Informationen: • ProcName: Name des ausgeführten Paketes • ObjType: Name des Objektes, dass den Protokolleintrag schreibt • StartTime: Startzeit des gesamten Paketes • MsgTime: Uhrzeit des Protokoll-Eintrages • Status: 0 regulär, 1 Warning, 2 Error • Msg: Text der Meldung Alternativ oder zusätzlich kann auch ein Connection Manager zu einer Textdatei verwendet werden, in der die Information gespeichert werden. Diese Möglichkeit wird bei produktiven Systemen von uns höchstens zusätzlich genutzt, da die Auswertung mit dem Team4-Log Report auf der Textdatei nicht möglich ist. Die Anzahl der Informationen, der beim Ausführung der Pakete protokolliert werden, lässt sich bei den Data Flow-Komponenten von Team4 durch Einstellung des Log Levels, einer Eigenschaft der Komponenten, beeinflussen. Das niedrigste Level ist 0 für wenig Protokoll-Einträge, das höchste ist 5 für ausführliche Protokollierung. Dazwischen gibt es noch die Stufen 1 bis 4. Nach der Ausführung des Paketes können keine Informationen ermittelt werden, die aufgrund eines niedrigen Log Levels nicht erfasst wurden. Falls diese Information erwünscht sind, müsste man den Log Level erhöhen und das Paket erneut ausführen, falls dies überhaupt möglich ist. Deshalb sollte man sich vor der Ausführung der Paketes überlegen, welche Informationen man benötigt und welche der eingestellte Log Level bietet. 16 4.2. Protokollverfahren in Microsoft SQL Server 2012 Wenn bei MS SQL 2012 das Project Deployment Model genutzt wird, gibt es in der dazugehörigen SSIS-Katalog-Datenbank Tabellen, in denen alle möglichen Laufzeitinformationen festgehalten werden. Bei der Ausführung eines Paketes, das im Katalog gespeichert ist, kann der Log Level auf vier vorgegebene Stufen gesetzt werden: • None: keine Protokollierung • Basic: Standard Protokollierung • Performance: reduzierte Protokollierung (gegenüber Standard) • Verbose: umfangreichste Protokollierung Unter anderem werden dort alle Events, die während einer Paketausführung erfasst werden, gespeichert. Welche Events erfasst werden, hängt direkt vom Log Level ab. Die Tabelle „internal.event_messages“ enthält für jedes erfasste Event einen Eintrag, die dazugehörige Nachricht wird in der Tabelle „internal.operation_messages“ gespeichert. Die Tabellen sind über einen Fremdschlüssel miteinander verbunden. Dann gibt es noch die Tabelle „internal.operations“, die ebenfalls mit einem Fremdschlüssel angebunden ist. In dieser kann man die Start- und Endzeit des Paketes ermitteln. Darüber hinaus gibt es noch eine Vielzahl von anderen Tabellen in dieser Datenbank, die Daten über die Ausführung der Pakete und die Einstellungen des Katalogs speichern. Diese kann man an dem Datenbankschema „internal“ erkennen, dass als Namensbestandteil vor dem eigentlichen Tabellennamen steht. Die Tabelle event_messages enthält unter anderem folgende Spalten: • package_name: Name des Paketes • event_name: Typ des protokollierten Event • message_source_name: Name des Objektes, dass den Event verursacht hat Die Tabelle operation_messages enhält u.a. folgendes • message: Inhalt der Nachricht • message_time: Uhrzeit der Nachricht Die Tabelle operations enhält u.a. folgendes: • start_time: Startzeit des Paketes • end_end: Endzeit des Paketes Insgesamt lassen sich die gleichen Information wie in den alten Tabellen auch in die neuen übertragen. Sie haben dort jedoch dann eine andere Struktur, die fest von Microsoft vorgegeben ist. Die Informationen werden nicht durch SQL-Befehle, die bisher in der Standard-Bibliothek von Team4 steckten, direkt in die Tabellen eingetragen, sondern durch die Ausführungseinheit von Microsoft, die automatisch protokolliert. 17 4.3. Vergleich der Verfahren (GAP-Analyse) Team4 Protokollverfahren Vorteile: • Ausführlichkeit der Protokollierung lässt sich bei jeder Komponente duch den Log Level einzeln festlegen. • Protokollierung findet auch bei Ausführung im Visual Studio statt. Nachteile: • Eigene Datenbankstruktur notwendig. • Keine standardisierte Berichte sind verwendbar. • Ausschließlich Meldungen von Team4-Komponenten werden protokolliert. • Ausführlichkeit lässt sich nicht mehr bei Ausführung des Paketes verändern. MS SQL Server 2012 Protokollverfahren bei Verwendung des Project Deployment Model Vorteile: • Standardisierter Katalog wird gemeinsam mit anderen Komponenten verwendet. • Standardisierte Berichte sind verwendbar. • Meldungen von allen Komponenten können gemeinsam protokolliert werden. • Ausführlichkeit lässt bei jeder Ausführung eines Paketes neu bestimmen. Nachteile: • Ausführlichkeit der Protokollierung ist nur für das ganze Paket gleich wählbar. • Protokollierung findet nur bei Ausführung im Katalog statt, nicht schon bei der Ausführung im Visual Studio bei der Entwicklung. Zusammenfassung: • Protokollierung findet vorher bei jeder Ausführung statt, nachher nur bei Ausführung aus dem Katalog heraus. • Ausführlichkeit der Protokollierung wird bisher beim Erstellen des Paketes festgelegt und ist bei jeder Ausführung des Paketes gleich. Beim neuen Verfahren wird die Ausführlichkeit erst bei jeder Ausführung einzeln bestimmt. • Protokollierung wird von einer eigenen Datenbankstruktur auf die genormte KatalogDatenbankstruktur verlegt. • Standardisierte Berichte sind nach einer Umstellung verwendbar, vorher nicht. 18 5. Realisierung: Notwendige Schritte zur Umstellung des Protokoll-Mechanismus Im folgenden wird zuerst aufgeführt, welche Meldungen unter welchen Bedingungen bisher protokolliert wurden. Danach wird untersucht, wie diese Meldungen und die Bedingungen auf das neue Konzept von MS SQL Server 2012 übertragen werden können. Team4 Log Task: Dieser Task erzeugt explizit einen Eintrag in der Tabelle T4LogBody. Man kann dort folgendes einstellen: • Process name: Enthällt den Namen des Paketes. Kann nicht durch die Maske verändert werden und wird im Feld ProcName übernommen • Object name: Frei wählbare Bezeichnung des Objektes der Meldung. Dort wird üblicherweise eingegeben, im welchem Kontext die Log-Meldung stattfindet und kommt ins Feld ObjType. • Message type: Man kann zwischen drei vorgegebenen Werte für den Typ einer Nachricht auswählen: Message, Warning, Error. Dieser Typ wird direkt im Feld Status der Tabelle T4LogBody verwendet. 0 wird bei Message, 1 für Warning und 2 für Error verwendet. • Message text: Hier kann der Text der Nachricht frei eingegeben werden. • Variable: Hier kann eine verfügbare Variable ausgewält werden. Verfügbar sind Variablen, die im gleichen Bereich (Scope) oder einem übergeordnetem angelegt wurden. Der Wert dieser Variable wird als Bestandteil der Nachricht als Text angehängt. Diese zusammengesetzt Nachricht kommt ins Feld Msg. Die sonstigen Felder der Tabelle werden automatisch gefüllt. StartTime enthält die Startzeit des Paketes, MsgTime enthält die Uhrzeit der Benachrichtigung. Bei den folgenden Komponenten kann man keine eigenen Meldungen in die Log-Tabelle T4LogBody schreiben. Hier werden die Meldungen durch die Ergebnisse der Ausführung während der Ausführung erzeugt. Man kann in sechs Stufen die Anzahl der automatisch erzeugten Meldungen einstellen. Bei 0 sind die wenigsten Meldungen, die nur über das wichtigste berichten. Bei 5 werden die meisten Meldungen geschrieben. Es folgt eine Aufzählung, was unter welchen Bedingungen protokolliert wird: Team4 MS CRM Source: Die Protokollierung ist abhängig von dem Log Level. Folgendes wird in dieser Komponente protokolliert: LogLevel ≥ 0 : • Version des MS CRM Connector 19 • Hinweis auf Probeversion, falls Software nicht registriert wurde LogLevel ≥ 1 : • Name MS CRM Connection, die für die Ausführung der Komponente angegeben wurde • Name der Entität aus MS CRM System, das die Ausführung der Komponente ausgewählt wurde. • Username, der auf das MS CRM zugreift LogLevel ≥ 2 : • Version der MS CRM Source • Version des MS CRM Connection Manager • Version der T4 Standard Bibliothek LogLevel ≥ 3 : • Es wird protokolliert, dass Daten gelesen werden: „[PrimeOutput] Retrieving entities from CRM“ LogLevel ≥ 4 : • Es wird protokolliert, welche Bestandteil (Column) der Entität bzw. des Resultates der fetchXML-Abfrage zum Auslesen ausgewählt wurden (Name und Datentyp) LogLevel ≥ 5 : • Es wird der Wert aller Bestandteile aller ausgelesenen Datensätze protokolliert. Team4 MS CRM Destination: Folgendes wird in dieser Komponente protokolliert: LogLevel ≥ 0 : • Version des MS CRM Connector • Hinweis auf Probeversion, falls Software nicht registriert wurde • Anzahl der veränderten Entitäten LogLevel ≥ 1 : • Name MS CRM Connection, die für die Ausführung der Komponente angegeben wurde • Name der Entität aus MS CRM System, das die Ausführung der Komponente ausgewählt wurde. 20 • Username, der auf das MS CRM zugreift • Modus des Schreibzugriffes LogLevel ≥ 2 : • Version der MS CRM Destination • Version des MS CRM Connection Manager • Version der T4 Standard Bibliothek LogLevel ≥ 3 : LogLevel ≥ 4 : • Es wird protokolliert, welche Bestandteil (Column) der Entität zu welchem Bestandteil der eingehenden Data-Flow-Pipe zugeordnet wurde. (Namen und Datentypen beider Bestandteile). LogLevel ≥ 5 : • Es wird der Wert aller Bestandteile aller geschriebenen Datensätze protokolliert. • Der Primärschlüssel (Uniqueidentifier) eines jeden geschriebenen Datensatzen oder die Art des Schreibzugriffes (Insert, Update, Delete) Team4 Lotus Notes Source: Folgendes wird in dieser Komponente protokolliert: LogLevel ≥ 0 : • Version des Team4 Lotus Notes Connector LogLevel ≥ 1 : • Name der Notes-Datenbank, die in der zugeordneten Notes Connection ausgewählt wurde • Benutzerdaten, die für den Datenbankzugriff benutzt werden • Verwendete Selektionsformel(leer, falls nicht ausgewählt) • Ausgewählter Wert für das Notes-Feld FormName, falls diese Auswahlfunktion des Connectors verwendet wurde LogLevel ≥ 2 : • Version der Team4 Notes Connection • Version der Team4 Lotes Bibliothek 21 • Version der Team4 Standard Bibliothek • Name der Notes Connection Manager • Version des Lotus Notes Server (Domino) • Anzahl der Dokumente, die der Selektionsformel entsprechen LogLevel ≥ 3 : • Aufbau der Verbindung zum Lotus Notes Server LogLevel ≥ 4 : • Es wird protokolliert, welche Felder in den Notes-Dokumenten als welcher Datentyp eingelesen wird LogLevel ≥ 5 : • Die Werte aller Felder von allen eingelesenen Dokumenten werden protokolliert Team4 Lotus Notes Destination: Folgendes wird in dieser Komponente protokolliert: LogLevel ≥ 0 : • Version des Team4 Lotus Notes Connector LogLevel ≥ 1 : • Name der Notes-Datenbank, die in der zugeordneten Notes Connection ausgewählt wurde • Benutzerdaten, die für den Datenbankzugriff benutzt werden • verwendete Abschlussformel • Ausgewählter Wert für das Notes-Feld FormName, falls diese Auswahlfunktion des Connectors verwendet wurde • Ausführungsmodus (Insert, Update, Delete) LogLevel ≥ 2 : • Version der Team4 Notes Connection • Version der Team4 Lotes Bibliothek • Version der Team4 Standard Bibliothek • Name der Notes Connection Manager • Version des Lotus Notes Server (Domino) 22 LogLevel ≥ 3 : • Aufbau der Verbindung zum Lotus Notes Server LogLevel ≥ 4 : • Es wird protokolliert, welche Felder in den Notes-Dokumenten als welcher Datentyp geschrieben wird LogLevel ≥ 5 : • Die Werte aller geschriebener Felder von allen veränderten Dokumenten werden protokolliert Alle diese Komponenten nutzen die gleichen Tabellen für ihr Protokoll. Sie greifen zum protokollieren auf eine Team4-Standard-Bibliothek zu, die gemeinsam genutzt wird. Diese enthält die Funktionen, die die Einträge erzeugt. Die eine Funktion heißt „Print“ und bei ihr kann der Log Level angegeben werden, am dem die Meldung protokolliert wird. Für Warnmeldungen gibt es „PrintWarning“ , für Fehlermeldungen „PrintError“. Diese werden immer protokolliert. Man muss sich zuerst überlegen, welche Meldungen von Team4-Komponenten bei MS SQL Server 2012 unter welchen Bedingungen protokolliert werden sollen. Man muss dazu die bisherigen Log Level 0 bis 5 auf die Log Level „None“, „Perfomance“, „Basic“ und „Verbose“ von MS SQL Server 2012 zuordnen. Um dieses zu bewerkstelligen muss man bei der Protokollfunktion in der Team4-Standard-Bibliothek jeweils ein Event auslösen, dass bei dem ausgewählten Log Level einen Eintrag verursacht. Folgende Events werden bei den Log Level-Einstellungen protokolliertMicrosoft (2011a): • None: Keine Events • Perfomance: Nur OnWarning und OnError • Basic: Alle Events außer Custom und Diagnostic Events • Verbose: Alle Events inklusive Custom und Diagnostic. Die Funktionen für die Warn- und Fehlermeldungen sollten dann das Event „OnError“ bzw. „OnWarning“ auslösen. Für allgemeine Informationsmeldungen wäre das Event „OnInformation“ geeignet. Mit ihm könnte man die Informationen von Log Level 0 bis 4 protokollieren. Die Detailinformation sollten nur bei Verbose protokolliert werden. Das könnte man realisieren, wenn die Meldungen auf Log Level 5 Ebene den Event „OnCustomEvent“ auslösen. Man muss außerdem in den Komponenten die Einstellmöglichkeit des Log Levels entfernen, da dies erst durch die Ausführung bestimmt werden soll. Es müssen die entsprechenden Stellen in den Quellcodes und in den Masken des GUIs der Komponenten abgeändert werden. 23 6. Ergebnisse und Schlussfolgerungen Die Umstellung der Protokollierung von dem bisherigen Mechanismus auf den neuen ist möglich. Allerdings lassen sich nicht so viele unterschiedliche Stufen bei der Protokollierung abbilden. Auch lassen sich nicht die Komponenten einzeln in Hinsicht auf die Ausführlichkeit einstellen. Es ist also keine 100-prozentige deckungsgleiche Realisierung möglich. Die Informationen, die in einem der bisherigen Einträge vorhanden waren, werden auch nicht in identischer Weise gespeichert. Wesentliche Informationen wie Status der Meldung und Text sind jedoch auch im neuen Protokollsystem vorhanden, wenn auch wieder nicht vollständig deckungsgleich. So ist beim früheren Mechanismus ein eigenes Feld für den Status vorgesehen, beim neuen wird dies anhand des Event-Typs bestimmt. Der größte Nachteil bei der Umstellung ist, dass die Protokollierung nur noch bei Ausführung im Katalog stattfindet. Dies erfordert außerdem die Verwendung des neuen Veröffentlichungsmodell, anstatt des bisherigen. Dieses hat auch an anderen Stellen einige Änderungen bei der Entwicklung von SSIS-Projekten zu Folge. Es gibt die Möglichkeit, durch Einfügen eines Log Providers auch während der Entwicklung zu protokollieren, doch die Verwendung dieser ist beim Project Deployment Model nicht sinnvoll, da dann Protokollinformationen mehrfach abgelegt werden. Denn diese Log Provider nutzen nicht den Katalog zur Speicherung des Protokolls, sondern jeweils eigene spezifische Ziele. Sie sollten also spätestens bei der Veröffentlichung vom Paket wieder entfernt werden. 24 7. Zusammenfassung und Ausblick Die Anforderungen von Kapitel 3 können mit dem Verfahren aus der Analyse mit den Mitteln aus der Realisierung umgesetzt werden. Zur Umstellung muss vor allem der Quellcode der Team4-Standardbibliothek verändert werden, der von allen Komponenten gemeinsam genutzt wird. Dann muss in den Komponenten die Möglichkeit herausgenommen werden, den Log Level zu bestimmen. Dazu sind Änderungen im Quellcode der Komponenten notwendig und in deren Maskendesigns . Ich halte die Umstellung des Protokollmechanismus für sinnvoll. Die Protokollierung findet nicht mehr in einer eigene Datenbankstruktur statt, in der nur eigene Meldungen von Team4-Komponenten stehen. Durch die Protokollierung in der standardisierten Katalog-Datenbank bei Verwendung des Project Deployment Models sind die Informationen gemeinsam mit denen von anderen ersichtlich und können mit standardisierten Reports ausgewertet werden. Dies bietet für die Vermarktung der Komponente als einzelne Software große Vorteile. Es werden nur standardisierte Protokolltechniken verwendet, der Kunde muss nicht noch einen Report zur Auswertung des Team4-Protokolls kaufen oder umständlich die Protokoll-Tabellen auslesen. Er können zur Auswertung des Protokolls Reporte verwenden, die teilweise schon von Microsoft mitgeliefert werden oder kostenlos im Internet verfügbar sind. Diese Reporte haben zudem auch den Vorteil, die Protokollmeldungen von allen Komponenten anzuzeigen. Es wird von Team4 erhofft, dass durch die Standardkonformität eine Zertifizierung der Komponenten durch Microsoft stattfinden kann. Dass würde die allgemeinen Marktchancen noch einmal deutlich erhöhen. 25 A. Literaturverzeichnis Literatur [Bachrach u. a. 2011] Bachrach, Ann ; Yu, Daniel ; Komo, Darmadi ; Ashok, Gopal ; Hodgins, Joanne ; Javaheri, Pej ; Mcbride, Sabrena ; Tharian, Tharun: Whats New Whitepaper 7 11. Website, 2011. – Online verfügbar http://download. microsoft.com/download/E/9/D/E9DBB1BB-89AE-4C70-AF02-AAFC29451A85/SQL_ Server_Whats_New_Whitepaper_7_11.pdf; besucht am 21.12.2011. [Ericson u. a. 2011] Ericson, Gary ; Price, Ed ; Sabota, Ms.: Project Deployment Overview in SQL Server Denali CTP1 - SSIS. Website, 2011. – Online verfügbar http://social.technet.microsoft.com/wiki/contents/articles/ project-deployment-overview-in-sql-server-quot-denali-quot-ctp1-ssis. aspx; besucht am 21.12.2011. Revision 8 [Microsoft 2011a] Microsoft: catalog.set_execution_parameter_value (SSISDB Database). Website, 2011. – Online verfügbar http://msdn.microsoft.com/en-us/ library/ff877990%28v=sql.110%29.aspx; besucht am 22.12.2011. [Microsoft 2011b] Microsoft: SQL Server Integration Services. Website, 2011. – Online verfügbar http://msdn.microsoft.com/en-en/library/ms141026(v=SQL.110) .aspx; besucht am 21.12.2011. 26 B. Abkürzungsverzeichnis CRM Customer Relationship Management ETL Extract Transform Load FTP File Transfer Protocol MS Microsoft SQL Structured Query Language SSIS SQL Server Integration Services 27
