Blog

Maintenance des bases de données


Scripts d’Ola Hallengren

Tout ou partie de la maintenance des bases de données peut reposer sur les scripts d’Ola Hallengren. Elle offre plusieurs avantages :

– Très simple à déployer
– Paramétrable, ex : gestion d’exclusion ou d’individualisation de la maintenance
– Standard, ex : seuils de maintenance des index conformes aux standards Microsoft
– Supporte toutes les versions de SQL Server depuis 2005
– Optimisée (peut être limitée dans le temps, etc.)

Déploiement des scripts

Pour télécharger le script de déploiement de la maintenance et consulter l’aide en ligne, se rendre sur :

https://ola.hallengren.com/

Expert SQL Server - Maintenance des bases de données - SQL Server  - olahallengren

Ouvrir SQL Management Studio et exécuter le script précédemment téléchargé, MaintenancSolution.sql, sur la base de données système master ou idéalement une base de données utilisateur dédiée aux scripts d’administration DBA. L’exécution du script peut être relancée plusieurs fois sur la même instance sans entraîner d’erreur (contrôle d’existence). Son exécution va entraîner la création de plusieurs objets :

Type
Nom Description
Table CommandLog Historique des logs de maintenance avec une rétention par défaut de 30 jours
PS CommandExecute Procédure principale d’exécution de commandes de maintenance
PS DatabaseBackup Gestion des sauvegardes complète, différentielle et logs
PS DatabaseIntegrityCheck Contrôle d’intégrité
PS IndexOptimize Maintenance des index et statistiques
Job CommandLog Cleanup Job de nettoyage de la table d’historique de la maintenance (CommandLog, 30 jours par défaut)
Job DatabaseIntegrityCheck – SYSTEM_DATABASES Contrôle d’intégrité des bases de données système
Job DatabaseIntegrityCheck – USER_DATABASES Contrôle d’intégrité des bases de données utilisateur
Job DatabaseBackup – SYSTEM_DATABASES – FULL Sauvegarde complète des bases de données système
Job DatabaseBackup – USER_DATABASES – LOG Sauvegarde des logs de bases de données utilisateur
Job sp_delete_backuphistory Nettoyage de la table d’historique des sauvegardes
Job Output File Cleanup Nettoyage des fichiers de logs
Job DatabaseBackup – USER_DATABASES – DIFF Sauvegarde différentielle des bases de données utilisateur
Job IndexOptimize – USER_DATABASES Maintenance des index et des statistiques
Job sp_purge_jobhistory Nettoyage de la table d’historique des jobs
Job DatabaseBackup – USER_DATABASES – FULL Sauvegarde complète des bases de données utilisateur

NB : Les jobs par défaut ne sont pas planifiés. Ils peuvent être complétés et/ou personnalisés par base de données.

Application de la maintenance

Bases de données système & utilisateur
– Contrôle d’intégrité (quotidien)
– Maintenance des index et des statistiques (quotidien)
– Sauvegarde complète (quotidien ou hebdomadaire)
– Sauvegarde des logs des bases de données en mode de récupération complet (ex : toutes les heures)
– Shrink des logs des bases de données en mode de récupération simple (quotidien)
– Sauvegarde différentielle (quotidien)
Autre
– Purge de la table d’historique de maintenance (quotidien)
– Purge des fichiers de logs de maintenance (quotidien)

Maintenance des bases de données système

Maintenance des index et statistiques

Contrôle d’intégrité

Sauvegarde complète avec rétention de 7 jours (@CleanupTime = 168)

Toutes les base de données système doivent être sauvegardées, à l’exception de tempdb qui elle est reconstruite à chaque redémarrage de l’instance.

master : Contient l’intégralité des informations liées à un système SQL Server. Cela inclut les comptes d’ouverture de session, les points de terminaison, les serveurs liés et les paramètres de configuration du système, etc.

msdb : Utilisée par l’Agent SQL Server pour planifier des alertes et des travaux, ainsi que par d’autres fonctionnalités telles que Service Broker, la messagerie de base de données, etc.

model : Fait office de modèle pour toutes les bases de données créées sur l’instance de SQL Server. Les modifications apportées à celle-ci, telles que la taille de la base de données, le classement, le mode de récupération et les autres options de base de données, s’appliquent aux bases de données créées par la suite.

tempdb : Espace de travail destiné à accueillir les objets temporaires ou les ensembles de résultats intermédiaires, exclue de la maintenance.

Maintenance des bases de données utilisateur

Les scripts peuvent être lancés sur une base de données spécifique (@Databases = ‘DatabaseName’) ou exclure une ou plusieurs base de données (@Databases = ‘USER_DATABASES, -Db1’), voir aide en ligne https://ola.hallengren.com.

Maintenance des index et statistiques

Contrôle d’intégrité

Sauvegarde complète avec rétention de 7 jours (@CleanupTime = 168)

Sauvegarde des logs des bases de données en mode de récupération complet

Sur une base de données utilisant le mode de récupération complet, la sauvegarde des logs est indispensable pour deux raisons :

– Se prémunir d’une perte minimale en cas de corruption des données ou autre. Si la sauvegarde de logs s’exécute toutes les heures, la perte de données sera de 1h maximum.

– Recycler l’espace pour éviter une saturation du disque, Low disk space et dans le meilleur des cas l’erreur The transaction log for database ‘xxx’ is full (taille du log limité).

Le mode récupération complet présente les caractéristiques suivantes :

– Tous les types de sauvegardes sont disponibles : complète, différentielle et logs

– Restauration à un instant T

– Requis pour les solutions de haute disponibilité : AlwaysOn, miroir, log shipping

– Utilisé pour des bases de données critiques et/ou sur des environnements de production

– Sauvegarde de log requise pour recycler l’espace

Shrink des logs des bases de données en mode de récupération simple

Sur une base de données utilisant le mode de récupération simple, un shrink du fichier de transaction est indispensable à intervalle régulier pour éviter une saturation du disque, Low disk space et dans le meilleur des cas l’erreur The transaction log for database ‘xxx’ is full (taille du log limité). Le script T-SQL ci-dessous permet de purger les log des toutes les bases de données en mode de récupération simple sur une instance SQL Server. Il suffit de planifier son exécution une fois par jour.

Le mode récupération simple présente les caractéristiques suivantes :

– Pas de sauvegarde possible du journal de transaction et donc pas de restauration à un instant T, seule les sauvegardes complètes et différentielles sont disponibles

– Non compatible avec les solutions de haute disponibilité : AlwaysOn, miroir, log shipping

– Ce mode de récupération est utilisé pour des bases de données non critiques et/ou sur des environnements de développement

– Shrink du fichier de transaction requis pour recycler l’espace

Sauvegarde différentielle des bases de données

La sauvegarde différentielle est préconisée pour des bases de données très volumineuses qui ne peuvent pas faire l’objet d’une sauvegardée complète au quotidien. En règle générale, le plan de sauvegarde est le suivant :

– Sauvegarde complète une fois par semaine

– Sauvegarde différentielle les autres jours

– Sauvegarde de logs toutes les heures

Purge de la table d’historique de maintenance

Tous les logs de maintenance sont consignés dans la table CommandLog. Cette table d’historisation permet d’avoir une vue exhaustive des opérations (sauvegarde, reconstruction d’index, etc) qui ont été réalisées avec la date/heure de début et de fin, la base de données concernée, etc. Voir job par défaut CommandLog Cleanup, il suffit d’appliquer la rétention souhaitée et de planifier l’exécution du job.

Purge des fichiers de logs de maintenance

Planifier l’exécution du job Output File Cleanup pour nettoyer les fichiers de sortie générés par la maintenance.

Auteur

Expert SQL Server - Maintenance des bases de données - SQL Server  - avatar_ninja_tete-150x150
Sarah Bessard
Experte SQL Server Prod/Etude avec un bonus sur la BI, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur. Besoin d'une expertise SQL Server ? N'hésitez pas à me contacter.

Leave a comment

Your email address will not be published.

error: