SSISDB Catalog Defaults Best Practices

Wprowadzenie

Baza danych SSISDB (a.k.a. the Integration Services catalog) została wprowadzona jeszcze w SQL Server 2012 w celu usunięcia bałaganu w bazie danych MSDB i zapewnienia wewnętrznej infrastruktury logowania i raportowania. W skrócie, SSISDB jest frameworkiem SSIS czyniącym SQL Server Integration Services bardziej solidnym i przyjaznym dla przedsiębiorstw poprzez zapewnienie:

• Kopia zapasowa bazy danych
• Szyfrowanie bazy danych
• Obsługa środowisk
• Obsługa parametrów projektów i pakietów
• Pakiety. wersjonowanie
• Raporty wydajności SSRS klienta wbudowane w SSMS
• Wdrażanie bezpośrednio z SSDT

Choć framework SSISDB sprawił, że SSIS ma znacznie większe możliwości, przyszedł z pewnymi założeniami Microsoftu (a.k.a. defaults). Te domyślne założenia mają pomóc Ci stanąć na nogi, ale mogą nie być optymalne, gdy zaczynasz biegać i są dalekie od ideału, gdy biegniesz sprintem.

Ten artykuł omawia domyślne założenia, dlaczego te domyślne założenia mogą nie być optymalne i jak zmienić te domyślne założenia.

Problem

SSISDB Catalog dostarcza z wbudowanym procesem czyszczenia operacji i wersjonowania projektu. Ten proces czyszczenia opiera się na domyślnych ustawieniach SSISDB, które mogą uniemożliwić działanie SSIS, jeśli nie zostaną zmienione.

Prawie każdy nowy projekt SSIS zaczyna się od zbudowania i uruchomienia tylko kilku pakietów, gdy jest dużo wolnego miejsca na dysku. Po upływie 3-6 miesięcy mamy już kilka projektów SSIS, dziesiątki lub setki pakietów, a także zapotrzebowanie na świeże dane do raportowania i analiz (czytaj: ciągłe uruchamianie pakietów przez całą dobę). Te pakiety SSIS mogą gromadzić dane wersjonowania (mniejszy problem) i dane logowania (większy problem) daleko poza domyślnymi ustawieniami Microsoft cleanup, które mogą prowadzić do rozmiaru bazy danych SSISDB do balonu i wzrostu rozmiaru nieoczekiwanie (czytaj: zjeść całą dostępną przestrzeń dyskową).

Jednym domyślnym ustawieniem, które jest odpowiedzialne za przechwytywanie danych związanych z SSIS run-time jest Server-wide Default Logging Level = Basic. To ustawienie jest ostatecznie tym, co zarządza rozmiarem bazy danych SSISDB i ostatecznie zajmuje miejsce na dysku. SSIS Catalog (Rys. 1) zapewnia inne opcje kontroli rozmiaru bazy danych SSISDB i przestrzeni dyskowej:

1. Domyślny poziom logowania dla całego serwera
2. Domyślne ustawienia czyszczenia:
   1. Clean Logs Periodically = True
   2. Periodically Remove Old Versions = True
   3. Retention Period (days) = 365
   4. Maximum Number of Versions per Projects = 10

Zmiana domyślnego poziomu logowania w całym serwerze z Basic na None w celu odzyskania miejsca na dysku jest wysoce niepraktyczna i sprawi, że rozwiązywanie problemów stanie się koszmarem, zmiany innych ustawień SSISDB Catalog, takich jak Maximum Number of Versions per Projects i Retention Period (days) mogą mieć minimalny wpływ na rozwiązywanie problemów, jednocześnie utrzymując bazę danych SSISDB / przestrzeń dyskową pod kontrolą.

Wszystkie te ustawienia domyślne napędzają proces czyszczenia (a.k.a. SSIS Server Maintenance Job). Podczas gdy zarówno Clean Logs Periodically = True i Periodically Remove Old Versions = True są praktycznie zawsze idealne, ustawienia Maximum Number of Versions per Projects = 10 i Retention Period (days) = 365 mogą być zbyt optymistyczne. A oto dlaczego.

Głównym powodem, dla którego obie wartości domyślne Clean Logs Periodically = True i Periodically Remove Old Versions = True są idealne, jest to, że bez tych wartości domyślnych (lub efektywnie, jeśli obie zmienią się na False), SSISDB będzie rosło w nieskończoność, ponieważ będzie tylko gromadzić dane dziennika i wersjonowania bez usuwania żadnych danych i zabraknie miejsca na dysku.

Głównym powodem, dla którego zarówno Maximum Number of Versions per Projects = 10, jak i Retaining logging data for 365 days mogą być zbyt optymistyczne, jest decyzja Microsoftu, aby zachować dane w oparciu o okres czasu, a nie rozmiar. Oznacza to, że ustalona liczba okresów nie uwzględnia tego, jak wiele danych zostanie zgromadzonych. Podczas gdy Maximum Number of Versions per Projects może stać się problemem, gdy masz tysiące pakietów, Retention Period (days) stanie się ogromnym problemem, gdy będziesz miał pakiety, które będą uruchamiane co kilka minut. Nie sądzę, że Microsoft kiedykolwiek spodziewał się nagromadzenia danych dziennika dla 1440 uruchomień dziennie (uruchamianych co minutę), które będą wymagały czyszczenia częściej niż co 365 dni lub zabraknie miejsca na dysku.

Rozwiązanie

Rozwiązanie polegałoby na zmianie wartości domyślnych na mniejsze liczby, więc SSISDB przechowuje mniej informacji. Bazując na moim osobistym doświadczeniu z uruchamianiem pakietów co minutę (wymóg platformy e-commerce) i posiadaniem do 10 projektów, zmniejszenie okresu retencji (dni) z 365 (domyślnie) do 7 dni i maksymalnej liczby wersji na projekt z 10 (domyślnie) do 5 wystarczyłoby do kontrolowania przestrzeni dyskowej SSISDB, jednocześnie zachowując wystarczającą ilość informacji do celów debugowania i rozwiązywania problemów.

Mają Państwo dwie możliwości dokonania tej zmiany:

• Zmienić ustawienia domyślne za pomocą SQL Server Management Studio (a.k.a. „SSMS”) rozwijając Integration Services, klikając prawym przyciskiem myszy na SSISDB, a następnie klikając na pozycję menu Właściwości (Rys. 2 – SSMS).

• Zmień ustawienia domyślne używając poniższego T-SQL, aby bezpośrednio zaktualizować rekordy w tabeli ssisdb.catalog.catalog_properties.

Po zakończeniu zmiany, powinieneś być w stanie potwierdzić te zmiany uruchamiając poniższe zapytanie T-SQL (Rys. 3 – SSISDB Catalog)

Choć zmiana ustawień domyślnych może rozwiązać problem w przyszłości, może nie rozwiązać istniejącego problemu z miejscem na dysku SSISDB wystarczająco szybko. Możesz przyspieszyć proces czyszczenia poprzez modyfikację kodu i zmianę @delete_batch_size z 1000 na 10000 poprzez zmianę procedury składowanej ssisdb.internal.cleanup_server_retention_window (Rys 4 – Procedura retencyjna).

Podsumowanie

Ten artykuł zwrócił uwagę na potencjalny problem z domyślnymi ustawieniami SSISDB Catalog, który może prowadzić do wyczerpania przestrzeni dyskowej i dostarczył rozwiązanie, które zmusi SSISDB do zachowania mniejszej ilości informacji.

.