Les plans de maintenance SQL Server

Mise en place de plans de maintenance sur Sql Server.

Il y a longtemps maintenant que je ne fais plus de plans de maintenance graphiques avec les outils proposés par Sql Server.
Je préfère et de loin utiliser les plans de maintenance de Ola Hallengren car ils sont tout simplement excellents et en Open source !
Je vais donc vous montrer ici comment je les utilise généralement puis j’ajouterai quelques cas particuliers.

Je ne crée plus de base dédiée à l’administration pour les plans de maintenance.
Je les déploie systématiquement dans MSDB.

Les types de maintenances

Je définis généralement deux types de maintenances.
1-La maintenance hebdomadaire.
2-La maintenance quotidienne.

La maintenance hebdomadaire effectue les tâches suivantes :
1-Vérification de l’intégrité.
2-Maintenance des index et des statistiques.
3-Backup Full des bases système et des bases utilisateur.
La maintenance quotidienne, elle, effectue les tâches suivantes :
1-Maintenance des index et des statistiques *.
2-Backup Full des bases système.
3-Backup Diff des bases utilisateur.
Pour ce qui est des planifications, chaque cas est particulier donc je n’aborde pas le sujet.

*Pour la maintenance quotidienne, la mise à jour des statistiques est suffisante dans la majorité des cas, la maintenance des index elle, peut n’être faite qu’une fois par semaine.

Exemple de maintenance hebdomadaire dite (FULL).

La vérification d'intégrité.

La vérification de l’intégrité est définie dans les premières étapes du JOB SQL hebdomadaire.
Il est aussi possible de séparer le traitement des bases de données système dans des JOB’s dédiés.
Je n’entre pas dans ce niveau de détail, c’est à chacun de décider.

				
					-- CHECKDB sur les bases système (par défaut)
EXECUTE [msdb].[dbo].[DatabaseIntegrityCheck]
@Databases = 'SYSTEM_DATABASES',
@LogToTable = 'Y';

-- CHECKDB sur les bases utilisateur (par défaut)
EXECUTE [msdb].[dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y';

-- CHECKDB sur les bases utilisateur (physical_only)
EXECUTE [msdb].[dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@PhysicalOnly = 'Y',
@LogToTable = 'Y';

-- CHECKDB sur les bases utilisateur (physical_only, data_purity)
EXECUTE [msdb].[dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@PhysicalOnly = 'Y',
@DataPurity = 'Y',
@LogToTable = 'Y';

/*
Depuis SQL 2014, le CHECKDB se fait dans tempdb (plus de Snapshot fantôme qui ne fonctione pas avec REFS) 
donc il faut avoir l'espace requis pour vérifier l'intégrité d'une base de données dans tempdb.
L'intéret de la PROC de Ola et que l'on peut faire le CHECK à des niveaux plus fins pour contourner ce problème.
CHECKDB, CHECKFILEGROUP, CHECKTABLE
Les objects sont vérifiés séquentiellement, par exemple le CHECKTABLE s'effectue table par table.
*/
-- CHECKFILEGROUP sur les bases utilisateur (physical_only, data_purity)
EXECUTE [msdb].[dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKFILEGROUP',
@PhysicalOnly = 'Y',
@DataPurity = 'Y',
@LogToTable = 'Y';

-- CHECKTABLE sur les bases utilisateur (physical_only, data_purity)
EXECUTE [msdb].[dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKTABLE',
@PhysicalOnly = 'Y',
@DataPurity = 'Y',
@LogToTable = 'Y';

Il y a un grand nombre d’options disponibles.
Je vous invite à aller voir la documentation ici (Database integrity check).

La maintenance des index et statistiques.

La maintenance des index et statistiques doit faire l’objet d’une attention particulière car il y a une subtilité à savoir.
Ne seront mises à jour que les statistiques des index qui entrent dans les seuils de fragmentation.
Le Rebuild met à jour les statistiques implicitement, le Reorganize lui nécessite un Update des statistiques explicite.
Jusqu’ici tout va bien puisque c’est parfaitement géré par la PROC de Ola.

Mais ne seront traitées que les statistiques des index qui correspondent aux seuils de fragmentation spécifiés, à savoir dans le cas présent, les recommandations de Microsoft:
Fragmentation < 5% on ne fait rien, entre 5% et 30% on fait un Reorganize et au delà de 30% un Rebuild.
Or, la fragmentation n’a rien à voir avec la volatilité d’une table et de ses index.
Atteindre un seuil de fragmentation sur une table de plusieurs centaines de millions de ligne ou plus peut prendre du temps et dans ce cas le paramétrage spécifique pour les statistiques peut s’avérer très utile 😉

				
					-- Maintenance des index et statistiques, usage standard
EXECUTE [msdb].[dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PartitionLevel = 'Y',
@SortInTempdb = 'Y',
@MaxDOP = 0,
@LogToTable = 'Y',
@MSShippedObjects = 'Y';

-- Maintenance des statistiques des index hors seuils
EXECUTE [msdb].[dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y';

Et voilà, encore une fois la PROC de Ola permet de contourner ce problème facilement.
Il y a un grand nombre d’options disponibles pour la maintenance des index et statistiques.
Je vous invite à aller voir la documentation ici (Index and statistics maintenance).

Les Backups FULL, DIFF, LOG.

Pour finir notre tour d’horizon, regardons l’aspect sauvegardes (Backup).

				
					-- Les Backups FULL
EXECUTE [msdb].[dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = 'D:\Databases\Backups',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 370,
@Compress = 'Y',
@CheckSum = 'Y',
@NumberOfFiles = 4,
@LogToTable = 'Y';

-- Les Backups DIFF
EXECUTE [msdb].[dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = 'D:\Databases\Backups',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 370,
@Compress = 'Y',
@CheckSum = 'Y',
@NumberOfFiles = 4,
@LogToTable = 'Y';

-- Les Backups de LOG
EXECUTE [msdb].[dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = 'D:\Databases\Backups',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 370,
@Compress = 'Y',
@CheckSum = 'Y',
@NumberOfFiles = 4,
@LogToTable = 'Y';

*Le paramètre @CleanupTime est en heures, les anciens Backups seront supprimés passé ce délais.
Et voilà, encore une fois la PROC de Ola fait merveille.
Inutile de chercher ailleurs, tout y est et bien plus.
Il y a un grand nombre d’options disponibles pour les Backups.
Je vous invite à aller voir la documentation ici (SQL Server Backup).

L'ordonnancement des tâches.

Comme je le disait au début, je définis généralement deux types de maintenances.

La maintenance hebdomadaire effectue les tâches suivantes :
1-Vérification de l’intégrité (Stop job on Error).
2-Maintenance des index et des statistiques (Stop job on Error).
3-Backup Full des bases système et des bases utilisateur (Stop job on Error).
La maintenance quotidienne, elle, effectue les tâches suivantes :
1-Maintenance des index et des statistiques (Stop job on Error).
2-Backup Full des bases système.
3-Backup Diff des bases utilisateur.

Le Backup des LOG lui est dans un JOB à part et est planifié toutes les 10 minutes H24.

Les notifications.

Il est possible d’ajouter l’envoi de notifications en cas d’erreur, il faudra configurer Database Mail et ajouter un Opérateur pour que cela fonctionne.
Ci-dessous, un exemple de code avec l’envoi de notification.

				
					-- CHECKDB sur les bases utilisateur (par défaut) avec notification
Begin Try
	EXECUTE [msdb].[dbo].[DatabaseIntegrityCheck]
	@Databases = 'USER_DATABASES',
	@LogToTable = 'Y';
End Try
Begin Catch
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

	DECLARE @INSTANCE_NAME nvarchar (50)
	DECLARE @MailBody  NVARCHAR(MAX)
	, @Operator nvarchar(1000), @MailSubject nvarchar(1000);

	Declare @Date DateTime;

	SET  @INSTANCE_NAME=CONVERT(NVARCHAR(50),SERVERPROPERTY('MachineName')) +
		CASE
			WHEN (SERVERPROPERTY('InstanceName') IS NOT NULL) THEN '.'+ CONVERT(NVARCHAR(50),SERVERPROPERTY('InstanceName'))
			ELSE '\MSSQLSERVER'
		END

	Set @MailSubject = (@INSTANCE_NAME + ' - Integrity Check error');
	Set @Operator = N'My Operator';
	Set @MailBody = N'Error in USER_DATABASES Integrity Check!'
	+CHAR(13)+Cast(@Date as nvarchar)+CHAR(13)+@ErrorMessage;

	EXECUTE msdb.dbo.sp_notify_operator
	@name=@Operator,
	@subject=@MailSubject,
	@body=@MailBody;

	--Lève une Exception pour faire planter le JOB (le Throw peut aussi être utilisé à la place du Raiserror)
	Raiserror('Error in Integrity Check!', @ErrorSeverity, @ErrorState) with nowait;
End Catch

Conclusion.

Comme vous pouvez le constater, il est relativement simple de mettre en place un PRA en FULL SQL.
L’avantage de la gestion des Backups via la PROC de Ola est de pouvoir Spliter les Backups sur plusieurs fichiers ce qui accélère beaucoup les temps de Backup, Verify, et Restore.
A ma connaissance, les outils de Backup en VDI ne proposent pas ce type d’optimisation (mais je peux me tromper).

Bon PRA avec la solution de Ola Hallengren ;-).