Principes d’une sauvegarde à chaud

Rien de plus simple de que faire une sauvegarde sur SQL Server:

BACKUP DATABASE maBase to disk='D:\MSSQL.1\MSSQL\Backup\maBase.bak' with init, stats

10 percent processed.

21 percent processed.

32 percent processed.

43 percent processed.

54 percent processed.

60 percent processed.

71 percent processed.

82 percent processed.

93 percent processed.

Processed 144 pages for database 'maBase', file 'maBase' on file 1.

100 percent processed.

Processed 2 pages for database 'maBase', file 'maBase_log' on file 1.

BACKUP DATABASE successfully processed 146 pages in 0.816 seconds (1.463 MB/sec).

Et voilà. L’avantage d’une telle sauvegarde est que je peux la lancer sans stopper mon service, sous entendu avec de l’activité: connexions utilisateurs, transactions en cours, etc… Et tout ça en me garantissant de restaurer une base transactionnellement cohérente. Pour y parvenir, elle commence par sauvegarder les données puis les transactions présentes dans le journal.

Avant de parler de ce que fait le BACKUP DATABASE, il faut parler un peu des rôles respectifs du CHECKPOINT et du COMMIT dans le processus de RECOVERY.

i)Lorsqu’un utilisateur initie une transaction, ses modifications sont d’abord journalisées en mémoire dans une zone qui est réservée pour sa session. Puis les pages de données et d’index concernées sont modifiées dans le cache de données.

ii)Lorsqu’il valide sa transaction de manière implicite ou explicite (avec COMMIT TRANSACTION), le contenu de son journal de session est enregistré physiquement dans le fichier LDF. Il est impératif de s’assurer que le COMMIT ne peut rendre la main à la commande appelante que lorsque la transaction a été véritablement écrite sur disque (le ‘write-ahead logging’ en d’autres termes). Ce mécanisme permet de pouvoir récupérer la transaction validée lorsqu’un problème survient avant que les pages de données n’aient pu être écrites.

iii)De manière complètement indépendante et asynchrone, le processus d’arrière plan CHECKPOINT écrit les pages de données et d’index modifiées (dirty pages) sur le disque, sans se préoccuper de savoir si elles appartiennent à une transaction validée ou non.

A partir de là, deux situations peuvent se produire au point de crash:

1)Une transaction peut être validée (committée) par l’utilisateur mais le CHECKPOINT n’a pas eu le temps d’écrire les pages de données / d’index sur disque. Il va falloir réécrire ces pages dans le fichier de données au redémarrage de l’instance (REDO ou ROLL-FORWARD).

2)Le CHECKPOINT qui n’a pas de vision sur l’état de la transaction peut avoir écrit ses pages sur disque avant qu’elle n’ait été validée par l’utilisateur. Auquel cas il faudra défaire les pages écrites car au point de crash car la transaction ne peut être considérée comme valide. (UNDO ou ROLLBACK).

C’est la combinaison de ces deux actions que l’on appelle le RECOVERY. Ce processus intervient dans de nombreux changements d’état de la base, et notamment pour ce qui nous intéresse, lors de la phase de restauration d’une sauvegarde.

Lorsque le BACKUP DATABASE est exécuté, il commence par forcer l’exécution d’un CHECKPOINT dans la base concernée. Ce CHECKPOINT aura pour effet d’entériner des transactions validées depuis ma dernière sauvegarde de journal : Les pages de données / d’index sont écrites sur disque, la sauvegarde n’a donc plus besoin de s’en préoccuper. Bien sûr il peut aussi écrire les pages de données / d’index d’une transaction qui n’est pas encore validée. Immédiatement ensuite, il va relever un numéro de transaction (LSN pour Log Sequence Number)dans le journal pour s’en servir comme borne de départ pour le RECOVERY. Appelons-le LSN1.

Puis il va lire et écrire les pages de données depuis le fichier MDF ou les différents filegroups, et les écrire dans le fichier de sauvegarde. Une fois terminées ces écritures, il renote le LSN courant (qui entre temps peut avoirévolué), et s’en sert comme borne de fin (LSN2). Puis il va sauvegarder tout ce qui se trouve entre (LSN2 – LSN1). Certaines transactions seront validées, certaines ne seront peut être pas terminées, mais peut importe, car au moment de la restauration, le processus de RECOVERY fera son œuvre pour ramener la base à un état transactionnel consistant.

Si le LSN2 lui ne change pas car c’est toujours le dernier numéro généré dans le journal qui sera utilisé, le problème vient plus du calcul du LSN de départ car il va dépendre de la situation dans laquelle se trouve le BACKUP DATABASE au départ. Globalement, je peux me trouver dans deux cas :

–Soit il n’y a aucune transaction en cours au moment où commence la sauvegarde : auquel cas LSN1 sera égale à la valeur du LSN généré par le CHECKPOINT (lancé par le BACKUP):

L’effet du CHECKPOINT ici est de flusher les pages de données modifiées par T1, T2 et T3, pour ne pas à avoir à les prendre dans la sauvegarde du journal. Ces pages se retrouvent sur disque, et seront capturées lors de la copie de données.

LSN2 est toujours le LSN courant capté dès la fin de la copie de données. Dans ce cas, T4 est terminée mais on est en plein milieu de T5, donc on prend quand même ce ‘fragment’ de T5. Lors de la restauration, T4 sera réappliquée et T5 sera rollbackée:

Soit il yaura au moins une transaction en cours : auquel cas, SQL Server va aller rechercher le LSN de départ de la transaction active la plus ancienne. Dans l’exemple ci-dessous, il s’agit de T2:

Dans ce cas, T2 et T4 qui sont terminées avant la fin de la copie de données seront réappliquées et T5 et T3 rollbackées:

La table msdb.dbo.backupset qui stocke des données pour chaque sauvegarde effectuée permet de vérifier dans quel état se trouvait la base au moment de la sauvegarde. Pour le déterminer, il faut comparer les valeurs de checkpoint_lsn et first_lsn:

– checkpoint_lsn = first_lsn: alors il n’y avait pas de transaction en cours au moment du BACKUP DATABASE.

select first_lsn, checkpoint_lsn, last_lsn from msdb.dbo.backupset where database_name= ‘CAPDATA’ 

first_lsn checkpoint_lsn last_lsn 

-------------------- -------------------- -------------------- 

1341 0000000302 000511341 0000000302 000511341 0000000323 00001 

– first_lsn < checkpoint_lsn: alors il y avait au moins une transaction en cours.

select first_lsn, checkpoint_lsn, last_lsn from msdb.dbo.backupset where database_name= ‘CAPDATA’  first_lsncheckpoint_lsnlast_lsn 

-------------------- --------------- -------------------- 

1341 0000000510 00021341 0000000523 000981341 0000000563 00001

Continuez votre lecture sur le blog :

• Modes de récupération et journal de transactions, épisode 1 (David Baffaleuf) [SQL ServerVintage]
• How-To: réduire la taille du journal de transactions sur disque (David Baffaleuf) [SQL Server]
• Point-in-time recovery et fn_dump_dblog() (David Baffaleuf) [SQL Server]
• Modes de récupération et journal de transactions, épisode 2 (David Baffaleuf) [SQL ServerVintage]
• Modifier PAGE_VERIFY après une migration depuis SQL 2000 (David Baffaleuf) [SQL Server]