SSISDB Catalog Defaults Best Practices

Einführung

Die SSISDB-Datenbank (auch bekannt als der Integration Services-Katalog) wurde in SQL Server 2012 eingeführt, um die MSDB-Datenbank zu entrümpeln und eine eigene Protokollierungs- und Berichtsinfrastruktur bereitzustellen. Kurz gesagt, SSISDB ist ein SSIS-Framework, das die SQL Server Integration Services robuster und unternehmensfreundlicher macht, indem es Folgendes bietet:

• Datenbank-Backup
• Datenbank-Verschlüsselung
• Unterstützung für Umgebungen
• Unterstützung für Projekt- und Paketparameter
• Paket Versionierung
• Kunden-SSRS-Leistungsberichte in SSMS
• Einsatz direkt aus SSDT

Während das SSISDB-Framework SSIS viel leistungsfähiger machte, kam es mit einigen Microsoft-Annahmen (a.k.a. defaults). Diese Vorgaben sind dazu da, um Ihnen zu helfen, auf den Füßen zu stehen, aber sie sind möglicherweise nicht optimal, wenn Sie anfangen zu laufen, und sie sind bei weitem nicht perfekt, wenn Sie sprinten.

Dieser Artikel befasst sich mit den Vorgaben, warum diese Vorgaben möglicherweise nicht optimal sind und wie Sie diese Vorgaben ändern können.

Problem

SSISDB Catalog wird mit einem eingebauten Prozess zum Bereinigen von Vorgängen und der Projektversionierung geliefert. Dieser Bereinigungsprozess stützt sich auf SSISDB-Standardwerte, die Ihr SSIS unbrauchbar machen können, wenn sie nicht geändert werden.

Fast jedes neue SSIS-Projekt beginnt mit der Erstellung und Ausführung einiger weniger Pakete, während noch reichlich freier Speicherplatz vorhanden ist. Nach drei bis sechs Monaten haben Sie eine Reihe von SSIS-Projekten, Dutzende oder Hunderte von Paketen und möglicherweise auch Bedarf an frischen Daten für Berichte und Analysen (sprich: Pakete laufen rund um die Uhr). Diese SSIS-Pakete könnten Versionsdaten (weniger problematisch) und Protokollierungsdaten (eher problematisch) weit über die Microsoft-Bereinigungsvorgaben hinaus anhäufen, was dazu führen könnte, dass die SSISDB-Datenbankgröße unerwartet ansteigt (d. h. den gesamten verfügbaren Speicherplatz auffrisst).

Die einzige Vorgabe, die für die Erfassung von SSIS-Laufzeitdaten verantwortlich ist, ist die serverweite Standardprotokollierungsebene = Basic. Diese Einstellung ist letztendlich dafür verantwortlich, die Größe der SSISDB-Datenbank zu verwalten und letztendlich den Festplattenplatz zu belegen. Abgesehen davon bietet der SSIS-Katalog (Abb. 1) weitere Optionen zur Steuerung der SSISDB-Datenbankgröße/des Festplattenspeichers:

1. Serverweites Standard-Logging-Level
2. Standardeinstellungen für die Bereinigung:
   1. Periodisches Bereinigen von Protokollen = Wahr
   2. Periodisches Entfernen alter Versionen = Wahr
   3. Aufbewahrungszeitraum (Tage) = 365
   4. Maximale Anzahl von Versionen pro Projekt = 10

Die Änderung der serverweiten Standardprotokollierungsstufe von „Basic“ auf „None“, um Speicherplatz zu gewinnen, ist höchst unpraktisch und macht die Fehlersuche zu einem Albtraum, Änderungen an anderen SSISDB-Katalogeinstellungen wie Maximale Anzahl von Versionen pro Projekt und Aufbewahrungszeitraum (Tage) können minimale Auswirkungen auf die Fehlerbehebung haben und gleichzeitig die SSISDB-Datenbank/den Speicherplatz unter Kontrolle halten.

Alle diese Standardeinstellungen steuern den Bereinigungsprozess (auch bekannt als SSIS Server Maintenance Job). Während die beiden Einstellungen Logs periodisch bereinigen = True und Alte Versionen periodisch entfernen = True praktisch immer perfekt sind, könnten die Einstellungen für Maximale Anzahl von Versionen pro Projekt = 10 und Aufbewahrungszeitraum (Tage) = 365 zu optimistisch sein. Und hier ist der Grund dafür.

Der Hauptgrund, warum die beiden Standardeinstellungen „Protokolle regelmäßig bereinigen = Wahr“ und „Alte Versionen regelmäßig entfernen = Wahr“ perfekt sind, ist, dass SSISDB ohne diese Standardeinstellungen (oder effektiv, wenn beide auf „Falsch“ geändert werden) unendlich wachsen wird, da es nur Protokoll- und Versionierungsdaten ansammelt, ohne Daten zu entfernen, und der Speicherplatz ausgeht.

Der Hauptgrund, warum sowohl Maximale Anzahl von Versionen pro Projekt = 10 als auch Beibehaltung der Protokolldaten für 365 Tage zu optimistisch sein könnten, ist die Entscheidung von Microsoft, die Daten auf der Grundlage eines Zeitraums und nicht der Größe aufzubewahren. Das bedeutet, dass die feste Zeitspanne nicht berücksichtigt, wie viele Daten sich ansammeln werden. Während die maximale Anzahl von Versionen pro Projekt zu einem Problem werden könnte, wenn man Tausende von Paketen hat, wird die Aufbewahrungszeit (Tage) zu einem großen Problem, wenn man Pakete hat, die alle paar Minuten laufen. Ich glaube nicht, dass Microsoft jemals die Ansammlung von Protokolldaten für 1440 Läufe pro Tag (jede Minute) erwartet hat, die eine Bereinigung häufiger als alle 365 Tage erfordern, da sonst der Speicherplatz knapp wird.

Lösung

Die Lösung würde darin bestehen, die Standardwerte auf kleinere Zahlen zu ändern, so dass SSISDB weniger Informationen speichert. Basierend auf meiner persönlichen Erfahrung, dass Pakete jede Minute ausgeführt werden (E-Commerce-Plattformanforderung) und ich bis zu 10 Projekte habe, würde eine Verringerung des Aufbewahrungszeitraums (Tage) von 365 (Standard) auf 7 Tage und der maximalen Anzahl von Versionen pro Projekt von 10 (Standard) auf 5 ausreichen, um SSISDB/Speicherplatz zu kontrollieren und gleichzeitig genügend Informationen für Debugging- und Fehlerbehebungszwecke zu behalten.

Sie haben zwei Möglichkeiten, diese Änderung vorzunehmen:

• Ändern Sie die Standardeinstellungen mit SQL Server Management Studio (auch bekannt als „SSMS“), indem Sie die Integrationsdienste erweitern, mit der rechten Maustaste auf SSISDB klicken und dann auf den Menüpunkt Eigenschaften klicken (Abb. 2 – SSMS).

• Ändern Sie die Standardwerte mithilfe der folgenden T-SQL, um die Datensätze in der Tabelle ssisdb.catalog.catalog_properties direkt zu aktualisieren.

Wenn die Änderung abgeschlossen ist, sollten Sie in der Lage sein, die Änderungen zu bestätigen, indem Sie die folgende T-SQL-Abfrage ausführen (Abb. 3 – SSISDB-Katalog)

Während das Ändern der Standardeinstellungen das Problem in Zukunft beheben könnte, kann es Ihr bestehendes SSISDB-Plattenplatzproblem nicht schnell genug lösen. Sie können den Bereinigungsprozess beschleunigen, indem Sie den Code modifizieren und @delete_batch_size von 1000 auf etwa 10000 ändern, indem Sie die gespeicherte Prozedur ssisdb.internal.cleanup_server_retention_window ändern (Abb. 4 – Retention Procedure).

Zusammenfassung

Dieser Artikel hat ein potenzielles Problem mit den SSISDB-Katalogvorgaben aufgezeigt, das dazu führen kann, dass der Speicherplatz knapp wird, und eine Lösung angeboten, die SSISDB zwingt, weniger Informationen zu speichern.