Module 5

Modèles de récupération et
stratégies de sauvegarde
Présentation du module

• Stratégies de sauvegarde
• Description de la journalisation des transactions
SQL Server
• Planification d'une stratégie de sauvegarde SQL
Server
Leçon 1: Comprendre les stratégies de
sauvegarde

• Discussion : Expérience passée en matière de

stratégies de sauvegarde
• Définition d'une stratégie de sauvegarde
appropriée
• Choix du support de sauvegarde approprié
• Définition d'une stratégie de conservation et de
test pour les sauvegardes
• SQL Server sauvearde avce Azure Blob Storage:
• Démonstration : Sauvegarde locale Sur la base de
données de Microsoft Azure
Discussion: Expérience précédente avec les
stratégies de sauvegarde

• Quels types de sauvegarde avez-vous utilisé?

• À quelle fréquence effectuez -vous des
sauvegardes?
• Qui est responsable de la planification et
l'exécution d'une stratégie de sauvegarde?
• Comment sont souvent testées vos sauvegardes?
• Utilisez-vous des outils tiers pour les
sauvegardes?
• Quel type de support de sauvegarde utilisez-
vous?
La détermination d'une stratégie de sauvegarde
appropriée
• Les différents types de sauvegarde peuvent être
combinés
• Déterminer les niveaux de sécurité:
• Comment prendre de temps de récupération peut? (RTO)
• Quelle quantité de données est-il acceptable de perdre?
(RPO)
• Est-il possible de récupérer les données provenant
d'autres sources?
• stratégie de sauvegarde devrait correspondre avec
des exigences:
• Types et fréquence des sauvegardes
• Les supports de sauvegarde à utiliser
• Période de conservation des sauvegardes et des médias
• politique de test de sauvegarde
 Définition d'une stratégie de sauvegarde appropriée
• Différents types de sauvegarde peuvent être combinés
• Sauvegardes basées sur des données (sauvegardes complètes, différentielles, de copie, de
groupe de fichiers, de fichier)
• Sauvegardes basées sur des journaux (sauvegardes de journal, de la fin du journal)
• Les niveaux de sécurité doivent être déterminés
• Combien de temps peut durer la récupération ? (RTO)
• Quelle quantité de données est-il acceptable de perdre ? (RPO)
• Est-il possible de récupérer les données d'autres sources ?
• La stratégie de sauvegarde doit être adaptée aux besoins
• Types et fréquence des sauvegardes
• Support de sauvegarde à utiliser
• Période de conservation des sauvegardes et des supports
• Stratégie de test des sauvegardes
Choix du support de sauvegarde approprié

• Une seule sauvegarde est appelée un jeu de

sauvegarde

• Un ensemble de support comprend un ou

plusieurs ensembles de sauvegarde

• Les jeux de sauvegarde sont écrites dans des jeux

de supports qui composent d'un ou plusieurs
dispositifs de sauvegarde

• périphériques de sauvegarde peuvent être:

• fichiers disque physique
• Logique-pointeur vers un fichier de disque
Choix du support de sauvegarde approprié

• Une sauvegarde unique est appelée un jeu de

sauvegarde
• Les jeux de sauvegarde sont écrits sur des supports
de sauvegarde composés d'une ou plusieurs unités
de sauvegarde
• Les supports de sauvegarde peuvent contenir
plusieurs jeux de sauvegarde
• Les unités de sauvegarde peuvent être physiques
ou logiques
• Les unités physiques ( des fichiers disque)
• Certains utilisateurs peuvent effectuer des
sauvegardes
La détermination d'une politique de
conservation des sauvegardes (Retention)

• La planification de la conservation de sauvegarde

doit faire partie de la stratégie et font partie du
plan d'essai pour assurer l'exactitude

• Plusieurs considérations:
• Combinaison des sauvegardes nécessaires pour une
récupération de base de données
• exigences d'archives
• Synchronisation avec des contrôles de base de
données
• emplacement de stockage sécurisé disponible
• Matériel requis pour la restauration des sauvegardes
• Exhaustivité des sauvegardes
Définition d'une stratégie de conservation
et de test pour les sauvegardes
• La planification de la conservation des
sauvegardes doit faire partie de la stratégie
• Doit faire partie du plan de test pour garantir
l'exactitude
• Plusieurs éléments à prendre en compte
• Combinaison de sauvegardes requises pour une
récupération de base de données
• Critères d'archivage
• Synchronisation avec les vérifications de base de
données
• Emplacement de stockage sécurisé disponible
• Matériel nécessaire pour restaurer les sauvegardes
• Exhaustivité des sauvegardes
SQL Server Backup avec Azure Blob Storage

• Parfois appelé sauvegarde SQL Server à l'URL

• Avantages:
• stockage illimité
• solution de sauvegarde hors site sans avoir besoin de
bandes et de transport
• Aucun matériel de sauvegarde pour acheter ou
maintenir
• Les sauvegardes hors site disponibles instantanément
Démonstration: sauvegarder une base de
données sur site à Microsoft Azure

Dans cette démonstration, vous verrez comment

sauvegarder une base de données de stockage
Azure Blob
Leçon 2: journaux de transactions SQL Server

• Vue d'ensemble des transactions SQL Server

• Structure des Journaux des transactions
• Utilisation des modes de récupération
• Planification des capacités pour les journaux de
transaction
• Utilisation des options de point de contrôle
• Démonstration: Journaux et récupération
complète
Vue d'ensemble des journaux de transactions
SQL Server

Les journaux de transactions fournissent un

historique des actions exécutées par un système
de gestion de base de données pour garantir
l'atomicité et la durabilité des opérations:
1. la modification des données sont envoyées par
l'application
2. Les pages de données sont situés dans ou lues
dans le cache de mémoire tampon, puis
modifiés
3. La modification est enregistrée dans le journal
sur le disque de transaction
4. Plus tard, point de contrôle écrit des pages
sales aux données des dossiers
Vue d'ensemble des journaux de transaction
SQL Server
Les journaux de transaction fournissent un historique des actions effectuées par un système
de gestion de base de données pour garantir les propriétés d'atomicité et de durabilité

La modification de données
1 est envoyée par l'application
La modification est enregistrée
Cache de 3 dans le journal des transactions
sur le disque
la mémoire
tampon

Les pages de données sont situées,

2 ou lues, dans le cache de la mémoire
tampon, puis modifiées
Par la suite, le point de contrôle
4 écrit des pages de modifications
dans la base de données
Transaction Log Structure du fichier

• Des informations suffisantes sont enregistrées

pour être en mesure de:
• Annuler les transactions à la demande
• Récupérer la base de données en cas de défaillance

• Write-Ahead Logging est utilisé pour créer les

entrées du journal:
• Les journaux de transactions sont écrits dans l'ordre
chronologique de manière circulaire
• la politique de troncature pour les journaux est basé sur
le modèle de récupération
 Structure du fichier journal des transactions
• Des informations suffisantes sont stockées pour permettre de
• Rrestaurer les transactions en cas de besoin
• Récupérer la base de données en cas d'échec

• Le protocole WAL (Write Ahead Logging) est utilisé pour

créer des entrées de journal
• Les journaux de transactions sont écrits dans l'ordre
chronologique de façon circulaire
• La stratégie de troncation des journaux est basée sur le mode
de récupération
Travailler avec des modèles de récupération

• Simple
• Ne pas permettre ou exiger des sauvegardes de
journaux
• tronque automatiquement le journal pour maintenir les
exigences de l'espace petite
• Plein
• Nécessite des sauvegardes de journaux pour gérabilité
• Evite la perte de données en raison d'un fichier de
données endommagées ou manquantes
• Permet la récupération à un point déterminé dans le
temps
• journalisée en bloc
• Nécessite des sauvegardes de journaux pour gérabilité
• Peut améliorer les performances des opérations de
Utilisation des modes de récupération

Mode de récupération Description

Simple • N'autorise pas ou ne requiert pas
de sauvegardes de journal
• Tronque automatiquement le journal afin
de minimiser l'espace requis
Complète • Nécessite des sauvegardes de journal pour
faciliter la gestion
• Évite la perte de données causée par un fichier
de données endommagé ou manquant
• Permet la récupération à un point spécifié dans
le temps
Journalisée en bloc • Nécessite des sauvegardes de journal pour
faciliter la gestion
• Peut améliorer les performances des opérations
de copie en bloc
• Réduit l'espace du journal utilisé en utilisant
un enregistrement minimal pour la plupart
des opérations en bloc
Planification de la capacité pour les journaux de
transactions

• Les besoins en capacités sont basées sur

plusieurs facteurs:
• Modèle de récupération utilisé pour la base de
données
• Transaction fréquence de sauvegarde du
journal dans les modèles de récupération
complète et en vrac connecté
• Nombre et taille des transactions dans la base
de données

• Examiner le comportement du journal au cours

prédéploiement essai
Planification des capacités pour les journaux
de transaction
• Les besoins en capacité reposent sur plusieurs
facteurs
• Mode de récupération utilisé pour la base de
données
• Fréquence de sauvegarde du journal des transactions
en mode de récupération complète et journalisée en
bloc
• Nombre et taille des transactions dans la base de
données
• Examinez le comportement du journal pendant
le test de prédéploiement
Utilisation des options de contrôle (Checkpoint)

• Types d'opérations Point de contrôle:

• Automatique
• Indirect
• Manuel
• Interne

• L'instruction CHECKPOINT
• Permet de définir la durée de récupération cible
Démonstration: Journaux et récupération
complète

Dans cette démonstration, vous allez découvrir

le fonctionnement de la troncation de journal
en mode de récupération complète
Leçon 3: La planification des stratégies de
sauvegarde

• Présentation de Microsoft SQL Server Types de

sauvegarde de base de données de sauvegarde
complète des stratégies de journaux de
transactions des stratégies de sauvegarde
différentielle des stratégies de sauvegarde
sauvegarde partielle et stratégies de groupe de
fichiers
Leçon 3 : Planification d'une stratégie
de sauvegarde SQL Server
• Vue d'ensemble des types de sauvegarde
Microsoft SQL Server
• Stratégies de sauvegarde complète de base de
données
• Stratégies de sauvegarde du journal des
transactions
• Stratégies de sauvegarde différentielle
• Discussion : Répondre aux besoins de
récupération de l'entreprise
Vue d'ensemble des types de sauvegarde Microsoft
SQL Server
Type de sauvegarde La description
Complète Tous les fichiers de données et la partie
active du journal des transactions
Différentielle Les parties de la base de données qui ont
changé depuis la dernière sauvegarde de
base de données complète
Partielle Le groupe de fichiers primaire, chaque
groupe de fichiers en lecture / écriture, et
groupes de fichiers en lecture seule
Journal des Toute modification de la base de données
transactions enregistrées dans les fichiers journaux

Queue-log Sauvegarde de journal prise de la queue du

(Tail-log) journal juste avant opération de
restauration
Fichier / groupe de fichiers ou groupes de fichiers
fichiers
copie seule La base de données ou un journal (sans
affecter la séquence de sauvegarde)
Base de données complète des stratégies de
sauvegarde

Une stratégie de sauvegarde complète de base

de données:
• Implique prendre une sauvegarde complète du
fichier de données primaires
• En mode simple, la base de données ne peut
être récupérée au point que la dernière
sauvegarde a été prise
• Peut-être une solution optimale lorsque les
données sont modifiées fréquemment ou est
utilisé dans un test environnement
Stratégies de sauvegarde complète de base
de données

Dimanche Lundi Mardi

Les sauvegardes complètes de base de données

• Sauvegardent toutes les données et une partie du journal
des transactions
• Peuvent être utilisées pour restaurer l'intégralité de la base
de données
• Permettent la récupération sur les périodes de sauvegarde
uniquement
Stratégies du journal des transactions de
sauvegarde

Une stratégie de base de données et de

sauvegarde du journal des transactions:
• Implique au moins des sauvegardes du journal
complet et transactions
• Permet la récupération à temps
• Permet la base de données soit intégralement
rétablie dans le cas du fichier de données perte
Stratégies de sauvegarde du journal
des transactions

Dimanche Lundi

Une stratégie de sauvegarde de base de données et de journal

de transactions
• Implique au moins des sauvegardes complètes et du journal
de transactions
• Permet la récupération à un point précis dans le temps
• Permet la restauration complète de la base de données
en cas de perte de fichiers de données
Stratégies de sauvegarde différentielles

Une stratégie de sauvegarde différentielle:

• Consiste à effectuer des sauvegardes de bases
de données complètes et différentielles
• Comprend les sauvegardes différentielles avec
des données modifiées ne
• Est utile si un sous-ensemble d'une base de
données est modifiée plus
souvent que le reste de la base de données
• Utilisation modified_extent_page_count pour
déterminer si d'effectuer un différentiel ou base
de données complète sauvegarde
Stratégies de sauvegarde différentielle

Lundi Mardi

Une stratégie de sauvegarde différentielle

• Implique l'exécution de sauvegardes de base de données
complètes et différentielles
• Inclut les sauvegardes différentielles contenant uniquement
des données modifiées
• S'avère utile uniquement si un sous-ensemble d'une base
de données est modifié plus fréquemment que le reste de
la base de données
• Utilisation modified_extent_page_count pour déterminer
si d'effectuer un différentiel ou base de données complète
sauvegarde
Stratégies de sauvegarde partielle et groupe de
fichiers

sauvegardes Partilelles et de groupes de fichiers:

• Sauvegarde et restauration Plus rapide pour des
bases de données très grandes
• Peut être complexe à mettre en place et à gérer
Lab: Comprendre les modèles de récupération
SQL Server

• Exercice 1: Planifier une stratégie de sauvegarde

Exercice 2: Configurer les modèles de récupération
de base de données
• Exercice 3: Exercice de Défi : Examen des modèles
de récupération et de la stratégie (si le temps le
permet)
Informations de connexion
Virtuel machine: 20764C-MIA-SQL
Nom d'utilisateur: AdventureWorks \ étudiants
Mot de passe: Pa55w.rd

Temps estimé: 45 minutes

Scénario Lab

Vous devez mettre en œuvre une stratégie de

rétablissement de base de données. L'unité d'affaires de
Proseworks Inc. a fourni les besoins de disponibilité pour
les bases de données sur la nouvelle Proseware instance
SQL Server. Vous devez planifier la façon dont vous
répondez aux exigences, puis mettre en œuvre votre
stratégie.
Si vous avez le temps, il y a une autre question que votre
gestionnaire souhaite que vous travaillez. Il y a une autre
instance de SQL Server installé pour supporter le
clientopérations de service.
Laboratoire Scénario (suite)

Votre gestionnaire craint que les bases de données

existantes sur l'instance du serveur CustomerService sont
mal configurés et des stratégies de sauvegarde non valides,
en fonction de leur RPO et RTO exigences. Vous devez
passer en revue les modèles de récupération de bases de
données et des stratégies de sauvegarde pour les bases de
données sur l'instance CustomerService, et de fournir les
changements recommandés.
Laboratoire Scénario (suite)

Documentation à l'appui
Exigences en matière de continuité de la base de données
d'affaires pour bases de données sur l'instance Proseware
Server (pour les exercices 1 et 2):
 Recovery Time Objectives
o La base de données MarketDev ne doit jamais être indisponible
pendant plus de huit heures.
o La base de données de recherche ne doit jamais être indisponible
pendant plus de deux heures.
 Objectifs de point de récupération
o Lorsque la base de données MarketDev est récupéré à partir d'un
échec, pas plus de 30 minutes de transactions peuvent être
perdues.
o Lorsque la base de données de recherche est récupéré à partir
d'un échec, toutes les transactions qui ont été effectuées jusqu'à
la fin de la semaine précédente doivent être récupérés.
Laboratoire Scénario (suite)

Caractéristiques prévues
Caractéristiques Valeur estimée
la taille de la base de données MarketDev 20 Go
taille de la base de données de recherche 200 MB
le débit de sauvegarde totale 100 Mo / minute
Débit total de restauration 80 Mo / minute
Taux moyen de modification de la base de 1 Go / heure
données MarketDev pendant les heures de
bureau
Taux moyen de modification de la base de 10 Mo / heure
données de recherche pendant les heures de
bureau
Pourcentage de la base de données chaque 1,2%
jour MarketDev a changé (en moyenne)
Pourcentage de la base de données de 80%
recherche a changé chaque jour (en
moyenne)
Heures de bureau (pas de sauvegardes de 8h00-18h00
base de données permises pendant ces
heures)
Laboratoire Scénario (suite)

Affaires Base de données Exigences en matière de

continuité pour les bases de données sur l'instance
CustomerService Server (pour l'exercice 3):
 Le temps de récupération Objectifs
o le base de données CreditControl ne doit jamais être indisponible
pendant plus de deux heures.
o le base de données PotentialIssue ne doit jamais être indisponible
pendant plus d'une heure.
 Récupération Point Objectifs
o Quand la base de données CreditControl est récupéré à partir d'un
échec, pas plus de cinq minutes de transactions peuvent être
perdu.
o Quand la base de données PotentialIssue est récupéré à partir
d'un échec, pas plus de 30 minutes de transactions peuvent être
perdues.
Laboratoire Scénario (suite)

Caractéristiques prévues
Caractéristiques Valeur estimée
la taille de la base de données CreditControl 20 Go
la taille de la base de données PotentialIssue 200 MB
(au début de chaque semaine après l'activité
d'archivage est terminée)
le débit de sauvegarde totale 100 Mo / minute
Débit total de restauration 80 Mo / minute
Taux moyen de modification de la base de 500 Mo / heure
données CreditControl pendant les heures de
bureau
Taux moyen de modification de la base de 10 Mo / heure
données PotentialIssue (constante tout au
long de la semaine, 24 heures par jour)
Pourcentage de la base de données chaque 60%
jour CreditControl a changé (en moyenne)
Pourcentage de la base de données chaque 50%
jour PotentialIssue a changé (en moyenne)
Heures de bureau (pas d'activité de base de 8:00-19h00
données permise pendant ces heures)
Laboratoire Scénario (suite)

Existant Stratégie de sauvegarde pour la base de

données CreditControl
Modèle de récupération: Plein
Type de sauvegarde Programme
Plein samedi 06h00
mercredi 06h00
Différentiel dimanche 22h00
lundi 22h00
mardi 22h00
jeudi 22h00
vendredi 22h00
Journal des transactions Toutes les 60 minutes à l'heure
Laboratoire Scénario (suite)

Existant Stratégie de sauvegarde pour PotentialIssue

Base de données
Modèle de récupération: Plein
Type de sauvegarde Programme
Plein dimanche 22h00
Bûche Toutes les 15 minutes, à partir
de 10 minutes après l'heure
Revue de laboratoire

• Dans ce laboratoire, vous avez appris comment

planifier une stratégie de sauvegarde, configurer
des modèles de récupération de base de données
et vérifier qu'un modèle de récupération choisi
répond aux exigences spécifiques.
Révision du module et plats à emporter

• Critique Question (s) les meilleures pratiques