Maintenance des bases de données

2 février 2016

Commentaires fermés

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

EXECUTE [dbo].[IndexOptimize] @Databases = 'SYSTEM_DATABASES', @LogToTable = 'Y'

1	EXECUTE [dbo].[IndexOptimize] @Databases = 'SYSTEM_DATABASES', @LogToTable = 'Y'

Contrôle d’intégrité

EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'SYSTEM_DATABASES', @LogToTable = 'Y'

1	EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'SYSTEM_DATABASES', @LogToTable = 'Y'

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

EXECUTE [dbo].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'

1	EXECUTE [dbo].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'

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

EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'

1	EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'

Contrôle d’intégrité

EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'

1	EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'

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

EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'

1	EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'

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é).

EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'

1	EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'

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.

DECLARE @DynTSQL NVARCHAR(MAX)

SELECT	@DynTSQL = (  
				SELECT 
						'USE [' + d.name + N']' + CHAR(9) + CHAR(10) 
					+ 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY);' 
					+ CHAR(9) + CHAR(10) AS [data()]
				FROM	sys.master_files mf 
				INNER	JOIN sys.databases d ON mf.database_id = d.database_id 
				AND		d.recovery_model = 3
				AND		d.database_id > 4
				AND		d.user_access = 0
				AND		d.state = 0
				AND		mf.type = 1
				FOR XML PATH('')
			)

EXECUTE sp_executesql @DynTSQL

DECLARE @DynTSQL NVARCHAR(MAX)

SELECT @DynTSQL = (

SELECT

'USE [' + d.name + N']' + CHAR(9) + CHAR(10)

+ 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY);'

+ CHAR(9) + CHAR(10) AS [data()]

FROM sys.master_files mf

INNER JOIN sys.databases d ON mf.database_id = d.database_id

AND d.recovery_model = 3

AND d.database_id > 4

AND d.user_access = 0

AND d.state = 0

AND mf.type = 1

FOR XML PATH('')

)

EXECUTE sp_executesql @DynTSQL

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

EXECUTE [dbo].[DatabaseBackup] @Databases = 'DATABASENAME', @Directory = N'C:\Backup', @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'

1	EXECUTE [dbo].[DatabaseBackup] @Databases = 'DATABASENAME', @Directory = N'C:\Backup', @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'

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.

DELETE FROM [dbo].[CommandLog] WHERE StartTime < DATEADD(dd,-30,GETDATE())

1	DELETE FROM [dbo].[CommandLog] WHERE StartTime < DATEADD(dd,-30,GETDATE())

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

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Générer un jeu de données aléatoire

24 heures Hop francophone : 29 et 30 juin 2017

sp_change_users_login : Utilisateurs orphelins

Resource Governor

Blog