SSISDB Catalog Defaults Best Practices

Introductie

De SSISDB database (ook bekend als de Integration Services catalogus) werd geïntroduceerd in SQL Server 2012 om de MSDB database overzichtelijker te maken en te voorzien in een eigen logging en rapportage infrastructuur. In een notendop, SSISDB is een SSIS framework dat SQL Server Integration Services robuuster en bedrijfsvriendelijker maakt door te voorzien in:

• Database backup
• Database encryptie
• Ondersteuning voor omgevingen
• Ondersteuning voor project en package parameters
• Package versioning
• SSRS-prestatierapporten van klanten ingebouwd in SSMS
• Deployment direct vanuit SSDT

Terwijl SSISDB framework SSIS veel meer mogelijkheden gaf, kwam het met een aantal Microsoft aannames (a.k.a. standaardinstellingen). Die defaults zijn er om je op de been te helpen, maar zijn misschien niet optimaal als je begint te rennen, en zijn verre van perfect als je sprint.

Dit artikel behandelt de defaults, waarom die defaults misschien niet optimaal zijn, en hoe je die defaults kunt veranderen.

Probleem

SSISDB Catalog wordt geleverd met een ingebouwd proces om operaties en project versiebeheer op te schonen. Dit opschoonproces is gebaseerd op SSISDB-standaardinstellingen die uw SSIS mogelijk onbruikbaar maken als ze niet worden gewijzigd.

Virtueel elk nieuw SSIS-project begint met het bouwen en uitvoeren van slechts een paar pakketten terwijl er een overvloed aan vrije schijfruimte is. Spoel 3-6 maanden vooruit en je hebt een aantal SSIS-projecten, tientallen of honderden pakketten, en je hebt misschien ook behoefte aan verse gegevens voor rapportage en analyse (lees: constant pakketten draaien de klok rond). Deze SSIS-pakketten kunnen versiegegevens (minder een probleem) en logboekgegevens (meer een probleem) verzamelen die veel groter zijn dan de standaardinstellingen van Microsoft voor opschonen, wat ertoe kan leiden dat de SSISDB database groter wordt en onverwacht in omvang toeneemt (lees: alle beschikbare schijfruimte opeet).

De enige standaardinstelling die verantwoordelijk is voor het vastleggen van SSIS-runtimegerelateerde gegevens is Server-wide Default Logging Level = Basic. Deze instelling bepaalt uiteindelijk de grootte van de SSISDB database en neemt uiteindelijk de schijfruimte in beslag. SSIS Catalog (Fig 1) biedt echter andere opties om de SSISDB databasegrootte/schijfruimte te regelen:

1. Server-wide Default Logging Level
2. Clean-up defaults:
   1. Schoon logs periodiek = True
   2. Verwijder oude versies periodiek = True
   3. Bewaarperiode (dagen) = 365
   4. Maximum aantal versies per project = 10

Wijzigingen in het standaardregistratieniveau voor de hele server van Basis naar Geen om schijfruimte terug te winnen is zeer onpraktisch en zal het oplossen van problemen tot een nachtmerrie maken, wijzigingen in andere instellingen van de SSISDB-catalogus, zoals Maximum aantal versies per project en Bewaarperiode (dagen), kunnen een minimaal effect hebben op het oplossen van problemen, terwijl de SSISDB-database/schijfruimte onder controle blijft.

Al deze standaardinstellingen sturen het opschoonproces (ook wel SSIS Server Onderhoudsopdracht genoemd). Hoewel zowel Clean Logs Periodically = True als Periodically Remove Old Versions = True vrijwel altijd perfect zijn, zijn de instellingen voor zowel Maximum Number of Versions per Project = 10 als voor Retention Period (dagen) = 365 wellicht wat te optimistisch. En hier is waarom.

De belangrijkste reden waarom zowel Clean Logs Periodically = True als Periodically Remove Old Versions = True standaardinstellingen perfect zijn, is dat zonder deze standaardinstellingen (of effectief als beide worden gewijzigd in False), SSISDB oneindig zal groeien omdat het alleen log- en versiegegevens zal accumuleren zonder gegevens te verwijderen en schijfruimte zal opraken.

De belangrijkste reden waarom zowel Maximum aantal versies per project = 10 als het 365 dagen bewaren van logboekgegevens te optimistisch zou kunnen zijn, is de beslissing van Microsoft om de gegevens te bewaren op basis van een tijdsperiode en niet op basis van de grootte. Dat betekent dat het aantal vaste perioden geen rekening houdt met de hoeveelheid gegevens die wordt geaccumuleerd. Maximum aantal versies per project kan een probleem worden als je duizenden pakketten hebt, maar de bewaarperiode (dagen) wordt een enorm probleem als je pakketten hebt die om de paar minuten draaien. Ik denk niet dat Microsoft ooit verwachtte dat er loggegevens zouden worden verzameld voor 1440 runs per dag (die elke minuut worden uitgevoerd), die vaker dan elke 365 dagen moeten worden opgeschoond, anders is er geen schijfruimte meer.

Oplossing

De oplossing zou zijn om de standaardwaarden te wijzigen in kleinere getallen, zodat SSISDB minder informatie bewaart. Op basis van mijn persoonlijke ervaring met het elke minuut uitvoeren van pakketten (eis van het e-commerce platform) en het hebben van maximaal 10 projecten, zou het verlagen van de bewaarperiode (dagen) van 365 (standaard) naar 7 dagen en het maximum aantal versies per project van 10 (standaard) naar 5 voldoende zijn om de SSISDB/schijfruimte te beheersen en toch voldoende informatie te bewaren voor debugging- en probleemoplossingsdoeleinden.

U hebt twee mogelijkheden om deze wijziging door te voeren:

• De standaardinstellingen wijzigen met behulp van SQL Server Management Studio (ook bekend als “SSMS”) door Integration Services uit te vouwen, met de rechtermuisknop te klikken op SSISDB en vervolgens te klikken op het menu-item Eigenschappen (Afb. 2 – SSMS).

• Wijzig de standaardwaarden met behulp van onderstaande T-SQL om records in de tabel ssisdb.catalog.catalog_properties rechtstreeks bij te werken.

Als de wijziging is voltooid, zou u de wijzigingen moeten kunnen bevestigen door de onderstaande T-SQL query uit te voeren (Fig 3 – SSISDB Catalogus)

Hoewel het wijzigen van de standaardinstellingen het probleem in de toekomst kan verhelpen, kan dit het bestaande SSISDB-schijfruimteprobleem niet snel genoeg oplossen. U kunt het opschoonproces versnellen door de code aan te passen en @delete_batch_size te wijzigen van 1000 in bijvoorbeeld 10000 door de opgeslagen procedure ssisdb.internal.cleanup_server_retention_window te wijzigen (Afb. 4 – Retentieprocedure).

Samenvatting

In dit artikel wordt gewezen op een mogelijk probleem met de standaardinstellingen van de SSISDB-catalogus dat kan leiden tot een gebrek aan schijfruimte en wordt een oplossing geboden die SSISDB dwingt om minder informatie te bewaren.