Modèle de récupération SQL Server : pensez récupération, pas espace disque

L’alerte arrive tôt le matin : le disque qui héberge une base de données de production est plein, et l’application ne peut plus écrire. Le coupable est un fichier de journal des transactions qui a atteint des centaines de gigaoctets. Une recherche en ligne suggère une porte de sortie facile et, en quelques minutes, la base de données passe au modèle de récupération SIMPLE, le journal est réduit et le système est de nouveau en ligne. Personne ne remarque qu’une décision beaucoup plus importante vient d’être prise au passage. Avec une seule sauvegarde par nuit, l’entreprise a discrètement accepté de pouvoir perdre jusqu’à une journée complète de transactions, sans que quiconque en dehors de la salle des serveurs ait été consulté.

Pourquoi le journal des transactions remplit-il le disque?

SQL Server inscrit les modifications dans le journal des transactions avant qu’elles atteignent les fichiers de données. Le sort de ces enregistrements dépend ensuite du modèle de récupération, un paramètre propre à chaque base de données. En mode SIMPLE, le moteur réutilise automatiquement l’espace du journal dès qu’il n’est plus nécessaire. En mode FULL, le journal conserve chaque modification jusqu’à une sauvegarde du journal, parce que ces enregistrements permettent une restauration à un moment précis. Sa proche variante, BULK_LOGGED, retient le journal de la même façon, mais journalise au minimum la plupart des opérations en bloc. Lorsqu’une base de données en mode FULL reçoit des sauvegardes complètes mais aucune sauvegarde du journal, rien ne libère cet espace, et le fichier grossit jusqu’à atteindre sa taille maximale ou remplir le disque. Les modèles de récupération existent depuis SQL Server 2000, et pourtant nous trouvons régulièrement des bases de données dont personne n’a choisi le modèle de récupération. Chaque base de données créée sur une instance hérite de son paramètre de la base système model, si bien que la configuration reflète souvent une valeur par défaut plutôt qu’une décision.

Que coûte réellement le passage au mode SIMPLE?

Quand le journal attend seulement une sauvegarde, passer au mode SIMPLE règle bel et bien le problème de disque, et c’est exactement ce qui le rend dangereux. En mode SIMPLE, une base de données ne peut être restaurée qu’à la fin de sa sauvegarde la plus récente, de sorte qu’aucune modification effectuée depuis n’est protégée. Avec une seule sauvegarde complète par nuit, cela peut représenter une journée de commandes, de factures et de données de production à saisir de nouveau à la main, à condition de pouvoir les reconstituer. Il devient impossible de restaurer à la minute précédant une mise à jour erronée. Le changement brise aussi la chaîne de sauvegardes du journal : aucune sauvegarde du journal ne peut faire avancer la base de données au-delà de ce changement tant qu’elle n’est pas revenue en mode FULL et qu’une nouvelle sauvegarde complète ou différentielle n’a pas été effectuée. Imaginez un fabricant dont le système de commandes tombe en panne l’après-midi même : la restauration ramène les données de la veille au soir, et l’entreprise découvre une promesse de récupération qu’elle avait faite sans le savoir.

Comment choisir le bon modèle de récupération?

Le bon modèle de récupération est la réponse technique à deux questions d’affaires. Combien de données pouvons-nous nous permettre de perdre, ce qu’on appelle l’objectif de point de récupération, ou RPO? En combien de temps devons-nous être de nouveau opérationnels, ce qu’on appelle l’objectif de temps de récupération, ou RTO? Ces réponses appartiennent aux personnes responsables du processus d’affaires, pas seulement à la personne qui gère le serveur. Une base de données qui ne tolère que quelques minutes de perte fonctionne en mode FULL, avec des sauvegardes du journal au moins à cette fréquence. Une copie destinée aux rapports, reconstruite chaque nuit à partir de ses sources, peut sans risque fonctionner en mode SIMPLE, puisque la perdre ne coûte qu’un rechargement. La réponse est rarement la même dans tout un environnement : un environnement sain documente donc des cibles pour chaque base de données, y aligne le modèle de récupération et les sauvegardes, et teste les restaurations pour prouver que ces cibles sont atteignables. L’espace disque devient alors une question de dimensionnement planifiée à l’avance, et non le déclencheur d’une décision de récupération.

Comment remettre un environnement existant en ordre?

Bien faire les choses demande une séquence réfléchie plutôt qu’un grand projet. Premièrement, nous dressons la liste de chaque base de données avec son modèle de récupération, tiré de sys.databases, et les dates de ses dernières sauvegardes complète et du journal, tirées de l’historique des sauvegardes que SQL Server conserve dans msdb. Deuxièmement, nous convenons avec les responsables d’affaires d’un RPO et d’un RTO pour chaque base de données, en commençant par les systèmes qui génèrent des revenus. Troisièmement, nous planifions des sauvegardes du journal là où le mode FULL est requis et n’utilisons le mode SIMPLE que là où la tolérance convenue le permet. Lorsqu’une base de données passe de SIMPLE à FULL, nous effectuons immédiatement une sauvegarde complète ou différentielle, parce que le changement ne prend effet qu’après cette première sauvegarde des données. Quatrièmement, une fois que les sauvegardes du journal s’exécutent de façon fiable, nous redimensionnons une seule fois tout fichier journal surdimensionné, puis nous n’y touchons plus, puisqu’après chaque réduction, le fichier finit par grossir de nouveau. Enfin, nous testons une restauration par rapport à chaque cible, parce qu’un objectif de récupération jamais testé n’est qu’un espoir.

Au fond, il s’agit moins d’un paramètre de configuration que du moment où la conversation sur la récupération a lieu. Passer en mode SIMPLE une fois le disque plein est un choix réactif, fait sous pression par la personne de garde. La position la plus solide consiste à fixer les cibles de récupération avant le moindre incident et à traiter la croissance du journal comme un signal de routine plutôt que comme une urgence. Quand le modèle de récupération reflète une décision que l’entreprise a réellement prise, un disque plein devient un simple événement d’entretien plutôt qu’un pari caché, et tout le monde sait ce qu’une restauration ramènera. Cette certitude vaut bien plus que l’espace disque qu’elle coûte.

FAQ

Pourquoi le journal des transactions SQL Server continue-t-il de grossir?
Une cause fréquente est une base de données en mode FULL sans sauvegardes régulières du journal. Des transactions de longue durée, la réplication ou un réplica de groupe de disponibilité en retard peuvent aussi bloquer la réutilisation du journal. SQL Server en indique la raison dans la colonne log_reuse_wait_desc de sys.databases.

À quoi sert le modèle de récupération BULK_LOGGED?
BULK_LOGGED est une variante de FULL qui journalise au minimum la plupart des opérations en bloc, comme les chargements massifs de données. Il exige tout de même des sauvegardes du journal, et une sauvegarde du journal qui contient des opérations en bloc ne peut être restaurée que jusqu’à sa fin, pas à un moment précis à l’intérieur.

Toutes les bases de données SQL Server doivent-elles utiliser le modèle de récupération FULL?
Non, parce que le bon choix dépend de la quantité de données que chaque base peut se permettre de perdre. Les groupes de disponibilité Always On exigent le mode FULL, et la copie des journaux de transaction exige FULL ou BULK_LOGGED, alors qu’une base de données intermédiaire reconstruite à partir de ses sources peut souvent fonctionner en mode SIMPLE.

Vous ne savez pas où en est votre environnement? Réservez une conversation gratuite de 30 minutes avec notre équipe.