Cele mai bune practici cu privire la valorile implicite ale catalogului SSISDB

Introducere

Baza de date SSISDB (alias catalogul serviciilor de integrare) a fost introdusă în SQL Server 2012 pentru a dezordona baza de date MSDB și pentru a oferi o infrastructură internă de logare și raportare. Pe scurt, SSISDB este un cadru SSIS care face ca SQL Server Integration Services să fie mai robust și mai prietenos pentru întreprinderi, oferind:

• Salvare a bazei de date
• Criptare a bazei de date
• Suport pentru medii
• Suport pentru parametri de proiect și pachet
• Pachet. versionare
• Rapoarte de performanță SSRS pentru clienți încorporate în SSMS
• Deployment direct din SSDT

În timp ce cadrul SSISDB a făcut SSIS mult mai capabil, a venit cu unele ipoteze Microsoft (a.k.a. defaults). Aceste valori implicite sunt acolo pentru a vă ajuta să vă țineți pe picioare, dar s-ar putea să nu fie optime atunci când începeți să alergați și sunt departe de a fi perfecte atunci când sprintați.

Acest articol abordează valorile implicite, de ce aceste valori implicite s-ar putea să nu fie optime și cum să schimbați aceste valori implicite.

Problemă

Catalogul SSISDB este livrat cu un proces încorporat pentru a curăța operațiunile și versionarea proiectelor. Acest proces de curățare se bazează pe valorile implicite ale SSISDB care ar putea face ca SSIS-ul dumneavoastră să fie inoperabil dacă nu este modificat.

Practic fiecare proiect SSIS nou începe cu construirea și rularea a doar câteva pachete în timp ce există o abundență de spațiu liber pe disc. Mergeți rapid înainte cu 3-6 luni și aveți un număr de proiecte SSIS, zeci sau sute de pachete și este posibil să aveți, de asemenea, nevoie de date proaspete pentru raportare și analiză (a se citi: pachete care rulează în mod constant 24 de ore din 24). Aceste pachete SSIS ar putea acumula date de versionare (mai puțin o problemă) și date de logare (mai mult o problemă) mult peste valorile implicite de curățare Microsoft, ceea ce ar putea duce la creșterea neașteptată a dimensiunii bazei de date SSISDB și la o creștere neașteptată a acesteia (a se citi: consumă tot spațiul disponibil pe disc).

Singura valoare implicită care este responsabilă de capturarea datelor legate de timpul de execuție SSIS este Server-wide Default Logging Level = Basic. Această setare este, în cele din urmă, cea care gestionează dimensiunea bazei de date SSISDB și care, în cele din urmă, ocupă spațiul pe disc. Acestea fiind spuse, SSIS Catalog (Fig. 1) oferă alte opțiuni pentru a controla dimensiunea bazei de date SSISDB/spațiul pe disc:

1. Server-wide Default Logging Level
2. Clean-up defaults:
   1. Curățarea periodică a jurnalelor = True
   2. Îndepărtarea periodică a versiunilor vechi = True
   3. Perioada de păstrare (zile) = 365
   4. Număr maxim de versiuni pe proiecte = 10

În timp ce modificarea nivelului implicit de logare la nivelul serverului de la Basic la None pentru a recupera spațiu pe disc este extrem de nepractică și va face ca depanarea să fie un coșmar, modificările altor setări ale catalogului SSISDB, cum ar fi Maximum Number of Versions per Projects (Numărul maxim de versiuni pe proiecte) și Retention Period (perioadă de păstrare) (zile), pot avea un efect minim asupra soluționării problemelor, menținând în același timp sub control spațiul pe disc/baza de date SSISDB.

Toate aceste valori implicite determină procesul de curățare (a.k.a. SSIS Server Maintenance Job). În timp ce atât Clean Logs Periodic = True (Curățare periodică a jurnalelor = True), cât și Periodically Remove Old Versions (Eliminare periodică a versiunilor vechi = True) sunt practic întotdeauna perfecte, setările pentru Maximum Number of Versions per Projects (Număr maxim de versiuni pe proiecte) = 10 și Retention Period (zile) = 365 ar putea fi prea optimiste. Și iată de ce.

Motivul principal pentru care atât setările implicite Clean Logs Periodically = True (Curățare periodică a jurnalelor = True), cât și Periodically Remove Old Versions = True (Eliminare periodică a versiunilor vechi = True) sunt perfecte este că, fără aceste setări implicite (sau efectiv dacă ambele sunt schimbate la False), SSISDB va crește la nesfârșit, deoarece va acumula doar date de jurnal și de versiuni fără a elimina date și va rămâne fără spațiu pe disc.

Motivul principal pentru care atât Maximum Number of Versions per Projects = 10 (Număr maxim de versiuni pe proiecte), cât și Retaining logging data for 365 days (Păstrarea datelor de logare timp de 365 de zile) ar putea fi prea optimiste este decizia Microsoft de a păstra datele pe baza unei perioade de timp și nu a dimensiunii. Aceasta înseamnă că numărul perioadei fixe nu ia în considerare cât de multe date vor fi acumulate. În timp ce Numărul maxim de versiuni pe proiecte ar putea deveni o problemă odată ce aveți mii de pachete, Perioada de păstrare (zile) va deveni o problemă uriașă odată ce aveți pachete care rulează la fiecare câteva minute. Nu cred că Microsoft s-a așteptat vreodată la acumularea de date de jurnal pentru 1440 de execuții pe zi (care rulează la fiecare minut), ceea ce va necesita o curățare mai des decât la fiecare 365 de zile, altfel va rămâne fără spațiu pe disc.

Soluție

Soluția ar implica schimbarea valorilor implicite cu numere mai mici, astfel încât SSISDB să păstreze mai puține informații. Pe baza experienței mele personale de a rula pachete la fiecare minut (cerința platformei de comerț electronic) și de a avea până la 10 proiecte, scăderea Perioadei de păstrare (zile) de la 365 (implicită) la 7 zile și a Numărului maxim de versiuni pe proiecte de la 10 (implicită) la 5 ar fi suficiente pentru a controla SSISDB/spațiul pe disc, păstrând în același timp suficiente informații pentru depanare și depanare.

Aveți două opțiuni pentru a face această modificare:

• Modificați valorile implicite utilizând SQL Server Management Studio (alias „SSMS”) prin extinderea Integration Services, făcând clic dreapta pe SSISDB și apoi făcând clic pe elementul de meniu Properties (Fig 2 – SSMS).

• Modificați valorile implicite utilizând T-SQL de mai jos pentru a actualiza direct înregistrările din tabela ssisdb.catalog.catalog_properties.

După ce modificarea este finalizată, ar trebui să puteți confirma aceste modificări executând interogarea T-SQL de mai jos (Fig 3 – Catalogul SSISDB)

În timp ce schimbarea valorilor implicite ar putea rezolva problema pe viitor, s-ar putea să nu rezolve suficient de repede problema de spațiu pe disc SSISDB existent. Puteți accelera procesul de curățare modificând codul și schimbând @delete_batch_size de la 1000 la aproximativ 10000 prin modificarea procedurii stocate ssisdb.internal.cleanup_server_retention_window (Fig. 4 – Procedura de păstrare).

Summary

Acest articol a evidențiat o problemă potențială cu valorile implicite ale Catalogului SSISDB care ar putea duce la epuizarea spațiului pe disc și a oferit o soluție care ar forța SSISDB să rețină mai puține informații.