de données

Module 3 : Gestion des
fichiers de base de données
Vue d'ensemble

Présentation des structures de données

Création de bases de données

Gestion des bases de données

Emplacement des fichiers et journaux
de base de données

Optimisation d'une base de données à l'aide d'une
solution RAID matérielle

Optimisation d'une base de données à l'aide de
groupes de fichiers

Optimisation d'une base de données en utilisant
des groupes de fichiers avec une solution RAID
matérielle

Planification de la capacité
 Présentation des structures de données

Mode de stockage des données

Présentation multimédia : Transactions

Fonctionnement du journal des transactions
Mode de stockage des données
Base de
données
Fichier de données
Fichier journal
.mdf ou .ndf
.ldf
Extension
(8 pages de
8 Ko contiguës)
Tables, index
Page
de données (8 Ko)
Taille maximale des lignes = 8 060 octets
Présentation multimédia : Transactions
Fonctionnement du journal des transactions
1 Une modification des
données est envoyée
par l'application
Cache
2 Les pages de données sont
chargées en mémoire (cache)
et sont modifiées
La modification est
3 enregistrée dans le journal
des transactions sur le
disque
Disque
Disque
4 Le point de contrôle
écrit les transactions
effectuées dans la
base de données
 Création de bases de données

Processus de création d'une base de données

Définition des options lors de la création d'une
base de données

Modification des options de base de données
après la création de la base de données

Affichage des propriétés de base de données
Processus de création d'une base de données

SQL Server crée un fichier de données et un
journal des transactions

Il requiert que le propriétaire et le créateur soient
autorisés à utiliser la base de données master

Il vous permet de définir les éléments suivants :

Nom de la base de données

Propriétés de la base de données

Emplacement des fichiers de base de données
Définition des options lors de la création
d'une base de données

Fichier principal

Fichiers secondaires

Journal des transactions

Nom de fichier et emplacement

Taille

Croissance des fichiers

Taille maximale

Classement
Modification des options de base de données
après la création de la base de données
Catégorie
des options de
base de données
Contrôles
Automatique
Certains comportements automatiques
Curseur
Comportement et portée du curseur
Restauration
Modèle de restauration de la base de données
SQL
Options de compatibilité ANSI
État
État connecté ou déconnecté de la base de données
Utilisateurs pouvant se connecter à la base de
données
Mode lecture seule de la base de données
Affichage des propriétés de base de données

Utilisation de SQL Server Enterprise Manager

Utilisation de l'Analyseur de requêtes SQL Server

Fonctions système

Procédures stockées système (sp_helpdb ou
sp_spaceused)

Instructions DBCC : DBCC SQLPERF
(LOGSPACE)
 Gestion des bases de données

Gestion de la croissance des fichiers journaux et
de données

Compactage automatique d'une base de données
ou d'un fichier de base de données

Compactage manuel d'une base de données ou
d'un fichier de base de données

Suppression d'une base de données
Gestion de la croissance des fichiers journaux
et de données

Utilisation de la croissance automatique des fichiers

Spécification de l'espace alloué, de la taille maximale
et de l'incrément de croissance de chaque fichier

Optimisation des performances en allouant
suffisamment d'espace, en définissant une taille
maximale et des incréments de croissance

Croissance manuelle des données et des fichiers
journaux de transactions

Choix entre croissance automatique ou manuelle de
la taille des fichiers

Création de fichiers de données et de fichiers
journaux secondaires
Compactage automatique d'une base de
données ou d'un fichier de base de données


Activation du compactage automatique

Spécification des options de compactage
automatique dans SQL Server Enterprise Manager

Exécution de l'instruction ALTER DATABASE
AUTO_SHRINK

Exécution de la procédure stockée système
sp_dboption
Activités de SQL Server au cours du compactage
automatique

SQL Server réduit les fichiers de données et journaux
de transactions lorsque l'espace inutilisé dans le
fichier dépasse 25 %

SQL Server exécute cette tâche en arrière-plan, et
n'affecte aucune des activités des utilisateurs
Compactage manuel d'une base de données
ou d'un fichier de base de données

Méthodes de compactage

Compactage d'une base de données et de fichiers
de données

Compactage des fichiers journaux de transactions


SQL Server réduit les parties inactives du journal
des transactions qui dépassent la taille souhaitée

Si cela ne suffit pas à ramener le journal des
transactions à la taille souhaitée, SQL Server
renvoie un message et vous indique la procédure
à suivre
Configuration des options de compactage de base
de données
Suppression d'une base de données

Méthodes de suppression d'une base de données

Limites relatives à la suppression d'une base de
données

Lorsqu'elle est en cours de restauration

Lorsqu'un utilisateur y est connecté

Lorsqu'elle effectue la publication de l'une de ses
tables dans le cadre de la réplication

Lorsqu'il s'agit d'une base de données système
Emplacement des fichiers et journaux de base
de données

Gestion de l'espace disque

Performance

Tolérance de panne

Répartition des fichiers de données

Création de journaux des transactions sur des
disques séparés

Emplacement de la base de données tempdb
Optimisation d'une base de données à l'aide
d'une solution RAID matérielle


Utilisation d'une solution RAID matérielle
 Offre de meilleures performances qu'une solution
RAID reposant sur le système d'exploitation
 Vous permet de remplacer un lecteur défectueux
sans arrêter le système
Types de solutions RAID
 Disques miroirs et duplexage de disques (RAID 1)
pour assurer la redondance du journal des
transactions
 Entrelacement avec parité pour combiner
performances et redondance des fichiers de données
et des journaux des transactions
 Disques miroirs avec entrelacement avec parité pour
assurer des performances maximales au niveau des
fichiers de données
 Optimisation d'une base de données à l'aide
de groupes de fichiers

Présentation des groupes de fichiers définis par
l'utilisateur

Création de groupes de fichiers définis par
l'utilisateur

Utilisation de groupes de fichiers pour améliorer
les performances

Utilisation de groupes de fichiers pour la
maintenance

Remarques relatives à la création de groupes de
fichiers
Présentation des groupes de fichiers définis
par l'utilisateur
Base de données Northwind
sys…
sys…
sysusers
sysobjects
…
orders
customers
products
OrdHistYear2
OrdHistYear1
C:\
D:\
E:\
Northwnd.mdf
OrdHist1.ndf OrdHist2.ndf
Northwnd.ldf
Groupe de fichiers
principal
Groupe de fichiers défini
par l'utilisateur
Journal des
transactions
Création de groupes de fichiers définis par
l'utilisateur

Méthodes de création de groupes de fichiers
définis par l'utilisateur

Choix d'un groupe de fichiers par défaut

SQL Server désigne un groupe de fichiers comme
groupe de fichiers par défaut

Un groupe de fichiers par défaut est défini comme
groupe de fichiers principal

Changez le groupe de fichiers principal par défaut
si vous créez des groupes de fichiers définis par
l'utilisateur

Taille du groupe de fichiers par défaut

Affichage des informations sur les groupes de
fichiers
Utilisation de groupes de fichiers pour
améliorer les performances

Répartition de la charge des données sur
plusieurs disques
Un fichier dans un
groupe de fichiers
Plan de
fichiers
Salesdata.mdf
Deux fichiers dans un
groupe de fichiers
Salesdata1.ndf
Plan de
fichiers
Salesdata.mdf
Plan de
fichiers

Utilisation de threads parallèles pour améliorer
l'accès aux données
Utilisation de groupes de fichiers pour la
maintenance

Sauvegarder ou restaurer des fichiers ou des
groupes de fichiers plutôt que de sauvegarder ou
restaurer l'intégralité de la base de données

Regrouper les tables et les index ayant les mêmes
exigences en matière de maintenance au sein des
mêmes groupes de fichiers

Attribuer une table demandant un niveau de
maintenance élevé à son propre groupe de
fichiers
Remarques relatives à la création de groupes
de fichiers

Surveiller les performances du système

Tenir compte des exigences en termes de
maintenance plutôt que des performances

Spécifier un groupe de fichiers définis par
l'utilisateur comme groupe de fichiers par défaut

Indiquer que les groupes de fichiers n'assurent
pas la tolérance de panne
Optimisation d'une base de données en utilisant des
groupes de fichiers avec une solution RAID matérielle
FichierA
FichierB
Contrôleur
de disque
FichierC
FichierD
FichierE
Contrôleur
de disque
Contrôleur
de disque
Groupe
de
fichiers
FichierF
FichierG
FichierH
Système d'exploitation
Contrôleur
de disque
Journal des transactions
Journal des transactions
 Planification de la capacité

Évaluation de la taille d'une base de données

Évaluation de la quantité de données contenues
dans les tables
Évaluation de la taille d'une base de données
Fichier journal
Fichier
de données
Activité
Tables
Index
Utilisateur
et système
Valeur de clé
Nombre
de lignes
Nombre
de lignes
Taux de
remplissage
Fréquence
Taille des
transactions
Sauvegarde
Évaluation de la quantité de données
contenues dans les tables



Calculez le nombre d'octets par ligne

Comptez les octets par ligne

Effectuez la moyenne des colonnes à longueur
variable
Déterminez le nombre de lignes contenues dans
chaque page de données

Divisez 8 060 par le nombre d'octets par ligne

Arrondissez le résultat au nombre entier le plus
proche
Divisez le nombre de lignes dans la table par le
nombre de lignes contenues dans une page de
données
Remarques sur les performances

Utilisez la solution RAID pour améliorer les
performances ou assurer la tolérance de panne

Placez les fichiers de données et les journaux des
transactions sur des disques physiques différents

Utilisez des groupes de fichiers définis par
l'utilisateur pour simplifier les stratégies de
sauvegarde des bases de données très
volumineuses
Conseils pratiques
Sauvegardez la base de données master
Spécifiez une taille de fichier maximale
Définissez la taille initiale du fichier de base de données
et des incréments de croissance automatique suffisamment
élevés
Utilisez les disques miroirs, l'entrelacement avec parité ou
les disques miroirs avec entrelacement
Créez un fichier pour chaque disque physique
Changez de groupe de fichiers par défaut
Atelier A : Gestion des fichiers de base de
données
Contrôle des acquis

Présentation des structures de données

Création de bases de données

Gestion des bases de données

Emplacement des fichiers et journaux
de base de données

Optimisation d'une base de données à l'aide d'une
solution RAID matérielle

Optimisation d'une base de données à l'aide de
groupes de fichiers

Optimisation d'une base de données en utilisant
des groupes de fichiers avec une solution RAID
matérielle

Planification de la capacité