Praktischer Leitfaden und Optimierungshandbuch zu SSIS

Technischer Artikel zu SQL Server

Autoren: Alexei Khalyako, Carla Sabotta, Silvano Coriani, Sreedhar Pelluru, Steve Howard

Technische Bearbeiter: Cindy Gross, David Pless, Mark Simms, Daniel Sol

Veröffentlicht: Dezember 2012

Betrifft: SQL Server 2012, Windows Azure SQL-Datenbank

Zusammenfassung: SQL Server Integration Services (SSIS) kann als effizientes Tool zum Verschieben von Daten in und aus Windows Azure SQL-Datenbanken, als Teil der ETL-Gesamtlösung (Extrahieren, Transformieren, Laden) sowie im Rahmen der Datenverschiebung eingesetzt werden. SSIS ist ein wirkungsvolles Instrument zum Übertragen von Daten zwischen Quell- und Zielsystemen in der Cloud sowie in einem Hybridszenario zwischen der Cloud und einem lokalen Standort. In diesem Whitepaper werden folgende Themen behandelt: Best Practices für die Verwendung von SSIS für cloudbasierte Quellen und Ziele, Planung von SSIS-Projekten, die vollständig in der Cloud ausgeführt werden oder hybride Datenverschiebungen umfassen, und eine exemplarische Vorgehensweise zur Leistungsoptimierung bei hybriden Datenverschiebungen mittels horizontaler Skalierung.

Copyright

Die Informationen werden in ihrem derzeitigen Zustand zur Verfügung gestellt. Die in diesem Dokument enthaltenen Angaben und Ansichten, einschließlich URLs und anderer Verweise auf Internetwebsites, können ohne vorherige Ankündigung geändert werden. Ihnen obliegt das Risiko der Verwendung.

Einige der hier dargestellten Beispiele werden nur zu Illustrationszwecken bereitgestellt und sind fiktiv. Ähnlichkeiten mit realen Gegebenheiten sind weder beabsichtigt noch erwünscht.

Mit diesem Dokument erwerben Sie keine Rechte an geistigem Eigentum in Microsoft-Produkten. Sie können dieses Dokument zu internen Zwecken und als Referenz kopieren und verwenden.

© 2011 Microsoft. Alle Rechte vorbehalten.

2

InhaltEinführung...................................................................................................................................................6

Projektentwurf............................................................................................................................................6

Problembestimmung und Beschreibung.................................................................................................6

Die Bedeutung der Datenverschiebung in Azure.....................................................................................7

Wichtige Szenarien bei der Datenverschiebung......................................................................................8

Anfängliches Laden und Migrieren der Daten von der lokalen Umgebung in die Cloud......................8

Verschieben der in der Cloud generierten Daten in lokale Systeme..................................................10

Verschieben von Daten zwischen Clouddiensten..............................................................................10

Vorhandene Tools, Dienste und Lösungen............................................................................................11

SQL Server Integration Services (SSIS)...............................................................................................11

SqlBulkCopy-Klasse in ADO.NET.........................................................................................................12

Befehlszeilentool BCP.EXE für Massenkopiervorgänge.....................................................................13

Azure-Speicher-BLOBs und Warteschlange für Azure-Speicher.........................................................13

Entwurfs- und Implementierungsentscheidungen................................................................................14

Entwurf und Implementierung einer ausgeglichenen Architektur.....................................................14

Überlegungen zu Datentypen............................................................................................................15

Packen und Bereitstellen von Lösungen................................................................................................16

Erstellen portierbarer Lösungen........................................................................................................16

Verteilung von Paketen und Codekomponenten...............................................................................16

Azure SQL-Datenbank als Ziel von Datenverschiebungen..................................................................17

Überlegungen zur Architektur...................................................................................................................18

Neustart ohne Verlust des Pipelinefortschritts......................................................................................19

Grundprinzip..........................................................................................................................................19

Beispiel mit nur einem Ziel................................................................................................................20

Beispiel mit mehreren Zielen.................................................................................................................26

Weitere Tipps zu Neustarts...................................................................................................................28

Wiederholungslogik ohne manuelles Eingreifen...................................................................................30

Integration einer Wiederholungslogik...................................................................................................30

3

SSIS-Optionen zur Leistungsoptimierung...................................................................................................35

Optimieren der Netzwerkeinstellungen................................................................................................35

Netzwerkeinstellungen......................................................................................................................36

Hinweis: Bevor Sie die Einstellungen der Netzwerkschnittstellenkarte für die Verwendung von Jumbo Frames ändern, sollten Sie sich vergewissern, dass dieser Frametyp von der Netzwerkinfrastruktur unterstützt wird............................................................................................36

SSIS-Paketeinstellungen........................................................................................................................37

Spezielle Überlegungen zu BLOB-Daten............................................................................................39

Leistungsüberwachung in einem verteilten System mithilfe neuer Funktionen in SSIS 2012................41

Protokollieren von Leistungsstatistiken.............................................................................................41

Anzeigen von Ausführungsstatistiken................................................................................................43

Überwachen des Datenflusses...........................................................................................................49

Schlussfolgerung........................................................................................................................................53

4

EinführungSQL Server Integration Services (SSIS) ist ein effizientes Tool, das zum Verschieben von Daten in und aus Windows Azure SQL-Datenbanken, als Teil der ETL-Gesamtlösung (Extrahieren, Transformieren, Laden) oder im Rahmen von Datenverschiebungen, die keine Transformationen erfordern, genutzt werden kann. SSIS ist für zahlreiche Quellen und Ziele geeignet: in der Cloud, an einem lokalen Standort oder in einer Hybridumgebung. In diesem Whitepaper werden folgende Themen behandelt: Best Practices für die Verwendung von SSIS für cloudbasierte Quellen und Ziele, Planung von SSIS-Projekten, die vollständig in der Cloud ausgeführt werden oder hybride Datenverschiebungen umfassen, und eine exemplarische Vorgehensweise zur Leistungsoptimierung bei hybriden Datenverschiebungen mittels horizontaler Skalierung.

ProjektentwurfProjekte, mit denen Daten zwischen cloudbasierten und lokalen Datenspeichern verschoben werden, können verschiedenartige Prozesse in unterschiedlichen Lösungen umfassen. Hier laufen zahlreiche Aufgaben zusammen. Vom ersten Auffüllen des Ziels mit Daten aus anderen Systemen oder Plattformen über Wartungstasks wie das Umverteilen von Datasets auf eine neue Anzahl von Partitionen oder Shards bis hin zu regelmäßigen Massendatenvorgängen oder Aktualisierungen. Der Projektentwurf und die Grundannahmen stellen sich bei einer Lösung, die ausschließlich auf Clouddaten basiert, häufig anders dar als bei einer herkömmlichen Umgebung, in der Daten lokal verschoben werden. Während viele Erfahrungswerte und Verfahrensweisen weiterhin Gültigkeit besitzen, machen neue Voraussetzungen ein Umdenken erforderlich, das den Wandel von einer eigenständigen, kontrollierbaren Umgebung zu einem gemeinsamen Pool von Standardressourcen einläutet. Damit dieser Schritt gelingt, ist ein optimal abgestimmter und anpassungsfähiger Ansatz erforderlich.

Problembestimmung und BeschreibungSowohl für native Lösungen, die von Anfang an für die Cloud entwickelt wurden, als auch für migrierte Lösungen müssen Daten bewegt werden. Dies geschieht in verschiedenen Phasen des Anwendungslebenszyklus, beispielsweise in Vorproduktionstests, beim anfänglichen Laden von Daten, bei nachfolgenden Datensynchronisierungen zwischen cloudgenerierten Daten und den lokalen Originaldatenbanken sowie beim Erstellen regelmäßiger Momentaufnahmen, die von der Cloud auf andere lokale Systeme (z. B Data Warehouses) übertragen werden.

5

Abbildung 1 Szenarien für Datenverschiebungen

Der Schwerpunkt dieses Abschnitts ist die anfängliche Datenladephase, d. h., der gesamte Ablauf vom Extrahieren der Daten aus der Quelldatenbank, über die Umlagerung von der lokalen Umgebung in die Cloud, bis hin zum Laden der Daten in das endgültige Zielsystem. Es sollte beachtet werden, dass sich nahezu alle Best Practices und Optimierungen in diesem Whitepaper mit minimalen Modifikationen auf die meisten hier beschriebenen Szenarien anwenden lassen. Die Szenarien und die zentralen Fragestellungen werden in den folgenden Abschnitten besprochen.

Die Bedeutung der Datenverschiebung in AzureIm Vergleich zu einem herkömmlichen Rechenzentrum, in dem es beim Verschieben von Daten zwischen Anwendungen und Systemen auf Anwendungskompatibilität, Prozessorchestrierung und Synchronisierung ankommt oder in dem Probleme mit physischen Hardwareressourcen und Netzwerkfunktionalität bewältigt werden müssen, zeichnen sich Cloudumgebung wie Windows Azure durch eine deutlich höhere Komplexität aus. Diese drückt sich beispielsweise in der Stabilität, Bandbreite und Latenz der Verbindungen zwischen lokaler und Cloudumgebung (oder zwischen Clouddiensten) aus. In einer optimalen Lösung für die Datenverschiebung muss diese Komplexität berücksichtigt werden. Je höher der Anteil „nicht stationärer“ Teile einer Lösung ist, umso wichtiger wird es, ein ausgewogenes Verhältnis zwischen allen involvierten Komponenten und Technologien zu schaffen. Das schwächste Glied in der Kette darf nicht überlastet werden, weil dies negative Auswirkungen auf alle anderen hätte.

6

SQL-Datenbank oder SQL Server auf einer VM

Tests haben ergeben, dass die Fähigkeit des Datenziels, die von externen Quellen hochgeladene Datenflut mit einer angemessenen Geschwindigkeit zu bewältigen, eine der Schwachstellen darstellt. Am besten kann man diesem Problem begegnen, indem die Zieldatenbank mit einem angepassten Shardingansatz horizontal auf mehrere Back-End-Knoten skaliert wird (http://social.technet.microsoft.com/wiki/contents/articles/1926.how-to-shard-with-windows-azure-sql-database.aspx). Sobald die zu ladende Datenmenge eine signifikante Größenordnung erreicht hat (die bei Veröffentlichung dieses Beitrags bei +20 GB/Stunde lag), wird diese Technik zwingend notwendig und kann sowohl auf Azure SQL-Datenbankinstanzen als auch auf SQL Server auf virtuellen Windows Azure-Computern (WA-VMs) angewendet werden. Da der Datenladevorgang bei diesem Ansatz nicht automatisch linear skaliert wird, ist es umso wichtiger, ein ausgeglichenes Verhältnis zwischen den übrigen verschiebbaren Teilen der Lösung herzustellen. In den nächsten Abschnitten werden die wichtigsten Bereiche und Entwurfsoptionen beschrieben, die zu einem optimalen Endergebnis beitragen.

Wichtige Szenarien bei der DatenverschiebungIm Folgenden die drei Hauptszenarien, die beim Verschieben von Daten zwischen Quelle und Ziel erörtert werden. Behandelt werden die am häufigsten aufgetretenen Fragestellungen.

Anfängliches Laden und Migrieren der Daten von der lokalen Umgebung in die Cloud Verschieben der in der Cloud generierten Daten in lokale Systeme Verschieben von Daten zwischen Clouddiensten

Anfängliches Laden und Migrieren der Daten von der lokalen Umgebung in die Cloud

7

Abbildung 2 Szenario für den anfänglichen Datenladevorgang

Bei jeder Anwendung, die von einer lokalen Bereitstellung in eine Cloudumgebung umgelagert wird, muss auch eine bestimmte Datenmenge verschoben werden. Sobald das Datenvolumen eine bestimmte Grenze erreicht hat, erfordert dieser Vorgang eine andere Herangehensweise als bei lokalen Lösungen. Dies liegt vor allem an: der Bandbreite und Latenz öffentlicher Netzwerke sowie der Anzahl der für die Datenladephase (gemeinsam) genutzten Ressourcen auf den physischen Hardwareknoten, die eine Datenbank (Azure SQL-Datenbank oder WA-VMs) in der Cloudumgebung hosten. Es gibt verschiedene Möglichkeiten, die Auswirkungen des schlechtesten Performers auf die Gesamtlösung zu mindern, z. B. indem die ursprünglichen Daten in mehrere Bucketdateien partitioniert und vor der Netzwerkübertragung komprimiert werden (vgl. Abbildung 2). Die Datenpartitionierung ist auch in der Cloud von Nutzen, da sie Einfügeprozesse in das Datenziel erleichtert, das häufig über mehrere Azure SQL-Datenbankinstanz-Shards verteilt ist oder von mehreren WA-VMs gehostet wird.

Sowohl lokal als auch in der Cloud übernimmt SSIS eine wichtige Rolle beim physischen Datenexport und -import. Die Gesamtlösung erfordert zusätzliche Technologien wie Azure-BLOB-Speicher und -Warteschlangen, um Zwischendateiformate zu speichern und die Kopier- und Übertragungsvorgänge über verteilte SSIS-Importprozesse hinweg zu koordinieren.

Weitere spezifische Informationen zur Migration eines Datenbankschemas und der Objekte zu Azure SQL-Datenbank finden Sie im Leitfaden Migrieren datenorientierter Anwendungen zu Windows Azure (http://msdn.microsoft.com/de-de/library/windowsazure/jj156154.aspx).

Verschieben der in der Cloud generierten Daten in lokale SystemeDieses Szenario ist eng mit dem vorherigen verknüpft, da der Ladevorgang und Datenfluss aus technischer Sicht einfach umgekehrt werden, wobei die Gesamtziele jedoch anders gelagert sein können. Dieses Szenario beinhaltet in der Regel frisch generierte Daten, die in lokalen Systemen wie Data Warehouses ständig neu abgerufen und geladen werden müssen, oder aber Daten für lokale Transaktionslösungen. Folglich haben die meisten im vorherigen Abschnitt erwähnten Techniken und Technologien für dieses Szenario ebenfalls Gültigkeit. Daten werden von SSIS auf der Cloudseite extrahiert, komprimiert und dann zurück an das lokalen System übertragen, wo die üblichen Verarbeitungsschritte ausgeführt werden. Weitere Informationen finden Sie im Leistungsleitfaden für das Laden von Daten (http://msdn.microsoft.com/de-de/library/dd425070(v=SQL.100).aspx).

8

Verschieben von Daten zwischen ClouddienstenIn einigen Szenarien müssen Daten zwischen unterschiedlichen Clouddiensten und -datenbanken verschoben werden. Dazu gehört der Datenaustausch zwischen verschiedenen Lösungen, die miteinander interagieren müssen, und die Neuverteilung partitionierter Daten zwischen Tabellen, die in verschiedenen Shards gehostet werden, wie in Abbildung 3 unten dargestellt.

Diese Shards können sowohl von Azure SQL-Datenbankinstanzen als auch von SQL Server auf einer WA-VM gehostet werden, ohne dass der zugrunde liegende Ansatz oder die Architektur angepasst werden müssen. Anders als in den oben genannten Szenarien werden sämtliche Daten üblicherweise nur innerhalb der Grenzen einer einzelnen WA-Region bewegt. So kann nicht nur der Einfluss der Netzwerklatenz auf ein Minimum reduziert, sondern auch auf den Datenexport und -import über einen Zwischenspeicher (z. B. lokale Datenträger oder Azure-Speicher) verzichtet werden. In einigen Fällen sind auch regionsübergreifende Datenverschiebungen erforderlich, diese zu erläutern, geht jedoch über den Rahmen dieses Whitepapers hinaus. Auch wenn sowohl Datenquelle als auch Ziel in einer gemeinsamen Cloudumgebung gehostet werden, ist eine sorgfältige Abstimmung der Ladephase gerade dann von entscheidender Bedeutung.

Vorhandene Tools, Dienste und LösungenZur Implementierung der in den vorangehenden Szenarien beschriebenen Lösungen können vorhandene und neue Tools mit Komponenten und Verfahren kombiniert werden, die lokal wie in der Cloud von Nutzen sind. In einer Hybridumgebung müssen einige dieser Komponenten im Kontext lokaler, bereits vorhandener Systeme und Datenquellen ausgeführt werden, während andere von der Nähe zu den Datenzielen in der Cloud profitieren.

9

Abbildung 3 Verschieben von Daten zwischen Datenbankshards

SQL-Datenbank oder SQL Server auf einer VM

SQL Server Integration Services (SSIS)Als Hauptinstrument für die Datenverschiebung und -integration bietet SSIS zahlreiche Funktionen, die die meisten Bereiche innerhalb der behandelten Szenarien abdecken. Obwohl nicht speziell für Hybridumgebungen ausgelegt, können SSIS-Pakete seit Einführung von WA-VMs lokal und in der Cloud ausgeführt werden und eine direkte Verbindung zwischen beiden Welten schaffen. So kann die DBA-/ETL-Entwicklercommunity aus ihrem reichen Erfahrungsschatz schöpfen, weil viele IT-Profis mit dieser Technologie bereits vertraut sind. Dabei darf aber auch nicht außer Acht gelassen werden, dass nicht alle mit SSIS implementierten, vorhandenen ETL-Prozesse bei der Umlagerung lokaler Daten in die Cloud 1:1 wiederverwendet werden können.

Je nach Prozesskomplexität, Datenvolumen und Geschwindigkeit sowie den charakteristischen Unterschieden zwischen cloudbasierten Datenzielen, wie SQL Server auf einer WA-VM und Azure SQL-Datenbank, muss die Architektur bis zu einem gewissen Grad umstrukturiert werden.

Es gilt, einige Herauforderungen zu meistern, zum Beispiel das Aneignen von Fachwissen im Umgang mit Cloudverbindungen zu Windows Azure SQL-Datenbanken oder die Erstellung von SSIS-Paketen zur Behandlung von Ausfällen und Wiederholungen bei Datenladevorgängen.

Auch die Implementierung von Paketen, die Verbindungen mit partitionierten (auf mehrere Shards verteilten) Datenzielen herstellen müssen, kann aufwändig sein. Zeitweise werden die Datenbankentitäten von einer unterschiedlichen Anzahl physischer Knoten gehostet. Partitionierungslogik und Metadaten müssen verwaltet und aus Anwendungskonfigurationsdateien oder Datenstrukturen abgerufen werden.

Die meisten Funktionen zur Bewältigung dieser Aufgaben sind bereits in die SSIS-Plattform integriert. Mithilfe von Datenflusskomponenten wie der „Transformation für bedingtes Teilen“ und der Multicasttransformation lässt sich beispielsweise eine Partitionierungslogik implementieren.

Im Hinblick auf die Architektur gilt es, das neue Design praktisch umzusetzen – entweder mit den bekannten visuellen Tools oder automatisch, mit einer programmgesteuerten Methode, falls eine komplexere Lösung benötigt wird. Für die programmgesteuerte Herangehensweise bietet SSIS eine vollständig skriptfähige Umgebung zur Erledigung zahlreicher Aufgaben: vom Erstellen benutzerdefinierter Tasks innerhalb der Transformationspipeline bis hin zur Modulinstrumentierung, die bei der Paketausführung für das Debuggen und die Problembehandlung genutzt werden kann.

Integration Services in SQL Server 2012 umfasst eine vollständige, katalogbasierte Überwachungs- und Verwaltungslösung, die die Entwicklung verteilter Datenverschiebeprojekte und die Erfassung von Paketausführungsergebnissen und -statistiken leicht macht.

10

SqlBulkCopy-Klasse in ADO.NETWenn ein Problem beim Verschieben von Daten mithilfe einer benutzerdefinierten Datenverschiebelösung behoben werden soll, ist die SqlBulkCopy-Klasse (http://msdn.microsoft.com/de-de/library/system.data.sqlclient.sqlbulkcopy.aspx) in der ADO.NET-Datenzugriffsbibliothek zweifellos die beste Wahl. Die Klasse ist ein schlanker Wrapper für die ODBC-APIs zum Massenkopieren. Sie akzeptiert eine Datentabelle als Eingabe und eine vorhandene Datenbankverbindung und bietet einen schnellen, voll konfigurierbaren Ansatz, um Daten in eine SQL Server- oder Azure SQL-Datenbank zu laden.

Bei der Verwendung der SqlBulkCopy-Klasse für die Interaktion mit einem cloudbasierten Datenziel besteht ein wichtiger Aspekt darin, die herkömmliche, für die Interaktion mit dem Server verwendete SqlConnection-Klasse (http://msdn.microsoft.com/de-de/library/system.data.sqlclient.sqlconnection.aspx) durch die besser geeignete ReliableSqlConnection-Klasse (http://msdn.microsoft.com/de-de/library/microsoft.practices.enterpriselibrary.windowsazure.transientfaulthandling.sqlazure.reliablesqlconnection(v=pandp.50).aspx) zu ersetzen, die Bestandteil des Anwendungsblocks zur Behandlung vorübergehender Fehler (http://msdn.microsoft.com/de-de/library/hh680934(v=PandP.50).aspx) ist. Dadurch wird die Implementierung einer Wiederholungslogik in einem neuen oder vorhandenen Datenladevorgang erheblich vereinfacht. Ein weiterer Vorteil besteht darin, dass die Bibliothek standardmäßige und benutzerdefinierte Wiederholungsrichtlinien bereitstellen kann, um unterschiedlichen Konnektivitätsszenarien gerecht zu werden.

Die in der SqlBulkCopy-Klasse verfügbaren Attribute und Eigenschaften ermöglichen die Anpassung des Ladevorgangs an praktisch jede Situation. In diesem Artikel wird erläutert, wie Batchgrößen unter Berücksichtigung folgender Fragestellungen optimiert werden: wo der Datenladeprozess ausgeführt wird, wie viele Daten vom Prozess importiert werden müssen und welche Verbindung zwischen Prozess und Datenziel verfügbar ist.

Wenn die Datenmenge in einem einzelnen Batch sehr gering ist, beispielsweise zwischen 10 und 1000 Zeilen pro Batch liegt, ist die SqlBulkCopy-Klasse weniger für das Laden von Daten in ein Ziel geeignet. Da bei der SqlBulkCopy-Klasse vor dem eigentlichen Start des Ladevorgangs zunächst eine Metadatenüberprüfung durchgeführt werden muss, entsteht ein Mehraufwand, der die Gesamtleistung beeinträchtigen kann. Bei kleinen Batches empfiehlt es sich, alternativ einen Tabellenwertparameter zum Implementieren des gewünschten Schemas und „INSERT INTO Destination SELECT * FROM @TVP“ zum Laden der Daten zu verwenden.

Ein vollständiges Beispiel zur Verwendung der API zum Massenkopieren finden Sie unter SqlBulkCopy-Klasse (http://msdn.microsoft.com/de-de/library/system.data.sqlclient.sqlbulkcopy.aspx).

11

Befehlszeilentool BCP.EXE für MassenkopiervorgängeDas Befehlszeilentool BCP.EXE (das unter Verwendung derselben APIs zum Massenkopieren wie die SqlBulkCopy-Klasse erstellt wurde) wird bereits seit Längerem für Massenladevorgänge auf SQL Server-Instanzen eingesetzt. Das Programm ist ein einfaches und dennoch leistungsstarkes Tool zur effizienten Automatisierung einfacher Datenverschiebelösungen. Einer der Hauptvorteile besteht darin, dass sich die Toolinstallation auf Azure-Serverknoten oder VMs einfach automatisieren lässt und dass vorhandene Skripts nach der Anpassung an die Cloudumgebung problemlos weiterverwendet werden können.

Andererseits unterstützt BCP.EXE keine erweiterte Verbindungsverwaltung. Zusätzlich verursacht BCP.EXE bei der Implementierung zuverlässiger Datenverschiebetasks auf Grundlage von Wiederholungsvorgängen den gleichen Aufwand wie SSIS, was die Stabilität von Verbindungen beeinträchtigen und zu Verbindungsabbrüchen führen kann. Im Gegensatz zu den anderen beschriebenen Tools muss BCP.EXE Daten unter Verwendung physischer Dateien importieren oder exportieren, die sich auf lokalen, zugeordneten oder angefügten Laufwerken befinden. So können Daten weder direkt zwischen Quelle und Ziel gestreamt noch programmgesteuert aus unterschiedlichen Quellen gelesen werden – Voraussetzungen, die SSIS oder SqlBulkCopy-basierte Anwendungen mitbringen.

Azure-Speicher-BLOBs und Warteschlange für Azure-SpeicherObwohl sie nicht unmittelbar für Datenverschiebungen eingesetzt werden, sind die Azure-Speicherfunktionen unverzichtbar, wenn es darum geht, komplexe Lösungen zu implementieren, die eine Zwischenspeicherung zwischen lokalen und Cloudprozessen erfordern, oder um Phasen und Vorgänge zwischen den beiden Umgebungen zu koordinieren. Das leistungsfähige Speicherprinzip von Azure-Speicher-BLOBs ermöglicht das Laden von Zwischendateien und den Austausch dieser Dateien zwischen Azure-Serverknoten oder VMs und lokal ausgeführten Anwendungen. Die Warteschlange für Azure-Speicher ist ein einfaches Messagingtool, mit dem der Zugriff von Datenladeprozessen auf Dateien und Inhalte, die als Azure-BLOBs gespeichert sind, signalisiert und koordiniert wird.

Azure-Speicher-BLOBs und die Warteschlange für Azure-Speicher lassen sich dank der .NET Azure Storage Client Library, die eine Reihe einfacher Klassen für die Interaktion mit Speicherkonten, Containern, BLOBs und ähnlichen Komponenten enthält, leicht in vorhandene Anwendungen integrieren. Die Bibliothek verbirgt die Details der zugrunde liegenden REST-basierten Schnittstelle und bildet eine Brücke zwischen lokalen Daten und Clouddaten. Weitere Informationen zur Verwendung der Warteschlange für Azure-Speicher sowie von Azure-Speicher-BLOBs finden Sie unter Verwenden des Warteschlangenspeicher-Diensts (http://www.windowsazure.com/en-us/develop/net/how-to-guides/queue-service/) und Verwenden des Windows Azure-BLOB-Speicherdiensts in .NET (http://www.windowsazure.com/en-us/develop/net/how-to-guides/blob-storage/).

12

Entwurfs- und ImplementierungsentscheidungenEntwurfs- und Implementierungsentscheidungen im Hinblick auf hybride Datenverschiebelösungen werden von mehreren Faktoren beeinflusst. Ob es um das Recycling bestehender Artefakte und Prozesse oder um einen kompletten Neuanfang geht, ist eine der wichtigsten Fragen, die die Wahl der Architektur beeinflusst. Als Nächstes müssen die Kenntnisse und Fertigkeiten der Mitarbeiter sondiert werden. (Gibt es z. B. mehr Entwickler oder mehr Datenbankadministratoren im Team?) Ist das Team fähig, programmgesteuert eine angepasste neue Lösung zu entwickeln, oder sollten besser vorhandene ETL-Prozesse adaptiert werden? In beiden Fällen ist der Schritt in die Cloud mit Vorüberlegungen verbunden, weil einige Annahmen, die auf traditionelle lokale Umgebungen zutreffen, in der Cloud nicht mehr gültig sind.

Zudem muss entschieden werden, wo spezifische Datenverschiebetasks und -dienste ausgeführt werden sollen, z. B. mit der Logik für bedingtes Teilen (Conditional Split), die für Sharding oder Datenkomprimierung zuständig ist. Abhängig davon, wie die Tasks auf Komponentenebene implementiert werden (SSIS-Pipeline oder benutzerdefinierte Tasks), können die Komponenten sehr viel CPU-Leistung beanspruchen. Möglicherweise ist es sinnvoll, Tasks auf Azure-VMs auszulagern und die natürliche Elastizität der Cloudumgebung auszunutzen, um einen ausgewogenen Ressourcenverbrauch zu gewährleisten. Gleichzeitig ist es vorteilhafter, die Tasks in unmittelbarer Nähe der zugrunde liegenden Datenquellen zu platzieren, da die für solche Lösungen kritische Netzwerklatenz so reduziert wird. In einer Planungs- und Testphase können spezifische Ressourcenengpässe ermittelt und Entscheidungen zur geeigneten Implementierung der verschiedenen Tasks getroffen werden.

Der mit der Implementierung oder Anpassung einer vorhandenen Lösung an ein Hybridszenario verbundene Aufwand muss durch die Vorteile der Hybridumgebung gerechtfertigt sein. Um den richtigen Eindruck zu erhalten und eine Hybridimplementierung erfolgreich umzusetzen, müssen die technischen Vorteile, die die Umlagerung von Teilen der Lösung in die Cloud birgt, gegen eventuelle Funktionalitätsverluste abgewogen werden. Die Vor- und Nachteile beziehen sich auf sehr greifbare Aspekte des Lösungsentwurfs. Wie werden die massiven Skalierungseffekte einer Cloudplattform optimal genutzt, ohne die Kontrolle über die Hauptkomponenten der Lösung zu verlieren? Wie können Datenladevorgänge für eine Plattform ausgeführt werden, die für horizontale und nicht für vertikale Skalierung ausgelegt ist, während trotzdem eine akzeptable, voraussagbare Leistung erzielt wird? Die Beantwortung dieser Fragen erfordert ein Umdenken. Waren Netzwerkleistung und -zuverlässigkeit früher immer gegeben und Anwendungskomponenten und -dienste immer verfügbar, müssen Ressourcen heute bedarfsgesteuert aktiviert werden, um Leistungsproblemen zu begegnen. Die neue Lösung sollte ausfalltolerant sein, höhere Latenzzeiten als frühere Umgebungen akzeptieren und Arbeitslasten möglichst auf viele kleine VMs (Virtual Machines) oder Dienste aufteilen.

Entwurf und Implementierung einer ausgeglichenen ArchitekturAll diese richtungsweisenden Überlegungen sollten in den Entwurf einer komplexen Datenverschiebelösung mit mehreren nicht stationären Elementen einfließen, allerdings erhebt keine von ihnen den Anspruch, die perfekte Entsprechung zu herkömmlichen lokalen Komponenten zu sein.

13

Grundsätzlich sollte der Datenverschiebeprozess von der Datenquellenextraktion bis zum Laden der Daten am Ziel in kleinere asynchrone Einheiten unterteilt und koordiniert werden, um eine bessere Anpassung an die wesentlich latentere Hybridumgebung zu erzielen. Das richtige Gleichgewicht zwischen allen Komponenten einer Umgebung zu finden, ist viel wichtiger, als das Optimum aus einzelnen Komponenten herauszuholen. Selbst einzelne Schritte, wie der Datenladevorgang, müssen möglicherweise in kleinere Datenströme für unterschiedliche Shards oder physische Datenbanken partitioniert werden, um die Einschränkungen eines einzelnen Back-End-Knotens in der Azure SQL-Datenbankarchitektur zu überwinden.

Da sich einige Systemkomponenten (Azure SQL-Datenbankknoten und das Azure-Speicher-BLOB-Repository einer WA-VM, die SQL Server hostet) durch Hochverfügbarkeit, gemeinsame Nutzung und Mehrinstanzenfähigkeit auszeichnen, entstehen zusätzliche Leistungsprobleme, wenn zu viele Daten auf einem einzelnen Knoten platziert werden. Beispielsweise könnte die Überlastung des Replikationsmechanismus ein Leistungsproblem verursachen, das den gesamten Datenladevorgang verlangsamt.

Abbildung 4 Schematische Darstellung einer Architektur mit gleichmäßig verteilten Datenladevorgängen

Überlegungen zu DatentypenDas Datenbankschema, das Entity Design und die verwendeten Datentypen können den Datenverschiebeprozess in vielerlei Hinsicht beeinflussen. Wenn Daten für temporäre Zugriffe in Azure-BLOBs oder lokale Speicher massengeladen werden, sind Datentypen mit einem höheren Komprimierungsgrad in der Regel von Vorteil. Wenn Daten vor der Übertragung komprimiert werden, verbessert sich natürlich die Leistung.

14

Packen und Bereitstellen von LösungenDie Implementierung und Bereitstellung einer Lösung, die lokale Rechenzentren und cloudbasierte Umgebungen verbindet, umfasst normalerweise eine breite Palette von Komponenten und Diensten. Die Bereitstellung und Konfiguration aller beteiligten Komponenten so weit wie möglich zu automatisieren, wird noch entscheidender, wenn die Datenverschiebelösung auf mehreren Instanzen bereitgestellt werden soll. Virtualisierungstechnologien können beim Erstellen eines Masterimages helfen. Das Masterimage kann die Bereitstellung allgemeiner Dienste vereinfachen, die sowohl lokal als auch in der Azure-VM-Infrastruktur benötigt werden.

Gleichzeitig bringt die Verwendung von WA-VMs Einschränkungen für Anwendungspakete, abhängige Pakete und Dienste mit sich, die andere Azure-Serverknoten wie Web- und Workerrollen nicht aufweisen (zum Beispiel Starttasks).

Wenn Sie bereits Lösungen zur Softwareverteilung nutzen, beispielsweise Produkte aus der Windows Server- und System Center-Familie, besteht eine andere Möglichkeit darin, Lösungskomponenten und Pakete so zu verteilen, dass einige Komponenten in der Cloud und andere in der lokalen Umgebung ausgeführt werden.

Zudem können die verschiedenen Komponenten wie SSIS und das Azure SDK (für den Zugriff auf Azure-Speicherfunktionen) und die Anwendungsinstallationspakete (MSI-Dateien), die für jede in der verteilten Umgebung ausgeführte VM erforderlich sind, manuell installiert und konfiguriert werden.

Erstellen portierbarer LösungenInsbesondere in einer Architektur für horizontales Skalieren ist es wichtig, dass Optionen wie Verbindungszeichenfolgen, Anmeldeinformationen und alle anderen Konfigurationsoptionen der Lösung schnell geändert werden können. Dies erfordert in der Regel einen zentralen Konfigurationsmechanismus, mit dem Informationen abgerufen und an alle an der Datenverschiebung beteiligten Komponenten und Dienste weitergegeben werden, sodass Änderungen minimalen Aufwand verursachen. Vor diesem Hintergrund lassen sich sowohl Standardtools wie SSIS als auch benutzerdefinierte Komponenten und Anwendungen problemlos implementieren. Azure-Speicher eignet sich gut zum Speichern und Verwalten der Konfigurationsinformationen, weil der Zugriff sowohl lokal als auch in der Cloud problemlos möglich ist.

Die SSIS-Plattform enthält bereits mehrere Funktionen, wie Konfigurationsdateien und -parameter, die das Portieren und Skalieren einer Lösung vereinfachen. Zusätzliche Prozesse und Dienste, die die gesamte Datenverschiebelösung ausmachen, sollten denselben Konfigurationsansatz verfolgen, damit die Lösung nahtlos zwischen unterschiedlichen Umgebungen portiert werden kann.

15

Verteilung von Paketen und CodekomponentenNachdem alle Elemente der Lösung implementiert wurden, muss das richtige Verfahren zur physischen Verteilung der verschiedenen SSIS-Pakete und Codekomponenten auf zahlreiche Computer gefunden werden. Noch wichtiger ist jedoch, wie diese Pakete und Codeelemente auf den verschiedenen Servern und VMs (Virtual Machines) gehostet und ausgeführt werden. Obwohl die native SSIS-Umgebung in SQL Server 2012 verschiedene Paketspeichertypen und Bereitstellungsmodelle bietet, erfordert die Entwicklung einer End-to-End-Datenverschiebelösung u. U. alternative Methoden. Wie können Orchestrierungsdienste/-anwendungen bedarfsgesteuert zur Überwachung und Steuerung des Datenverschiebeprozesses implementiert werden? Und in welchem Umfang kann die zugrunde liegende SSIS-Infrastruktur genutzt werden? Das Whitepaper „SQL Server 2012 SSIS für Azure und Datenverschiebung im Hybridmodell“ enthält ein spezifisches Beispiel zur Komponentenverteilung und -koordination. Sie finden das Dokument in der MSDN Library unter Microsoft-Whitepapers für SQL Server 2012.

Da einige Funktionen, die die Azure Platform für Web- und Workerrollen bereithält, von WA-VMs und physischen Windows Server-basierten Servern nicht implementiert werden, sollten diese Komponenten idealerweise als Windows-Dienste implementiert werden, um sicherzustellen, dass die Prozesse zusammen mit den verschiedenen Hosts gestartet und unabhängig von einer interaktiven Benutzersitzung auf dem jeweiligen Computer weiter ausgeführt werden. Die .NET-Plattform macht es sehr einfach, diese Softwarepakete zu erstellen, zu packen und anschließend unter Verwendung der zuvor beschriebenen Optionen auf den verschiedenen Hosts zu verteilen und bereitzustellen.

Die Orchestrierungsdienste/-anwendungen interagieren mit verschiedenen externen Komponenten (Windows Azure-BLOB-Speicher, Warteschlangen usw.), rufen das SSIS-Ausführungsmodul (DtExec.exe) auf und orchestrieren die Datenlade- und -transformationstasks auf mehreren Hosts oder VMs.

Für die Paketausführung benötigte angepasste Komponenten müssen ebenfalls über mehrere Knoten verteilt werden.

Mit diesem verteilten Ansatz kann eine robuste, portierbare and flexible Bereitstellungs- und Ausführungsumgebung aufgebaut werden, die die End-to-End-Datenverschiebelösung in einer vollständig hybriden Infrastruktur hostet.

Azure SQL-Datenbank als Ziel von DatenverschiebungenObwohl SQL Server und Windows Azure SQL-Datenbanken Gemeinsamkeiten aufweisen, sollten Sie nicht fälschlicherweise als identisch angesehen werden. Einige Unterschiede können die Anwendungsleistung unter dem Windows Azure SQL-Datenbankdienst im Vergleich zu SQL Server-Datenbanken beeinflussen.

16

Windows Azure SQL-Datenbank ist ein gehosteter Dienst, der eine komplette, mehrinstanzenfähige Architektur implementiert. Im Gegensatz zu herkömmlichen SQL Server-Implementierungen bietet Windows Azure SQL-Datenbank Funktionen wie integrierte Hochverfügbarkeit (HA) und automatisierte Sicherungen und wird auf Standardhardware anstatt auf Großservern ausgeführt. In der Datenbank wird ein Teil der in lokalen Umgebungen verwendeten Funktionen ausgeführt, darunter Datenbankkomprimierung, parallele Abfragen, ColumnStore-Indizes, Tabellenpartitionierung usw. Weitere Informationen zu Funktionseinschränkungen bei Windows Azure SQL-Datenbanken finden Sie unter Einschränkungen für SQL Server-Funktionen (Windows Azure SQL-Datenbank) (http://msdn.microsoft.com/de-de/library/windowsazure/ff394115.aspx).

Einer der größten Unterschiede zwischen Windows Azure SQL-Datenbank und SQL Server besteht darin, dass Windows Azure SQL-Datenbank einen mehrinstanzenfähigen Dienst mit horizontaler Skalierung verfügbar macht, unter dem die Ressourcen eines oder mehrerer Computer in einem Microsoft-Rechenzentrum von verschiedenen Abonnenten genutzt werden. Ziel ist es, die Gesamtarbeitslast durch die Umlagerung auf unterschiedliche Computer im Rechenzentrum gleichmäßig zu verteilen. Als Computer werden rackbasierte Standardserver eingesetzt, bei denen ein optimales Preis-Leistungs-Verhältnis vor der Gesamtperformance kommt. Nicht jeder Windows Azure SQL-Datenbankknoten im Hostingmodell verwendet hochleistungsfähige Hardware.

Wenn die Kapazität eines einzelnen Computers für eine Kundenanwendung nicht mehr ausreicht, muss die Anwendung so angepasst werden, dass die Arbeitslast von einem einzelnen Server auf mehrere Datenbanken (vermutlich mehrere Computer) aufgeteilt wird. Die Flexibilität und Verwaltbarkeit der Umgebung birgt jedoch einen Nachteil, weil die Anwendung unerwartet auf einen anderen Computer umgelagert werden kann. Da die Sitzungen zustandslos sind, sollte die Anwendung mit Techniken ausgestattet werden, die einzelne Fehlerquellen (SPoF) ausschalten. Dazu gehören das Caching auf andere Ebenen und eine Wiederholungslogik für Verbindungen und Befehle, um Ausfallsicherheit zu gewährleisten.

Darüber hinaus werden die verschiedenen Ebenen einer WA-Infrastruktur nicht im selben Netzwerksubnetz implementiert, sodass es immer gewisse Latenzunterschiede zwischen Clientanwendungen und Windows Azure SQL-Datenbanken geben wird, und zwar selbst dann, wenn Anwendungen und Datenbank im selben physischen Rechenzentrum gehostet werden. Herkömmliche SQL Server-Lösungen zum Laden von Daten, die viele kleine Abfragen oder Updates ausführen, werden in Windows Azure aufgrund dieser physischen Netzwerkunterschiede möglicherweise langsamer ausgeführt. Für die früheren Client-/Servermodelle gilt dasselbe: Roundtrips zwischen den Ebenen einer Lösung bedürfen besonderer Aufmerksamkeit, wenn es darum geht, sichtbare Latenzunterschiede zu behandeln.

17

Überlegungen zur ArchitekturEine der häufigsten Herausforderungen bei SSIS-Paketen ist die Behandlung unerwarteter Fehler während der Ausführung. Zusätzlich stellt sich die Frage, wie die Zeit zum Abschließen eines ETL-Prozesses verkürzt werden kann, wenn die Verarbeitung nach einem Ausfall fortgesetzt werden muss. Bei Ablaufsteuerungstasks, beispielsweise im Dateisystem, kann die Ausführung mithilfe von Prüfpunkten fortgesetzt werden, ohne dass bereits erledigte Aufgaben wiederholt werden müssen. Vollständige Anweisungen zur Verwendung von Prüfpunkten finden Sie unter Neustarten von Paketen mit Prüfpunkten (http://msdn.microsoft.com/de-de/library/ms140226.aspx).

Häufig stellt der Datenfluss den größten Teil des SSIS-Pakets dar. Dieser Abschnitt enthält Entwurfsstrategien. Sie erfahren, wie Sie Pakete erstellen, die automatische Wiederholungen nach Fehlern zulassen, und die Datenflusspipeline so konfigurieren, dass die Verarbeitung an der Fehlerquelle fortgesetzt werden kann, ohne dass der gesamte Datenfluss wiederholt werden muss.

Weitere Ausführungen zur Behandlung vorübergehender Fehler der WA-Plattform finden Sie im Whitepaper „SQL Server 2012 SSIS für Azure und Datenverschiebung im Hybridmodell“. Sie finden das Dokument in der MSDN Library unter Microsoft-Whitepapers für SQL Server 2012.

Neustart ohne Verlust des PipelinefortschrittsPakete sollten so konzipiert sein, dass der Fortschritt der Paketausführung bis zum Auftreten eines Fehlers nicht verloren geht. Elemente in der Ablaufsteuerung eines Pakets werden mithilfe von Prüfpunkten abgesichert. Die Fähigkeit, einen Datenfluss verlustfrei neu zu starten, kann jedoch nur direkt beim Paketentwurf implementiert werden. In diesem Abschnitt wird eine Strategie vorgestellt, bei der der Datenfluss an der Fehlerstelle neu gestartet und die Paketausführung automatisch wiederholt werden kann, sodass sie bei einem Verbindungsverlust nicht abbricht. Derartige Verbindungsunterbrechungen oder kurze Aussetzer einzuplanen, ist besonders bei Datenverschiebungen in oder aus einer WA SQL-Datenbank unerlässlich.

GrundprinzipObwohl die Leistung bei jeder Art von Datenbewegung relevant ist, sollten Sie abwägen, inwieweit Ihre Leistungsanforderungen den Verlust des Arbeitsfortschritts bei einem Fehler während der Datenverschiebung tolerieren.

Wenn Daten verschoben werden, müssen Sie feststellen können, welche Daten bereits am Ziel eingetroffen sind und welche nicht. Bei reinen Einfügevorgängen reicht dazu meist schon der Primärschlüssel aus. Bei anderen Daten kann das Datum der letzten Änderung Aufschluss geben. Wie immer die Daten auch geartet sind, bei der Implementierung eines Neustartmechanismus müssen Sie als erstes wissen, wie Sie die Daten identifizieren, die bereits am Ziel vorhanden sind, die am Ziel aktualisiert werden müssen und die noch an das Ziel übermittelt werden müssen. Nachdem alle Voraussetzungen erfüllt sind, können Sie die Daten segmentieren und sortieren, um nur diejenigen zu verarbeiten, die noch nicht am Ziel sind, oder um den Aufwand für die in jeder Phase erforderliche Nachbearbeitung zu minimieren.

18

Durch die Segmentierung können Sie leicht nachverfolgen, welche Blöcke verarbeitet wurden, und die Sortierung zeigt an, welche Datensätze innerhalb eines Blockes bereits verarbeitet wurden. Bei diesem Ansatz ist es nicht notwendig, jede Quellzeile mit der Zielzeile zu vergleichen, um zu ermitteln, ob der jeweilige Datensatz verarbeitet wurde.

Komplexere ETL-Prozesse weisen möglicherweise mehrere Stagingumgebungen auf. Jede Stagingumgebung stellt ein Ziel für eine Phase des ETL-Prozesses dar. In diesem Zusammenhang sollte jede Stagingumgebung als separates Ziel angesehen werden und jedes Segment des ETL-Prozesses über einen eigenen Neustartmechanismus verfügen.

Beispiel mit nur einem ZielDer einfachste wiederstartbare Datenfluss ist klein und verfügt über eine einzelne ganze Zahl als Primärschlüssel. Wenn SQL Server die Quelle darstellt, besteht eine Best Practice darin, die aus der Quelle abgerufenen Daten zu begrenzen. Betrachten Sie die klassische Beispieltabelle aus AdventureWorks: „Production.TransactionHistory“. Die Tabelle weist folgende Struktur auf:

CREATE TABLE [Production].[TransactionHistory](

[TransactionID] INT NOT NULL PRIMARY KEY,[ProductID] INT NOT NULL,[ReferenceOrderID] INT NOT NULL,[ReferenceOrderLineID] INT NOT NULL,[TransactionDate] INT NOT NULL,[TransactionType] NCHAR(1) NOT NULL,[Quantity] INT NOT NULL,[ActualCost] MONEY NOT NULL,[ModifiedDate] DATETIME NOT NULL,

)

In diesem Beispiel enthält die Tabelle eine einzelne ganze Zahl als Primärschlüssel. Wenn die Daten zum Zeitpunkt der Verschiebung statisch sind oder Tabelleneinfügungen (und keine Updates) ausgeführt werden, benötigen Sie lediglich den Primärschlüssel, um festzustellen, ob eine Zeile am Ziel angekommen ist oder nicht. Jeden Primärschlüsselwert mit dem Ziel zu vergleichen, ist relativ zeitaufwändig. Sie sollten stattdessen strategisch vorgehen und die Daten in der Quelldatei nach der TransactionID-Spalte sortieren.

Sie müssen lediglich wissen, dass Daten der Reihe nach verarbeitet werden und die höchste TransactionID kennen, für die am Ziel ein Commit ausgeführt wurde.

In einem SSIS-Paket gehen Sie dazu wie folgt vor.

1. Ermitteln Sie die höchste TransactionID am Ziel.2. Erstellen Sie eine Abfrage, durch die nur Datensätze mit einer TransactionID aus der Quelle

abgerufen werden, die größer als der höchste Wert am Ziel ist.3. Verwenden Sie in der Quellabfrage eine ORDER BY TransactionID-Anweisung, um

sicherzustellen, dass der Vergleich der höchsten TransactionID auch beim nächsten Paketstart noch Gültigkeit besitzt.

19

Bei Verwendung einer relationalen Datenquelle können Sie in SSIS mit einem „SQL ausführen“-Task den höchsten Wert in eine Paketvariable übernehmen. Allerdings besteht die Möglichkeit, dass am Ziel gar keine Zeilen vorhanden sind.

SELECT MAX(TransactionID) FROM Production.TransactionHistory

Beim Abruf der höchsten TransactionID aus einer leeren Tabelle lautet das Ergebnis NULL. Dies kann Probleme mit der im Paket verwendeten Logik verursachen. Es empfiehlt sich daher, mit einem „SQL ausführen“-Task die niedrigste, nicht verarbeitete TransactionID in der Quelle zu ermitteln. Als Nächstes fragen Sie entweder die höchste TransactionID vom Ziel ab oder verwenden, falls keine vorhanden ist, einen Wert, der niedriger ist als der kleinste TransactionID-Wert in der Quelle. Erstellen Sie eine Quellabfrage, mit der nur Datensätze über diesem/größer als dieser Wert abgerufen werden, und vergessen Sie nicht die ORDER BY TransactionID-Anweisung.

HINWEIS: Obwohl Sie dieselben Ergebnisse erhalten, wenn Sie nur einen Wert aus dem Ziel abrufen und eine ISNULL-Funktion oder CASE-Anweisung in der WHERE-Klausel der Quellabfrage verwenden, können insbesondere bei komplexeren Quellabfragen Leistungsprobleme auftreten. Daher sollten Sie auf diese „Abkürzung“ besser verzichten. Ermitteln Sie stattdessen einen Wert, den Sie sicher als Untergrenze verwenden können, und erstellen Sie die Quellabfrage mit diesem Wert.

HINWEIS: Wenn SQL Server die Quelle darstellt, muss bei Verwendung einer ORDER BY-Klausel für einen gruppierten Index keine zusätzliche Sortierung ausgeführt werden. Die Daten sind bereits so sortiert, dass sie ohne SORT-Befehl abgerufen werden können. Wenn die Daten am Ziel ebenfalls über einen gruppierten Index für dieselbe Spalte verfügen, werden durch die Sortierung der Quelldaten nicht nur die Quellabfrage sondern auch Einfügungen am Ziel optimiert. Außerdem wird dadurch die Reihenfolge der Daten innerhalb der SSIS-Pipeline beibehalten, was einen Neustart des Datenflusses an der Fehlerquelle ermöglicht.

Verfahren Sie wie folgt, um dieses Beispielpaket zu erstellen.

1. Erstellen Sie ein neues Paket in einem neuen oder vorhandenen Projekt. Benennen Sie das Paket mit „SimpleRestart“.

2. Erstellen Sie Verbindungs-Manager, um eine Verbindung mit der Quelle und dem Ziel herzustellen. In diesem Beispiel wird für den Quell- und Zielserver ein OLE DB-Verbindungs-Manager erstellt.

3. Ziehen Sie einen neuen „SQL ausführen“-Task auf die Ablaufsteuerungsoberfläche, und nennen Sie ihn „Pull Min TransactionID From Source“.

4. Erstellen Sie eine SSIS-Variable auf Paketebene, und nennen Sie diese „minTransactionIDAtSource“. Mithilfe dieser Variablen wird der Wert gespeichert, den Sie aus dem gerade hinzugefügten „SQL ausführen“-Task abrufen. Vergewissern Sie sich, dass der Datentyp Int32 lautet und dem Wert der TransactionID in der Tabelle entspricht, und legen Sie einen geeigneten Anfangswert fest.

20

5. Konfigurieren Sie „Pull Min TransactionID From Source“ wie folgt.a. Bearbeiten Sie den Task, und geben Sie für Verbindung den Verbindungs-Manager für

den Quellserver an.b. Obwohl Sie SQLStatement in einer Variablen speichern können, belassen Sie

SQLSourceType in diesem Beispiel bei Direkteingabe. Öffnen Sie das Eingabefenster für SQLStatement, und geben Sie die folgende Abfrage ein: SELECT ISNULL(MIN(TransactionID), 0) FROM

Production.TransactionHistory

HINWEIS: Sie sollten SQL-Abfragen vor der Eingabe in die SSIS-Editoren testen, weil dadurch das Debuggen vereinfacht wird. Die Fenster des SSIS-Abfrage-Editors bieten praktisch keine Hilfe zum Debuggen.

Abbildung 5: Konfigurieren des „SQL ausführen“-Tasks zur Suche der niedrigsten TransactionID in der Quelle.

c. Schließen Sie das Fenster SQL-Abfrage eingeben, indem Sie auf OK klicken. d. Legen Sie die Eigenschaft ResultSet auf Einzelne Zeile fest.e. Klicken Sie im linken Bereich des Editors für den „SQL ausführen“-Task auf Resultset,

um anzugeben, wie der Wert dieser Abfrage erfasst wird.f. Klicken Sie auf die Schaltfläche Hinzufügen, um ein Resultset hinzuzufügen.

21

g. Ändern Sie im neuen Resultset den Eintrag unter Ergebnisname in 0. Vergewissern Sie sich, dass unter Variablenname die (in Schritt 4 erstellte) Variable User::minTransactionIDAtSource angezeigt wird. In dieser Variablen wird das Ergebnis der SQL-Abfrage gespeichert.

h. Schließen Sie den Editor für den Task „SQL ausführen“. Nach dem Schließen sollten keine Fehler für den Task angezeigt werden.

6. Ziehen Sie einen weiteren „SQL ausführen“-Task auf die Ablaufsteuerungsoberfläche. Nennen Sie diesen Task Pull Max TransactionID from Destination. Verbinden Sie Pull Min TransactionID From Source mithilfe einer Success-Rangfolgeneinschränkung mit diesem neuen Task.

7. Erstellen Sie eine neue Variable im Bereich des Pakets. Nennen Sie die neue Variable maxTransactionIDAtDestination. Weisen Sie ihr den Int32-Datentyp, d. h. den Datentyp der TransactionID, und einen geeigneten Anfangswert zu.

8. Öffnen Sie für den neuen Task den Editor für den Task „SQL ausführen“, und gehen Sie wie folgt vor:a. Legen Sie ResultSet auf Einzelne Zeile fest.b. Legen Sie den Verbindungs-Manager für den Zielserver fest.c. SQLSourceType: Direkteingabe d. Verwenden Sie für SQLStatement „SELECT ISNULL(MAX(TransactionID), ?) FROM

Production.TransactionHistory“.HINWEIS: „?“ ist ein Abfrageparameter, dessen Wert vorübergehend festgelegt wird.

e. Schließen Sie den Abfrage-Editor, indem Sie auf OK klicken, und klicken Sie im linken Bereich des Editors für den Task „SQL ausführen“ auf Parameterzuordnung.

f. Klicken Sie auf Hinzufügen, um einen einzelnen Parameter hinzuzufügen. i. Wählen Sie für Variablenname den Namen User::minTransactionIDAtSource.

ii. Für Richtung wählen Sie Eingabe. iii. Der Datentyp sollte LONG lauten, was in diesem Kontext einer 32-Bit-Ganzzahl

entspricht. iv. Ändern Sie Parametername in 0. Beachten Sie, dass der Wert in 0 geändert

werden muss, da der Zeichenname einen Fehler verursacht.g. Klicken Sie im linken Bereich auf Resultset. Klicken Sie auf die Schaltfläche Hinzufügen,

um ein neues Resultset hinzuzufügen. i. Ändern Sie Ergebnisname in 0.

ii. Wählen Sie unter Variablenname den Namen User::maxTransactionIDAtDestination aus, der der in Schritt 7 erstellten Variablen entspricht. Diese Variable enthält das Ergebnis der Abfrage, die Sie nach Ausführung dieses Tasks eingeben.

HINWEIS: Der nächste Schritt richtet sich nach dem Typ der im Datenfluss verwendeten Quelle. Für eine OLE DB-Quelle kann eine SSIS-Variable mit einer SQL-Anweisung als Abfrage verwendet werden. Bei einer ADO.NET-Verbindung ist dies nicht möglich, sie kann aber für die Verwendung eines Paket- oder Projektparameters als Quellabfrage parametrisiert werden. In ersten Beispiel verwenden Sie eine OLE DB-Quelle mit einer Variablen, die die Quellabfrage enthält.

22

9. Ziehen Sie einen Datenflusstask auf die Ablaufsteuerungsoberfläche. Nennen Sie ihn Main data move, und verbinden Sie Pull Max TransactionID From Destination mithilfe einer Success-Rangfolgeneinschränkung mit diesem Datenflusstask.Wenn das Paket an diesem Punkt ausgeführt wird, haben Sie die Werte gespeichert, die Sie zum Herstellen des Startpunkts für die aktuelle Ausführung benötigen. Anschließend müssen Sie eine Variable einrichten, die die SQL-Quellabfrage enthält.

10. Erstellen Sie eine neue Variable, deren Bereich auf Paketebene definiert ist. Nennen Sie diese Variable sourceQuery, und legen Sie den Datentyp auf string fest. Sie verwenden einen Ausdruck, um den Wert dieser Variablen zur Laufzeit dynamisch abzurufen. Als Grundlage dient der Wert, den Sie als Startpunkt für die Abfrage bestimmt haben. Verfahren Sie wie folgt.

a. Klicken Sie rechts neben der Spalte Ausdruck auf die Schaltfläche mit den Auslassungspunkten, um den Ausdrucks-Generator zu öffnen.

b. Erweitern Sie im Ausdrucks-Generator im Fenster oben links den Knoten Variablen und Parameter. Sie verwenden die in Schritt 7 erstellte Variable User::MaxTransactionIDAtDestination. Diese sollte in den aufgeführten Variablen enthalten sein. Die Variable weist den Int32-Datentyp auf, soll jedoch als Teil einer String-Variablen verwendet werden. Zu diesem Zweck müssen Sie sie in einen DT_WSTR-Datentyp umwandeln. Erweitern Sie im oberen rechten Bereich den Knoten Typumwandlungen, und suchen Sie die Typumwandlung (DT_WSTR, <<length>>).

c. Geben Sie unter Ausdruck die Abfrage ein. An den Stellen, an denen Sie den Variablennamen oder die Typumwandlung hinzufügen müssen, ziehen Sie diese einfach aus dem entsprechenden Fenster in das Ausdrucksfeld. So vermeiden Sie Tippfehler im Editor. Erstellen Sie den Ausdruck wie folgt:

„SELECT TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate FROM Production.TransactionHistory WHERE TransactionID > “ + (DT_WSTR, 12) @[User::maxTransactionIDAtDestination] + „ORDER BY TransactionID“

Beachten Sie, dass der ganzzahlige Wert durch die Typumwandlung in eine Zeichenfolge mit einer maximalen Breite von 12 Zeichen geändert wird.

Die Breite von 12 Zeichen ist völlig ausreichend für die gesamte Palette an SQL INT-Werten, einschließlich negativer Werte, falls erforderlich. Für BIGINT benötigen Sie eine Breite von 22 Zeichen. Wählen Sie die Größe der Zeichenvariablen entsprechend dem abgerufenen Datentyp.

Nachdem Sie den Ausdruck eingegeben haben, klicken Sie auf die Schaltfläche Ausdruck auswerten, um sicherzustellen, dass der Ausdruck von SSIS ordnungsgemäß analysiert werden kann. Sie werden feststellen, dass der Anfangswert von „maxTransactionIDAtDestination“ ordnungsgemäß in den ausgewerteten Ausdruck eingefügt wurde. Dieser Wert wird zur Laufzeit entsprechend festgelegt.

23

Achten Sie darauf, dass die SQL-Anweisung die ORDER BY-Klausel enthält, da bei relationalen Datenbanken eine garantierte Reihenfolge nur mit ORDER BY gewährleistet werden kann. Die erstellte Neustartmethode hängt von der Reihenfolge der Schlüsselwerte ab.

d. Schließen Sie den Ausdrucks-Generator, indem Sie auf die Schaltfläche OK klicken. Die dynamisch erstellte SQL-Anweisung ist jetzt in der SourceQuery-Variablen gespeichert.

11. Doppelklicken Sie auf den Datenflusstask Main data move, um die Entwurfsoberfläche Datenfluss zu öffnen.

12. Ziehen Sie eine OLE DB-Quelle auf die Ablaufsteuerungsoberfläche des Datenflusses. Nennen Sie sie Retrieve from TransactionHistory.

HINWEIS: Da der Name einer Datenflusskomponente keinen Punkt aufweisen darf, ist der vollständige Name Pull From Production.TransactionHistory nicht zulässig. Wenn Sie für Tabellennamen keine Unterstriche verwenden, können Sie den Punkt in der SSIS-Benennungskonvention durch den Unterstrich ersetzen.

13. Doppelklicken Sie auf die Quelle Retrieve From TransactionHistory, um den Quellen-Editor für OLE DB zu öffnen.

a. Wählen Sie für OLE DB-Verbindungs-Manager den Verbindungs-Manager Quellserver aus.b. Wählen Sie in der Dropdownliste Datenzugriffsmodus die Option SQL-Befehl aus

Variable aus.c. Wählen Sie in der Dropdownliste Variablenname die Variable User::sourceQuery aus,

die Sie in Schritt 10 erstellt haben. d. Klicken Sie auf Vorschau, um sicherzustellen, dass die Abfrage auf dem Quellserver

ausgeführt werden kann.e. Vergewissern Sie sich im Editor auf der Seite Spalten, dass alle Spalten ausgewählt sind.f. Klicken Sie auf OK, um den Quellen-Editor für OLE DB zu beenden.

14. Ziehen Sie ein OLE DB-Ziel auf die Ablaufsteuerungsoberfläche. Nennen Sie es TransactionHistory Destination. Verbinden Sie die Quelle Retrieve From TransactionHistory mit dem neuen Ziel. Öffnen Sie das Ziel, indem Sie darauf doppelklicken, und konfigurieren Sie es wie folgt.

a. Wählen Sie in der Dropdownliste OLE DB-Verbindungs-Manager den Verbindungs-Manager Zielserver aus.

b. Wählen Sie unter Datenzugriffsmodus die Option Tabelle oder Sicht – schnelles Laden aus, falls nicht bereits geschehen.

c. Wählen Sie unter Name der Tabelle oder Sicht den Namen aus der Dropdownliste aus, oder geben Sie den Namen des Zielservers ein. In diesem Fall lautet er Production.TransactionHistory.

24

d. Wenn Sie die oben beschriebene TransactionHistory-Definition verwenden, können Sie die Standardeinstellungen auf der Seite Verbindungs-Manager zu Demozwecken beibehalten. Bei Verwendung der AdventureWorks-Datenbank müssen Sie Identitätswerte beibehalten auswählen.

e. Ordnen Sie im Editor auf der Seite Zuordnungen die Spalten zu.

HINWEIS: In den meisten Fällen empfiehlt es sich, Fehlerzeilen an eine Zieldatei zu senden und die Ausgabe umzuleiten. Dies ist nicht erforderlich, um einen Neustart in der Pipeline zu veranschaulichen. Die Schritte zum Erstellen eines Fehlerflusses und Umleiten von Zeilen finden Sie unter Konfigurieren einer Fehlerausgabe in einer Datenflusskomponente (http://msdn.microsoft.com/de-de/library/ms140083.aspx).

f. Klicken Sie auf OK, um den Ziel-Editor für OLE DB zu beenden. 15. Führen Sie einen Test durch, indem Sie einen Import starten, beenden und dann neu starten.

Bei jedem Neustart sollte im Datenfluss die nächste zu verschiebende Zeile ausgewählt werden. Davor liegende Zeilen werden ignoriert.

Sie haben gerade ein einfaches Paket erstellt, das in der Lage ist, den Datenfluss nach einem Fehler neu zu starten. Das Beispiel zum Entwerfen eines Pakets, mit dem die Ausführung wiederholt werden kann, basiert auf diesem Paket. Beachten Sie, dass von keinem Task in der Ablaufsteuerung Prüfpunkte gespeichert werden sollen. Falls es erforderlich ist, das Paket aufgrund eines Fehlers neu zu starten, müssen die Komponenten Pull Min TransactionID From Source und Pull Max TransactionID From Destination ausgeführt werden, um genau zu ermitteln, an welcher Stelle die vorherige Datenflussausführung unterbrochen wurde. Der Entwurf von Paketen, deren Ausführungsfortschritt ermittelt und Datenfluss neu gestartet werden kann, ist immer von Vorteil, solange anhand der zugrunde liegenden Daten festgestellt werden kann, an welcher Stelle der Datenfluss unterbrochen wurde. In Cloudumgebungen, die anfälliger für Netzwerkschwankungen sind, ist diese Vorgehensweise praktisch unverzichtbar.

Beispiel mit mehreren ZielenDieses Prinzip kann auf Datenflüsse mit mehreren Zielen ausgeweitet werden und ist z. B. beim Verschieben von Daten in ein Shard-Datenmodell, was häufig bei WA SQL-Datenbanken der Fall ist, hilfreich. In diesen Fällen wird eine bedingte Teilung verwendet, um die einzelnen Zeilen an das richtige Ziel zu übermitteln. Verweisdaten können mithilfe eines Multicasts an alle Ziele gesendet werden. Im Folgenden die wichtigsten Prinzipien für den Entwurf wiederstartbarer Pakete.

25

Es ist möglich, dass der Datenfluss in den verschiedenen Zielen bei Auftreten des Fehlers an unterschiedlichen Stellen unterbrochen wurde. Daher müssen Sie für jedes Ziel den höchsten eingefügten Schlüsselwert ermitteln. Erstellen Sie für jedes Ziel eine Variable, in die der höchste Wert eingefügt wird.

Der Ausgangspunkt in der Quelle ist der auf den niedrigsten Schlüsselwert – der erfolgreich an den Zielorten eingefügt wurde – folgende Datensatz. Beispiel: Wenn der höchste in einen Shardsatz eingefügte Schlüsselwert wie folgt lautet, muss der Datenfluss in der Quelle mit dem auf den Schlüsselwert 1000 folgenden Datensatz fortgesetzt werden.

o Shard00-Schlüsselwert 1000o Shard01-Schlüsselwert 1100o Shard02-Schlüsselwert 1050

Da die Gefahr besteht, dass Daten mehrfach extrahiert werden, müssen Sie nach jedem Ziel filtern, um eine Primärschlüsselverletzung zu verhindern. Verwenden Sie eine Transformation für bedingtes Teilen, um die bereits verarbeiteten Schlüsselwerte herauszufiltern.

Im oben genannten Shardingbeispiel würden Sie die Variablen „maxTransactionIDAtShard00“, „maxTransactionIDAtShard01“ und „maxTransactionIDAtShard02“ erstellen. Mithilfe der „SQL ausführen“-Tasks würden Sie die Werte ermitteln, die in den einzelnen Variablen gespeichert werden sollen. In der Transformation für bedingtes Teilen könnten Sie die Ausgaben „Shard00“, „Shard01“ und „Shard02“ definieren. Der Ausdruck für die Ausgaben würde wie folgt aussehen.

ShardID == 0 && TransactionID > @[User::maxTransactionIDAtShard00]ShardID == 1 && TransactionID > @[User::maxTransactionIDAtShard01]ShardID == 2 && TransactionID > @[User::maxTransactionIDAtShard02]

Falls Zeilen in die Pipeline eingefügt werden, deren Schlüsselwert unter der TransactionID auf einem bestimmten Shard liegt, der Datensatz jedoch für diesen Shard vorgesehen ist, wird er nicht gesendet, sodass keine Primärschlüsselverletzungen auftreten. Leiten Sie diese Datensätze an die Standardausgabe oder eine getrennte Ausgabe um, damit sie nicht weiterverarbeitet werden.

26

Abbildung 2: Eine bedingte Teilung, die für drei Shards und für Neustarts ohne Primärschlüsselverletzungen am Ziel konfiguriert wurde. Zu Beginn jeder Ausführung wird der höchste Schlüsselwert an jedem Ziel in den maxTransactionIDAtShardXX-Variablen gespeichert. In der Pipeline werden an diesen Shard gerichtete Zeilen, die einen zu niedrigen Schlüsselwert aufweisen, nicht an das Ziel gesendet und daher nicht erneut verarbeitet. Diese Zeilen werden an die Standardausgabe umgeleitet. Da die Standardausgabe nicht verbunden ist, werden sie in der Pipeline nicht weiterverarbeitet.

Weitere Tipps zu Neustarts Wenn das Ziel eine Datei ist, verwenden Sie eine Variable zur Darstellung des Dateinamens.

Wählen Sie aussagekräftige Dateinamen (und fügen Sie z. B. eine Folgenummer oder einen Timestamp an). Lassen Sie unterschiedliche Datenblöcke in verschiedenen Dateien verarbeiten. Wenn die Blöcke in derselben Reihenfolge verarbeitet werden und deterministisch definiert sind, können Sie feststellen, welche Dateien bereits erstellt wurden und so den Startpunkt bestimmen. Je nach Anforderungen und Logik müssen bereits verarbeitete Dateien u. U. verschoben, umbenannt oder gelöscht werden.

27

Umfasst die Quelle mehrere Dateien, verarbeiten Sie jede Datei einzeln und verfolgen, welche die aktuelle Datei ist. Beim Neustart kann die Verarbeitung nach der zuletzt erfolgreich verarbeiteten Datei fortgesetzt werden. Mit einer Sortierung nach Dateiname oder Datum können Sie sicherstellen, dass die Dateien jedes Mal in derselben Reihenfolge verarbeitet werden, und problemlos die zuletzt erfolgreich verarbeitete Datei ermitteln.

Wenn die Quelle auf SQL Server beruht, können Sie ganzzahlige Werte mit dem SSIS-Datentyp string abfragen. Solange die Zeichenfolge als SQL Integer umgewandelt werden kann, wird der Datentyp von SQL während der Optimierung umgewandelt. Nach diesem Prinzip können Sie den Variablen MaxTransactionIDAtDestination und MinTransactionIDAtSource im Beispielpaket den String-Datentyp zuweisen, die Eingabeparametertypen in Pull Max TransactionID From Destination ändern und dieses Paket als Vorlage nutzen, die auch Primärschlüssel in Form von Buchstaben unterstützt. Verwenden Sie keinen SQL_VARIANT-Datentyp für andere Datentypen in SQL, da beim Abrufen der minimalen und maximalen Schlüsselwerte ansonsten vollständige Scans ausgeführt werden.

Wenn die Quelle eine Datei oder eine andere Datenquelle ist, in der keine Abfragen mit WHERE-Klauseln oder anderen Filterklauseln möglich sind, verfahren Sie wie folgt.

1. Fügen Sie zwischen der Quell- und Zielkomponente eine Transformation für bedingtes Teilen ein.

2. Konfigurieren Sie in der bedingten Teilung eine Ausgabe. Bezeichnen Sie diese Ausgabe mit Trash oder einem anderen Namen, der erkennen lässt, das Sie diese Zeilen nicht benötigen.

3. Verwenden Sie für die Bedingung, mit der die an das Ziel gesendeten Zeilen abgerufen werden, einen Ausdruck, um die bereits am Ziel gespeicherten Zeilen herauszufiltern. Der Ausdruck für das vorherige Beispiel könnte wie folgt lauten.

[TransactionID] <= @[User::maxTransactionIDAtDestination]

4. Verbinden Sie die Ausgabe Trash mit keiner Komponente, sofern diese Zeilen nicht unbedingt ausgewertet werden müssen. Sie dient lediglich zum Herausfiltern der im letzten Durchgang verarbeiteten Zeilen.

Dies erspart zwar nicht das Lesen der in dieser Datei bereits verarbeiteten Zeilen, verhindert jedoch, dass sie erneut an das Ziel übertragen werden und spart so Netzwerkbandbreite. Da diese Zeilen nicht an das Ziel gesendet werden, müssen dort keine Daten gelöscht werden.

Bei Verwendung zusammengesetzter Schlüssel bestimmen Sie den genauen Endpunkt mithilfe korrelierter Unterabfragen. Achten Sie darauf, die Daten nach denselben Schlüsseln zu sortieren. Im Folgenden ein Beispiel.

SELECT MAX(EmployeeID) AS EmployeeID, (SELECT MAX(EffectiveDate)

FROM HumanResources.EmployeeHistory i_forDate

28

WHERE h.EmployeeID = i_forDate.EmployeeID) as EffectiveDate,(SELECT MAX(EffectiveSequence)

from HumanResources.EmployeeHistory i_forSeq where h.EmployeeID = i_forSeq.EmployeeID

and h.EffectiveDate = i.ForSeq.EffectiveDate) as EffectiveDateFROM HumanResources.EmployeeHistory hORDER BY EmployeeID, EffectiveDate, EffectiveSequence

Beachten Sie, was die Reihenfolge in diesem Beispiel bewirkt. Wenn die Datensätze aller Mitarbeiter (Employees) mit der angegebenen Schlüsselreihenfolge verschoben werden sollen, ist die Sortierung in Ordnung. Wenn jedoch alle (oder einige) Mitarbeiter am Ziel bereits vorhanden sind und nur die Änderungen seit dem letzten Import übertragen werden sollen, müsste nach EffectiveDate, EffectiveSequence und EmployeeID sortiert werden, damit die Verarbeitung an der Fehlerquelle fortgesetzt werden kann. Mithilfe einer Analyse der zu importierenden Daten und der richtigen Reihenfolge können Sie den Punkt bestimmen, an dem die Ausführung wieder aufgenommen werden sollte.

Wiederholungslogik ohne manuelles EingreifenDie SSIS-Quell- und -Zielkomponenten weisen keine integrierte Wiederholungslogik auf. Allerdings steht der SSIS-Paketentwickler der Situation nicht hilflos gegenüber. Pakete, die den Neustart von SSIS-Paketen ohne Verlust des Datenflussfortschritts unterstützen, bieten auch die Möglichkeit, mit geringem Mehraufwand einen automatischen Wiederholungsmechanismus zu implementieren. Wenn Daten in oder aus WA SQL-Datenbanken verschoben werden, sind automatische Wiederholungen u. U. zur Behandlung vorübergehender Fehlerbedingungen, wie Ressourcen- oder Verbindungseinschränkungen, erforderlich. Dieser Abschnitt baut auf den Ausführungen im vorherigen Abschnitt auf und enthält ein Beispiel für eine einfache Wiederholungslogik in Paketen. Anschließend wird ein segmentierter Ansatz vorgestellt, der die Wiederholung jedes einzelnen Blockes ermöglicht und das Paket widerstandsfähiger gegen vorübergehende Fehlerbedingungen macht.

Weitere Informationen zu Einschränkungen in SQL finden Sie im Handbuch zur Leistung und Flexibilität von Windows Azure SQL-Datenbanken (http://social.technet.microsoft.com/wiki/contents/articles/3507.windows-azure-sql-database-performance-and-elasticity-guide.aspx).

Integration einer WiederholungslogikMithilfe folgender Schritte wird ein Wiederholungsmechanismus in ein einzelnes Paket integriert.

1. Bestimmen Sie, wie häufig die Paketausführung wiederholt werden soll, bevor sie fehlschlägt. In diesem Beispiel darf eine Komponente höchstens fünfmal wiederholt werden, bevor das Paket einen Fehler verursacht. Erstellen Sie im Paket eine Variable namens maxNumOfRetries. Weisen Sie der Variablen, die in Ausdrücken im Paket verwendet wird, den Datentyp int und den Wert 5 zu.

2. Richten Sie eine Variable ein, in der der Erfolgsstatus gespeichert wird. Erstellen Sie eine neue Variable im SSIS-Paket, und nennen Sie diese attemptNumber.

29

3. Verwenden Sie eine FOR-Schleife, um die Wiederholungen auszuführen, die bei einem Fehler im Datenfluss maximal zulässig sind.

4. Fügen Sie den Datenflusstask in die FOR-Schleife ein.

30

5. Legen Sie die Eigenschaft MaximumErrorCount in der FOR-Schleife auf die maximale Anzahl der für den Datenfluss zulässigen Wiederholungen fest, damit das Paket nach einer erfolgreichen Wiederholung nicht als fehlerhaft eingestuft wird. Verwenden Sie dazu einen Ausdruck mit der in Schritt 1 eingerichteten maxNumOfRetries-Variablen.

6. Ermitteln Sie mithilfe der „SQL ausführen“-Tasks die niedrigsten Schlüsselwerte in der Quelle und die höchsten Schlüsselwerte im Ziel, wie im vorherigen Abschnitt beschrieben. Bei einem einfachen Wiederholungsszenario kann die Suche in der FOR-Schleife, bei den fortgeschrittenen Beispielen an anderer Stelle in der Ablaufsteuerung vor der FOR-Schleife erfolgen.

7. Fügen Sie die „SQL ausführen“-Tasks in die FOR-Schleife ein. 8. Verbinden Sie den „SQL ausführen“-Task mithilfe der Success-Einschränkung mit dem

Datenflusstask.

31

9. Verbinden Sie den Datenflusstask mithilfe einer Success-Rangfolgeneinschränkung mit einem Skripttask, durch den die Variable für den Erfolgsstatus auf true festgelegt wird, um die FOR-Schleife zu verlassen. Im Folgenden ein Konfigurationsbeispiel für den Skripttask.

32

10. Verbinden Sie den Datenflusstask mithilfe einer Failure-Rangfolgeneinschränkung mit einem weiteren Skripttask. Konfigurieren Sie den Task mit einem Zeitintervall, das länger als das häufigste bzw. schwerwiegendste Problem dauert, das eintreten kann. Für dieses Beispiel werden 10 Sekunden als Puffer festgelegt. Legen Sie den Wert der Success-Variablen auf false fest.

11. Legen Sie die Eigenschaft FailPackageOnFailure für jeden Task in der FOR-Schleife auf false fest. In dieser Konfiguration tritt erst ein Paketfehler auf, nachdem die gesamte Anzahl der in der FOR-Schleife konfigurierten Wiederholungsversuche aufgebraucht und ein Fehler gemeldet wurde.

12. Konfigurieren Sie „SQL ausführen“-Tasks, um nach dem Fehler erneut die niedrigsten Schlüsselwerte in der Quelle und die höchsten Schlüsselwerte im Ziel zu ermitteln, damit der Datenfluss fortgesetzt werden kann, ohne dass Arbeit wiederholt werden muss. Konfigurieren Sie die Tasks, wie unter Neustart ohne Verlust des Pipelinefortschritts beschrieben.

33

Jedes Mal, wenn ein Fehler im Datenfluss auftritt und die maximale Anzahl von Wiederholungen noch nicht erreicht ist, wird vom Prozess erneut der höchste Schlüsselwert im Ziel gesucht. Der Prozess setzt die Verarbeitung an dem Punkt fort, an dem im Ziel gerade ein Commit ausgeführt wurde. Wenn der Datenfluss mehrere Ziele umfasst, beginnen Sie nach dem niedrigsten, sicher im Ziel eingegangenen Schlüsselwert und behandeln Zeilen, die eine Primärschlüsselverletzung verursachen würden, mithilfe einer bedingten Teilung oder eines Fehlerflusses.

SSIS-Optionen zur LeistungsoptimierungBevor Tipps und Tricks zur ETL-Leistungsoptimierung thematisiert werden, sollten Sie sich noch einmal vergegenwärtigen, welche Komponenten und verschiebbaren Elemente Ihr Szenario umfasst. Normalerweise besteht der ETL-Prozess aus folgenden Komponenten:

Datenquelle – liefert Daten Auf SSIS-Server ausgeführtes Paket – extrahiert Daten aus der Datenquelle, verarbeitet ggf.

Transformationen und lädt Daten in das Ziel Ziel – empfängt Daten Ein Ziel ist normalerweise eine Datenbank mit den Tabellen, in die die

Daten eingefügt werden.

All diese Schlüsselkomponenten interagieren über die Netzwerkschnittstelle und tauschen Daten untereinander aus. In einem ersten Schritt der ETL-Leistungsoptimierung sollte sichergestellt werden, dass das Netzwerk die bestmögliche Leistung erzielt.

Weitere Informationen zum SSIS-Server finden Sie unter Integration Services (SSIS)-Server (http://msdn.microsoft.com/de-de/library/gg471508.aspx).

34

Optimieren der NetzwerkeinstellungenEs gibt zwei Ebenen, die Einfluss auf die Datenübertragung zwischen den oben definierten Komponenten haben können: die physische Netzwerkkonfiguration und die Verbindungseinstellungen.

NetzwerkeinstellungenWie viele Daten gleichzeitig über das Netzwerk übertragen werden können, wird durch den Ethernetframe festgelegt. Jeder Frame wird unter Nutzung von Hardware- und Softwareressourcen verarbeitet. Unterstützt der Netzwerkadapter eine höhere Framegröße, können mehr Bytes gesendet werden. Auf diese Weise wird die CPU-Auslastung gesenkt und der Durchsatz erhöht, weil sich auch die Anzahl der zu verarbeitenden Frames reduziert.

Ein Ethernetframe kann bis zu 9.000 Bytes aufnehmen, was ihn zu einem so genannten Jumbo Frame macht. Zur Verwendung von Jumbo Frames müssen die Einstellungen der Netzwerkschnittstellenkarten (NICs) geändert werden. Im folgenden Beispiel wird die Eigenschaft MaxJumboBuffers auf 8192 festgelegt, um die Verwendung von Jumbo Frames zu ermöglichen.

Hinweis: Bevor Sie die Einstellungen der Netzwerkschnittstellenkarte für die Verwendung von Jumbo Frames ändern, sollten Sie sich vergewissern, dass dieser Frametyp von der Netzwerkinfrastruktur unterstützt wird.SQL Server akzeptiert SSIS-Netzwerkpakete mit bis zu 32.676 Bytes. Verfügt eine Anwendung über eine andere Standardpaketgröße, wird die SQL Server-Einstellung normalerweise von diesem Standardwert überschrieben. Daher wird empfohlen, die Eigenschaft Paketgröße für den Ziel-Verbindungs-Manager im SSIS-Paket auf die Standardpaketgröße der Anwendung festzulegen.

Um die Eigenschaft zu bearbeiten, klicken Sie im SSIS-Designer mit der rechten Maustaste auf den Verbindungs-Manager und klicken dann auf Bearbeiten. Klicken Sie im Dialogfeld Verbindungs-Manager auf Alle.

35

SSIS-PaketeinstellungenNeben den Einstellungen für Verbindungszeichenfolgen kann die Verarbeitungsleistung von SQL Server mit weiteren Einstellungen optimiert werden.

Im SSIS-Datenfluss werden Speicherpuffer für die Datenverarbeitung reserviert. Bei Verwendung dedizierter Multi-Core-Server mit hoher Arbeitsspeicherkapazität können die Standardspeichereinstellungen für SSIS häufig angepasst werden, um die Leistungsfähigkeit des SSIS-Servers auszuschöpfen.

Die folgenden SSIS-Speichereinstellungen können angepasst werden.

DefaultBufferSize DefaultBufferMaxRows EngineThreads

36

DefaultBufferSize und DefaultBufferMaxRows stehen in Relation zueinander. Das Datenflussmodul versucht, die Größe der einzelnen Datenzeile zu schätzen. Nachdem diese Größe mit dem in DefaultBufferMaxRows gespeicherten Wert multipliziert wurde, versucht das Datenflussmodul, ein entsprechend großes Arbeitsspeichersegment für den Puffer zu reservieren.

[Wert für Puffergröße] = [Größe einer Datenzeile] x [DefaultBufferMaxRows]

Wenn der Wert für die Puffergröße DefaultBufferSize überschreitet, wird die Anzahl der Datenzeilen vom Datenflussmodul reduziert.

Wenn die intern berechnete Mindestpuffergröße über dem Wert für die Puffergröße liegt, wird die Anzahl der Datenzeilen vom Datenflussmodul erhöht. DefaultBufferMaxRows wird vom Modul jedoch nicht überschritten.

Wenn Sie DefaultBufferSize und DefaultBufferMaxRows anpassen, sollten Sie darauf achten, welche Werte dazu führen, dass das Datenflussmodul Daten auf die Datenträger schreibt. Auf die SSIS-Serverfestplatte ausgelagerte Speicherdaten beeinträchtigen die Leistung bei der SSIS-Paketausführung. Sie können den Indikator „Gespoolte Puffer“ überwachen, um zu bestimmen, ob Datenpuffer vorübergehend auf den Datenträger geschrieben werden, während ein Paket ausgeführt wird.

Weitere Informationen zu Leistungsindikatoren für SSIS-Pakete sowie zu Leistungsindikatorstatistiken finden Sie unter Leistungsindikatoren (http://msdn.microsoft.com/de-de/library/ms137622.aspx).

Die EngineThreads-Einstellung stellt eine Empfehlung für das Datenflussmodul dar und gibt an, wie viele Threads zum Ausführen eines Tasks verwendet werden können. Bei Verwendung eines Multi-Core-Servers ist es ratsam, den Standardwert von 10 Threads zu erhöhen. Allerdings verwendet das Modul unabhängig vom Wert dieser Eigenschaft nicht mehr Threads als nötig. Das Modul verwendet bei Bedarf auch mehr Threads als in der Eigenschaft angegeben, um Parallelitätsprobleme zu vermeiden. Ein Modulthread pro Ausführungsstruktur ist ein guter Ausgangswert, der Standardwert 10 sollte jedoch nicht unterschritten werden.

Weitere Informationen zu den Einstellungen finden Sie unter Funktionen für die Datenflussleistung (http://msdn.microsoft.com/de-de/library/ms141031.aspx).

37

Spezielle Überlegungen zu BLOB-DatenWenn in einer SSIS-Pipeline mehr Daten enthalten sind, als der vordimensionierte Pipelinepuffer aufnehmen kann, werden die Daten gespoolt. Dies kann besonders bei BLOB-Daten, wie XML-, Text- oder Bilddaten, zu Leistungsproblemen führen. Befinden sich BLOB-Daten in der Pipeline, reserviert SSIS eine Hälfte des Puffers für Daten in Zeilen und die andere Hälfte für BLOB-Daten. Die BLOB-Daten, die keinen Platz in der einen Pufferhälfte finden, werden gespoolt. Falls BLOB-Daten in der Pipeline verarbeitet werden, können SSIS-Pakete auf folgende Weise optimiert werden:

1. Ändern Sie den Verweiswert von BLOBTempStoragePath und BufferTempStoragePath in ein hochleistungsfähiges Laufwerk. Gespoolte Objekte werden standardmäßig in temporäre Dateien geschrieben, die sich in dem von den Umgebungsvariablen TEMP oder TMP definierten Verzeichnis befinden. Dieses Verzeichnis befindet sich normalerweise auf dem Betriebssystemlaufwerk, das erfahrungsgemäß nicht extrem leistungsstark ist. Um sicherzustellen, dass die temporären Spooldateien auf ein Hochleistungslaufwerk geschrieben werden, ändern Sie den Wert von BLOBTempStoragePath in den Eigenschaften des SSIS-Datenflusstasks in ein Verzeichnis auf einem hochperformanten Laufwerk. Dieser Wert kann wie alle SSIS-Eigenschaften mithilfe von Ausdrücken festgelegt werden.

2. Wählen Sie für DefaultBufferSize und DefaultBufferMaxRows eine Größe, durch die das Spooling minimiert wird. Da Festplatten normalerweise die langsamste Komponente auf dem Server bilden und ihre Geschwindigkeit in der Regel nur einen Bruchteil der Prozessor- oder Arbeitsspeicherleistung ausmacht, kann die ineffiziente Verwendung von Pufferspeicher manchmal effizienter als das Spoolen von Daten sein. Um das Spoolen von BLOB-Daten im Datenfluss einzudämmen, bestimmen Sie DefaultBufferSize und DefaultBufferMaxRows mithilfe einer der folgenden Methoden.

a. Ermitteln Sie MaxBufferSize. Da der Datenfluss BLOB-Daten enthält, ist der maximal zulässige Wert von 100 MB oder 104.857.600 Bytes ein guter Ausgangswert.

b. Wenn Sie diesen Wert durch 2 dividieren, erhalten Sie in diesem Beispiel 52.428.800 Bytes, was der Hälfte des Puffers entspricht, der für BLOB-Daten reserviert ist.

c. Wählen Sie eine ungefähre Größe für die BLOB-Daten, die in diesem Datenfluss verarbeitet werden sollen. Eine gute Ausgangsbasis für die Größe ist die durchschnittliche Länge + 2 Standardabweichungen von der durchschnittlichen Länge aller in einem Puffer vorgehaltenen BLOB-Daten. Dieser Wert entspricht ca. 98 % aller BLOB-Daten. Da ein einzelner SSIS-Puffer wahrscheinlich mehr als eine Zeile aufnehmen wird, ist sichergestellt, dass fast keine Daten gespoolt werden.

38

Sofern die Quelle unter SQL Server ausgeführt wird, können Sie die Länge mit folgender Abfrage abrufen.

SELECT CAST(

AVG(DATALENGTH(ColName)) + (2 * STDEV(DATALENGTH(Demographics))) AS INT

) AS Length FROM SchemaName.TableName

Falls es aufgrund der Tabellengröße nicht möglich ist, zur Ermittlung der durchschnittlichen und standardmäßigen Abweichung das vollständige Dataset abzufragen, verwenden Sie eine Methode wie unter Zufällige Stichprobenentnahme in T-SQL (http://msdn.microsoft.com/de-de/library/aa175776(v=SQL.80).aspx) beschrieben, um die zu verwendende Länge anhand einer Stichprobe zu bestimmen.

d. Dividieren Sie den in Schritt b erhaltenen Wert durch den Wert aus Schritt c. Verwenden Sie das Ergebnis oder einen etwas geringeren DefaultBufferMaxRows-Wert für den Datenflusstask.

TIPP: DefaultBufferMaxRows und MaxBufferSize können beide mithilfe von Ausdrücken konfiguriert werden. Dies ist bei Datasets mit BLOB-Daten von Vorteil, deren statistische Länge sich häufig ändert, oder bei der Erstellung von Vorlagenpaketen, mit denen die Werte zur Laufzeit festgelegt werden. So konfigurieren Sie die Werte dynamisch.

1. Erstellen Sie eine neue Variable auf Paketebene. Nennen Sie die neue Variable DefaultMaxRowsInBuffer. Behalten Sie den Int32-Datentyp bei. Um die Eigenschaft MaxBufferSize dynamisch festzulegen, können Sie eine ähnliche Variable erstellen.

2. Ermitteln Sie den Wert für DefaultBufferMaxRows mithilfe eines „SQL ausführen“-Tasks oder Skripttasks. Speichern Sie den berechneten Wert in der unter Schritt 1 erstellten DefaultMaxRowsInBuffer-Variablen.

HINWEIS: Weitere Informationen dazu, wie Sie einen einzelnen Wert mithilfe eines „SQL ausführen“-Tasks in eine SSIS-Variable übernehmen, finden Sie unter Resultsets im Task „SQL ausführen“ (http://technet.microsoft.com/de-de/library/cc280492.aspx).

3. Klicken Sie im Eigenschaftenfeld des Datenflusstasks, für den DefaultBufferMaxRows festgelegt werden soll, auf Ausdrücke, um das Dialogfeld Eigenschaftsausdrucks-Editor zu öffnen.

4. Wählen Sie im Eigenschaftsausdrucks-Editor im Dropdownmenü Eigenschaft die Einstellung DefaultBufferMaxRows aus, und klicken Sie auf die Schaltfläche mit den Auslassungspunkten, um den Ausdrucks-Generator zu öffnen.

39

5. Ziehen Sie die in Schritt 1 erstellte Variable aus der Liste Variablen und Parameter in der oberen linken Ecke in das Feld Ausdruck, und klicken Sie auf Ausdruck auswerten. Daraufhin wird der Standardwert der Variablen im Feld Ausgewerteter Wert angezeigt.

6. Klicken Sie in den Dialogfeldern Ausdrucks-Generator und Eigenschaftsausdrucks-Editor auf OK, um die Konfiguration zu speichern. Da die Eigenschaftswerte in dieser Konfiguration zur Laufzeit festgelegt werden, verringert sich das Risiko, dass BLOB-Daten auf Datenträger gespoolt werden.

Leistungsüberwachung in einem verteilten System mithilfe neuer Funktionen in SSIS 2012Mithilfe neuer Funktionen in SQL Server 2012 kann die Leistung der auf dem SSIS-Server bereitgestellten Integration Services (SSIS)-Projekte überwacht werden. Sie können die Laufzeitleistung der Paketausführung protokollieren, Statistiken zur Paketausführung sammeln und den Paketdatenfluss überwachen.

Protokollieren von LeistungsstatistikenUm anzugeben, in welchem Umfang Informationen während der Paketausführung protokolliert werden sollen, wählen Sie unter folgenden Protokolliergraden aus. Zur Erfassung von Leistungsstatistiken wählen Sie den Protokolliergrad Leistung oder Ausführlich aus.

Protokolliergrad Wert BeschreibungKeiner 0 Die Protokollierung ist deaktiviert. Nur der Paketausführungsstatus wird

protokolliert.Standard 1 Alle Ereignisse mit Ausnahme benutzerdefinierter und Diagnoseereignisse

werden protokolliert. Dies ist der Standardwert.

40

Protokolliergrad Wert BeschreibungLeistung 2 Nur Leistungsstatistiken sowie OnError- und OnWarning-Ereignisse

werden protokolliert.Ausführlich 3 Alle Ereignisse werden protokolliert, einschließlich benutzerdefinierter

Ereignisse und Diagnoseereignisse.

Integration Services bietet zahlreiche benutzerdefinierte Ereignisse zum Schreiben von Protokolleinträgen für Pakete und viele Tasks. Sie können diese Einträge verwenden, um detaillierte Informationen zum Fortschritt sowie über die Ergebnisse und Probleme der Ausführung zu speichern, indem Sie vordefinierte Ereignisse bzw. benutzerdefinierte Meldungen für die spätere Analyse erfassen.

Weitere Informationen finden Sie unter Benutzerdefinierte Meldungen für die Protokollierung (http://msdn.microsoft.com/de-de/library/ms345174.aspx)

Sie können den Protokolliergrad angeben, indem Sie folgende Schritte für eine Paketausführungsinstanz ausführen.

Festlegen von Parametern für eine Paketausführungsinstanz mit der gespeicherten Prozedur catalog.set_execution_parameter_value (http://msdn.microsoft.com/de-de/library/ff877990.aspx)

Konfigurieren einer Paketausführungsinstanz mithilfe des Dialogfelds Paket ausführen Konfigurieren eines SQL Server-Agentauftrags für eine Paketausführung mit dem Dialogfeld

Neuer Auftragsschritt

So legen Sie den Protokolliergrad im Dialogfeld „Paket ausführen“ fest

1. Navigieren Sie im Objekt-Explorer von SQL Server Management Studio zum Paket.2. Klicken Sie mit der rechten Maustaste auf das Paket, und wählen Sie Ausführen aus.3. Klicken Sie auf die Registerkarte Erweitert.4. Wählen Sie unter Protokolliergrad den Protokolliergrad aus.

So legen Sie den Protokolliergrad im Dialogfeld „Neuer Auftragsschritt“ fest

1. Erstellen Sie einen neuen Auftrag, indem Sie im Objekt-Explorer den Knoten SQL Server-Agent erweitern, mit der rechten Maustaste auf Aufträge klicken und dann auf Neuer Auftrag klicken.

- oder -

Ändern Sie einen vorhandenen Auftrag, indem Sie den Knoten SQL Server-Agent erweitern, mit der rechten Maustaste auf einen vorhandenen Auftrag klicken und dann auf Eigenschaften klicken.

41

2. Klicken Sie im linken Bereich auf Schritte, und klicken Sie dann auf Neu, um das Dialogfeld Neuer Auftragsschritt zu öffnen.

3. Wählen Sie im Listenfeld Typ den Eintrag SQL Server Integration Services-Paket aus.4. Wählen Sie auf der Registerkarte Paket im Listenfeld Paketquelle den Eintrag SSIS-Katalog aus,

geben Sie den Server an, und geben Sie dann den Paketpfad im Feld Paket ein.5. Klicken Sie auf der Registerkarte Konfiguration auf Erweitert, und wählen Sie dann im Listenfeld

Protokolliergrad einen Protokolliergrad aus.6. Schließen Sie die Konfiguration des Auftragsschritts ab, und speichern Sie die Änderungen.

Um den Protokolliergrad mit der gespeicherten Prozedur catalog.set_execution_parameter_value anzugeben, legen Sie parameter_name auf LOGGING_LEVEL und parameter_value auf den Wert für Leistung oder Ausführlich fest. Im folgenden Beispiel wird eine Ausführungsinstanz für das Paket Package.dtsx erstellt und der Protokolliergrad auf 2 festgelegt. Das Paket ist im Projekt SSISPackages und das Projekt im Ordner Packages enthalten.

Declare @execution_id bigint

exec catalog.create_execution 'Packages', 'SSISPackages', 'Package.dtsx', NULL, 1, @execution_id outputexec catalog.set_execution_parameter_value @execution_id, 50, 'LOGGING_LEVEL', 2

Anzeigen von AusführungsstatistikenDie SSISDB-Datenbanksichten, gespeicherte Prozeduren sowie die in SQL Server Management Studio verfügbaren Standardberichte bieten eine Fülle von Informationen, die nicht nur für Paketausführungen, sondern auch für Paketausführungsinstanzen relevant sind. Eine Paketausführungsinstanz wird auch als Ausführung bezeichnet.

Unter den Standardberichten sind Integration Services-Dashboard, Alle Ausführungen und Alle Verbindungen besonders hilfreich bei der Erfassung von Paketausführungsinformationen.

Der Bericht Integration Services-Dashboard enthält folgende Informationen zu Paketen, die gerade aktiv sind oder innerhalb der letzten 24 Stunden ausgeführt wurden.

Bericht „Integration Services-Dashboard“

Berichtsabschnitt BeschreibungAusführungsinformationen Zeigt die Anzahl der Ausführungen mit unterschiedlichem Status an

(fehlerhaft, ausgeführt, erfolgreich, sonstige).Paketinformationen Zeigt die Gesamtanzahl der Pakete an, die ausgeführt wurden.Verbindungsinformationen Zeigt die Verbindungen an, die in fehlerhaften Ausführungen verwendet

wurden.Detaillierte Paketinformationen

Zeigt für jedes Paket Details zu abgeschlossenen Ausführungen an. Beispielsweise wird die Anzahl der fehlerhaften Ausführungen im Vergleich zur Gesamtzahl der Ausführungen, die Dauer der Ausführungen (in Sekunden)

42

Berichtsabschnitt Beschreibungund die durchschnittliche Dauer der Ausführungen in den vergangenen drei Monaten angezeigt. Um zusätzliche Ausführungsinformationen zu einem Paket anzuzeigen, klicken Sie auf Ausführungsleistung, Übersicht und Alle Meldungen.

Im Bericht Ausführungsleistung werden die Dauer der letzten 10 erfolgreichen Ausführungen sowie die Aktive Zeit und Gesamtzeit für die Komponenten des Paketdatenflusses grafisch dargestellt. Die aktive Zeit bezieht sich auf die gesamte Zeit, die für die Ausführung der Komponente in allen Phasen benötigt wurde, und die Gesamtzeit bezieht sich auf die insgesamt verstrichene Zeit für eine Komponente. Der Bericht zeigt diese Informationen für Datenflusskomponenten nur an, wenn der Protokolliergrad bei der letzten Paketausführung auf Leistung oder Ausführlich festgelegt war.

Der Bericht Übersicht zeigt den Status von Pakettasks an. Der Bericht Meldungen zeigt die Ereignismeldungen und Fehlermeldungen für das Paket und die Tasks an, beispielsweise Meldungen mit den Start- und Beendigungszeiten sowie die Anzahl der geschriebenen Zeilen.

Der Bericht Alle Ausführungen enthält folgende Informationen zu Ausführungen, die auf der verbundenen SQL Server-Instanz ausgeführt wurden. Es können mehrere Ausführungen desselben Pakets existieren. Im Gegensatz zum Bericht Integration Services-Dashboard können Sie den Bericht Alle Ausführungen für die Anzeige von Ausführungen konfigurieren, die in einem bestimmten Datumsbereich gestartet wurden. Die Datumsangaben können mehrere Tage, Monate oder Jahre umfassen.

43

Bericht „Alle Ausführungen“

Berichtsabschnitt BeschreibungFilter Zeigt den aktuellen, auf den Bericht

angewendeten Filter an, z. B. den Startzeitraum.Ausführungsinformationen Zeigt Startzeit, Beendigungszeit und Dauer jeder

Paketausführung an.

Sie können eine Liste der bei einer Paketausführung verwendeten Parameterwerte einsehen, beispielsweise Werte, die mit dem „Paket ausführen“-Task an ein untergeordnetes Paket übergeben wurden. Um die Parameterliste anzuzeigen, klicken Sie auf Übersicht.

Der Bericht Alle Verbindungen enthält folgende Informationen zu Verbindungen, die bei Ausführungen auf der SQL Server-Instanz fehlgeschlagen sind.

Berichtsabschnitt BeschreibungFilter Zeigt den aktuellen, auf den Bericht angewendeten Filter

an, z. B. Verbindungen mit einer bestimmten Zeichenfolge und dem Bereich Uhrzeit des letzten Fehlers.

Sie legen den Bereich Uhrzeit des letzten Fehlers fest, um lediglich Verbindungsfehler anzuzeigen, die in einem Datumsbereich aufgetreten sind. Der Bereich kann mehrere Tage, Monate oder Jahre umfassen.

Details Zeigt die Verbindungszeichenfolge, die Anzahl der Ausführungen, bei denen ein Verbindungsfehler aufgetreten ist, und das Datum des letzten Verbindungsfehlers an.

44

Neben den in SQL Server Management Studio enthaltenen Standardberichten bieten auch die SSISDB-Datenbanksichten ähnliche Informationen zu Paketausführungen. In der folgenden Tabelle sind die wichtigsten Sichten beschrieben.

SSISDB-Datenbanksicht Beschreibungcatalog.executable_statistics(http://msdn.microsoft.com/de-de/library/hh479592.aspx)

Zeigt eine Zeile für jede ausführbare Datei an, die ausgeführt wird, einschließlich der einzelnen Iterationen einer ausführbaren Datei. Eine ausführbare Datei ist ein Task oder ein Container, den Sie der Ablaufsteuerung eines Pakets hinzufügen.

Die Sicht zeigt z. B. an, wie lange die ausführbare Datei ausgeführt wurde, wann sie in die Phase vor bzw. nach der Ausführung eingetreten ist und was die Ausführung der Datei ergeben hat, ob sie beispielsweise erfolgreich oder fehlerhaft war.

Ein Beispiel für ein Ausführungsergebnis ist der Rückgabecode einer gespeicherten Prozedur, die durch einen „SQL ausführen“-Task ausgeführt wurde. Weitere Informationen finden Sie unter Parameter und Rückgabecodes im Task „SQL ausführen“.

catalog.executions(http://msdn.microsoft.com/de-de/library/ff878089.aspx)

Zeigt Informationen zu Paketausführungen an. Mit dem „Paket ausführen“-Task initiierte Pakete und das übergeordnete Paket laufen in derselben Ausführung.

Die Sicht zeigt beispielsweise Folgendes an: den Status einer Ausführung (ausgeführt, fehlerhaft, erfolgreich und sonstige), den gesamten und auf dem Server verfügbaren physischen Speicher bei Ausführungsstart sowie den gesamten und verfügbaren Seiten-Arbeitsspeicher bei Ausführungsstart.

catalog.execution_component_phases(http://msdn.microsoft.com/de-de/library/hh230981.aspx)

Zeigt die Start- und Beendigungszeiten der Datenflusskomponenten für jede Ausführungsphase an. Diese Informationen werden für mehrere Paketausführungen angezeigt.

Die Sicht zeigt diese Informationen für Datenflusskomponenten nur an, wenn der Protokolliergrad der Paketausführung auf Leistung oder Ausführlich festgelegt ist.

45

SSISDB-Datenbanksicht Beschreibungcatalog.event_messages(http://msdn.microsoft.com/de-de/library/hh479594.aspx)

Zeigt Informationen zu Meldungen an, die während bestimmter Vorgänge protokolliert wurden, beispielsweise beim Erstellen und Starten einer Paketausführung. Der Wert für diesen Vorgangstyp lautet 200.

In der Sicht werden der Meldungstext, die Paketkomponente und die Datenflusskomponente angezeigt, auf die sich die Meldung bezieht, sowie das der Meldung zugeordnete Ereignis.Welche Meldungen in der Sicht für eine Paketausführung angezeigt werden, richtet sich nach dem für die Ausführung angegebenen Protokolliergrad.

catalog.event_message_context(http://msdn.microsoft.com/de-de/library/hh479590.aspx)

Zeigt Informationen zu den Bedingungen an, die Ereignismeldungen für Ausführungen zugeordnet sind.

Die Sicht zeigt beispielsweise das Objekt (Variablenwert oder Task) sowie den Eigenschaftsnamen und -wert an, die der Ereignismeldung zugeordnet sind.

Außerdem wird hier die ID jeder Ereignismeldung angezeigt. Sie können weitere Informationen zu einer bestimmten Ereignismeldung suchen, indem Sie die catalog.event_messages-Sicht abfragen.

Mithilfe der catalog.execution_component_phases-Sicht können Sie die in allen Phasen aufgewendete Ausführungszeit (aktive Zeit) und die insgesamt verstrichene Zeit (Gesamtzeit) für Paketkomponenten berechnen. Auf diese Weise werden Komponenten identifiziert, die langsamer als erwartet ausgeführt werden.

Diese Sicht enthält Daten, wenn der Protokolliergrad der Paketausführung Leistung oder Ausführlich lautet. Weitere Informationen finden Sie unter Protokollieren von Leistungsstatistiken in diesem Artikel.

Im folgenden Beispiel werden die aktive Zeit und die Gesamtzeit für die in der Ausführung enthaltenen Komponenten mit der ID 33 berechnet. Die Berechnung verwendet die sum-Funktion und DATEDIFF-Funktion.

Declare @execution_id bigint

Set @execution_id = 33

select package_name, task_name, subcomponent_name, execution_path,

sum (DATEDIFF(ms, start_time, end_time)) as active_time,

DATEDIFF(ms,min(start_time), max(end_time)) as total_time

from catalog.execution_component_phases

46

where execution_id = @execution_id

group by package_name, task_name, subcomponent_name, execution_path

order by active_time desc

Schließlich können mit der dm_execution_performance_counters-Funktion Leistungsindikatorstatistiken abgerufen werden, z. B. die Anzahl der für eine laufende Ausführung verwendeten Puffer sowie gelesenen und geschriebenen Zeilen.

Im folgenden Beispiel gibt die Funktion Statistikdaten für eine aktive Ausführung mit der ID 34 zurück.

select * from [catalog].[dm_execution_performance_counters] (34)

Im folgenden Beispiel gibt die Funktion Statistikdaten für alle aktiven Ausführungen zurück.

select * from [catalog].[dm_execution_performance_counters] (NULL)

Überwachen des DatenflussesDank den in Integration Services integrierten Funktionen und Tools können Probleme mit einem Datenfluss in einem Paket während der Ausführung behandelt werden.

47

Abzweigen des Datenflusses während der AusführungIn der Integration Services-Version von SQL Server 2012 wurde eine neue Funktion eingeführt, mit der dem Datenflusspfad eines Pakets zur Laufzeit eine Datenabzweigung hinzugefügt und deren Ausgabe an eine externe Datei weitergeleitet werden kann. Zur Verwendung dieser Funktion muss das SSIS-Projekt unter Verwendung des Projektbereitstellungsmodells auf einem SSIS-Server bereitgestellt werden. Um Datenabzweigungen hinzuzufügen, müssen nach der Paketbereitstellung auf dem Server T-SQL-Skripts für die SSISDB-Datenbank ausgeführt werden, bevor das Paket ausgeführt wird. Hier ein Beispielszenario:

1. Erstellen Sie mit der gespeicherten Prozedur catalog.create_execution (http://msdn.microsoft.com/de-de/library/ff878034) eine Ausführungsinstanz des Pakets.

2. Fügen Sie mit der gespeicherten Prozedur catalog.add_data_tap (http://msdn.microsoft.com/de-de/library/hh230989) oder der gespeicherten Prozedur catalog.add_data_tap_by_guid (http://msdn.microsoft.com/de-de/library/hh230991) eine Datenabzweigung hinzu.

3. Starten Sie die Ausführungsinstanz des Pakets mit catalog.start_execution (http://msdn.microsoft.com/de-de/library/ff878160).

Hier ein SQL-Beispielskript, das die Schritte aus dem oben beschriebenen Szenario ausführt:

Declare @execid bigintEXEC [SSISDB].[catalog].[create_execution] @folder_name=N'ETL Folder', @project_name=N'ETL Project', @package_name=N'Package.dtsx', @execution_id=@execid OUTPUTEXEC [SSISDB].[catalog].add_data_tap @execution_id = @execid, @task_package_path = '\Package\Data Flow Task', @dataflow_path_id_string = 'Paths[Flat File Source.Flat File Source Output]', @data_filename = 'output.txt'EXEC [SSISDB].[catalog].[start_execution] @execid

Die in der gespeicherten Prozedur create_execution verwendeten Parameter für den Ordner-, Projekt- und Paketnamen entsprechen dem Ordner-, Projekt- und Paketnamen im Integration Services-Katalog. Sie finden den Ordner-, Projekt- und Paketnamen, der im create_execution-Aufruf verwendet werden muss, in SQL Server Management Studio (vgl. folgende Abbildung). Wenn das SSIS-Projekt dort nicht angezeigt wird, haben Sie es möglicherweise noch nicht auf dem SSIS-Server bereitgestellt. Klicken Sie in Visual Studio mit der rechten Maustaste auf das SSIS-Projekt, klicken Sie auf Bereitstellen, um das Projekt auf dem erwarteten SSIS-Server bereitzustellen.

48

Anstatt die SQL-Anweisungen manuell einzugeben, können Sie das Paketausführungsskript mit folgenden Schritten generieren:

1. Klicken Sie mit der rechten Maustaste auf Package.dtsx, und klicken Sie dann auf Ausführen. 2. Klicken Sie auf die Symbolleistenschaltfläche Skript, um das Skript zu generieren.3. Fügen Sie jetzt die Anweisung add_data_tap vor dem Aufruf start_execution ein.

Der Parameter task_package_path der gespeicherten Prozedur add_data_tap entspricht der Eigenschaft PackagePath des Datenflusstasks in Visual Studio. Klicken Sie in Visual Studio mit der rechten Maustaste auf den Datenflusstask, und klicken Sie auf Eigenschaften, um das Eigenschaftenfenster zu öffnen. Notieren Sie sich den Wert der Eigenschaft PackagePath, um ihn als Wert für den Parameter task_package_path des Aufrufs der gespeicherten Prozedur add_data_tap zu verwenden.

49

Der Parameter dataflow_path_id_string der gespeicherten Prozedur add_data_tap entspricht der Eigenschaft IdentificationString des Datenflusspfads, dem Sie eine Datenabzweigung hinzufügen möchten. Um dataflow_path_id_string abzurufen, klicken Sie auf den Datenflusspfad und notieren sich den Wert der Eigenschaft IdentificationString im Eigenschaftenfenster.

Wenn Sie das Skript ausführen, wird die Ausgabedatei in <Programme>\Microsoft SQL Server\110\DTS\DataDumps gespeichert. Wenn bereits eine Datei mit demselben Namen vorhanden ist, wird eine neue Datei mit einem Suffix (beispielsweise output[1].txt) erstellt.

Wie zuvor erwähnt, können Sie anstelle der gespeicherten Prozedur add_data_tap auch die gespeicherte Prozedur catalog.add_data_tap_by_guid (http://msdn.microsoft.com/de-de/library/hh230991) verwenden. Diese gespeicherte Prozedur verwendet die ID des Datenflusstasks und nicht task_package_path als Parameter. Sie finden die ID des Datenflusstasks im Eigenschaftenfenster von Visual Studio.

Entfernen einer DatenabzweigungSie können eine Datenabzweigung vor Beginn der Ausführung mit der gespeicherten Prozedur catalog.remove_add_data_tap entfernen. Diese gespeicherte Prozedur verwendet die ID der Datenabzweigung als Parameter, die von der gespeicherten Prozedur add_data_tap ausgegeben wird.

DECLARE @tap_id bigint

EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execid, @task_package_path = '\Package\Data Flow Task', @dataflow_path_id_string = 'Paths[Flat File Source.Flat File Source Output]', @data_filename = 'output.txt' @data_tap_id=@tap_id OUTPUT

EXEC [SSISDB].[catalog].remove_data_tap @tap_id

50

Auflisten aller DatenabzweigungenIn der Sicht catalog.execution_data_taps können Sie alle Datenabzweigungen auflisten. Im folgenden Beispiel werden Datenabzweigungen für eine bestimmte Ausführungsinstanz extrahiert (ID: 54).

select * from [SSISDB].[catalog].execution_data_taps where execution_id=@execid

LeistungsaspekteDurch die ausführliche Protokollierung und zusätzliche Verwendung von Datenabzweigungen erhöhen sich die von der Datenintegrationslösung ausgeführten E/A-Vorgänge. Daher ist es ratsam, Datenabzweigungen nur zur Fehlerbehebung einzusetzen.

Überwachen des Datenflusses während der AusführungDer Datenfluss von Paketen kann mit der SSISDB-Datenbanksicht catalog.execution_data_statistics (http://msdn.microsoft.com/de-de/library/hh230986.aspx) analysiert werden. In dieser Sicht wird immer dann eine Zeile angezeigt, wenn eine Datenflusskomponente Daten an eine Downstreamkomponente sendet. Die Informationen geben detaillierten Aufschluss über die an die einzelnen Komponenten gesendeten Zeilen.

Hinweis: Damit Informationen mit der Sicht catalog.execution_data_statistics erfasst werden, muss der Protokolliergrad auf Ausführlich festgelegt werden.

Im folgenden Beispiel wird die Anzahl der Zeilen angezeigt, die zwischen Komponenten eines Pakets gesendet wurden. Die execution_id entspricht der ID einer Ausführungsinstanz, die Sie dem Rückgabewert der gespeicherten Prozedur create_execution oder der Sicht catalog.executions entnehmen können.

use SSISDBselect package_name, task_name, source_component_name, destination_component_name, rows_sentfrom catalog.execution_data_statisticswhere execution_id = 132order by source_component_name, destination_component_name

51

Im folgenden Beispiel wird die Anzahl der Zeilen pro Millisekunde berechnet, die von jeder Komponente für eine bestimmte Ausführung gesendet wurden. Die berechneten Werte lauten wie folgt:

total_rows: die Summe aller von der Komponente gesendeten Zeilen wall_clock_time_ms: die insgesamt für jede Komponente aufgewendete Ausführungszeit in

Millisekunden num_rows_per_millisecond: die Anzahl der von jeder Komponente gesendeten Zeilen pro

Millisekunde

Die HAVING-Klausel verhindert in Berechnungen Fehler aufgrund von Divisionen durch 0 (null).

use SSISDBselect source_component_name, destination_component_name, sum(rows_sent) as total_rows, DATEDIFF(ms,min(created_time),max(created_time)) as wall_clock_time_ms, ((0.0+sum(rows_sent)) / (datediff(ms,min(created_time),max(created_time)))) as [num_rows_per_millisecond]from [catalog].[execution_data_statistics]where execution_id = 132group by source_component_name, destination_component_namehaving (datediff(ms,min(created_time),max(created_time))) > 0order by source_component_name desc

SchlussfolgerungSQL Server Integration Services (SSIS) kann als effizientes Tool zum Verschieben von Daten in und aus Windows Azure SQL-Datenbanken, als Teil der ETL-Gesamtlösung (Extrahieren, Transformieren, Laden) sowie im Rahmen der Datenverschiebung eingesetzt werden. SSIS ist ein wirkungsvolles Instrument zum Übertragen von Daten zwischen Quell- und Zielsystemen in der Cloud sowie in einem Hybridszenario zwischen der Cloud und einem lokalen Standort. In diesem Whitepaper wurden folgende Themen behandelt: Best Practices für die Verwendung von SSIS für cloudbasierte Quellen und Ziele, Planung von SSIS-Projekten, die vollständig in der Cloud ausgeführt werden oder hybride Datenverschiebungen umfassen, und eine exemplarische Vorgehensweise zur Leistungsoptimierung bei hybriden Datenverschiebungen mittels horizontaler Skalierung.

52

Weitere Informationen:

http://www.microsoft.com/sqlserver/: SQL Server-Website

http://technet.microsoft.com/de-de/sqlserver/: SQL Server TechCenter

http://msdn.microsoft.com/de-de/sqlserver/: SQL Server DevCenter

War dieses Dokument hilfreich? Geben Sie uns Ihr Feedback. Bewerten Sie dieses Dokument auf einer Skala von 1 (schlecht) bis 5 (ausgezeichnet), und begründen Sie Ihre Bewertung. Beispiel:

Bewerten Sie es hoch aufgrund guter Beispiele, ausgezeichneter Screenshots, klarer Formulierungen oder aus einem anderen Grund?

Bewerten Sie es niedrig aufgrund schlechter Beispiele, ungenauer Screenshots oder unklarer Formulierungen?

Dieses Feedback hilft uns, die Qualität von veröffentlichten Whitepapers zu verbessern.

Feedback senden

53