Best practices N° 1 : tâches de maintenance

Par Arian Papillon le vendredi 22 septembre 2023, 09:40 - Lien permanent

A l'installation d'une instance SQL Server, il y a au minimum quelques "best practices" à appliquer.
Cette série d'articles décrit les principales configurations indispensables et les moyens de les mettre en place avec de simples requêtes SQL : tâches de maintenance, alertes, notifications par email, configuration de l'instance, ...

Nous commençons ici par la configuration des tâches de maintenance.

Mettre en place les tâches de maintenance : backup, check d'intégrité, optimisation d'index.

Pour cela les scripts du MVP suédois Ola Hallengren sont les meilleurs (et bien plus puissants que les plans de maintenance intégrés à Management Studio). Ils sont utilisés et recommandés quasi unanimement par la communauté d’experts et MVP renommés : Jonathan Kehayias (SQLSkills), Brent Ozar (brentozar.com), Paul Randall (SQLSkills), Benjamin Nevarez (SQLSkills), Tibor Karaszi, etc…

Ils sont régulièrement maintenus par leur auteur depuis janvier 2008 et prennent en charge les versions de SQL Server 2005 à 2022 (non, pas 2000… !)

Le script crée seulement 4 procédures stockées qui gèrent l’ensemble des tâches de maintenance, avec nombre d’options utiles entièrement documentées.

  • DatabaseBackup : backups de tous types
  • DatabaseIntegrityCheck : intégrité
  • IndexOptimize : index et statistiques
  • CommandExecute : procédure de lancement des commandes

Comment faire :

USE [master] -- Specify the database in which the objects will be created.

SET NOCOUNT ON

DECLARE @CreateJobs nvarchar(max)          = 'Y'         -- Specify whether jobs should be created.
DECLARE @BackupDirectory nvarchar(max)     = NULL        -- Specify the backup root directory. If no directory is specified, the default backup directory is used.
DECLARE @CleanupTime int                   = NULL        -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.
DECLARE @OutputFileDirectory nvarchar(max) = NULL        -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
DECLARE @LogToTable nvarchar(max)          = 'Y'         -- Log commands to a table.
  1. Base de données où créer les objets de maintenance : personnellement, je préfère remplacer [master] par [msdb]
  2. @CreateJobs : par défaut, des travaux de l'agent SQL Server seront créés et vous n'aurez plus qu'à les planifier
  3. @BackupDirectory : si non renseigné, les sauvegardes sont créées dans le dossier de backup par défaut, sinon indiquer le chemin de remplacement
  4. @CleanupTime : indiquer en heures la rétention des sauvegardes sur le disque
  5. @OutputFileDirectory : si non renseigné, les tâches créeront des rapports (fichiers texte) dans le dossier log par défaut, sinon indiquer le chemin de remplacement
  6. @LogToTable : par défaut, toutes les opérations effectuées seront journalisées dans une table nommée CommandLog
  • Exécutez le script (en tant qu'administrateur SQL Server, bien sûr).
  • Vous n'avez plus qu'à planifier chacun des 11 travaux de maintenance :
  1. DatabaseIntegrityCheck - SYSTEM_DATABASES
  2. DatabaseIntegrityCheck - USER_DATABASES
  3. IndexOptimize - USER_DATABASES
  4. DatabaseBackup - SYSTEM_DATABASES - FULL
  5. DatabaseBackup - USER_DATABASES - FULL
  6. DatabaseBackup - USER_DATABASES - DIFF
  7. DatabaseBackup - USER_DATABASES - LOG
  8. CommandLog Cleanup
  9. Output File Cleanup
  10. sp_delete_backuphistory
  11. sp_purge_jobhistory

Pourquoi pas planifier tout cela avec des commandes SQL ? Par exemple (modifiez selon votre convenance) :-- Integrity Check - SYSTEM_DATABASES : 23:00

DECLARE @schedule_id INT
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseIntegrityCheck - SYSTEM_DATABASES')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES', @name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES',
        @enabled=1,
        @freq_type=4, -- Daily
        @freq_interval=1, -- Once
        @freq_subday_type=1, -- at specified time
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20240101,
        @active_end_date=99991231,
        @active_start_time=230000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Integrity Check - USER_DATABASES : 23:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseIntegrityCheck - USER_DATABASES')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseIntegrityCheck - USER_DATABASES', @name=N'DatabaseIntegrityCheck - USER_DATABASES',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20210101,
        @active_end_date=99991231,
        @active_start_time=230500,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Index Optimize : 00:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'IndexOptimize - USER_DATABASES')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'IndexOptimize - USER_DATABASES', @name=N'IndexOptimize - USER_DATABASES',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20210101,
        @active_end_date=99991231,
        @active_start_time=000000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Backups Full SYSTEM_DATABASES : 01:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseBackup - SYSTEM_DATABASES - FULL')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseBackup - SYSTEM_DATABASES - FULL', @name=N'DatabaseBackup - SYSTEM_DATABASES - FULL',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20220207,
        @active_end_date=99991231,
        @active_start_time=010000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Backups Full USER_DATABASES : 01:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseBackup - USER_DATABASES - FULL')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseBackup - USER_DATABASES - FULL', @name=N'DatabaseBackup - USER_DATABASES - FULL',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20220207,
        @active_end_date=99991231,
        @active_start_time=010500,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT


-- Backups differential - USER_DATABASES : 12:30
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseBackup - USER_DATABASES - DIFF')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseBackup - USER_DATABASES - DIFF', @name=N'DatabaseBackup - USER_DATABASES - DIFF',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20220207,
        @active_end_date=99991231,
        @active_start_time=123000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Backups log - USER_DATABASES : hourly between 5:15 and 1:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseBackup - USER_DATABASES - LOG')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseBackup - USER_DATABASES - LOG', @name=N'DatabaseBackup - USER_DATABASES - LOG',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=8,
        @freq_subday_interval=1,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20220207,
        @active_end_date=99991231,
        @active_start_time=051500,
        @active_end_time=010000, @schedule_id = @schedule_id OUTPUT

-- All Cleanup tasks : Daily 5:30
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'Maintenance History Cleanup')
BEGIN
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'CommandLog Cleanup', @name=N'Maintenance History Cleanup',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20210101,
        @active_end_date=99991231,
        @active_start_time=053000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
EXEC msdb.dbo.sp_attach_schedule @job_name=N'Output File Cleanup',@schedule_id=@schedule_id
EXEC msdb.dbo.sp_attach_schedule @job_name=N'sp_delete_backuphistory',@schedule_id=@schedule_id
EXEC msdb.dbo.sp_attach_schedule @job_name=N'sp_purge_jobhistory',@schedule_id=@schedule_id
END