SSISDB-katalogens standardinställningar Bästa praxis

Introduktion

SISDB-databasen (även kallad Integration Services-katalogen) introducerades i SQL Server 2012 för att rensa MSDB-databasen och tillhandahålla en intern infrastruktur för loggning och rapportering. I ett nötskal är SSISDB ett SSIS-ramverk som gör SQL Server Integration Services mer robust och företagsvänligt genom att tillhandahålla:

• Backup av databas
• Kryptering av databas
• Stöd för miljöer
• Stöd för projekt- och paketparametrar
• Paket versionering
• Kundars SSRS prestandarapporter inbyggda i SSMS
• Distribution direkt från SSDT

Men SSISDB-ramverket gjorde SSIS mycket mer kapabelt, men det kom med vissa antaganden från Microsoft (a.k.a. standardvärden). Dessa standardvärden finns där för att hjälpa dig att stå på fötter, men är kanske inte optimala när du börjar springa, och är långt ifrån perfekta när du sprintar.

Denna artikel tar upp standardvärdena, varför dessa standardvärden kanske inte är optimala och hur du ändrar dessa standardvärden.

Problem

SSISDB-katalogen levereras med en inbyggd process för att städa upp operationer och versionering av projekt. Denna rensningsprocess bygger på SSISDB:s standardvärden som kan göra ditt SSIS obrukbart om de inte ändras.

Nästan alla nya SSIS-projekt börjar med att bygga och köra några få paket medan det finns gott om ledigt diskutrymme. Spola fram 3-6 månader och du har ett antal SSIS-projekt, tiotals eller hundratals paket, och du kanske också har ett behov av färska data för rapportering och analys (läs: ständigt körande paket dygnet runt). Dessa SSIS-paket kan ackumulera versioneringsdata (mindre problem) och loggningsdata (mer problem) långt utöver Microsofts rensningsinställningar, vilket kan leda till att SSISDB-databasens storlek ökar oväntat (läs: äter upp allt tillgängligt diskutrymme).

Den enda inställningen som är ansvarig för att fånga SSIS-körningsrelaterade data är Server-wide Default Logging Level = Basic. Den här inställningen är i slutändan det som styr SSISDB-databasens storlek och i slutändan tar upp diskutrymmet. SSIS Catalog (fig. 1) erbjuder dock andra alternativ för att kontrollera SSISDB-databasens storlek/diskutrymme:

1. Server-wide Default Logging Level
2. Rensa upp standardinställningar:
   1. Rensa loggar regelbundet = True
   2. Periodiskt ta bort gamla versioner = True
   3. Lagringsperiod (dagar) = 365
   4. Maximalt antal versioner per projekt = 10

Och ändringar av standardloggningsnivån för hela servern från Basic till None för att återfå diskutrymme är högst opraktiskt och kommer att göra felsökning till en mardröm, Ändringar av andra SSISDB-kataloginställningar som maximalt antal versioner per projekt och lagringsperiod (dagar) kan ha minimal effekt på felsökningen, samtidigt som SSISDB-databas/diskutrymme hålls under kontroll.

Alla dessa standardinställningar styr rensningsprocessen (alias SSIS Server Maintenance Job). Medan både Clean Logs Periodically = True och Periodically Remove Old Versions = True nästan alltid är perfekta, kan inställningarna för både Maximum Number of Versions per Projects = 10 och Retention Period (days) = 365 vara överoptimistiska. Och här är varför.

Den huvudsakliga anledningen till att både Clean Logs Periodically = True och Periodically Remove Old Versions = True är perfekta är att utan dessa standardvärden (eller effektivt om båda ändras till False) kommer SSISDB att växa i all oändlighet eftersom den bara kommer att ackumulera logg- och versionsdata utan att ta bort några data och få slut på diskutrymme.

Den främsta anledningen till att både Maximalt antal versioner per projekt = 10 och Behålla loggningsdata i 365 dagar kan vara överoptimistiska är Microsofts beslut att behålla data baserat på en tidsperiod och inte storlek. Det innebär att antalet fasta perioder inte tar hänsyn till hur mycket data som kommer att ackumuleras. Maximalt antal versioner per projekt kan bli ett problem när du har tusentals paket, men lagringsperioden (dagar) blir ett stort problem när du har paket som körs med några minuters mellanrum. Jag tror inte att Microsoft någonsin förväntade sig ackumulering av loggdata för 1440 körningar per dag (körs varje minut) som kommer att kräva en rensning oftare än var 365:e dag, annars kommer diskutrymmet att ta slut.

Lösning

Lösningen skulle innebära att ändra standardvärdena till mindre tal, så att SSISDB behåller mindre information. Baserat på min personliga erfarenhet av att köra paket varje minut (krav från e-handelsplattformen) och ha upp till 10 projekt, skulle det räcka att minska Retention Period (dagar) från 365 (standard) till 7 dagar och Maximalt antal versioner per projekt från 10 (standard) till 5 för att kontrollera SSISDB/diskutrymme samtidigt som man behåller tillräckligt med information för felsökning och felsökning.

Du har två alternativ för att göra den här ändringen:

• Ändra standardvärdena med hjälp av SQL Server Management Studio (alias ”SSMS”) genom att expandera Integration Services, högerklicka på SSISDB och sedan klicka på menyalternativet Egenskaper (Fig 2 – SSMS).

• Förändra standardvärdena med hjälp av T-SQL nedan för att uppdatera poster i tabellen ssisdb.catalog.catalog_properties direkt.

När ändringen är klar, bör du kunna bekräfta ändringarna genom att köra T-SQL-frågan nedan (Fig 3 – SSISDB-katalog)

Och om du ändrar standardvärdena kan det lösa problemet framöver, men det kanske inte löser problemet med diskutrymme i SSISDB tillräckligt snabbt. Du kan påskynda rensningsprocessen genom att ändra koden och ändra @delete_batch_size från 1000 till 10000 genom att ändra den lagrade proceduren ssisdb.internal.cleanup_server_retention_window (Fig 4 – Retention Procedure).

Summary

Denna artikel belyser ett potentiellt problem med SSISDB Catalog defaults som kan leda till att diskutrymmet tar slut och ger en lösning som tvingar SSISDB att behålla mindre information.