SQL Server : Les modes de récupération de Bases de données (Et les mythes autour du mode simple)
Existant officiellement depuis la version 2000 sous les noms qu'on leur connait aujourd'hui, leurs caractéristiques existaient auparavant avec SQL Server 7 entre autres sous formes d'options de bases de données (tel qu'activer ou non les opérations faiblement journalisées).
A partir de la version 2000, ils sont aux nombre de 3 :
- Journalisation Complete : « Full »
- Journalisation pour mises à jour par lot : « Bulk_Logged »
- Journalisation Simple : « Simple »
Ce mode de récupération se paramètre au niveau de la base de données, à l'exception de certaines bases de données système (tempdb, qui est et reste en mode simple).
A partir de SQL Server 2000, le changement se fait par :
ALTER
DATABASE MaBase SET
RECOVERY
{
FULL
|
BULK_LOGGED
|
SIMPLE
}
Ces modes de récupération définissent la manière dont se comporte le moteur de base de données pour remplir le journal de transaction. On peut se dire que l'impact de ce comportement sur les performances est direct, eh bien oui et non.
Mode de récupération complet / Full.
Passage en mode récupération complet :
ALTER
DATABASE MaBase SET
RECOVERY
FULL
Attention le passage en mode récupération complet nécessite obligatoirement d'exécuter une sauvegarde complète après l'exécution de la commande précédente si vous étiez au préalable en mode de récupération simple. A la création d'une nouvelle base de données ayant le mode de récupération complet, tant qu'une sauvegarde complète n'est pas exécutée le comportement est celui du mode récupération simple !
Dans ce mode toute opération est journalisée de la même manière sans exception. C'est-à-dire qu'un INSERT ira écrire la même quantité d'information qu'un SELECT / INTO ou un BULK INSERT. Il n'y a donc en termes de performance aucunes différences entre ces commandes pour le chargement des données dans une table.
Idem pour les commande de création et de maintenance d'index, elles sont 100% journalisées, donc généralement assez lente et déconseillé dans ce mode hors des fenêtres de maintenance prévues à ces effets.
A noter tout de même que le TRUNCATE TABLE, qui est par définition non journalisé est aussi faiblement journalisé en mode Complet. En fait la seule contrainte d'un TRUNCATE TABLE est de pouvoir réaliser un ROLLBACK dans une transaction. Si cela est nécessaire, cette opération verrouillera les données de la table jusqu'à la fin de la transaction. Cette opération est donc, quelque que soit le mode récupération la plus efficace pour vider une table de son contenu.
Il a des contraintes au niveau de SQL Server qui vous forceront à rester dans ce mode de récupération. Citons entre autres la mise en place d'un miroir (mirroring).
Mode de récupération pour mises à jour par lot / Bulk Logged.
Passage en mode récupération faiblement journalisé (BULK LOGGED):
ALTER
DATABASE MaBase SET
RECOVERY
BULK_LOGGED
Dans ce mode, un certain nombre d'opérations sont dites faiblement journalisées :
- SELECT / INTO
- BULK INSERT sur une table vide (et ses cousines : bcp, FastLoad, etc.)
- CREATE INDEX (et par extension clef primaire et contrainte unique)
- DBCC DBREINDEX / ALTER INDEX REBUILD
- DROP INDEX (clustered index)
- Ecriture ou modification d'un LOB (Large OBject, tel que varbinary(max) ou vachar(max) s'ils font plus de 8ko).
A cette liste s'ajoute à partir de SQL Server 2008 et à condition d'utiliser le TraceFlag 610 (http://blogs.codes-sources.com/christian/archive/2009/09/11/sql-server-quelques-trace-flags-utiles.aspx) au niveau serveur :
- BULK INSERT sur table déjà remplie (et ses équivalentes)
- INSERT sous certaines conditions
Dans ce mode, les opérations faiblement journalisées n'écrivent plus dans le journal de transactions leur progression (tout au moins le début et la fin de l'opération y sont consignés). Au lieu de cela, des pages systèmes contiennent la liste des extensions (bloc de 8 pages, soit 64 ko) modifiées par ces opérations via un bit dans ce type de page = 1 extension modifiée.
La caractéristiques des opérations faiblement journalisées est d'être facilement re-éxécutable sans avoir à consigner la progression de la modification des données. En effet le chargement d'un fichier texte par BULK INSERT nécessiterait juste de recharger ce même fichier avec la même commande. La création d'index nécessitera la même commande, etc.
Les données ainsi impactées sont copiées directement au moment de la sauvegarde du journal de transactions dans le fichier de sauvegarde. A aucuns moment ces données sont dans le journal de transactions lui-même elles sont exclusivement dans les fichiers de données !
BACKUP
LOG mabase TO
DISK
=
'chemin\fichier'
Après cette commande, les pages systèmes permettant de suivre les opérations faiblement journalisées sont réinitialisées. Il est très fortement conseiller d'exécuter une sauvegarde du journal de transaction après chaque opérations de type listé plus haut, car sinon en cas de perte d'un ou plusieurs fichiers de données la récupération de la base de données pourrait être difficile ou compromise. En complément dans ce mode, pensez à garder vos fichiers d'import au moins jusqu'au sauvegarde différentielles ou complète, par précaution.
A noter que la journalisation faible pour les insertions de données se fait à quelques conditions strictes :
- La table de destination des données doit être vide
- La table doit être verrouillée à l'aide d'un verrou de type table (TABLOCK)
- Ne s'applique qu'au BULK INSERT et opération similaires (en aucun cas à l'INSERT)
A partir de SQL Server 2008 et à condition d'activer le TraceFlag 610 :
Les conséquences de tout çà sont que seules les applications prévues pour optimiser ce type de traitement de données peuvent en profiter (utilisation explicite du BULK INSERT et du TABLOCK) et que ce n'est généralement pas juste en passant dans ce mode de récupération que les gains vont être visibles. Par contre en termes de développement, il est mieux de réaliser un développement utilisant ce type de commande, des SELECT / INTO au cas où le mode de journalisation serait BULK_LOGGED ou SIMPLE.
Mode de récupération simple / Simple.
Passage en mode récupération simple:
ALTER
DATABASE MaBase SET
RECOVERY
SIMPLE
C'est le mode sur lequel il y a le plus de préjugé. En effet ce mode est identique en termes de performance au précédent ! Il n'y a aucune différence sur la manière de journaliser les opérations dans ce mode vis-à-vis du BULK_LOGGED.
La différence entre ce mode et le précédent, vient de la gestion interne du journal de transaction. Là où en mode de récupération BULK_LOGGED il faut sauvegarder le journal de transaction, le mode SIMPLE lui simplifie la gestion en exécutant automatiquement des opérations tronquant le contenu du journal. A chaque fois que les données stockées en mémoire sont inscrites dans les fichiers de données, le journal est tronqué.
En pratique dans ce mode de récupération ont augmentera la cadence des sauvegardes complètes et différentielles pour pallier l'absence de sauvegarde des journaux de transactions.
Quelques clarifications sur ce mode :
- Il n'est pas plus performant que le BULK_LOGGED
- Le journal de transaction peut grossir dans ce mode, car les transactions courantes l'utilisent
- La réduction du journal de transaction n'est pas automatique
- Les pertes de données sont possibles dans ce mode, mais à condition qu'un fichier de données ou le journal de transaction soit corrompu
- Les DELETE sont totalement journalisés… Il n'existe pour le moment aucunes alternatives à ce comportement
Modes particuliers
Il existe et existera, 2 modes particuliers supplémentaires. Ils ne sont à proprement parlé pas des modes utilisable explicitement sur une base de données
Le premier est celui de tempdb, cette base de données est bien entendue en mode récupération simple. En réalité depuis SQL Server 2005, ce base de données est moins journalisée que les modes BULK_LOGGED et SIMPLE, en effet on part du principe que si le moteur de base de données est redémarré cette base de données est tout simplement recrée vide. Dès lors seuls les informations nécessaires à l'exécution des transactions (phase d'annulation, dite Undo) sont nécessaires, les informations nécessaire au démarrage (phase de Redo, qui ré-exécute les transactions dont les données n'ont pas été écriture dans les fichiers de données à l'arrêt de l'instance) de chaque base de données est de fait inutile pour tempdb.
De plus tempdb, bénéficie d'une optimisation concernant la mise en cache de ses table et une réutilisation des structures en cas de DROP / CREATE fréquent. Ce qui la rend plus perfomante en contrepartie de quoi la perte de données est garantie en cas de redémarrage de l'instance.
L'autre est un mode, dit SUPPLEMENTAL_LOGGING, en fait ce mode n'a tout simplement pas été mis en place pour le moment dans SQL Server. Il est apparu dans les premières versions de la documentation en ligne de SQL Server 2008 et a vite été retiré. Peut-être verra-t-il le jour dans une version ultérieure du moteur, son rôle devant être similaire à celui des moteurs concurrent, on ajoute un certain nombre d'information du journal, ceci pour aider les outils tiers lisant ce dernier.
J'essaye de faire un comparatif de performance entre ces modes dans un prochain billet.
Bonne récupération...
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
