Difficile de parler de sauvegarde et encore plus de restauration de base de données (BDD) dans SQL serveur sans parler du mode de récupération utilisé par les bases dans ce SGBD (Système de Gestion de Base de Données).
Ce article me permet de faire suite au post de notre ami Laurent V., sur le blog Dotnet France, qui nous à déjà décortiqué la première partie et que je vous conseille de lire ICI.
SQL serveur et ses trois modes de récupérations:
- Mode Simple
- Mode Bulk-LOGGED (Journalisé en bloc)
- Mode Complet
Avant de rentrer dans les détails des sauvegardes/restaurations, modes de récupérations et autres optimisations possibles, faisons un petit rappel sur ce que représente physiquement sur le système de fichiers une base de données SQL.
- Petit rappel des éléments d’une base de données:
Une base de donnée est constituée d’au moins deux fichiers ‘dans sa conception la plus basique’.
- Un fichier à extension MDF (Master Data File) qui contient à proprement parlé les données (Tables, Vues, Données, Procédures Stockées et autres fonctions… )
- Un fichier à extension LDF (Log Data File) qui lui ne contient que les transactions que reçoit le serveur SQL pour cette base de donnée avant de les écrire physiquement dans le fichier MDF.
Toutes les commandes que reçoit le serveur SQL sont des transactions (il en existe deux types: Implicite et Explicite)
- Examen d’une transaction:
- Inspection d’un fichier de donnée:
Comment un fichier MDF est t’il structuré????
SQL Serveur stocke ses données par plage de 64 ko (C’est ce que l’on appel une ‘Extension’), c’est l’unité de base dans laquelle l’espace est géré. Dans une ‘Extension’ on y retrouve 8 pages de 8ko contigües (8 x 8ko = 64 ko)
Autrement dit, SQL serveur contient :
-
Chaque page (de données) commence par un en-tête de 96 octets (Stocke les informations systèmes relatives à la page)
-
128 pages par mégaoctet
-
16 extensions par mégaoctet
-
Une ligne de donnée ne peut pas dépasser 8060 octets
-
Les lignes de donnée sont placées de manière séquentielle dans le fichier (Comme ci-dessous)
(Détail d’une page de données)
Le site MSDN pour plus de détails sur les pages et les extensions (http://msdn.microsoft.com/fr-fr/library/ms190969(SQL.90).aspx)
- Inspection d’un fichier de logs
C’est bien compliqué un fichier de donnée MDF, heureusement les fichiers de journalisation (LDF) sont un peu plus simple, ou presque…
En fait, les fichiers LDF ne possèdent pas de pages mais uniquement une série d’enregistrements de fichiers journaux.
*********************************************
Voilà, le décore est planté, vous en savez un peu plus sur les fichiers de base de données SQL Serveur, nous pouvons passer à la suite des opérations, les modes de récupérations de SQL Serveur qui déterminent les moyens à votre disposition pour la récupération de vos données.
*********************************************
Pour faire simple… En mode de récupération SIMPLE, SQL Server ne journalise que les transactions durant une certaine période. En fait le log n’est plein que pendant les phases entre les différents ‘Check Point’ (Point de Contrôle – moment ou SQL Serveur écrit dans le fichier de donnée (MDF).
En fait le mode simple, ressemble (ou correspond) à l’instruction T-SQL:
TRUNCATE LOG ON CHECKPOINT
qui tronque le fichier log après la publication de chaque point de contrôle. Pour ainsi dire, que le fichier LOG (LDF) est quasi vide en permanence en théorie. Donc, il est impossible d’utiliser celui-ci pour des opérations de restaurations. d’ailleurs, il est impossible de sauvegarder le journal dans SQL Server en mode simple.
Dans ce mode, SQL Server commence à journaliser les instructions et les fichiers de logs peuvent commencer à être utilisé pour la restauration des données. Seulement, petit hic!!! SQL Server journalise de manière minimal certaines opérations, notamment les opérations d’insertion en bloc (ou en masse) comme SELECT INTO ou BULK INSERT.
Donc dans ce mode récupération, vous pouvez restaurer les bases de données jusqu’à la fin de la sauvegarde du journal… En fait seul les occurrences des transactions, les extensions des données modifiées sont journalisé pour les opérations de masse. Donc impossible de restaurer les données à un point dans le temps ou à une transaction MARK.
Comme son nom l’indique…. Il est complet. C’est le mode qui vous permet de sauvegarder à la fois les données et les journaux de logs pour pouvoir restaurer celles-ci en fonction de vos besoins. Voir même jusqu’à un point dans le temps. C’est la méthode de restauration la plus fine pour les données.
Seul petit point noir au tableau, c’est que le SQL Server ne tronque jamais le journal… Ce qui implique une croissance de taille très rapide du fichier de log. Ce qui implicitement nécessite le plus de gestion administrative.
Pour définir le mode récupération de vos bases, vous pouvez utiliser ‘Enterprise Manager’ pour SQL 2000, ou SSMS (SQL Server Management Studio) dans SQL Server 2005. Autre méthode, qui fonctionne dans les deux cas…
ALTER DATABASE Nom_Base SET RECOVERY {FULL|SIMPLE|BULK_LOGGED}
En résumé:
| |
Avantages
|
Inconvénients
|
|
Mode Simple |
Taille du fichier LOG
|
Sauvegarde Complete ou différentiel pour restaurer les données. Peut provoquer une perte des données importantes en cas de sinistre important, puisque les données ne peuvent-être restauré qu’à la dernière sauvegarde complète ou différentiel.
|
Mode Bulk-LOGGED |
Performances lors des opérations d’insertion en masse. Minimise la taille du journal pour ces opérations.
|
Pas de possibilité d’utiliser le journal pour les restaurations à un point dans le temps ou à une MARK.
|
|
Mode Complet |
Restauration plus fine en cas de sinistre. Reprise des données plus précise, voir quasiment au point de défaillance en cas de crash.
Réduit les temps de restauration.
|
Taille du fichier LOG plus importante. Charge administrative plus importante.
Temps de sauvegarde plus long
|
*********************************************
Voilà, rapidement pour les modes de récupération de SQL Serveur. Pour plus de détails sur les instructions de sauvegarde/Restauration, je vous conseille de lire l’article pré-cité plus haut ICI ou encore les liens ci-dessous.
http://msdn.microsoft.com/fr-fr/library/ms191253.aspx
http://msdn.microsoft.com/fr-fr/library/ms175199(SQL.90).aspx
*********************************************
Que dire, mis à part que SQL Serveur est très couteux en terme de performance et d’optimisation. Et je ne rentrerais pas dans les détails des optimisations possibles d’une base de données, mais uniquement en parlant de performances et optimisation physiques.
Vous l’avez compris SQL effectue la majeure partie de son job sur les données lorsque celle-ci son en mémoire… Donc, plus SQL serveur Serveur a de mémoire pour lui et plus il est heureux…
En fait, vous pourriez allouez 90 à 95 % de votre mémoire physique, en exagérant à peine, à SQL Serveur, car c’est lui qui fait la plus grosse partie du boulot. Quant je dis qu’il fait tout, il fait tout… Seul la partie ‘Réseaux’ est conservé au niveau du système d’exploitation Windows. SQL Serveur est quasiment un système d’exploitation à lui seul.
Ensuite, côté stockage physique, c’est souvent là ou le bât blesse.
Arrêtons de concevoir des bases de données qui font 300 Mo avec des taux de croissances de 20 à 25 %… C’est ridicule…
Aujourd’hui ce n’est plus le prix du Téra-Octet qui est devenu le plus couteux en terme financier.
Donc, concevez des bases de données avec une taille qui vous permettent d’envisager l’avenir et de tenir 2 à 3 ans avant de devoir faire croître votre fichier de données.
Un fichier de données c’est physique, les données y sont stockées de manière séquentielle sur le disque. Hors, que ce passe t’il lorsque SQL Serveur nécessite une croissance de son fichier de données, il va tout simplement augmenter la taille de celui-ci… Mais s’il y d’autres données déjà inscrite sur le disque est contigüe à ce fichier de données… Le système va placer la suite du fichier de données au premier emplacement libre sur le disque.
C’est le phénomène classique de la fragmentation Windows.
Pour éviter cela est pouvoir justement garder toutes les données du fichier de données SQL le plus contigüe possible, créez des fichiers de données de grande taille des la création de votre base, afin d’éviter au maximum cet effet de fragmentation du disque.
Quant au taux de croissance utilisé par SQL Serveur, il en est de même.
D’après-vous quand SQL Serveur nécessite t’il la croissance de son fichier de donnée???
Tapez:
-
Pour garder Grégoire dans la ferme
-
Pour que Loana revienne
-
Pour continuer
Bon, on a le droit de déconner un peu… Non?
Et bien en fait, SQL serveur fait croitre son fichier de donnée lorsqu’il n’a plus de place dans celui-ci pour y écrire les nouvelles données qu’il devra y inscrire.
Donc en résumé, ce n’est jamais lorsque SQL Serveur ne fait rien, qu’il attend, que celui-fait croitre la taille de son fichier, c’est toujours sur une satanée transaction de la mort (@!!!@@@ #) qu’il va avoir besoin de place…. Et pendant qu’il augmente la taille du fichier, et bien il ne fait pas grand chose d’autre, ou alors très difficilement.
Donnez à vos fichiers de données une croissance fixe en taille et allouez lui une taille cohérente, du genre entre 300 et 500 Mo d’un coup. Ainsi, il ne devra pas réitérer cette opération tous les deux jours
Et en plus vous diminuerez, un peu, la fragmentation du fichier physique, puisque le système va chercher en priorité un espace libre sur le disque pour y insérer la suite de votre fichier de donnée. Ainsi cette portion du fichier devrait quasi-être la plus contigüe possible sur votre disque.
Ensuite, pour continuer avec les performances, il y a les I/O (Les Entrées/Sorties). Quoi de mieux qu’un disque rapide pour y stocker les données certes, mais surtout les fichiers de logs. Plus SQL Serveur aura un accès rapide en écriture au fichier de journalisation, plus les temps d’attentes des transactions qui arrivent derrière seront diminué d’autant.
(Exemple d’implémentation rapide)
Pour plus de détails sur les systèmes RAID: Wikipédia
A lire absolument:
L’article de Frederick Brouard de SQL Pro sur l’optimisation des bases de données
Bon, je crois que cela suffit pour aujourd’hui… C’est déjà assez long et en plus je suis en vacances…
Visualiser la vidéo d’installation – Phase 1 (Environ 2 Mo)
