Meilleures pratiques des défauts du catalogue SSISDB

Introduction

La base de données SSISDB (alias le catalogue des services d’intégration) a été introduite dès SQL Server 2012 pour désencombrer la base de données MSDB et fournir une infrastructure interne de journalisation et de reporting. En bref, SSISDB est un framework SSIS rendant SQL Server Integration Services plus robuste et plus convivial pour les entreprises en fournissant :

• Sauvegarde de la base de données
• Cryptage de la base de données
• Support des environnements
• Support des paramètres de projet et de package
• Package. versioning
• Rapports de performance SSRS des clients intégrés dans SSMS
• Déploiement directement à partir de SSDT

Alors que le cadre SSISDB a rendu SSIS beaucoup plus capable, il est venu avec certaines hypothèses de Microsoft (a.k.a. par défaut). Ces défauts sont là pour vous aider à vous tenir debout, mais peuvent ne pas être optimaux lorsque vous commencez à courir, et sont loin d’être parfaits lorsque vous sprintez.

Cet article aborde les défauts, pourquoi ces défauts peuvent ne pas être optimaux, et comment changer ces défauts.

Problème

Le catalogue SSISDB est livré avec un processus intégré pour nettoyer les opérations et le versionnement des projets. Ce processus de nettoyage s’appuie sur les valeurs par défaut de SSISDB qui pourraient rendre votre SSIS inopérant s’il n’est pas modifié.

Virtuellement, chaque nouveau projet SSIS commence par la construction et l’exécution de seulement quelques paquets alors qu’il y a une abondance d’espace disque libre. Avancez rapidement de 3 à 6 mois et vous avez un certain nombre de projets SSIS, des dizaines ou des centaines de paquets, et vous pourriez également avoir besoin d’une donnée fraîche pour le reporting et l’analyse (lire : exécuter constamment des paquets 24 heures sur 24). Ces paquets SSIS pourraient accumuler des données de versionnement (moins de problème) et des données de journalisation (plus de problème) bien au-delà des valeurs par défaut de nettoyage de Microsoft, ce qui pourrait conduire à la taille de la base de données SSISDB à gonfler et à croître de manière inattendue (lire : manger tout l’espace disque disponible).

L’unique valeur par défaut responsable de la capture des données liées à l’exécution de SSIS est Server-wide Default Logging Level = Basic. Ce paramètre est en fin de compte ce qui gère la taille de la base de données SSISDB et finalement prendre l’espace disque. Ceci étant dit, le catalogue SSIS (Fig 1) fournit d’autres options pour contrôler la taille de la base de données SSISDB/espace disque :

1. Niveau de journalisation par défaut à l’échelle du serveur
2. Nettoyage par défaut :
   1. Nettoyage périodique des journaux = True
   2. Suppression périodique des anciennes versions = True
   3. Période de rétention (jours) = 365
   4. Nombre maximal de versions par projets = 10

Tandis que les modifications du niveau de journalisation par défaut à l’échelle du serveur de Basic à None pour regagner de l’espace disque est très peu pratique et fera du dépannage un cauchemar, les modifications apportées à d’autres paramètres du catalogue SSISDB tels que le nombre maximal de versions par projets et la période de rétention (jours) peuvent avoir un effet minimal sur le dépannage, tout en gardant le contrôle de la base de données SSISDB/de l’espace disque.

Tous ces paramètres par défaut pilotent le processus de nettoyage (alias le travail de maintenance du serveur SSIS). Si les paramètres Nettoyer périodiquement les journaux = Vrai et Supprimer périodiquement les anciennes versions = Vrai sont pratiquement toujours parfaits, les paramètres du Nombre maximal de versions par projets = 10 et de la Période de rétention (jours) = 365 pourraient être trop optimistes. Et voici pourquoi.

La principale raison pour laquelle les deux paramètres par défaut Clean Logs Periodically = True et Periodically Remove Old Versions = True sont parfaits est que, sans ces paramètres par défaut (ou effectivement si les deux sont changés en False), SSISDB va croître indéfiniment car il ne fera qu’accumuler des données de journal et de versioning sans supprimer aucune donnée et manquera d’espace disque.

La principale raison pour laquelle à la fois le Nombre maximum de versions par projets = 10 et la Conservation des données de journalisation pendant 365 jours pourraient être trop optimistes est la décision de Microsoft de conserver les données en fonction d’une période de temps et non de la taille. Cela signifie que le nombre de périodes fixes ne tient pas compte de la quantité de données qui seront accumulées. Alors que le nombre maximum de versions par projet peut devenir un problème lorsque vous avez des milliers de paquets, la période de rétention (jours) va devenir un énorme problème lorsque vous avez des paquets qui s’exécutent toutes les quelques minutes. Je ne pense pas que Microsoft ait jamais prévu l’accumulation de données de journal pour 1440 exécutions par jour (exécution toutes les minutes) qui nécessitera un nettoyage plus souvent que tous les 365 jours ou il manquera d’espace disque.

Solution

La solution impliquerait de changer les valeurs par défaut pour des nombres plus petits, de sorte que SSISDB conserve moins d’informations. Sur la base de mon expérience personnelle d’exécution de paquets toutes les minutes (exigence de la plate-forme de commerce électronique) et ayant jusqu’à 10 projets, diminuer la période de rétention (jours) de 365 (par défaut) à 7 jours et le nombre maximum de versions par projets de 10 (par défaut) à 5 suffirait à contrôler l’espace SSISDB/disque tout en conservant suffisamment d’informations à des fins de débogage et de dépannage.

Vous avez deux options pour effectuer cette modification :

• Changer les valeurs par défaut en utilisant SQL Server Management Studio (alias « SSMS ») en développant Integration Services, en cliquant avec le bouton droit de la souris sur SSISDB, puis en cliquant sur l’élément de menu Propriétés (Fig 2 – SSMS).

• Changer les valeurs par défaut en utilisant le T-SQL ci-dessous pour mettre à jour directement les enregistrements dans la table ssisdb.catalog.catalog_properties.

Une fois le changement effectué, vous devriez être en mesure de confirmer ces changements en exécutant la requête T-SQL ci-dessous (Fig 3 – Catalogue SSISDB)

Bien que la modification des valeurs par défaut puisse régler le problème à l’avenir, il se peut que cela ne règle pas assez rapidement votre problème d’espace disque SSISDB existant. Vous pouvez accélérer le processus de nettoyage en modifiant le code et en changeant @delete_batch_size de 1000 à comme 10000 en modifiant la procédure stockée ssisdb.internal.cleanup_server_retention_window (Fig 4 – Procédure de rétention).

Sommaire

Cet article a mis en évidence un problème potentiel avec les valeurs par défaut du catalogue SSISDB qui pourrait conduire à l’épuisement de l’espace disque et a fourni une solution qui forcerait SSISDB à conserver moins d’informations.