SSISDB Catalog Defaults Best Practices

Úvod

Databáze SSISDB (neboli katalog integračních služeb) byla zavedena již v SQL Serveru 2012, aby se odstranilo znepřehlednění databáze MSDB a poskytla se interní infrastruktura pro protokolování a vytváření zpráv. Stručně řečeno, SSISDB je rámec SSIS, který činí integrační služby SQL Serveru robustnějšími a přívětivějšími pro podniky tím, že poskytuje:

• Zálohování databáze
• Šifrování databáze
• Podporu prostředí
• Podporu parametrů projektu a balíčku
• Balíček verzování
• Zákaznické přehledy o výkonu SSRS zabudované do SSMS
• Vysílání přímo z SSDT

Když rámec SSISDB umožnil SSIS mnohem více, přišel s některými předpoklady společnosti Microsoft (a.Defaults). Tato výchozí nastavení jsou zde proto, aby vám pomohla postavit se na nohy, ale nemusí být optimální, když se rozběhnete, a zdaleka nejsou dokonalá, když sprintujete.

Tento článek se zabývá výchozími nastaveními, důvody, proč tato výchozí nastavení nemusí být optimální, a tím, jak tato výchozí nastavení změnit.

Problém

SSISDB Catalog se dodává s integrovaným procesem pro čištění operací a verzování projektů. Tento proces čištění se spoléhá na výchozí nastavení SSISDB, které může způsobit nefunkčnost vašeho SSIS, pokud nebude změněno.

Prakticky každý nový projekt SSIS začíná sestavením a spuštěním jen několika balíčků, zatímco je na disku dostatek volného místa. Rychle se posunete o 3-6 měsíců dopředu a máte řadu projektů SSIS, desítky nebo stovky balíčků a možná také potřebujete čerstvá data pro reporting a analytiku (čtěte: neustále spuštěné balíčky po celý den). Tyto balíčky SSIS mohou hromadit data o verzích (menší problém) a data o protokolování (větší problém) daleko nad rámec výchozího nastavení čištění společnosti Microsoft, což může vést k tomu, že se velikost databáze SSISDB neočekávaně nafoukne a zvětší (čti: sežere veškeré dostupné místo na disku).

Jediným výchozím nastavením, které je zodpovědné za zachycení dat souvisejících s během SSIS, je Default Logging Level = Basic pro celý server. Toto nastavení v konečném důsledku řídí velikost databáze SSISDB a v konečném důsledku zabírá místo na disku. Jak již bylo řečeno, katalog SSIS (obr. 1) poskytuje další možnosti řízení velikosti databáze SSISDB / místa na disku:

1. Celoserverová výchozí úroveň protokolování
2. Výchozí nastavení pro čištění:
   1. Pravidelně čistit protokoly = True
   2. Periodicky odstraňovat staré verze = True
   3. Doba uchovávání (dny) = 365
   4. Maximální počet verzí na projekty = 10

Změny výchozí úrovně protokolování celého serveru ze základní na žádnou, aby se získalo zpět místo na disku, jsou velmi nepraktické a řešení problémů se stane noční můrou, změny ostatních nastavení katalogu SSISDB, jako je Maximální počet verzí na projekty a Doba uchovávání (dny), mohou mít minimální vliv na řešení problémů a zároveň udržet databázi SSISDB / místo na disku pod kontrolou.

Všechna tato výchozí nastavení řídí proces čištění (tzv. SSIS Server Maintenance Job). Zatímco obě nastavení Clean Logs Periodically = True a Periodically Remove Old Versions = True jsou prakticky vždy dokonalá, nastavení pro Maximum Number of Versions per Projects = 10 a Retention Period (days) = 365 mohou být příliš optimistická. A zde je důvod proč.

Hlavním důvodem, proč jsou obě výchozí nastavení Clean Logs Periodically = True a Periodically Remove Old Versions = True dokonalá, je to, že bez těchto výchozích nastavení (nebo efektivně, pokud se obě změní na False) bude SSISDB růst donekonečna, protože bude pouze hromadit data logů a verzí, aniž by odstraňovala jakákoli data a dojde jí místo na disku.

Hlavním důvodem, proč jak Maximální počet verzí na projekty = 10, tak Uchovávání dat protokolování po dobu 365 dní mohou být příliš optimistické, je rozhodnutí společnosti Microsoft uchovávat data na základě časového období, a nikoli velikosti. To znamená, že pevný počet období nebere v úvahu, kolik dat se nashromáždí. Zatímco maximální počet verzí na projekty může přerůst v problém, jakmile budete mít tisíce balíčků, doba uchovávání (dny) se stane velkým problémem, jakmile budete mít balíčky, které se spouštějí každých několik minut. Nemyslím si, že Microsoft někdy očekával hromadění dat protokolu pro 1440 spuštění denně (spouštěných každou minutu), což bude vyžadovat čištění častěji než každých 365 dní, jinak dojde místo na disku.

Řešení

Řešení by zahrnovalo změnu výchozího nastavení na menší čísla, takže SSISDB bude uchovávat méně informací. Na základě mých osobních zkušeností se spouštěním balíčků každou minutu (požadavek platformy e-commerce) a s maximálně 10 projekty by stačilo snížit Retention Period (dny) z 365 (výchozí) na 7 dní a Maximum Number of Versions per Projects (maximální počet verzí na projekty) z 10 (výchozí) na 5, aby bylo možné kontrolovat SSISDB/diskové místo a zároveň zachovat dostatek informací pro účely ladění a řešení problémů.

Máte dvě možnosti, jak tuto změnu provést:

• Změnit výchozí nastavení pomocí SQL Server Management Studio (neboli „SSMS“) rozbalením Integration Services, kliknutím pravým tlačítkem myši na SSISDB a následným kliknutím na položku nabídky Properties (Obr. 2 – SSMS).

• Změňte výchozí nastavení pomocí níže uvedeného jazyka T-SQL a aktualizujte přímo záznamy v tabulce ssisdb.catalog.catalog_properties.

Po dokončení změny, byste měli být schopni potvrdit tyto změny spuštěním níže uvedeného dotazu T-SQL (Obr. 3 – Katalog SSISDB)

Změna výchozího nastavení sice může vyřešit problém do budoucna, ale nemusí dostatečně rychle vyřešit stávající problém s místem na disku SSISDB. Proces čištění můžete urychlit úpravou kódu a změnou velikosti @delete_batch_size z 1000 na například 10000 změnou uložené procedury ssisdb.internal.cleanup_server_retention_window (obr. 4 – Retenční procedura).

Shrnutí

Tento článek upozornil na potenciální problém s výchozím nastavením katalogu SSISDB, který by mohl vést k vyčerpání místa na disku, a poskytl řešení, které by donutilo SSISDB uchovávat méně informací.