SSISDB-katalogstandardindstillinger Bedste praksis

Indledning

SISDB-databasen (også kendt som Integration Services-kataloget) blev indført tilbage i SQL Server 2012 for at gøre MSDB-databasen mere overskuelig og give en intern lognings- og rapporteringsinfrastruktur. I en nøddeskal er SSISDB en SSIS-ramme, der gør SQL Server Integration Services mere robust og virksomhedsvenlig ved at give:

• Database backup
• Database kryptering
• Support for miljøer
• Support for projekt- og pakkeparametre
• Pakke versionering
• Kunders SSRS ydelsesrapporter indbygget i SSMS
• Deployering direkte fra SSDT

Mens SSISDB-rammen gjorde SSIS meget mere kompetent, det kom med nogle Microsoft-forudsætninger (en.k.a. defaults). Disse standardindstillinger er der for at hjælpe dig med at stå på benene, men de er måske ikke optimale, når du begynder at løbe, og de er langt fra perfekte, når du sprinter.

Denne artikel omhandler standardindstillingerne, hvorfor disse standardindstillinger måske ikke er optimale, og hvordan du ændrer disse standardindstillinger.

Problem

SSISDB Catalog leveres med en indbygget proces til at rydde op i operationer og projektversionering. Denne oprydningsproces er afhængig af SSISDB-standardindstillinger, som kan gøre din SSIS ubrugelig, hvis de ikke ændres.

Næsten alle nye SSIS-projekter starter med at bygge og køre blot nogle få pakker, mens der er masser af ledig diskplads. Spol frem 3-6 måneder, og du har en række SSIS-projekter, ti eller hundrede pakker, og du har måske også behov for friske data til rapportering og analyse (læs: konstant kørende pakker døgnet rundt). Disse SSIS-pakker kan akkumulere versionsdata (mindre problematisk) og logningsdata (mere problematisk) langt ud over Microsofts standardindstillinger for oprydning, hvilket kan føre til, at SSISDB-databasen vokser uventet i størrelse (læs: æder al den tilgængelige diskplads op).

Den eneste standard, der er ansvarlig for at opfange SSIS-kørtidsrelaterede data, er Server-wide Default Logging Level = Basic. Denne indstilling er i sidste ende det, der styrer SSISDB-databasens størrelse og i sidste ende optager diskpladsen. Når det er sagt, giver SSIS Catalog (fig. 1) andre muligheder for at styre SSISDB-databasens størrelse/diskplads:

1. Server-wide Default Logging Level
2. Server-wide Default Logging Level
3. Sanering af standardindstillingerne:
   1. Rens logfiler periodisk = True
   2. Periodisk fjernelse af gamle versioner = True
   3. opbevaringsperiode (dage) = 365
   4. Maximalt antal versioner pr. projekt = 10

Mens ændringer af Server-wide Default Logging Level fra Basic til None for at genvinde diskplads er meget upraktisk og vil gøre fejlfinding til et mareridt, ændringer af andre SSISDB-katalogindstillinger som maksimalt antal versioner pr. projekt og opbevaringsperiode (dage) kan have minimal effekt på fejlfinding, samtidig med at SSISDB-database/diskplads holdes under kontrol.

Alle disse standardindstillinger styrer oprydningsprocessen (alias SSIS Server Maintenance Job). Mens både Clean Logs Periodically = True og Periodically Remove Old Versions = True stort set altid er perfekte, er indstillingerne for både Maximum Number of Versions per Projects = 10 og Retention Period (days) = 365 måske overoptimistiske. Og her er hvorfor:

Den vigtigste grund til, at både standardindstillingerne Clean Logs Periodically = True og Periodically Remove Old Versions = True er perfekte, er, at uden disse standardindstillinger (eller effektivt, hvis begge ændres til False) vil SSISDB vokse i det uendelige, da den kun vil akkumulere log- og versionsdata uden at fjerne data og løbe tør for diskplads.

Den vigtigste grund til, at både Maximum Number of Versions per Projects = 10 og Retaining logging data for 365 dage kan være overoptimistisk, er Microsofts beslutning om at opbevare dataene baseret på en tidsperiode og ikke på størrelse. Det betyder, at tallet for den faste periode ikke tager højde for, hvor mange data der vil blive akkumuleret. Mens maksimalt antal versioner pr. projekt kan blive et problem, når du har tusindvis af pakker, vil opbevaringsperioden (dage) blive et stort problem, når du har pakker, der kører med få minutters mellemrum. Jeg tror ikke, at Microsoft nogensinde har forventet akkumulering af logdata for 1440 kørsler om dagen (kører hvert minut), hvilket vil kræve en oprydning oftere end hver 365. dag, ellers løber den tør for diskplads.

Løsning

Løsningen ville indebære ændring af standardværdierne til mindre tal, så SSISDB gemmer færre oplysninger. Baseret på min personlige erfaring med at køre pakker hvert minut (krav til e-handelsplatformen) og have op til 10 projekter, ville det være tilstrækkeligt at reducere Retention Period (dage) fra 365 (standard) til 7 dage og Maximum Number of Versions per Projects fra 10 (standard) til 5 for at kontrollere SSISDB/diskplads og samtidig bevare nok oplysninger til fejlfinding og fejlfinding.

Du har to muligheder for at foretage denne ændring:

• Ændre standardindstillingerne ved hjælp af SQL Server Management Studio (alias “SSMS”) ved at udvide Integration Services, højreklikke på SSISDB og derefter klikke på menupunktet Egenskaber (fig. 2 – SSMS).

• Ændrer standardindstillingerne ved hjælp af T-SQL nedenfor for at opdatere posterne i tabellen ssisdb.catalog.catalog_properties direkte.

Når ændringen er gennemført, bør du kunne bekræfte ændringerne ved at køre nedenstående T-SQL forespørgsel (Fig 3 – SSISDB Catalog)

Mens ændring af standardindstillingerne måske løser problemet fremadrettet, løser det måske ikke dit eksisterende SSISDB-diskpladsproblem hurtigt nok. Du kan fremskynde oprydningsprocessen ved at ændre koden og ændre @delete_batch_size fra 1000 til gerne 10000 ved at ændre ssisdb.internal.cleanup_server_retention_window stored procedure (Fig 4 – Retention Procedure).

Summary

Denne artikel fremhævede et potentielt problem med SSISDB Catalog defaults, der kan føre til at løbe tør for diskplads, og gav en løsning, der ville tvinge SSISDB til at bevare mindre information.