SQL Server 2012 Integration Services
Werbung
Robert Panther | CGI SQL Server 2012 Integration Services Lessons Learned Vorstellung und Überblick 2 Vorstellung Name Robert Panther Rolle Senior Consultant, CGI Fachliche Schwerpunkte • Datenbanken: Design, Implementierung & Administration (insbesondere Performance Optimierung und ETL mit SSIS) • Mobile Anwendungen: Design & Implementierung • Webanwendungen: Implementierung & Administration Technologien • Microsoft SQL Server • Windows Mobile • .NET (VB .NET & C#), ASP, ASP.NET Methoden Datenbankdesign, Objektorientierte Programmierung, Strukturierte Programmierung, Schulungskonzeption & Durchführung, Projektleitung Zertifikate Informatik Diplom (FH) MCTS SQL Server 2005 Implementation & Maintenance MCTS SQL Server 2005 Business Intelligence MCTS SQL Server 2008 Implementation & Maintenance MCITP SQL Server 2008 Developer Spezielles Autor von mehreren Fachbüchern und Fachartikeln Leitung der SQL Server Expert Group bei Logica / CGI regelmäßiger Speaker auf Fachkonferenzen aktives PASS-Mitglied 3 Vorstellung Und wer sind Sie? • Was machen Sie mit SQL Server? – Entwicklung – Administration – Business Intelligence • Nutzen Sie SSIS? • Wenn ja, nutzen Sie SSIS 2012? • Wenn ja, mit welcher Visual Studio IDE? 4 Vorstellung: Inhalt Was Sie erwartet … • Ein kurzer Überblick über die neuen Features von SSIS 2012 • Ein Rückblick, welche Features sich bewährt haben • Tipps und Tricks zu SSIS (nicht nur für SQL 2012) Was Sie nicht erwarten sollten … • Eine Einführung in die SQL Server Integration Services • Eine 100% vollständige Abhandlung aller Neuerungen 5 Agenda 1. Vorstellung und Agenda 2. Neuerungen … … und wie sie sich bewährt haben 3. Deployment und Konfiguration 4. SSIS-Performance 5. Bekannte Probleme und Workarounds 6. Best Practices 7. Fazit und weitere Infos 6 Neuerungen … … und wie sie sich bewährt haben 7 Neuerungen Allgemeine Neuerungen • Oberfläche: aus BIDS wird SSDT BI • seit VS 2010 Zoom und frei verschiebbare Fenster • VS 2012 IDE über Nachinstallation (SQL Server Data Tools BI für VS 2012) • Scope von Variablen nachträglich anpassen • besser lesbares XML • endlich ein Undo im Designer! • copy & paste verbessert • Autosave & Recovery • kein DTS-Support mehr 8 Neuerungen SSIS-Server • verwaltet komplette Projekte, nicht nur einzelne Pakete • besitzt eigene Katalog-DB (SSISDB) • Pakete über SSIS-Dienst remote ausführbar • Verwaltung und Ausführung über SPs • eigene Performance-Counter • Integration Services-Dashboard 9 Neuerungen 10 Neuerungen SSIS-Projekte • Connection Manager wahlweise auf Paket- oder Projektebene (keine Shared DataSource mehr) • Konfiguration mit Hilfe von Projektparametern • Deployment in Form von .ispac-Dateien Ablaufsteuerung • Execute Package wahlweise aus Projektreferenz (ohne expliziten Connection Manager) • Expression Task (Ausdruckstask) (z.B. zum Vorbelegen von Variablen) 11 Neuerungen Datenfluss • Source/Destination Assistent • Work Offline • mapping changes reparieren (z.B. geänderte Spaltennamen) • Pivot über Spaltenbezeichnungen (nicht mehr LineageIDs) • Gruppieren im Datenfluss (mit collapse & expand) • Merge & Merge Join (gedrosselt wenn ein Input langsam, spart Ressourcen) • vereinfachter Datenviewer (keine graphische Darstellung) • spezielle Data Quality-Transformationen (DQS-Bereinigung) 12 Neuerungen SQL Server Data Tools BI für Visual Studio 2012 • bis vor Kurzem nutze SQL Server die Visual Studio 2010 IDE • inzwischen (seit Juni 2013) Update für VS 2012 IDE verfügbar • Bei Installation beachten! • als Administrator ausführen • neue Installation auswählen SQL Server 2012 SQL Server SSDT BI Data SSDTTools Dev. Visual Studio IDE 13 Neuerungen SQL Server Data Tools BI für Visual Studio 2012 • unterschiedliche Versionsnummern werden in Paket, Projekt und Solution abgelegt, z.B. im Projekt: • <ProductVersion>11.0.2100.60</ProductVersion> • <ProductVersion>11.0.3369.0</ProductVersion> • SSIS-Projekte (& Solutions) bleiben aber kompatibel 14 Deployment und Konfiguration 15 Deployment & Konfig Deployment bisher (vor SSIS 2012) • Deployment von einzelnen Paketdateien • Ablage in … • Dateisystem • Package Store • SQL Server (MSDB) 16 Deployment & Konfig Konfiguration bisher (vor SSIS 2012) • Konfiguration über Package Configuration • XML • Umgebungsvariablen • Registry • Parent Package • SQL Server • Verbindungsmanager bei Aufruf anpassen • über SQL Agent Job Step • über DTEXECUI • paketinterne Konfigurierbarkeit über Variablen und Expressions 17 Deployment & Konfig Deployment mit SSIS 2012 • Deployment von ganzen Projekten (.ISPAC-Dateien) • SSIS-Katalog muss vorher angelegt werden • einfacher im Handling • .ISPAC-Dateien enthalten • Pakete • Parameter • Verbindungsmanager 18 Deployment & Konfig Konfiguration mit SSIS 2012 • Konfiguration über Parameter • Verbindungsmanager • können nach wie vor bei Aufruf über DTEXECUI oder SQL Agent Job Step angepasst werden • Parameter und Verbindungsmanager können im Katalog explizit angepasst (konfiguriert) werden • über Umgebungen können Parameter und Verbindungsmanager vorbelegt werden • paketinterne Konfigurierbarkeit über Variablen und Expressions 19 Deployment & Konfig 20 Deployment & Konfig Package Configuration mit SSIS 2012 • nach wie vor nutzbar, wenn auf Package Deployment Model umgeschaltet wird • neue Funktionen des Project Deployment Model dann nicht mehr nutzbar 21 SSIS-Performance 22 SSIS-Performance Interne Arbeitsweise von SSIS-Paketen • Grundprinzipien bleiben dieselben • Daten werden normalerweise blockweise verarbeitet • Transformationen erfolgen im Hauptspeicher • Execution Trees beachten Non-Blocking Transformations – synchron (Daten laufen “direkt” durch) (Beispiele: Convert, Derived Column) Blocking Transformations – asynchron, wartet bis alle Daten gelesen sind (Beispiel: Sort, Aggregate) Partially Blocking Transformations – asynchron, wartet aber nicht bis Daten komplett sind (Beispiele: Merge, Merge Join) 23 SSIS-Performance Optimierungsansätze • Blocking Transformations vermeiden • Sortierung in Quell-Abfrage verlagern • wenn möglich, JOIN in Quellabfrage nicht als Transformation • Merge Join teilweise auch durch Lookup ersetzbar • Neu mit SQL 2012: automatische Drosselung bei Merge & Merge Join, wenn ein Input langsam 24 SSIS-Performance Engpässe lokalisieren 1. Paket im Debug-Modus analysieren 2. Kopie des Pakets erstellen 3. Zeiten für Quelle, Transformation & Ziel ermitteln • Laufzeit komplett • Laufzeit ohne Ziel • Laufzeit ohne Transformation & Ziel 4. Detailanalyse • Struktur (Execution Trees) • Quelle, Transformationen & Ziel optimieren 25 SSIS-Performance Datenquelle beschleunigen • Abfrage statt Tabelle nutzen • möglichst “schmal”: nur benötigte Spalten lesen • Daten möglichst stark vorselektieren: nur benötigte Zeilen lesen • Ausführungsplan der Abfrage prüfen • Verwendung von Indizes • Locking berücksichtigen (WITH (NOLOCK)) 26 SSIS-Performance Lookups prüfen • wie sieht die Quelle für den Lookup aus? • Tabelle oder Abfrage? • Abfrage optimieren (wie bei Datenquelle) • welcher Caching Modus wird verwendet? • kein Cache – wenn Werte nur einmal benötigt werden • Vollcache – wenn Werte mehrfach benötigt werden und Menge überschaubar • Teilcache – wenn Werte evtl. nochmal benötigt werden • wenn dieselben Daten für mehrere Lookups benötigt werden • Daten per Cachetransformation einmal in Cache laden • bei Lookups Cacheverbindungs-Manager verwenden 27 SSIS-Performance Datenziel beschleunigen • schnelles Laden (fast load) nutzen Nachteil: • Ziel im Datenfluss lässt bei einer fehlerhaften Zeile ganzen Block von Zeilen in die Fehlerausgabe laufen. Abhilfe: 1. OLE DB Ziel im Modus “schnelles Laden” mit Fehlerausgabe versehen. 2. Fehlerhafte Zeilen noch einmal im “langsamen” Modus schreiben. 3. Weitere Fehlerausgabe mit Behandlung für die final fehlerhaften Zeilen. 28 SSIS-Performance 29 Bekannte Probleme und Workarounds dazu 30 Probleme & Workarounds Problem: Bestimmte Datenquellen (z.B. Excel, Access) sind nicht nutzbar. Lösung: • Projekt auf 32Bit-Runtime umstellen. 31 Probleme & Workarounds Problem: Datenfluss schreibt in zwei Zieltabellen, von denen die eine auf die andere verweist. Lösungsvarianten: a) Für Primär- und Fremdschlüssel werden vorher bekannte IDs verwendet (evtl. vorher maximale ID ermitteln). b) Für Primär- und Fremdschlüssel werden im Datenfluss definierte Werte (z.B. in der Quellabfrage erzeugte Guids) verwendet. 32 Probleme & Workarounds Problem: Datenfluss schreibt in zwei Zieltabellen, von denen die eine auf die andere verweist. Die Tabellen verwenden IDs mit Identitätsspezifikation. Lösungsvarianten: a) In erste Tabelle wird per Stored Procedure geschrieben und die ID der neuen Zeile per Output-Parameter in Datenfluss zurückgegeben. b) Foreign Key-Spalte wird mit temporären Werten befüllt, die nach dem Datenfluss per Nachbereitungsskript durch die korrekte ID ersetzt werden. 33 Best Practices 34 Best Practices Allgemeine Konventionen • Pakete sollten stets wiederholt startbar sein • zu Beginn “aufräumen” • evtl. Transaktionen nutzen • Fehlerausgaben nutzen • Im einfachsten Fall in Multicast Container • Im Idealfall mit Fehlerprotokollierung • Protokollierung in zusätzliche Tabellen • z.B. für komplexe Mappings • Konfigurierbarkeit über Parameter, Variablen und Expressions 35 Best Practices Konventionen zur besseren Lesbarkeit • Ablaufsteuerung und Datenfluss von oben nach unten • Notwendigkeit zum horizontalen Scrollen vermeiden • Fehlerbehandlung im Datenfluss zur Seite führen 36 Best Practices Struktur von SSIS-Projekten • Ein Paket pro Datenquelle oder Datenziel • Ein Master-Paket, das Unterpakete aufruft • Konfiguration der Variablen • Variableninhalte über Parameter an Unterpakete übergeben • Allgemeine Vor- und Nachbereitung • z.B. Fremdschlüssel de- & re-aktivieren • Sequenzcontainer zur Strukturierung 37 Best Practices Struktur von SSIS-Paketen • Vorbereitungsschritte • (nicht gruppierte) Indizes deaktivieren • Zieltabellen leeren • Datenfluss • Nachbereitungsschritte • Indizes neu erstellen 38 Best Practices Namenskonventionen • Name des Pakets entspricht Quelle oder Ziel (einheitlich!) • Kurze Präfixe für Tasks und Container verwenden, z.B. … • SCR für Script Tasks • SQL für SQL Tasks • EP für Execute Package • LKP für Suche (von Lookup) • ODB für OLE DB Quellen & Ziele 39 Fazit und weitere Infos 40 Fazit Vieles ist besser geworden Projektorientiertes Arbeiten einfachere Konfiguration & Deployment zahlreiche Detailverbesserungen in Entwickleroberfläche etc. Einiges (weniges) leider auch schlechter kein DTS-Support Deployment von einzelnen Paketen problematisch SSDT 2012 Designer etwas hakelig (WPF) Bug bei Sortierung von Dateien im Projekt 41 Fazit Was fehlt noch? • Unterstützung bei der Ausrichtung von Objekten im Designer • Auskommentieren von Schritten im Datenfluss (z.B. Datenziel) • Strukturierung von SSIS-Paketen in der Solution (Unterordner, Sortierung etc.) • komfortablere SQL-Editoren • Zoom in Fehleransicht • Datenziel mit OUTPUT (inserted) 42 Links & Literatur Links zum Thema SSIS: • SSIS 2012 Online-Dokumentation: http://msdn.microsoft.com/de-de/library/ms141026.aspx • Ressourcen für SSIS-Entwickler und IT-Spezialisten: http://msdn.microsoft.com/de-de/sqlserver/cc511477.aspx • SQL Server Data Tools – Business Intelligence für VS 2012: http://www.microsoft.com/de-de/download/details.aspx?id=36843 Links des Referenten: • SQL Server Blog: http://pantheronsql.wordpress.com • Logica / CGI: http://www.logica.de / http://www.cgi.com 43 Links & Literatur Literatur: • Microsoft SQL Server Integration Services: Erstellen von Datenintegrations- und Datentransformationslösungen auf Unternehmensebene Bernd Jungbluth Microsoft Press, Februar 2011 ISBN: 978-3866456549 (aktualisierte Auflage für SQL 2012 & 2014 für April 2014 geplant) • Implementing a Data Warehouse with Microsoft SQL Server 2012 Training Kit (Exam 70-463) Grega Jerkic, Matija Lah, Dejan Sarka Microsoft Press, Januar 2013 ISBN: 978-0735666092 • SQL Server Performance Ratgeber Robert Panther entwickler.press, Februar 2010 ISBN: 978-3868020304 44 Ein kleines Anliegen in eigener Sache … Zur Verstärkung unseres SQL Server Teams am Standort Frankfurt/Sulzbach suchen wir … Datenbankarchitekten Microsoft SQL Server (m/w) Sie werden für die Beratung, Konzeption, Projektleitung, aber auch für die Realisierung von anspruchsvollen Lösungen verantwortlich sein, deren technologischer Schwerpunkt im Umfeld Microsoft SQL Server liegt. Sie helfen unseren Klienten, unternehmenskritische Anwendungen hochverfügbar, sicher und kostengünstig zu gestalten. Dabei setzen Sie moderne Methoden und Techniken ein und integrieren sie in die Unternehmenslandschaft. Auf diese Weise tragen Sie dazu bei, dass zukunftsfähige Plattformen entstehen, auf denen Business und E-Business sicher und stabil betrieben werden können. Interesse? Dann sprechen Sie mich an, oder schicken Sie mir eine Mail an [email protected] 45 Vielen Dank für Ihre Aufmerksamkeit! Robert Panther [email protected]
