SSISDB Catalog Defaults Best Practices

Introduzione

Il database SSISDB (noto anche come catalogo dei servizi di integrazione) è stato introdotto in SQL Server 2012 per liberare il database MSDB e fornire un’infrastruttura interna di log e reporting. In poche parole, SSISDB è un framework SSIS che rende SQL Server Integration Services più robusto ed enterprise-friendly fornendo:

• Salvataggio del database
• Crittografia del database
• Supporto per gli ambienti
• Supporto dei parametri di progetto e di pacchetto
• Pacchetto versioning
• Rapporti sulle prestazioni di SSRS costruiti in SSMS
• Deployment direttamente da SSDT

Mentre il framework SSISDB ha reso SSIS molto più capace, è venuto con alcuni presupposti Microsoft (a. k. a. defaults).k.a. defaults). Questi default sono lì per aiutarti a stare in piedi, ma potrebbero non essere ottimali quando inizi a correre, e sono tutt’altro che perfetti quando stai sprintando.

Questo articolo affronta i default, perché questi default potrebbero non essere ottimali, e come cambiare questi default.

Problema

SSISDB Catalog viene fornito con un processo incorporato per pulire le operazioni e le versioni dei progetti. Questo processo di pulizia si basa sulle impostazioni predefinite di SSISDB che potrebbero rendere il tuo SSIS inutilizzabile se non modificato.

Virtualmente ogni nuovo progetto SSIS inizia con la costruzione e l’esecuzione di pochi pacchetti mentre c’è un’abbondanza di spazio libero su disco. Andate avanti di 3-6 mesi e avrete un certo numero di progetti SSIS, decine o centinaia di pacchetti, e potreste anche avere bisogno di dati freschi per il reporting e l’analisi (leggi: pacchetti costantemente in esecuzione 24 ore su 24). Quei pacchetti SSIS potrebbero accumulare dati di versioning (meno di un problema) e dati di registrazione (più di un problema) ben oltre le impostazioni predefinite di pulizia di Microsoft che potrebbero portare le dimensioni del database SSISDB a gonfiarsi e crescere inaspettatamente (leggi: mangiare tutto lo spazio disponibile su disco).

Il singolo default che è responsabile di catturare i dati relativi al run-time SSIS è Server-wide Default Logging Level = Basic. Questa impostazione è in definitiva ciò che gestisce la dimensione del database SSISDB e in ultima analisi, occupando lo spazio su disco. Detto questo, SSIS Catalog (Fig 1) fornisce altre opzioni per controllare la dimensione del database SSISDB/spazio su disco:

1. Server-wide Default Logging Level
2. Clean-up defaults:
   1. Pulire periodicamente i log = True
   2. Rimuovere periodicamente le vecchie versioni = True
   3. Periodo di conservazione (giorni) = 365
   4. Numero massimo di versioni per progetti = 10

Mentre le modifiche al livello di registrazione predefinito a livello di server da Basic a None per recuperare spazio su disco è altamente poco pratico e renderà la risoluzione dei problemi un incubo, le modifiche ad altre impostazioni del catalogo SSISDB come il numero massimo di versioni per progetto e il periodo di conservazione (giorni) possono avere un effetto minimo sulla risoluzione dei problemi, mantenendo il controllo dello spazio su disco e del database SSISDB.

Tutti questi valori predefiniti stanno guidando il processo di pulizia (ovvero il lavoro di manutenzione del server SSIS). Mentre sia Pulisci registri periodicamente = Vero e Rimuovi periodicamente le vecchie versioni = Vero sono praticamente sempre perfetti, le impostazioni per il Numero massimo di versioni per progetti = 10 e il Periodo di conservazione (giorni) = 365 potrebbero essere troppo ottimistiche. Ed ecco perché.

La ragione principale per cui entrambi i valori predefiniti Clean Logs Periodically = True e Periodically Remove Old Versions = True sono perfetti è che senza questi valori predefiniti (o effettivamente se entrambi vengono cambiati in False), SSISDB crescerà indefinitamente poiché accumulerà solo dati di log e versioning senza rimuovere alcun dato e finirà lo spazio su disco.

Il motivo principale per cui sia il Numero massimo di versioni per progetto = 10 che la conservazione dei dati di log per 365 giorni potrebbero essere troppo ottimistici è la decisione di Microsoft di conservare i dati in base a un periodo di tempo e non alle dimensioni. Ciò significa che il numero del periodo fisso non tiene conto di quanti dati saranno accumulati. Mentre il numero massimo di versioni per progetto potrebbe diventare un problema una volta che si hanno migliaia di pacchetti, il periodo di conservazione (giorni) diventerà un problema enorme una volta che si hanno pacchetti che vengono eseguiti ogni pochi minuti. Non credo che Microsoft si sia mai aspettata l’accumulo di dati di log per 1440 esecuzioni al giorno (in esecuzione ogni minuto) che richiederanno una pulizia più spesso di ogni 365 giorni o si esaurirà lo spazio su disco.

Soluzione

La soluzione comporterebbe il cambiamento delle impostazioni predefinite a numeri più piccoli, così SSISDB conserva meno informazioni. Sulla base della mia esperienza personale di esecuzione di pacchetti ogni minuto (requisito della piattaforma e-commerce) e avendo fino a 10 progetti, diminuire il periodo di conservazione (giorni) da 365 (predefinito) a 7 giorni e il numero massimo di versioni per progetti da 10 (predefinito) a 5 sarebbe sufficiente per controllare SSISDB/spazio su disco mantenendo abbastanza informazioni per il debug e la risoluzione dei problemi.

Hai due opzioni per fare questa modifica:

• Cambiare le impostazioni predefinite usando SQL Server Management Studio (noto anche come “SSMS”) espandendo Integration Services, cliccando con il tasto destro del mouse su SSISDB e poi cliccando sulla voce di menu Proprietà (Fig 2 – SSMS).

• Cambia i default usando il T-SQL qui sotto per aggiornare direttamente i record nella tabella ssisdb.catalog.catalog_properties.

Una volta completata la modifica, dovresti essere in grado di confermare le modifiche eseguendo la query T-SQL sottostante (Fig 3 – SSISDB Catalog)

Mentre cambiare i default potrebbe risolvere il problema in futuro, potrebbe non risolvere il tuo problema di spazio su disco SSISDB esistente abbastanza velocemente. Puoi accelerare il processo di pulizia modificando il codice e cambiando @delete_batch_size da 1000 a circa 10000 alterando la stored procedure ssisdb.internal.cleanup_server_retention_window (Fig 4 – Retention Procedure).

Summary

Questo articolo ha evidenziato un potenziale problema con le impostazioni predefinite del catalogo SSISDB che potrebbe portare ad esaurire lo spazio su disco e ha fornito una soluzione che costringerebbe SSISDB a conservare meno informazioni.