Administration-SQL-SERVER

Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
La république algérienne démocratique et populaire
‫الجمهورية الجزائرية الديمقراطية الشعبية‬
Ministère de la Formation et de l’Enseignement Professionnels
‫وزارة التكوين و التعليم المهنيين‬
Institut National Spécialisé de la Formation Professionnelle ANOUEL -Tébessa-‫المعهد الوطني المتخصص في التكوين المهني أنوال –تبسة‬
Manuel Technique et Pédagogique
INFORMATIQUE
Base de Données
ADMINISTRATION A L’AIDE
DE SQL SERVER
Niveau 5
Enseignante : Nadjette Mouici –PSEP2-
Octobre 2014
Sommaire
A) INTRODUCTION
B) INFORMATIONS GéNéRALES
B.1 Introduction
B.2 Définition et but
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
B.3 Comment utiliser ce Manuel ?
B.4 Liste de modules
C) LES DIFFERENTES SPECIALITES
D) LE MODULE DE MANUEL TECHNIQUE ET PEDAGOGIQUE DU STAGIAIRE
E) FICHE DE PRESENTATION DU MODULE
Chapitre01 : Présentationdes éléments d’administration SQL Server.
Elément d’administration de SQL Server
- Utilitaire OSQL et BCQ.
- Outil de gestion de SQL Server.
- Tache d’administration.
Chapitre 02 :Implémentation en mode d’authentification.
Gestion des autorisations.
Rappel sur la sécurité de SQL Server.
Traitement de l’authentification.
- Sélection d’un mode d’authentification.
- Création des comptes de connexions.
Chapitre 03 :Définition des autorisations.
Définition d’autorisation.
Types d’autorisation
Octroi, refus et révocation d’autorisation.
Chapitre 04 :Les options de la Base De Données.
Définition d’option de Base De Données.
Catégories des options de Base De Données
Options automatiques.
Options de curseurs.
Options de récupération.
Options d’état.
Options SQL.
Chapitre 05 :Réalisation d’une Restauration des sauvegardes des données.
Sauvegarde des bases de données.
Protection contre la perte.
Sauvegarde de SQL Server.
Exécution de sauvegarde.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Méthode de sauvegarde.
Planification d’une stratégie de sauvegarde.
Restauration de bases de données.
Processus de récupération de SQL Server.
Préparation à la restauration.
Restauration de sauvegarde.
Restauration de BD système endommagées.
Chapitre 06 :Surveillance des performances de SQL Server.
Surveillance des performances de SQL Server.
Raisons justifiant la surveillance de SQL Server.
Surveillance et ajustement des performances.
Outils de surveillances de SQL Server.
Tâches contraintes liées à la surveillance.
F) ACRONYMES.
G) LEXIQUE.
H) BIBLIOGRAPHIE.
A- INTRODUCTION :
Le Manuel Technique et Pédagogique du Stagiaire s’inscrit dans l’approche d’élaboration
des programmes de formation sous forme modulaire.
Compte tenu de sa spécialité méthodologique, ce manuel a pour but la traduction des
éléments de contenu du programme d’étude d’une spécialité en fiches techniques et
pédagogique.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Ce manuel, de par sa caractéristique d’aide – mémoire, répond au but de formation :
donner les documents techniques et pédagogiques nécessaires au stagiaire et enseignant
lui permettant de gères ses apprentissage et ses préparations aux différent contrôles et
examens.
Ce manuel permet de présenter un apprentissage progressif en respectant l’ordre de
difficulté, de ce fait le manuel peut constituer un manuel pédagogique pour l’enseignant
comme il peut constituer un manuel des travaux pratiques pour le stagiaire.
L’étude du module Administration à l’aide de SQL SERVERest un manuel de travaux
pratiques, il enseigne à la fois les techniques des bases de données et découvrir les
principes relatifs aux bases de données et leurs utilités.et les outils qu’offre l’Administration
à l’aide de SQL Server.
Dans ce manuel technique et pédagogique du stagiaire vous allez certainement découvrir
une nouvelle méthode d’apprentissage, qui est le fruit d’une recherche visant à atteindre
une formation efficace de n’importe quelle technique, il ne s’agit pas uniquement de
maîtriser la technique elle-même, mais de savoir la transmettre efficacement.
B- INFORMATIONS GENERALES :
Ce manuel renferme toutes les activités nécessaires à l’acquisition de la compétence visée
par ce module Administration à l’aide de SQL SERVER.
En matière d’élaboration et structuration de contenus de formation, cette méthodologie
s’appuie sur trois étapes essentielles de réalisation :
- traitement des informations.
- Planification de la rédaction.
- La rédaction des fiches technique et pédagogiques constituant le manuel.
Les éléments de contenu et les critères particulières de performance définissent
successivement l’exactitude et la pertinence du contenu de formation à développes, et
également sur quoi porte l’évaluation pour chaque objectif intermédiaire.
La planification de la rédaction des fiches techniques et pédagogiques constitue l’étape
intermédiaire dans le processus du stagiaire.
Cette planification comprend essentiellement les phrases suivantes :
- l’élaboration de la fiche de planification de la rédaction
La rédaction des fiches techniques et pédagogiques est une étape essentielle dans le
processus d’élaboration du manuel technique et pédagogique du stagiaire.
Ce développement consiste dans un premier temps à rechercher et à choisir dans une
documentation, un contenu technique et pédagogique nécessaire à la rédaction des fiches
techniques et pédagogique.
Le deuxième temps formuler ce contenu dans un style rédactionnel obéissant à des règles
didactique et dans un troisième temps à la synthétiser et le présenter sous forme de
résumés de afin de faciliter la compréhension et la mémorisation.
C - LES DIFFÉRENTES SPÉCIALITÉS :
Code de
spécialité
Intitule de la spécialité
Spécialité : INFORMATIQUE
Durée de formation
Niveau de qualification
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
BDD
BASE DE DONNEES
30 MOIS
Fiche de présentation U.M.Q
U.M.Q : implémenter et administrer la base de données.
Spécialité : INFORMATIQUE
NIVEAU 5
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Code : UMQ3
Durée : 646 h
Objectif de l’U.M.Q
Comportement attendu :
A l’issue de cette unité, le stagiaire doit être capable d’implémenter et administrer la base de données
sous réseaux avec SQL Server et inter base.
Conditions d’évaluation :
A partir :
 Documentation
 Etude conceptuelle et schéma du MLD validé
A l’aide :


Micro-ordinateurs munis des logiciels
Programmes et algorithmes
SQL Server, Inter base.
Critères généraux de performance :




Utilisation appropriée de la machine et respect des règles de sécurité et de santé
Utilisation adéquate des langages SQL et ISQL.
Implémentation correcte de la base.
Administration de la base selon les critères de la définition de la base.
Structure de l’U.M.Q
U.M.Q : Implémenter et administrer les SGBD.
Code : UMQ3.
Durée : 646 h.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Désignation des modules
Code
T.S.
Durée
MQ31
Implémentation de SQL server.
170 H
MQ32
Administration de SQL Server.
170 H
MQ33
Delphi réseaux.
136 H
MC31
Réseaux Informatiques.
170 H
Fiche de présentation des modules
UMQ :
Implémenter et administrer la base de données.
Module : Administration à l’aide de SQL Server.
Code : MQ32
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Durée : 170 h
Objectif modulaire
Comportement attendu :
A l’issue de ce module, le stagiaire doit être capable d’exécuteret automatiser les tâches
administratives et créer des outils d'administration personnalisés.
Conditions d’évaluation :
A partir :

BD implanté, documentation SQL Server.
A l’aide :

Logiciels de SQL Server+microordinateur.
Critères généraux de performance :



Gestion correcte des automatisations.
Sécurisation et restauration correcte des BD.
Automatisation correcte des tâches administratives.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Objectifs intermédiaires
Présenter
les éléments
d’administration SQL Server.
Implémenter
en mode
d’authentification.
Critères
particuliers de
performance
Présentation
correcte.
Mode
Eléments contenus
Elément d’administration de SQL Server
-
Utilitaire OSQL et BCQ.
-
Outil de gestion de SQL Server.
-
Tache d’administration.
Implémentation d’un mode d’authentification.
d’authentificat Rappel sur la sécurité de SQL Server.
ion
Traitement de l’authentification.
correctement
- Sélection d’un mode
implémenté.
d’authentification.
-

Définir les autorisations.
Définition
correcte.
Création des comptes de
connexions.
Gestion des autorisations.
Définition d’autorisation.
Types d’autorisation
Octroi, refus et révocation d’autorisation.
Réalisation
d’une sauvegarde
de bases de données.
 Restauration des
sauvegardes de données.
sauvegarde
correctement
réalisé.
 Restauration
correcteme
nt réalisée.
Sauvegarde des bases de données.
Protection contre la perte.
Sauvegarde de SQL Server.
Exécution de sauvegarde.
Méthode de sauvegarde.
Planification d’une stratégie de sauvegarde.
Restauration de bases de données.
Processus de récupération de SQL Server.
Préparation à la restauration.
Restauration de sauvegarde.

Surveiller les
performances deSQL
Server.
 Surveillance
correctemen
t réalisée.
Restauration de BD système endommagées.
Surveillance des performances de SQL Server.
Raisons justifiant la surveillance de SQL Server.
Surveillance et ajustement des performances.
Outils de surveillances de SQL Server.
Tâches contraintes liées à la surveillance.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Chapitre 01 :
Présentation des
éléments
d’administration SQL
Server
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
I.
T.S.
Eléments d’Administration SQL SERVER :
SQL Server fournit plusieurs outils de gestion qui permettent de réduire etd'automatiser les tâches
administratives de routine. Les instructionsTransact-SQL constituent le mécanisme sous-jacent utilisé pour
administrerSQL Server.
Figure1 : Eléments
d'Administration SQL SERVER
Vous pouvez administrer SQL Server en utilisant les éléments suivants :

des utilitaires de traitement par lots fournis avec SQL Server, tels que OSQL et BCP ;

des outils de gestion graphiques fournis avec SQL Server, tels queSQL Server Enterprise
Manager ;

des applications compatibles COM, telles que Visual Basic.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Les outils de gestion de ligne de commande de SQL Server permettent de taperdes
instructionsTransact-SQL et d'exécuter des fichiers de script. Chaque fichier est un programme
exécutableet utilisés le plus fréquemment :
OSQL:Utilitaire tirant parti de la connectivité ODBC (Open DatabaseConnectivity) pour
communiquer avec SQL Server (utilisé principalementpour exécuter des fichiers de traitement
par lots contenant une ou plusieursinstructions SQL).
BCP:Utilitaire de traitement par lots permettant d'importer et d'exporter desdonnées depuis/vers
SQL Server (c'est à dire copier des donnéesdepuis/vers un fichier de données dans un format
spécifié par l'utilisateur).
II. Outil de gestion de SQL Server :
SQL-DMO (Distributed Management Objects) est un ensemble d'objetsd'administration basés sur COM, et
utilisés par SQL Server.
SQL-DMO masqueles détails des instructions Transact-SQL et convient à la rédaction de
scriptsd'administration pour SQL Server. Les outils de gestion graphiques fournis avecSQL Server sont rédigés
avec SQL-DMO.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
III.
T.S.
Taches d’administration :
Agent SQL SERVER : L'Agent SQL Server est un service qui fonctionne avec SQL Server afin d'exécuter
les tâches administratives décrites ci-dessous :
 Gestion des alertes :
Les alertes fournissent des informations sur l'état d'un processus, tel que la fin d'une tâche ou l'apparition d'une
erreur. L'Agent SQL Server surveille le journal d'événements Applications Windows et génère des alertes.
 Notification :
L'Agent SQL Server peut envoyer des messages électroniques, prévenir un opérateur par un dispositif de
recherche de personnes, ou démarrerune autre application lors de l'apparition d'une erreur. Par exemple, vous
pouvez définir une alerte qui se déclenche avant saturation d'une base de données ou du journal des
transactions ou en cas de succès d'une sauvegarde de base de données.
 Exécution des travaux :
L'Agent SQL Server comprend un moteur de création et de planification de travaux. Les travaux peuvent être
de simples opérations composées d'une seule étape ou bien des tâches complexes composées de plusieurs
étapes et devant être planifiées. Vous pouvez également créer des étapes de travail à l'aide de Transact-SQL,
de langages de script ou de commandes de système d'exploitation.
 Gestion de la réplication :
La réplication est le processus de copie de données ou de transactions d'un serveur SQL Server vers un autre.
L'Agent SQL Server est chargé de la synchronisation des données entre les serveurs, de la surveillancedes
données en cas de modification et de la réplication des informations versd'autres serveurs.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Chapitre 02 :
Implémentation
en mode
d’authentificatio
n.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
I. la sécurité de SQL Server :
SQL Server est intégré au système de sécurité de Windows NT. Cetteintégration permet d'accéder à SQL
Server et à Windows NT à partir d'un nomd'utilisateur et d'un mot de passe uniques. SQL Server utilise
également lesfonctionnalités de cryptage de Windows NT pour la sécurité du réseau. SQLServer offre ses
propres fonctionnalités de sécurité pour les clients autres queles clients Microsoft.
II. Traitement de l’authentification :
Un utilisateur doit disposer d'un compte de connexion pour se connecter à SQL Server. SQL Server
reconnaît deux mécanismes d'authentification de connexion (l'authentification SQL Server et
l'authentification Windows NT), chacun ayant un type de compte de connexion différent.
Figure 2: Traitement de l’authentification.
III.1. Authentification SQL SERVER :
Dans le cadre de l'authentification SQL Server, l'administrateur d'un systèmeSQL Server définit un
compte de connexion SQL Server et un mot de passe.
Les utilisateurs doivent fournir leur nom et leur mot de passe de connexionSQL Server lorsqu'ils se
connectent à SQL Server.
III.2.Authentification WINDOWS :
Dans le cadre de l'authentification Windows, un compte ou groupeWindows contrôle l'accès des
utilisateurs à SQL Server (l'utilisateur n'a pasbesoin de posséder de compte de connexion SQL Server
pour se connecter).
L'administrateur d'un système SQL Server doit définir le compte Windows ou le groupe Windows en
tant que compte de connexion SQL Server valide.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
III.
T.S.
Modes d’authentification:
Lorsque SQL Server s'exécute sous Windows, l'administrateur système peut demander à ce qu'il soit
exécuté dans l'un des deux modes d'authentification décrits ci-dessous.
III.1. Mode d’authentification WINDOWS : Seule l'authentification Windows estautorisée. Les
utilisateurs ne peuvent pas spécifier de compte de connexionSQL Server.
III.2. Mode mixte : Lors de l'utilisation de ce mode d'authentification, les utilisateurspeuvent se
connecter à SQL Server avec l'authentification Windows oul'authentification SQL Server.
IV.
Création des comptes de connexions :Rôles et comptes d’utilisateur de base de données
Une fois les utilisateurs authentifiés et autorisés à se connecter à SQL Server, ils doivent disposer
d'un compte dans une base de données. Les rôles et les comptes d'utilisateur identifient un utilisateur
au sein d'une base de données et contrôlent la propriété des objets et les autorisations d'exécution
d'instructions.
Figure 3: Rôles et comptes d’utilisateur de base de données.
IV.1. Comptes d’utilisateur de BDD :
Les comptes d'utilisateur employés pour attribuer des autorisations de sécurité sont des utilisateurs, des
groupes Windows ou des comptes de connexion SQL Server. Les comptes d'utilisateur sont spécifiques à une
base de données.
IV.2. ROLES :
Les rôles permettent de grouper des utilisateurs en une entité unique à laquelle vous pouvez attribuer des
autorisations. SQL Server propose des rôles de serveur et de base de données prédéfinis pour des fonctions
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
administratives courantes afin que vous puissiez accorder un ensemble d'autorisations administratives à un
utilisateur spécifique. Vous pouvez également créer vos propres rôles de base de données. Dans SQL Server,
les utilisateurs peuvent appartenir à plusieurs rôles.
a. Rôles fixes de serveur :
Les rôles fixes de serveur permettent de regrouper des privilèges administratifsau niveau du serveur. Ils
sont gérés au niveau du serveur, indépendamment desbases de données utilisateur. Le tableau suivant
décrit les rôles fixes de serveurles plus courants :
Rôle
Autorisation
Créateurs de bases de donnée s(dbcreator)
Création et modification des bases de données
Administrateurs de disques (diskadmin)
Gestion des fichiers sur disque
Administrateurs de processus (processadmin)
Gestion des processus SQL Server
Administrateurs de sécurité (securityadmin)
Gestion et audit des noms de connexion serveur
Administrateurs de serveur (serveradmin)
Configuration des paramètres de serveur
Administrateurs d'installation (setupadmin)
Installation de la réplication
Administrateurs système (sysadmin)
Exécution de toutes les activités
 Ajout d'un membre à un rôle de serveur fixe :
 A l'aide de Transact-Sql :
Utilisez la procédure stockée système sp_addsrvrolemember pour ajouter une connexion comme
membre d'un rôle de serveur fixe.
Syntaxes: sp_addsrvrolemember[ @loginame = ] 'login' , [ @rolename = ] 'role'
Arguments
Description
[@loginame =] 'login' Nom de la connexion ajoutée au rôle de serveur fixe.
[@rolename =] 'role'
Nom du rôle de serveur fixe dans lequel la connexion est ajoutée.
Exemple: Cet exemple ajoute un utilisateur Windows NT ServBDD\BhiriBau rôle de serveur
fixesysadmin.
EXEC sp_addsrvrolemember 'ServBDD\BhiriB', 'sysadmin'
 Suppression d'une connexion d'un rôle de serveur fixe :
 A l'aide de Transact-Sql :
Utilisez la procédure stockée système sp_dropsrvrolemember pour supprimer un compte
utilisateur Microsoft SQL Server ou un utilisateur ou groupe Microsoft Windows NT dans un rôle de
serveur fixe.
Syntaxe : sp_dropsrvrolemember[ @loginame = ] 'login' , [ @rolename = ] 'role'
Exemple : Cet exemple supprime la connexion ServBDD\BhiriBdu rôle de serveur fixe sysadmin.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
EXEC sp_dropsrvrolemember ' ServBDD\BhiriB', 'sysadmin'
b. Rôles fixes de BDD :
Les rôles fixes de base de données permettent de regrouper des privilèges administratifs au niveau de
la base de données. Le tableau suivant décrit les rôles fixes de base de données les plus courants :
Rôle
Autorisation
Public
Conservation de toutes les autorisations par défaut pour les utilisateurs d'une base
de données
db_owner
Exécution de toutes les activités de rôle de base de données
db_accessadmin
Ajout ou suppression d'utilisateurs, de groupes et de rôles de base de données
db_ddladmin
Ajout, modification ou suppression d'objets de base dedonnées
db_securityadmin
Affectation d'autorisations d'instructions et d'objets
db_backupoperator Sauvegarde et restauration de bases de données
db_datareader
Lecture de données dans toutes les tables
db_datawriter
Ajout, modification ou suppression de données dans toutes lestables
db_denydatareader Interdiction de lecture des données dans toutes les tables
db_denydatawriter
Interdiction de modification des données dans toutes les tables
 Ajout d'un membre à un rôle de base de données SQL Server :
 A l'aide de Transact-Sql :
Utilisez la procédure stockée système sp_addrolemember pour ajouter un membre à un rôle de
base de données fixe dans la base de données courante..
Syntaxe: sp_addrolemember[ @rolename = ] 'role' , [ @membername = ] 'security_account'
Arguments
Description
[@rolename =] 'role'
Nom du rôle SQL Server dans la base de données
courante
[@membername =] 'security_account' Compte sécurisé ajouté au rôle.
Exemple : Cet exemple ajoute l'utilisateur Windows NT Serv15B3\SelmiF à la base de données BD1
en tant qu'utilisateur Selmi Farid. Selmi Farid est ensuite ajouté au rôle db_owner.
USE BD1
GO
EXEC sp_grantdbaccess 'ServBDD\BhiriB', 'BhiriBachir'
GO
EXEC sp_addrolemember 'db_owner', 'Bhiribachir'
 Suppression d'un compte d'utilisateur d'un rôle fixe de base de données :
 A l'aide de Transact-Sql :
Utilisez la procédure stockée système sp_droprolemember pour supprimer un compte sécurisé
d'un rôle Microsoft SQL Server dans la base de données en cours.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Syntaxe: sp_droprolemember[ @rolename = ] 'role' , [ @membername = ] 'security_account'
Exemple : Cet exemple supprime l'utilisateur Bhiri Bachir dans le rôle db_owner.
EXEC sp_droprolemember 'db_owner', 'BhiriBachir'
c. Rôles de BDD définies par l’utilisateur :
Vous pouvez également créer vos propres rôles de base de données pour refléter le travail effectué
par un groupe d'employés dans votre entreprise. Il n'est pas nécessaire d'accorder ou de révoquer les
autorisations pour chaque personne. En cas de modification de la fonction d'un poste, vous pouvez
aisément modifier les autorisations pour ce rôle et appliquer automatiquement ces modifications à tous
les membres du rôle.
 Création d'un rôle de base de données SQL Server :
 A l'aide de Transact-Sql :
Utilisez la procédure stockée système sp_addrole pour crée un nouveau rôle SQL Server dans la
base de données courante.
Syntaxe: sp_addrole[ @rolename]= 'role' , [ @ownername] = ['owner' ]
Arguments
Description
[@rolename ]= 'role'
Nom du nouveau rôle.
[@ownername ]= 'owner'
Propriétaire du nouveau rôle.
Exemple : Cet exemple ajoute le nouveau rôle appelé Gestionnaires à la base de données courante.
EXEC sp_addrole 'Gestionnaires'
Exemple : Cet exemple ajoute le compte utilisateur de base de données Bhiri Bachir dans le rôle
Gestionnaires, dans la base de données courante.
EXEC sp_addrolemembre 'Gestionnaires',’ Bhiri Bachir'
 Suppression d'un rôle de base de données défini par l'utilisateur :
 A l'aide de Transact-Sql :
Utilisez la procédure stockée système sp_droprole pour supprimer un rôle SQL Server dans la base
de données en cours.
Syntaxe : sp_droprole [@rolename =] 'role'
Exemple : Cet exemple supprime le rôle Gestionnaires de SQL Server.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
EXEC sp_droprole 'Gestionnaires'
Chapitre 03 :
Les
autorisations.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
I.
T.S.
Définition d’autorisation :
Au sein de chaque base de données, vous affectez des autorisations aux comptes
d'utilisateur et aux rôles afin d'exécuter (ou restreindre) certaines actions.
SQL Server accepte les commandes une fois que l'utilisateur a accédé avec succès à une
base de données.
II.
GESTION des autorisations :
Figure 4: Gestion des autorisations
Lors de la validation des autorisations, SQL Server réalise les étapes décrites ci-dessous :
1. Lorsque l'utilisateur accomplit une action, telle que l'exécution d'une instruction
Transact-SQL ou la sélection d'une option de menu, les instructions Transact-SQL sont
envoyées à SQL Server.
2. Lorsque SQL Server reçoit une instruction Transact-SQL, il vérifie que l'utilisateur
possède l'autorisation d'exécuter l'instruction.
3. SQL Server effectue ensuite l'une des deux actions décrites ci-dessous :
• Si l'utilisateur ne possède pas les autorisations appropriées, SQL Server renvoie
une erreur.
• Si l'utilisateur possède les autorisations appropriées, SQL Server effectue l'action.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Chaque objet d'une base de données SQL Server a un propriétaire, généralement l'ID
utilisateur de la connexion active lors de la création de l'objet. Les autres utilisateurs ne
peuvent pas accéder à cet objet tant que le propriétaire n'autorise pas leur ID utilisateur à
le faire.
Certaines instructions Transact-SQL sont également limitées à certains ID utilisateur. Par
exemple, CREATE DATABASE est limité aux membres des rôles de serveur fixes sysadminet
dbcreator. Les utilisateurs ne peuvent accéder à un objet ou exécuter une instruction que
s'ils en ont l'autorisation.
Toutes les instructions Transact-SQL lancées par un utilisateur sont soumises à ses
autorisations. Les membres du rôle de serveur fixe sysadmin, les membres du rôle de base
de données fixe db_owner, et les propriétaires des objets de la base de données peuvent
octroyer (permettre), refuser ou révoquer les autorisations d'une personne ou d'un rôle.
Lorsque vous utilisez Transact-SQL, utilisez les instructions GRANT, DENY et REVOKE afin de
spécifier qui peut utiliser quelles instructions de modification de données :
• GRANT : donne les autorisations d'utiliser des données ou d'exécuter d'autres instructions
SQL.
• DENY : refuse l'autorisation et empêche l'utilisateur, le groupe ou le rôle spécifié d'hériter
de l'autorisation grâce à son appartenance à un groupe ou à un rôle.
• REVOKE : supprime les autorisations accordées ou refusées antérieurement.
Les autorisations qui peuvent être accordées pour des objets sont les suivantes :
• SELECT : Permet à un utilisateur d'exécuter des instructions SELECT dans une table ou une
vue.
• INSERT : Permet à l'utilisateur d'exécuter des instructions INSERT dans une table ou une
vue.
• UPDATE : Permet à l'utilisateur d'exécuter des instructions UPDATE dans une table ou une
vue.
• DELETE : Permet à l'utilisateur d'exécuter des instructions DELETE dans une table ou une
vue.
• REFERENCES : Permet à l'utilisateur de faire référence avec des clés étrangères à la clé
primaire et aux colonnes uniques d'une table. Cette autorisation est également utilisée pour
permettre desréférences SCHEMABINDING à partir de vues et de fonctions.
• EXECUTE: Permet à l'utilisateur d'exécuter des instructions EXECUTE dans une procédure
stockée.
Les autorisations peuvent également être accordées afin d'exécuter des instructions
Transact-SQL normalement limitées aux membres d'un rôle spécifique. Par exemple, un
membre du rôle de serveur fixe sysadminpeut octroyer des autorisations CREATE
DATABASE à un utilisateur qui ne peut habituellement pas créer de base de données.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
III.
T.S.
Types d’autorisation :
Lorsque des utilisateurs se connectent à une instance de SQL Server, les opérations qu'ils
peuvent effectuer sont déterminées par les autorisations accordées :
• A leurs comptes de sécurité ;
• Hiérarchies de rôle ou de groupes Microsoft Windows NT 4.0 ou Windows 2000 auxquels
appartiennent leurs comptes de sécurité.
L'utilisateur doit disposer des autorisations appropriées pour effectuer toute opération
impliquant une modification de la définition de la base de données ou l'accès aux données.
La gestion des autorisations comprend l'accord ou le retrait de droits utilisateur pour :
• Travailler avec des données et exécuter des procédures (autorisations d'objet) ;
• Créer une base de données ou un élément dans la base de données (autorisations
d'instruction) ;
• Utiliser des autorisations accordées à des rôles prédéfinis (autorisations implicites).
1. Autorisations sur les objets :
La manipulation de données ou l'exécution d'une procédure requièrent une classe
d'autorisations appeléeautorisations sur les objets :
• Les autorisations d'instructions SELECT, INSERT, UPDATE et DELETE, qui peuvent être
appliquées àla table ou à la vue tout entière ;
• les autorisations d'instructions SELECT et UPDATE, qui peuvent être appliquées de
manière sélective aux colonnes individuelles d'une table ou d'une vue ;
Autorisations SELECT, qui peuvent être appliquées à des fonctions définies par l'utilisateur ;
• Les autorisations d'instruction INSERT et DELETE, qui affectent la ligne entière et ne
peuvent, parconséquent, être appliquées qu'à la table et à la vue et non aux colonnes
individuelles ;
• Les autorisations d'instruction EXECUTE, qui n'affectent que les procédures stockées et les
fonctions.
2. Autorisations d'instruction :
Les activités impliquées dans la création d'une base de données ou d'un élément de base de
données, tels qu'unetable ou une procédure stockée, nécessitent une classe d'autorisations
différente appelée autorisations d'instructions. Si, par exemple, un utilisateur doit créer une
table à l'intérieur d'une base de données, vous devez donc lui accorder l'autorisation
d'instruction CREATE TABLE. Les autorisations d'instruction, telles que CREATE DATABASE,
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
sont appliquées à l'instruction proprement dite, plutôt qu'à un objet spécifique défini dans
la base de données.
Les autorisations d'instruction sont les suivantes :
• BACKUP DATABASE
• BACKUP LOG
• CREATE DATABASE
• CREATE DEFAULT
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE RULE
• CREATE TABLE
• CREATE VIEW
3. Autorisations implicites :
Les autorisations implicites contrôlent les opérations qui peuvent uniquement être
effectuées par des membresde rôles de système prédéfinis ou des propriétaires d'objets
de base de données. Par exemple, un membre du rôle de serveur fixe sysadmin hérite
automatiquement de toutes les autorisations requises pour faire ou afficher tout ce qu'il
veut dans une installation SQL Server.
Les propriétaires de bases de données disposent également d'autorisations implicites
leur permettant d'effectuer toutes les opérations qu'ils souhaitent sur l'objet leur
appartenant. Par exemple, un utilisateur possédant une table peut afficher, ajouter ou
supprimer les données de la table, modifier sa définition, ou contrôler les autorisations
permettant aux autres utilisateurs de travailler avec cette table.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
I.
T.S.
Octroi de permission :
Accordez des autorisations d'instruction et d'objet qui permettent à un compte utilisateur :
• D'effectuer des opérations ou de travailler avec des données dans la base de données en
cours ;
• De les restreindre quant aux opérations et informations qui ne font pas partie de leur
fonction.
Vous pourriez, par exemple, être tenté d'accorder l'autorisation sur l'objet SELECT sur la
table Employeeà tous les membres du rôle personnel, permettant ainsi à tous les membres
du service du personnel de consulter la table Employee.
Quelques mois plus tard, vous pourriez très bien entendre des membres du rôle personnel
discuter des salaires des cadres, une information qui n'est pas censée être connue de tous
les membres du rôle personnel.
Dans ce genre de situation, accordez l'accès à SELECT aux membres du rôle personnel pour
toutes les colonnes de la table Employee, à l'exception de la colonne Salaire.
Remarque :
Il est possible de n'accorder des autorisations qu'aux comptes utilisateur dans la base de
données en cours, pour les objets figurant dans cette base.
Si un utilisateur a besoin d'autorisations sur des objets d'une autre base de données, créez
le compte de l'utilisateur dans l'autre base de données, ou autorisez le compte de
l'utilisateur à accéder à la fois à l'autre base de données et à la base de données courante.
Les procédures système stockées constituent une exception car les autorisations pour
EXECUTE sont déjà accordées au rôle public, ce qui autorise tous les utilisateurs à les
exécuter. Cependant, après l'émission d'une instruction EXECUTE, les procédures système
stockées vérifient l'appartenance de l'utilisateur au rôle.
Si l'utilisateur n'est pas membre du rôle de serveur ou de base de données fixe approprié,
requis pour exécuter la procédure stockée, celle-ci ne poursuivra pas son exécution.
A l'aide de Transact-Sql :
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Utilisez GRANT pour créer une entrée dans le système de sécurité qui permet à un
utilisateur de la base de données courante de travailler avec les données de la base ou
d'exécuter des instructions Transact-SQL particulières.
• Octroi d'autorisations sur un objet aux utilisateurs d'une base de données :
Syntaxe :
Autorisations sur un objet :
GRANT
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ (column [ ,...n ] ) ] ON { table| view }
| ON { table| view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure| extended_procedure}
| ON { user_defined_function}
}
TO security_account[ ,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role} ]
Exemple1 : Cet exemple accorde l'autorisation Select, Insert et Update pour le compte Reguig Radia
sur la table Table1.
USE MaBaseDeDonnees
GO
GRANT SELECT, INSERT, UPDATE ON Table1 TO [ReguigRadia]
GO
Exemple2 : Cet exemple accorde l'autorisation Select à tous les membres du rôle Public pour la
table Table1.
GRANT SELECT ON Table1 TO Public
 Octroi d'autorisations d'instruction aux utilisateurs d'une base de données :
Syntaxe
Autorisations sur une instruction :
GRANT { ALL | statement [ ,...n ] }
TO security_account[ ,...n ]
Exemple : Cet exemple accorde plusieurs autorisations d'instruction aux utilisateurs Selmi Farid et
ReguigRadia.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
GRANT CREATE TABLE, BACKUP DATABASE TO [ReguigRadia], [SelmiFarid]
II.
Refus d'autorisation :
SQL Server permet aux utilisateurs et aux groupes Microsoft Windows 2008, aux utilisateurs
SQL Server et aux rôles de bases de données SQL Server d'être membres d'autres rôles. Il en
résulte un système de sécurité hiérarchisé permettant d'appliquer des autorisations à
travers plusieurs niveaux de rôles et de membres. Mais vous pouvez aussi parfois souhaiter
limiter les autorisations accordées à un utilisateur ou à un rôle. Le refus d'autorisation sur
un compte utilisateur :
• Supprime une autorisation précédemment accordée à l'utilisateur, au groupe ou au rôle ;
• Désactive une autorisation héritée d'un ou de plusieurs autres rôles ;
• Vous assure qu'un utilisateur, un groupe ou un rôle n'héritera pas à l'avenir d'une
autorisation émanant d'un groupe ou d'un rôle de niveau supérieur.
Par exemple, vous pouvez être amené à permettre à tous les employés titularisés de votre
société d'accéder à plusieurs tables dans une base de données, à l'exception de quelques
nouveaux employés répartis un peu partout dans l'Enterprise que vous voulez empêcher de
pouvoir consulter la table Employee.
Créez un rôle pour chaque service de la société et ajouter tous les employés dans le rôle de
leur service. Créez ensuite un rôle global Personnel, puis lui ajouter chacun des rôles de
service auxquels vous accorderez les autorisations requises pour afficher les tables. À ce
stade, chaque employé de la société sera en mesure d'afficher toutes les tables parce qu'il
aura hérité de l'autorisation du rôle Personnel par le biais des rôles de son service.
Pour interdire de manière sélective à certains employés d'afficher la table Employee, créez
un rôle Non_Employeedans lequel vous ajouterez les quelques employés qui ne doivent pas
pouvoir afficher la table.
Lorsque vous refusez à Non_Employeel'autorisation d'afficher Employee, l'accès à cette
table est supprimé pour tous les membres du rôle Non_Employee, sans affecter le reste des
employés de la société.
Vous pouvez également refuser des autorisations à un utilisateur individuel. Dans l'exemple
précédent, un utilisateur ne faisant pas partie du personnel peut avoir un compte
Windows2008 dans le cadre d'un travail sur un projet à court terme dans la base de
données. Vous pouvez refuser les autorisations d'afficher la table Employeeà son compte
utilisateur individuel sans devoir créer un rôle de base de données SQL Server spécifique.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Remarque :Il n'est possible de refuser des autorisations qu'à des comptes utilisateur dans la
base de données en cours, pour les objets figurant dans cette base de données.
• Interdiction d'accès par refus d'autorisations sur un objet :
A l'aide de Transact-Sql
Utilisez DENY pour créer une entrée dans le système de sécurité qui refuse une autorisation
à un compte de sécurité dans la base de données courante et empêche ce dernier d'hériter
de l'autorisation via les membres de son groupe ou de son rôle.
Syntaxe
Autorisations sur un objet :
DENY
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ (column [ ,...n ] ) ] ON { table | view }
| ON { table| view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure| extended_procedure}
| ON { user_defined_function}
}
TO security_account[ ,...n ]
[ CASCADE ]
Exemple1 : Cet exemple refuse l'autorisation Select, Insert et Update pour le compte Reguig
Radia sur la table Table1
USE MaBaseDeDonnees
GO
DENY SELECT, INSERT, UPDATE ON Table1 TO [ReguigRadia]
GO
Exemple2 : Cet exemple refuse l'autorisation Select à tous les membres du rôle Public pour
la table Table1.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
DENY SELECT ON Table1 TO Public
 Refus d'autorisations d'instruction aux utilisateurs d'une base de données :
A l'aide de Transact-Sql :
Syntaxe
Autorisations sur une instruction :
DENY { ALL | statement [ ,...n ] }
TO security_account[ ,...n ]
Exemple : Cet exemple refuse plusieurs autorisations d'instruction aux utilisateurs
Selmi Farid et ReguigRadia.
DENY CREATE TABLE, BACKUP DATABASE TO [ReguigRadia], [SelmiFarid]
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
III.
T.S.
Retrait d'autorisation :
Vous pouvez retirer une autorisation précédemment accordée ou refusée. Le retrait d'une
autorisation est similaire à un refus dans la mesure où les deux actions suppriment une
autorisation accordée au même niveau.
Cependant, bien que le retrait supprime une autorisation accordée, cela n'empêche pas
l'utilisateur, le groupe ou le rôle d'hériter d'une autorisation accordée à un niveau
supérieur. Par conséquent, si vous retirez l'autorisation d'affichage d'une table accordée à
un utilisateur, il est possible que celui-ci puisse encore l'afficher parce que l'autorisation
avait été accordée à un rôle auquel il appartient.
De même, il est également possible de supprimer une autorisation précédemment refusée
en retirant le refus d'autorisation. Cependant, si un utilisateur a d'autres autorisations
refusées au niveau du groupe ou du rôle, l'accès lui sera toujours refusé.
Remarque :Il n'est possible de retirer des autorisations que sur des comptes utilisateur de la base
de données en cours, pour les objets figurant dans cette base.
• Révocation des autorisations sur un objet :
A l'aide de Transact-Sql
Utilisez REVOKE pour supprimer une autorisation accordée ou refusée antérieurement à un
compte utilisateur pour la base de données en cours.
Autorisations sur un objet :
REVOKE [ GRANT OPTION FOR ]
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ (column [ ,...n ] ) ] ON { table| view }
| ON { table| view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure| extended_procedure}
| ON { user_defined_function}
}
{ TO | FROM }
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
security_account[ ,...n ]
[ CASCADE ]
[ AS { group | role} ]
Autorisations : Les autorisations REVOKE reviennent par défaut aux membres du rôle de
serveur
fixe
sysadmin,
des
rôles
de
bases
de
données
fixes
db_owneret
db_securityadminet aux propriétaires des objets.
Exemple1 : Cet exemple Retire le refus Select, Insert et Update pour le compte Reguig Radia
sur la table Table1.
USE MaBaseDeDonnees
GO
REVOKE SELECT, INSERT, UPDATE ON Table1 TO [ReguigRadia]
GO
Exemple2 : Cet exemple retire le refus Select à tous les membres du rôle Public pour la
table Table1.
REVOKE SELECT ON Table1 TO Public
 Révocation d'autorisations d'instruction auprès des utilisateurs d'une base de
données :
A l'aide de Transact-Sql
Syntaxe:
Autorisations sur une instruction :
REVOKE { ALL | statement [ ,...n ] }
FROM security_account[ ,...n ]
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Chapitre04 :
Les options
Des
Bases De
Données.
Spécialité : INFORMATIQUE
T.S.
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Fiche Technique
Spécialité:Informatique.
Option : Base de Données.
Enseignante:Nadjette Mouici.
Date :.
Nocours : 03No séance:03.
Module:Administration à l’aide de SQL SERVER.
Temps précis : 01h 30mn.
Chapitre04:Options de Base de Données. Temps alloué:………h……….mn.
Thème : Les catégories d’options de BDD SQL SERVER.
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE CITER LES
CATEGORIES D’OPTIONS DES BASES DE DONNEES SOUS SQL
SERVER;
A L’AIDE DES FICHIERS DE BDD ET SANS ERREURS.
Outils utilisés:
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Chapitre 04 :
Les options de
bases de
données SQL
SERVER.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
I.
T.S.
Définition des options de base de données :
Pour chaque base de données, il est possible de définir un certain nombre d'options au niveau de la base de
données afin de déterminer ses caractéristiques. Seuls l'administrateur système, les propriétaires de base de
données, les membres des rôles de serveur fixes sysadminet dbcreatoret du rôle de base de données fixe
db_ownerpeuvent modifier ces options.
Ces options sont propres à une base de données, et n'affectent pas les autres. Les options de base de
données peuvent être configurées à l'aide de la clause SET de l'instruction ALTER DATABASE, de la
procédure stockée système sp_dboptionou, dans certains cas, de SQL Server Enterprise Manager.
Syntaxe de la procédure stockée système sp_dboption
sp_dboption[ [@dbname = ] 'database' ]
[ ,[ @optname = ] 'option_name' ]
[ ,[ @optvalue = ] 'value' ]
Arguments
Description
[@dbname =] 'database'
Nom de la base de données dans laquelle il faut paramétrer l'option
spécifiée
[@optname =] 'option_name'
Nom de l'option que vous voulez paramétrer.
[@optvalue =] 'value'
Nouveau paramètre de l'option spécifiée par option_nam. value peut être
vrai (true) ou faux (false), ou activé (on) ou désactivé (off).
Après que vous avez défini une option de base de données, un point de contrôle est automatiquement créé de
façon à ce que la modification entre en vigueur immédiatement.
Pour modifier les valeurs par défaut de l'une des options de base de données afin qu'elles s'appliquent aux
nouvelles base de données créées, modifiez l'option de base de données appropriée dans la base de données
model.
Il existe cinq catégories d'options de base de données :
• Options automatiques ;
• Options de curseur ;
• Options de récupération ;
• Options sql ;
• Options d'état.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Fiche Technique
Spécialité:Informatique.
Option : Base de Données.
Enseignante:Nadjette Mouici.
Date :.
Nocours : 03No séance:03.
Module:Administration à l’aide de SQL SERVER.
Temps précis : 01h 30mn.
Chapitre04:Options de Base de Données. Temps alloué:………h……….mn.
Thème : Les options automatiques.
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE CITER LES
OPTIONS AUTOMATIQUES DES BASES DE DONNEES SOUS SQL
SERVER;
A L’AIDE DES FICHIERS DE BDD ET SANS ERREURS.
Outils utilisés:
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
1. Options automatiques :
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Les options automatiques (auto) contrôlent certains comportements automatiques.

AUTO_CLOSE
Si cette option est définie avec la valeur ON, la base de données est fermée proprement lorsque le dernier
utilisateur la quitte et que tous ses processus se terminent, ce qui libère ainsi les ressources.
Par défaut, cette option est activée pour toutes les bases de données avec SQL Server 2000 Desktop Engine,
et désactivée dans toutes les autres éditions, quel que soit le système d'exploitation. La base de données
s'ouvre automatiquement lorsqu'un utilisateur tente de l'utiliser à nouveau.
Si cette option possède la valeur OFF, la base de données reste ouverte même si aucun utilisateur ne l'utilise.

AUTO_CREATE_STATISTICS
Si cette option possède la valeur ON, des statistiques sont créées automatiquement sur les colonnes utilisées
dans un prédicat.
L'ajout de statistiques améliore les performances des requêtes car l'optimiseur de SQL Server est capable de
déterminer plus précisément comment évaluer une requête. Si les statistiques ne sont pas utilisées, SQL
Server les supprime automatiquement. Si cette option possède la valeur OFF, SQL Server ne crée pas
automatiquement les statistiques et celles-ci peuvent alors être créées manuellement.
AUTO_CREATE_STATISTICS est activée par défaut.

AUTO_UPDATE_STATISTICS
Si cette option possède la valeur ON, les statistiques existantes sont automatiquement mises à jour une fois
devenues obsolètes en raison de la modification de données des tables.
Si elle est possède la valeur OFF, les statistiques ne sont pas mises à jour automatiquement et peuvent être
mises à jour manuellement.
AUTO_UPDATE_STATISTICS possède par défaut la valeur ON.

AUTO_SHRINK
Si cette option possède la valeur ON, les fichiers de données et journaux des transactions peuvent être,
compactés automatiquement par SQL Server.
Si elle possède la valeur OFF, les fichiers de base de données ne sont pas compactés automatiquement lors
des vérifications périodiques de l'espace inutilisé.
Par défaut, cette option est activée pour toutes les bases de données avec SQL Server Édition Desktop, et
désactivée dans toutes les autres éditions, quel que soit le système d'exploitation.
AUTO_SHRINK ne réduit la taille du journal des transactions que si le modèle de récupération SIMPLE est
associé à la base de données ou que le journal est sauvegardé.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
L'option AUTO_SHRINK provoque le compactage dès qu'un fichier comprend plus de 25 % d'espace inutilisé.
Le fichier est compacté pour que 25 % du fichier demeure inutilisé ou à sa taille initiale au moment de sa
création, selon celle des deux valeurs qui est la plus grande.
Il est impossible de compacter une base de données en lecture seule.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Fiche Technique
Spécialité:Informatique.
Option : Base de Données.
Enseignante:Nadjette Mouici.
Date :.
Nocours : 03No séance:03.
Module:Administration à l’aide de SQL SERVER.
Temps précis : 01h 30mn.
Chapitre04:Options de Base de Données. Temps alloué:………h……….mn.
Thème : Les options de curseurs.
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE CITER LES
OPTIONS DE CURSEURS DES BASES DE DONNEES SOUS SQL
SERVER;
A L’AIDE DES FICHIERS DE BDD ET SANS ERREURS.
Outils utilisés:
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
2. Options de curseur :
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Les options de curseur contrôlent le comportement et la portée du curseur.

CURSOR_CLOSE_ON_COMMIT
Si cette option possède la valeur ON, tous les curseurs ouverts sont fermés automatiquement (conformément
à SQL-92) lors de la validation d'une transaction.
Cette option est désactivée (OFF) par défaut, et les curseurs restent ouverts d'une transaction à l'autre, ne se
fermant que lors de la fermeture de la connexion ou sur demande explicite.

CURSOR_DEFAULT LOCAL | GLOBAL
Si, alors que CURSOR_DEFAULT LOCAL est défini, aucun curseur n'est défini comme GLOBAL lors de sa
création, le curseur est de portée locale pour le lot d'instructions, la procédure stockée ou le déclencheur dans
lequel il a été créé. Le nom du curseur n'est valide que dans cette portée. Le curseur peut être référencé par
des variables de curseur locales du lot d'instructions, de la procédure stockée ou du déclencheur, ou bien par
un paramètre OUTPUT d'une procédure stockée. Le curseur est désalloué implicitement à la fin du lot
d'instructions, de la procédure stockée ou du déclencheur, à moins d'avoir été renvoyé dans un paramètre
OUTPUT. S'il a été renvoyé dans un paramètre OUTPUT, le curseur est désalloué lorsque la dernière variable
qui y fait référence est désallouée, ou lorsqu'il est hors de portée.
Si CURSOR_DEFAULT GLOBAL est défini et qu'aucun curseur n'est défini comme LOCAL lors de sa création,
le curseur est de portée globale pour la connexion. Toute procédure stockée ou tout lot d'instructions exécuté
par la connexion peut faire référence au nom du curseur. Le curseur n'est dés alloué implicitement qu'au
moment de la déconnexion.
CURSOR_DEFAULT GLOBAL est le paramètre par défaut.
Fiche Technique
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Spécialité:Informatique.
Option : Base de Données.
Enseignante:Nadjette Mouici.
Date :.
Nocours : 03No séance:03.
Module:Administration à l’aide de SQL SERVER.
Temps précis : 01h 30mn.
Chapitre04:Options de Base de Données. Temps alloué:………h……….mn.
Thème : Les options de récupération.
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE CITER LES
OPTIONS DE RECUPERATION DES BASES DE DONNEES SOUS SQL
SERVER;
A L’AIDE DES FICHIERS DE BDD ET SANS ERREURS.
Outils utilisés:
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
3.
Options de récupération :
Les options de récupération contrôlent le modèle de récupération de la base de données.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR

T.S.
RECOVERY FULL | BULK_LOGGED | SIMPLE :
Valeur: FULL, BULK_LOGGED, SIMPLE
Si l'option FULL est spécifiée, les sauvegardes de la base de données et celles du journal des transactions sont
utilisées pour assurer la récupération complète à la suite d'une panne de support. Toutes les
opérations,notamment les opérations en bloc telles que SELECT INTO, CREATE INDEX et le chargement en
bloc des
données, sont entièrement consignées.
Si l'option BULK_LOGGED est spécifiée, l'enregistrement de toutes les opérations SELECT INTO, CREATE
INDEX et de chargement en bloc des données, est minimal et requiert, par conséquent, moins d'espace dans le
journal. En contrepartie de meilleures performances et d'une utilisation moindre de l'espace du journal, le risque
de perte de données est plus important que dans le cas de la récupération complète.
Si l'option SIMPLE est spécifiée, la base de données peut être récupérée uniquement jusqu'à la dernière
sauvegarde complète ou différentielle.
L'option SIMPLE est le paramètre par défaut pour le moteur de données et SQL Server Édition Desktop, tandis
que l'option FULL est le paramètre par défaut pour toutes les autres éditions.

TORN_PAGE_DETECTION :
Cette option de récupération permet à SQL Server de détecter des opérations d'E/S incomplètes à la suite
d'une coupure de courant ou de toute panne d'alimentation du système.
Si cette option possède la valeur ON, un bit est inversé pour chaque secteur de 512 octets d'une page de base
de données de 8 kilo-octets (Ko) lorsque la page est écrite sur le disque. Si, lorsque SQL Server lit par la suite
la page, le bit n'a pas l'état voulu, cela signifie que la page a été écrite incorrectement, et elle est considérée
comme endommagée. Les pages endommagées sont généralement détectées au cours de la récupération,
étant donné que toute page incorrectement écrite peut être lue au cours de ce processus.
Bien que la taille des pages de base de données SQL Server soit de 8 Ko, les disques réalisent les opérations
d'E/S en utilisant des secteurs de 512 octets. Par conséquent, il est nécessaire d'écrire 16 secteurs par page
de base de données. Une déchirure de page peut se produire si le système tombe en panne (suite à une panne
de courant, par exemple) entre le moment où le système d'exploitation écrit le premier secteur de 512 octets
sur le disque et la fin de l'opération d'E/S de 8 Ko. Si le premier secteur de la page de base de données est
écrit correctement avant la panne, la page de base de données sur le disque semblera avoir été mise à jour,
même si cette opération risque de ne pas avoir réussi.
Remarque :L'utilisation de caches disque avec batterie de secours permet de garantir que les données sont
correctement écrites sur le disque ou ne le sont pas du tout.
Si une page endommagée est détectée, une erreur d'E/S est générée et la connexion est terminée. Si la page
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
endommagée est détectée au cours de la récupération, la base de données est également déclarée suspecte.
La sauvegarde de la base de données doit être restaurée et toutes les sauvegardes des journaux des
transactionsdoivent être appliquées, en raison d'une incohérence physique.
TORN_PAGE_DETECTION est activée par défaut.
Fiche Technique
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Spécialité:Informatique.
Option : Base de Données.
Enseignante:Nadjette Mouici.
Date :.
Nocours : 04No séance:03.
Module:Administration à l’aide de SQL SERVER.
Temps précis : 01h 30mn.
Chapitre04:Options de Base de Données. Temps alloué:………h……….mn.
Thème : Les options SQL.
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE CITER LES
OPTIONS SQL DES BASES DE DONNEES SOUS SQL SERVER;
A L’AIDE DES FICHIERS DE BDD ET SANS ERREURS.
Outils utilisés:
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
4. Options SQL
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Les options SQL contrôlent les options de conformité ANSI.

ANSI_NULL_DEFAULT
Lorsque cette option est activée (ON), tous les types de données définis par l'utilisateur ou les colonnes qui ne
sont pas définies explicitement comme NOT NULL dans une instruction CREATE TABLE ou ALTER
TABLE autorisent, par défaut, les valeurs nulles. Les colonnes sur lesquelles sont définies des contraintes
suivent les règles de contrainte, quelle que soit la valeur de ce paramètre.
Syntaxe: SET ANSI_NULL_DFLT_ON {ON | OFF}

ANSI_NULLS
Si cette option possède la valeur ON, toutes les comparaisons avec une valeur NULL donnent comme résultat
NULL (inconnu). Si elle possède la valeur OFF, les comparaisons des valeurs non-Unicode avec une valeur
NULL donnent comme résultat TRUE si les deux valeurs sont NULL.
Par défaut, l'option de base de données ANSI_NULLS est désactivée (OFF).
SET ANSI_NULLS doit également être défini avec la valeur ON lorsque vous créez ou manipulez des index
dans des colonnes calculées ou des vues indexées.
Syntaxe : SET ANSI_NULLS {ON | OFF}

ANSI_PADDING
Si cette option possède la valeur ON, les espaces à droite dans les valeurs de type caractère insérées dans
des colonnes varcharet les zéros à droite dans les valeurs binaires insérées dans des colonnes varbinaryne
sont pas supprimés. Les valeurs ne sont pas complétées à concurrence de la longueur de la colonne.
Si cette option possède la valeur OFF, les espaces à droite (dans les colonnes varchar) et les zéros à droite
(dans les colonnes varbinary) sont supprimés. Cette valeur affecte uniquement la définition de nouvelles
colonnes.
Les colonnes char(n) et binary(n) qui acceptent les valeurs NULL sont complétées dans toute leur largeur de
zéros et d'espaces à droite si SET ANSI_PADDING possède la valeur ON ; par contre, ces espaces et zéros à
droite sont supprimés si l'option est désactivée (OFF). Les colonnes char(n) et binary(n) qui n'acceptent pas
les valeurs NULL sont toujours complétées dans toute leur largeur.
Important :Il est recommandé que l'option SET ANSI_PADDING soit toujours activée (ON). SET
ANSI_PADDING doit être activé (valeur ON) lors de la création ou de la manipulation d'index dans des
colonnes calculées ou des vues indexées.
Syntaxe:SET ANSI_PADDING { ON | OFF }

ANSI_WARNINGS
Si cette option possède la valeur ON, des erreurs ou avertissements sont générés lorsque des conditions telles
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
qu'une division par zéro se produisent, ou que des valeurs NULL apparaissent dans des fonctions d'agrégation.
Si elle possède la valeur OFF, aucun avertissement n'est émis lorsque des valeurs NULL apparaissent dans
des fonctions d'agrégation et que des valeurs NULL sont renvoyées quand des conditions telles qu'une division
par zéro se produisent.
ANSI_WARNINGS est désactivée par défaut.
SET ANSI_WARNINGS doit être défini avec la valeur ON lorsque vous créez ou manipulez des index dans des
colonnes calculées ou des vues indexées.
Syntaxe:SET ANSI_WARNINGS { ON | OFF }

ARITHABORT
Si cette option possède la valeur ON, un dépassement de capacité ou une division par zéro provoquent l'arrêt
du traitement de la requête ou du lot d'instructions. Si l'erreur se produit dans une transaction, cette dernière
est annulée (roll back).
Si cette option possède la valeur OFF, un message d'avertissement s'affiche si l'une de ces erreurs se produit,
mais le traitement de la requête, du lot d'instructions ou de la transaction se poursuit, comme s'il n'y avait pas
d'erreur.
SET ARITHABORT doit être défini avec la valeur ON lorsque vous créez ou manipulez des index dans des
colonnes calculées ou des vues indexées
Syntaxe:SET ARITHABORT { ON | OFF }

NUMERIC_ROUNDABORT
Si cette option possède la valeur ON, une erreur est générée lorsqu'une perte de précision survient dans une
expression.
Lorsqu'elle possède la valeur OFF, les pertes de précision ne génèrent pas de messages d'erreur et le résultat
est arrondi en fonction de la précision de la colonne ou de la variable contenant le résultat.
SET NUMERIC_ROUNDABORT doit être défini avec la valeur OFF lorsque vous créez ou manipulez des index
dans des colonnes calculées ou des vues indexées.
Syntaxe:SET NUMERIC_ROUNDABORT { ON | OFF }

CONCAT_NULL_YIELDS_NULL
Si cette option possède la valeur ON, lorsqu'un des opérandes d'une opération de concaténation a la valeur
NULL, le résultat de l'opération est NULL. Par exemple, la concaténation de la chaîne de caractères « This is»
et de NULL donne la valeur NULL, et non pas la valeur « This is ».
Si cette option possède la valeur OFF, la concaténation d'une valeur NULL avec une chaîne de caractères a
pour résultat la chaîne de caractères ; la valeur NULL est considérée comme une chaîne de caractères vide.
CONCAT_NULL_YIELDS_NULL est désactivée par défaut.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
SET CONCAT_NULL_YIELDS_NULL doit être défini avec la valeur ON lorsque vous créez ou manipulez des
index dans des colonnes calculées ou des vues indexées
Syntaxe:SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

QUOTED_IDENTIFIER
Si cette option possède la valeur ON, les identificateurs peuvent être encadrés par des guillemets doubles et
les littéraux, par des guillemets simples.
Toutes les chaînes délimitées par des guillemets doubles sont considérées comme des identificateurs d'objet.
Les identificateurs entre guillemets n'ont pas à respecter les règles Transact-SQL applicables aux
identificateurs. Ils peuvent être des mots clés et contenir des caractères généralement interdits dans les
identificateurs de Transact-SQL. Si un guillemet simple (') fait partie de la chaîne littérale, il pourra être
représenté par un guillemet double ('').
Si cette option possède la valeur OFF, ce qui est le cas par défaut, les identificateurs ne peuvent pas être mis
entre guillemets et doivent respecter toutes les règles Transact-SQL applicables aux identificateurs. Les
chaînes littérales peuvent être délimitées par des guillemets simples ou doubles.
SQL Server permet également de délimiter les identificateurs par des crochets ([ ]). Les identificateurs entre
crochets peuvent toujours être utilisés, quels que soient le paramètre de QUOTED_IDENTIFIER.
SET QUOTED_IDENTIFIER doit être défini avec la valeur ON lorsque vous créez ou manipulez des index dans
des colonnes calculées ou des vues indexées.
Syntaxe:SET QUOTED_IDENTIFIER { ON | OFF }

RECURSIVE_TRIGGERS
Si cette option possède la valeur ON, les déclencheurs peuvent être activés de façon récurrente. Si elle
possède la valeur OFF, ce qui est le cas par défaut, les déclencheurs ne peuvent pas être activés de façon
récurrente.
Remarque :Seule la récurrence directe est inhibée lorsque RECURSIVE_TRIGGERS possède la valeur
OFF. Pour désactiver la récurrence indirecte, vous devez en outre affecter la valeur 0 à l'option de serveur
déclencheurs imbriqués (nested triggers).
Syntaxe : SET RECURSIVE_TRIGGERS {ON|OFF}
Fiche Technique
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Spécialité:Informatique.
Option : Base de Données.
Enseignante:Nadjette Mouici.
Date :.
Nocours : 04
Module:Administration à l’aide de SQL SERVER.
No séance:03.
Temps précis : 01h 30mn.
Chapitre04: Options de Base de Données. Temps alloué:………h……….mn.
Thème : Les options de d’état.
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE CITER
LES TROIS OPTIONS D’ETAT-CONNEXION, LECTURE | ECRITURE ET
UTILISATION-DES BASES DE DONNEES SOUS SQL SERVER; A L’AIDE
DE LANGAGE TRANSACT SQL ET SANS ERREURS.
Outils utilisés:
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
1. Options d'état :
Les options d'état déterminent si la base de données est en ligne ou hors connexion, si elle se trouve
en mode lecture seule et les utilisateurs qui peuvent s'y connecter. Une clause d'arrêt permet de définir
la façon dont les connexions sont interrompues lorsque la base de données passe d'un état à un autre.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR

T.S.
OFFLINE | ONLINE :
Si la valeur OFFLINE est spécifiée, la base de données est fermée et arrêtée proprement, puis
marquée comme étant hors connexion. Tant que la base de données est déconnectée, elle ne peut
pas être modifiée.
Si la valeur ONLINE est spécifiée, la base de données est ouverte et disponible. ONLINE est le
paramètre par défaut.
Exemple1:
EXEC sp_dboption 'BD1', 'offline', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'offline', 'FALSE'
Exemple2 :
EXEC sp_dboption 'BD1', 'online', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'online', 'FALSE'

READ ONLY | READ WRITE :
Lorsque l'option READ_ONLY est spécifiée, la base de données se trouve en mode lecture seule. Les
utilisateurs sont libres d'en récupérer les données mais ne peuvent, en aucun cas, les modifier. Étant
donné qu'une base de données en lecture seule interdit la modification des données,
Lorsque l'option READ WRITE est spécifiée, les utilisateurs peuvent enlever et modifier des
données.
READ_WRITE est le paramètre par défaut.
Exemple1:
EXEC sp_dboption 'BD1', 'read only', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'read only', 'FALSE'
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Exemple2:
EXEC sp_dboption 'BD1', 'read_write', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'read_write', 'FALSE'

DBO USE ONLY :
Si vrai (true), seul le propriétaire de la base de données peut utiliser celle-ci.
Par défaut cette option est à False.
Exemple :
EXEC sp_dboption 'BD1', 'dbo use only', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'dbo use only', 'FALSE'

SELECT INTO / BULKCOPY :
Si cette option possède la valeur vraie « true », l'instruction SELECT INTO et les copies en bloc
rapides sont autorisées.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Sinon (l’option possède la valeur fausse « false ») l'instruction SELECT INTO et les copies en bloc
rapides ne sont autorisées.
Exemple:
EXEC sp_dboption 'BD1', 'select into /bulkcopy', 'TRUE'
Ou EXEC sp_dboption 'BD1', ' select into /bulkcopy', 'FALSE'
 SINGLE_USER | RESTRICTED_USER | MULTI_USER :
 SINGLE_USER:
Si SINGLE_USER est spécifié, un seul utilisateur peut se connecter à la base de données à la fois.
Toutes les autres connexions utilisateur sont interrompues, et Les nouvelles tentatives de connexion
sont refusées.
La base de données reste en mode SINGLE_USER même si l'utilisateur qui a défini
l'option se déconnecte.
À partir de ce moment, un autre utilisateur (et un seul) peut se connecter à la base de données.
Exemple:
EXEC sp_dboption 'BD1', 'single_user', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'single_user', 'FALSE'
 RESTRICTED_USER:
RESTRICTED_USER permet uniquement aux membres du rôle de base de données fixe db_owneret
des rôles de serveur fixes dbcreatoret sysadminde se connecter à la base de données, mais n'en
limite pas le nombre.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Les nouvelles tentatives de connexion à l'initiative d'utilisateurs non qualifiés sont refusées.
Exemple:
EXEC sp_dboption 'BD1', 'restricted_user', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'restricted_user', 'FALSE'
 MULTI_USER :
MULTI_USER permet à tous les utilisateurs bénéficiant des autorisations appropriées de se connecter
à la base de données.
MULTI_USER est le paramètre par défaut.
Exemple:
EXEC sp_dboption 'BD1', 'Multi_user', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'Multi_user', 'FALSE'
Fiche Technique
Spécialité:Informatique.
Enseignante:Nadjette Mouici.
Spécialité : INFORMATIQUE
Option : Base de Données.
Date : 17/11/2014.
Nocours : 04
No séance:03.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Module:Administration à l’aide de SQL SERVER.
T.S.
Temps précis : 01h 30mn.
Chapitre04: Options de Base de Données. Temps alloué:………h……….mn.
Thème : Les options d’état.
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE CITER
LES TROIS OPTIONS D’ETAT-CONNEXION, LECTURE | ECRITURE ET
UTILISATION-DES BASES DE DONNEES SOUS SQL SERVER; A L’AIDE
DE LANGAGE TRANSACT SQL ET SANS ERREURS.
Outils utilisés:
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
5. Options d'état :
Les options d'état déterminent si la base de données est en ligne ou hors connexion, si elle se trouve en mode
lecture seule et les utilisateurs qui peuvent s'y connecter. Une clause d'arrêt permet de définir la façon dont les
connexions sont interrompues lorsque la base de données passe d'un état à un autre.

OFFLINE | ONLINE :
Si la valeur OFFLINE est spécifiée, la base de données est fermée et arrêtée proprement, puis marquée
comme étant hors connexion. Tant que la base de données est déconnectée, elle ne peut pas être modifiée.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Si la valeur ONLINE est spécifiée, la base de données est ouverte et disponible. ONLINE est le paramètre par
défaut.
Exemple: EXEC sp_dboption 'BD1', 'offline', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'offline', 'FALSE'

READ ONLY | READ WRITE :
Lorsque l'option READ_ONLY est spécifiée, la base de données se trouve en mode lecture seule. Les
utilisateurs sont libres d'en récupérer les données mais ne peuvent, en aucun cas, les modifier. Étant donné
qu'une base de données en lecture seule interdit la modification des données, il s'ensuit que :
• La récupération automatique est ignorée au démarrage du système ;
• Le compactage de la base de données est impossible ;
• Aucun verrouillage n'a lieu dans une base de données en lecture seule, ce qui contribue à améliorer les
performances des requêtes.
Lorsque l'option READ WRITE est spécifiée, les utilisateurs peuvent extraire et modifier des données.
READ_WRITE est le paramètre par défaut.
Exemple :EXEC sp_dboption 'BD1', 'read only', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'read only', 'FALSE'

DBO USE ONLY :
Si vrai (true), seul le propriétaire de la base de données peut utiliser celle-ci. Par défaut cette option est à
False.
Exemple :EXEC sp_dboption 'BD1', 'dbo use only', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'dbo use only', 'FALSE'

SINGLE_USER | RESTRICTED_USER | MULTI_USER :
Si SINGLE_USER est spécifié, un seul utilisateur peut se connecter à la base de données à la fois. Toutes les
autres connexions utilisateur sont interrompues. Le créneau d'interruption de la connexion est géré par la
clause d'arrêt de l'instruction ALTER DATABASE. Les nouvelles tentatives de connexion sont refusées. La
base de données demeure en mode SINGLE_USER même si l'utilisateur qui a défini l'option se déconnecte.
À partir de ce moment, un autre utilisateur (et un seul) peut se connecter à la base de données.
Pour que plusieurs connexions soient possibles, vous devez modifier l'état de la base de données vers le mode
RESTRICTED_USER ou MULTI_USER.
RESTRICTED_USER permet uniquement aux membres du rôle de base de données fixe db_owneret des
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
rôles de serveur fixes dbcreatoret sysadminde se connecter à la base de données, mais n'en limite pas le
nombre. Les utilisateurs n'étant pas membres de ces rôles sont déconnectés dans le créneau spécifié par la
clause d'arrêt de l'instruction ALTER DATABASE. En outre, les nouvelles tentatives de connexion à l'initiative
d'utilisateurs non qualifiés sont refusées.
MULTI_USER permet à tous les utilisateurs bénéficiant des autorisations appropriées de se connecter à la
base de données. MULTI_USER est le paramètre par défaut.
Exemple:EXEC sp_dboption 'BD1', 'single user', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'single user', 'FALSE'

TRUNC. LOG ON CHKPT :
Si vrai (true), un point de contrôle vide la partie inactive du journal (les transactions validées
sontsupprimées)lorsque la base de données est dans le mode troncature de journal. Il s'agit de la seule option
quevous pouvez valider pour la base de données master.
Exemple : EXEC sp_dboption 'BD1', 'trunc. log on chkpt', 'TRUE'
Ou EXEC sp_dboption 'BD1', 'trunc. log on chkpt', 'FALSE'

SELECT INTO /BULKCOPY :
Si vrai (true), l'instruction SELECT INTO et les copies en bloc rapides sont autorisées.
Exemple:EXEC sp_dboption 'BD1', 'select into /bulkcopy', 'TRUE'
Ou EXEC sp_dboption 'BD1', ' select into /bulkcopy', 'FALSE'
II.
Affichage des options de base de données :
Utilisez la procédure stockée système Sp_helpdbSignale toutes les bases de données situées sur un serveur
si aucun nom de base de données n'est spécifier sinon signale une base de données spécifiée uniquement.
Fournit le nom, la taille, le propriétaire, l'identificateur, la date de création et les options de l base de données
Syntaxe:sp_helpdb[ [ @dbname= ] 'name' ]
Exemple 1:Cet exemple affiche des informations sur la base de données Northwind.
Execsp_helpdbNorthwind
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Exemple 2:Cet exemple affiche les informations relatives à toutes les bases de données installées sur le
serveur sur lequel s'exécute SQL Server.
Execsp_helpdb
III.
Gestion des bases de données :
A mesure que votre base de données prend de l'ampleur ou subit des modifications, vous pouvez accroître ou
compacter sa taille automatiquement ou manuellement, lorsqu'une base de données devient obsolète, vous
pouvez la supprimer, ainsi que tous les fichiers qui lui sont associés.
1. Gestion de la croissance de fichiers journaux et de données :
Lorsque les fichiers de données augmentent ou que l'activité de modification des données s'intensifie, vous
pouvez être amené à accroître la taille des fichiers journaux ou de données. Vous pouvez gérer la croissance
de la base de données à l'aide de SQL Enterprise Manger ou de l'instruction ALTER DATABASE. Vous devez
vous placer dans la BD master pour utiliser cette instruction.
Vous pouvez contrôler la taille de la base de données en effectuant les tâches suivantes:
-
Configurer la base de données et les fichiers journaux afin qu'ils augmentent automatiquement.
-
Augmentez ou diminuez manuellement la taille actuelle ou maximale de la base de données et des
fichiers journaux existants.
-
Ajouter manuellement des fichiers de base de données et de journaux secondaires.
A l'aide de TransactSql
Syntaxe
ALTER DATABASE database
{ ADD FILE <filespec> [ ,...n ] [ TO FILEGROUP filegroup_name]
| ADD LOG FILE <filespec>[ ,...n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE <filespec>
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name{filegroup_property| NAME = new_filegroup_name}
| SET <optionspec>[ ,...n ] [ WITH < termination > ]
| COLLATE <collation_name>
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
}
<filespec> ::=
( NAME= logical_file_name
[ ,NEWNAME = new_logical_name]
[ ,FILENAME = 'os_file_name' ]
[ ,SIZE = size ]
[ ,MAXSIZE = { max_size| UNLIMITED } ]
[ ,FILEGROWTH = growth_increment] )
Exemples 1: L'exemple suivant augmente la taille d'un des fichiers ajoutés à la BD Archive.
USE master
GO
ALTER DATABASE Archive
MODIFY FILE
(NAME = arch2,
SIZE = 12MB)
GO
Exemple 2 : L'exemple suivant ajoute un nouveau fichier de données de 5 Mo à la BD Archive.
USE master
GO
ALTER DATABASE Archive
ADD FILE
( NAME = Arch3,
FILENAME = 'c:\program files\microsoftsql server\mssql\data\archdat3.ndf',
SIZE = 5MB,
MAXSIZE = 10,
FILEGROWTH = 1)
Remarque :La taille maximale de la base de données est déterminée par la quantité d'espace disponible sur le
disque et par les limites dues à la licence pour la version de SQL Server que vous utilisez.
Remarque :SQL Server ne peut pas compacter un fichier à une taille inférieure à celle des données ou à celle
spécifiée dans le paramètre SIZE de l'instruction CREATE DATABASE.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
2. Compactage d'une base de données ou d'un fichier :
Lorsque trop d'espace est alloué ou que les besoins en espace diminuent, vous pouvez compacter la totalité de
la base de données ou seulement certains de ses fichiers.
A)- Compactage de la totalité d'une base de données :
Vous pouvez compacter la totalité d'une base de données à l'aide de SQL Server Enterprise Manager ou de
l'instructionDBCC SHRINKDATABASE. Cette instruction compacte immédiatement la taille de tous les fichiers
de données de la base. Les fichiers journaux sont compactés par le biais d'une opération de compactage
différée comme si tous les fichiers journaux existaient dans un pool de journaux contigus. Les fichiers journaux
sont réinitialisés lorsque le journal est tronqué ; SQL Server essaie de compacter les fichiers journaux tronqués
en leur donnant une taille aussi proche que possible de la taille cible.
Syntaxe:
DBCC SHRINKDATABASE (nom_base_de_données [, pourcentage_cible] [,
{NOTRUNCATE | TRUNCATEONLY) ])
Argument
Description
pourcentage_cible
Spécifie le pourcentage d'espace libre souhaité dans le fichier de la base de
données lorsque celle-ci a été compactée.
NOTRUNCATE
Permet de conserver l'espace libéré dans les fichiers de la base de données.
Par défaut, l'espace libéré est restitué au système d'exploitation
TRUNCATEONLY
Restitue au système d'exploitation l'espace inutilisé dans les fichiers de
données et compacte le fichier à la dernière extension allouée, ce qui réduit sa
taille sans affecter les données. Aucune tentative de replacement des lignes
dans
des
pages
non
allouées
n'est
effectuée.
Le
paramètre
pourcentage_cibleest ignoré lors de l'utilisation de TRUNCATEONLY.
Exemple : Dans cet exemple, la taille de la base de données Archive est réduite de façon à obtenir 25%
d'espace libre.
DBCC SHRINKDATABASE (Archive, 25)
B)- Compactage d'un fichier de données de la base de données :
Vous pouvez compacter un fichier de données d'une base de données à l'aide de SQL Server Enterprise
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Manager ou de l'instruction DBCC SURINKFILE.
Syntaxe:
DBCC SHRINKFILE ({nom_de_fichier |id_de_fichier] [, taille_cible] [, EMPTYFILE | NOTRUNCATE |
TRUNCATEONLY }])
Argument
Description
taille_cible
Le paramètre taille_cible spécifie la taille souhaitée du fichier de données en
mégaoctets, exprimée sous la forme d'un nombre entier. Si le paramètre n'est pas
spécifié, l'instruction DBCC SHRINKFILE réduit la taille au maximum.
EMPTYFILE
Cette option permet de transférer toutes les données du fichier spécifié vers d'autres
fichiers du même groupe de fichiers. SQL Server n'autorise plus le transfert de données
vers le fichier utilisé avec le paramètre EMPTYFILE. Ce dernier permet de supprimer
le fichier à l'aide de l'instruction ALTER DATABASE.
Exemple : Dans cet exemple, la taille du fichier de données Arch1 est réduite à 5 Mo.
Use Archive
GO
DBCC SHRINKFILE (Arch1,5)
C)- Compactage automatique d'une base de données
Vous pouvez également affecter la valeur trueà l'option de base de données autoshrinkpour récupérer
automatiquement l'espace inutilisé. Il vous est également possible de modifier cette option avec SQL Server
Enterprise Manager.
Lors du compactage d'une base de données ou d'un fichier de données, tenez compte des instructions et
remarques décrites ci-dessous.
• La taille de la base de données résultante doit être supérieure a celle de la base de données model ou à
celle des données existant de la base de données ou du fichier de données.
• Avant de compacter une base de données ou un fichier de données, il est conseillé de sauvegarder la base
de données, ainsi que la base de données master.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
• Étant donné que les instructions DBCC SHRINKDATABASE et SHRINKFILE s'exécutent en mode différé, il se
peut que la réduction de la taille de la base de données ou du fichier ne soit pasimmédiatement visible.
• L'instruction DBCC SHRINKFILE vous permet de réduire la taille d'une base de données à une taille inférieure
à celle spécifiée lors de sa création ou de sa modification, mais pas inférieure à la taille des données.
3. Suppression d'une base de données :
Vous pouvez supprimer une base de données non système dès qu'elle n'est plus nécessaire ou si elle est
transférée vers une autre base de données ou un autre serveur. En cas de suppression d'une base de
données, les fichiers ainsi que leurs données sont supprimés. Une fois supprimée, une base de données ne
peut pas être récupérée sans faire appel à une sauvegarde précédente. Impossible de supprimer les bases de
données système (msdb, master, model, tempdb).
Lorsque vous supprimez une base de données, vous devez effectuer une sauvegarde de la base de données
master.
A l'aide de Transact-SQL
Syntaxe : DROP DATABASE database_name[ ,...n ]
Autorisations : Les autorisations d'utiliser DROP DATABASE sont octroyées par défaut au propriétaire de la
base de données et aux membres des rôles sysadminet dbcreatoret ne sont pas transmissibles.
Chapitre 05 :
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Réalisation
derestauration
des sauvegardes
de données.
I.
Sauvegarde des bases de données :
Une sauvegarde de base de données crée un double des données figurant dans la base de données lorsque
lasauvegarde est effectuée. C'est une opération unique qui est généralement planifiée à des intervalles
réguliers.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Les sauvegardes de base de données sont indépendantes.
Vous pouvez recréer la totalité de la base de données à partir d'une sauvegarde de base de données en une
étape en la restaurant. Le processus de restauration remplace la base de données existante ou en crée une si
elle n'existe pas. L'état de la base de données restaurée correspondra à celui lorsque la sauvegarde a été
terminée, sans les transactions non validées. Les transactions non validées sont annulées lors de la
récupération de la base de données.
Une sauvegarde de base de données utilise davantage d'espace de stockage par sauvegarde que pour les
sauvegardes du journal des transactions et de base de données différentielle. Par conséquent, les sauvegardes
de base de données nécessitent plus de temps pour réaliser l'opération de sauvegarde, et sont donc
généralement créées moins fréquemment que les sauvegardes de base de données différentielle ou de
journaux de transactions.
Pour sauvegarder une base de données dans SQL Server, vous devez déterminer les personnes autorisées à
effectuer cette opération et l'emplacement de stockage des données sauvegardées. Les membres disposant
des rôles suivants sont autorisés à sauvegarder une base de données : rôle fixe de serveur sysadmin; rôle fixe
de base de données db_ownerou rôle fixe de base de données dh_backupopcrator.
II.
Restauration d’une sauvegarde de bases de données :
La restauration d'une sauvegarde de base de données recrée la base de données et tous les fichiers associés
qui yétaient contenus lorsque la sauvegarde a été effectuée. Toutefois, toutes les modifications apportées à la
base de données après la création de la sauvegarde seront perdues. Pour restaurer les transactions réalisées
après la création de la sauvegarde totale de la base de données, vous devez utiliser les sauvegardes du journal
des transactions ou les sauvegardes différentielles.
Lors de la restauration d'une base de données, SQL Server:
1. Copie toutes les données de la sauvegarde dans la base de données ; le reste de la base de données est
créé comme un espace vide.
2. Annule toute transaction incomplète dans la sauvegarde de la base de données pour assurer la cohérence
de la base.
Pour éviter d'écraser une base de données par accident, l'opération de restauration effectue automatiquement
des contrôles de sécurité. L'opération de restauration échoue dans le cas où :
• Le nom de la base de données dans l'opération de restauration ne correspond pas au nom de la base de
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
données enregistré dans le jeu de sauvegarde ;
• La base de données nommée dans l'opération de restauration existe déjà sur le serveur mais il ne s'agit
pas de la même que celle contenue dans la sauvegarde de base de données ; Par exemple, les noms de
base de données sont identiques mais chaque base a été créée différemment.
• Un ou plusieurs fichiers doivent être créés automatiquement par l'opération de restauration mais lesnoms de
fichiers existent déjà.
Ces contrôles de sécurité peuvent être désactivés lorsque vous avez l'intention d'écraser une autre base de
données.
III.
Définition de l'option de base de données dbo use only :
Avant de restaurer une base de données, un membre du rôle sysadmin ou db.owner doit affecter la valeur
Trueà l'option de base de données dbo use only(réservé au DBO) pour limiter l'accès à la base de données.
IV.
Sauvegarde du journal des transactions :
Vous pouvez préserver la cohérence de la base de données en sauvegardant le journal des transactions
avantd'exécuter les opérations de restauration.La sauvegarde du journal des transactions est utilisée pour
récupérer la base de données en tant qu'étape ultime du processus de restauration.
Si vous ne sauvegardez pas le journal des transactions avant de restaurer des sauvegardes, vous perdez les
modifications apportées aux données entre la dernière sauvegarde du journal des transactions et le moment
où la base de données a été déconnectée.
V.
Utilisation des modèles de récupération :
Vous pouvez sélectionner un des trois modèles de récupération pour chaque base de données de Microsoft
SQL Server
pour déterminer la manière dont vos données sont sauvegardées et connaître votre
degréd'exposition à la perte de données. Les modèles de récupération suivants sont disponibles :
• Récupération simple :
La récupération simple permet la récupération de la base de données à l'aide de la sauvegarde la plus récente.
• Récupération complète :
La récupération complète permet la récupération de la base de données à partir du moment de l'échec.
• Récupération journalisée en bloc :
La récupération journalisée en bloc permet des opérations de journalisation en bloc.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Le modèle de récupération d'une nouvelle base de données est hérité à partir de la base de données model
lors de la création de la nouvelle base de données.
Remarque :Le modèle de récupération pour une nouvelle base de données dans SQL Server 2000, Édition
Personnelle et SQL Server 2000 Desktop Engine sélectionne par défaut la récupération simple.
• Récupération simple :
Avec ce modèle de récupération simple, la base de données peut être récupérée dans l'état où elle était au
moment de la dernière sauvegarde. Toutefois, vous ne pouvez pas restaurer la base de données au point où
elle en était au moment de la défaillance ou à un autre moment particulier. Pour cela, vous devez choisir le
modèle de récupération complète ou journalisée en bloc.
La stratégie de sauvegarde pour la récupération simple est composée de :
• Sauvegardes de bases de données ;
• Sauvegardes différentielles (facultatif) ;
Remarque :Ce modèle équivaut à la sélection de l'option de base de données trunc. log on chkpt. dans
SQLServer version 7.0 ou antérieure.
Pour récupérer en cas de défaillance des supports :
1. Restaurez la sauvegarde totale de la base de données la plus récente.
2. S'il existe des sauvegardes différentielles, restaurez la plus récente.
Les modifications apportées après la dernière sauvegarde différentielle ou totale de base de données sont
perdues.
V.5. Sauvegarde Complète de base de données :
• Création d'une sauvegarde complète de la base de données :
A l'aide de Transact-SQL :
1. Exécutez l'instruction BACKUP DATABASE, en spécifiant :
• Le nom de la base de données à sauvegarder.
• L'unité de sauvegarde où sera écrite la sauvegarde de la base de données.
2. Spécifiez éventuellement :
• La clause INIT pour écraser les fichiers du support de sauvegarde et écrire la sauvegarde en tant que premier
fichier sur le support de sauvegarde. S'il n'existe aucun en-tête de support, il s'en crée un automatiquement.
• Les clauses SKIP et INIT pour écraser le support de sauvegarde même s'il existe des sauvegardes
qui n'ont pas encore expiré ou si le nom du support ne correspond pas à celui du support de sauvegarde.
• La clause FORMAT lors de la première utilisation du support pour initialiser complètement lesupport de
sauvegarde et réécrire un en-tête de support existant.
La clause INIT n'est pas requise si la clause FORMAT est spécifiée.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Important :Soyez très vigilant en utilisant les clauses FORMAT ou INIT de l'instruction BACKUP, car elles
entraînent la destruction de toutes les sauvegardes préalablement stockées sur le support de sauvegarde.
Syntaxe :
Sauvegarde d'une base de données entière :
BACKUP DATABASE { database_name| @database_name_var}
TO <backup_device>[ ,...n ]
[ WITH
[ BLOCKSIZE= { blocksize| @blocksize_variable} ]
[ [, ] DESCRIPTION = { 'text' | @text_variable} ]
[ [, ] DIFFERENTIAL ]
[ [, ] EXPIREDATE = { date | @date_var}
| RETAINDAYS = { days| @days_var} ]
[ [, ] PASSWORD = { password| @password_variable} ]
[ [, ] FORMAT | NOFORMAT ]
[ [, ] { INIT | NOINIT } ]
[ [, ] MEDIADESCRIPTION = { 'text' | @text_variable} ]
[ [, ] MEDIANAME = { media_name| @media_name_variable} ]
[ [, ] MEDIAPASSWORD = { mediapassword| @mediapassword_variable} ]
[ [, ] NAME = { backup_set_name| @backup_set_name_var} ]
[ [, ] { NOSKIP | SKIP } ]
[ [, ] { NOREWIND | REWIND } ]
[ [, ] { NOUNLOAD | UNLOAD } ]
[ [, ] RESTART ]
[ [, ] STATS [ = percentage] ]
]
Exemple : cet exemple sauvegarde sur bande la totalité de la base de données MaBaseDeDonnees.
USE Master
Go
BACKUP DATABASE MaBaseDeDonnees
TO TAPE = '\\.\Tape0' WITH FORMAT,
NAME = 'Full Backup of MaBaseDeDonnees '
Go
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Exemple : Cet exemple sauvegarde sur disque et dans un fichier de sauvegarde temporaire la totalité de la
base de données MaBaseDeDonnees.
USE master
BACKUP DATABASE MaBaseDeDonnees TO
DISK ='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Sauvegarde_MaBD_1.bak'
Exemple : Cet exemple sauvegarde sur disque et dans un fichier de sauvegarde permanent la totalité de la
base de données MaBaseDeDonnees.
USE master
EXEC sp_addumpdevice 'disk', 'Sauvegarde_MaBD_1',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Sauvegarde_MaBD_1.bak'
BACKUP DATABASE MaBaseDeDonnees TO Sauvegarde_MaBD_1 WITH INIT
• Restauration d'une sauvegarde complète de base de données :
A l'aide de Transact-SQL :
Important :L'administrateur système restaurant la sauvegarde de la base de données doit être la seule
personne utilisant la base de données à restaurer.
2. Exécutez l'instruction RESTORE DATABASE pour restaurer la sauvegarde de la base de données, en
spécifiant :
• le nom de la base de données à restaurer ;
• L'unité de sauvegarde à partir de laquelle sera restaurée la sauvegarde de la base de données.
• La clause NORECOVERY si vous devez appliquer la sauvegarde différentielle de la base de données ou du
journal des transactions après avoir restauré la sauvegarde de la base de données.
3. Spécifiez éventuellement :
• La clause FILE pour identifier le jeu de sauvegarde sur l'unité de sauvegarde à restaurer.
Syntaxe:
Restaurer l'intégralité d'une base de données :
RESTORE DATABASE { database_name| @database_name_var}
[ FROM<backup_device> [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [, ] FILE = { file_number| @file_number} ]
[ [, ] PASSWORD = { password | @password_variable} ]
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
[ [, ] MEDIANAME = { media_name| @media_name_variable} ]
[ [, ] MEDIAPASSWORD = { mediapassword| @mediapassword_variable} ]
[ [, ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [, ] KEEP_REPLICATION ]
[ [, ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name} ]
[ [, ] { NOREWIND | REWIND } ]
[ [, ] { NOUNLOAD | UNLOAD } ]
[ [, ] REPLACE ]
[ [, ] RESTART ]
[ [, ] STATS [ = percentage] ]
]
Exemple : Cet exemple restaure la base de données MaBaseDeDonneesà partir de la sauvegarde sur bande.
USE Master
Go
RESTORE DATABASE MaBaseDeDonnees
FROM TAPE = '\\.\Tape0'
Go
Exemple : Cet exemple restaure la base de données MaBaseDeDonneesà partir du fichier de sauvegarde
Temporaire.
USE master
RESTORE DATABASE MaBaseDeDonnees
FROM DISK='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Sauvegarde_MaBD_1.bak'
Exemple : Cet exemple restaure la totalité de la base de données MaBaseDeDonnees, à partir du fichier de
sauvegarde permanent.
USE master
RESTORE DATABASE MaBaseDeDonnees
FROM Sauvegarde_MaBD_1
Exemple : Cet exemple restaure la totalité de la base de données MaBaseDeDonnees, à partir du fichier de
sauvegarde permanent Sauvegarde_MaBD_1 dans le cas de deux sauvegardes complètes ajoutées à ce
fichier, notre base de données sera restaurée à partir de la deuxième sauvegarde complète.
USE master
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
RESTORE DATABASE MaBaseDeDonnees
FROM Sauvegarde_MaBD_1
WITH FILE =2 , RECOVRY
Remarque :Le fait de spécifier un nouveau nom pour la base de données détermine automatiquement les
nouveaux noms des fichiers de base de données restaurés à partir de la sauvegarde de la base de données.
• Laisser la base opérationnelle. Aucun autre journal de transactions ne peut être restauré si aucune
autre sauvegarde de journal des transactions ou aucune sauvegarde différentielle de la base de données ne
doit être appliquée.
• Laisser la base non opérationnelle mais permettre la restauration d'autres journaux de transactions si
une autre sauvegarde du journal des transactions ou une autre sauvegarde différentiellede la base de données
doit être appliquée.
V.6. Sauvegardes différentielles de base de données :
Une sauvegarde différentielle de base de données n'enregistre que les données qui ont changé depuis la
dernière sauvegarde complète. Vous pouvez effectuer des sauvegardes plus fréquentes car les sauvegardes
différentielles de bases de données sont plus petites et plus rapides que les sauvegardes totales de bases de
données. Des sauvegardes fréquentes réduisent le risque de perdre des données.
Vous pouvez utiliser les sauvegardes différentielles de bases de données pour restaurer la base de données au
point auquel la sauvegarde différentielle de la base de données a été effectuée.
Envisagez d'utiliser les sauvegardes différentielles de base de données dans les cas suivants :
• Seule une relativement petite partie des données de la base de données a changé depuis la dernière
sauvegarde complète de la base de données ; Les sauvegardes de base de données différentielles sont
particulièrement efficaces si les mêmes données sont souvent modifiées.
• Vous utilisez le modèle de récupération simple et voulez des sauvegardes plus fréquentes, mais vous ne
souhaitez pas effectuer des sauvegardes de base de données complètes fréquemment.
• Vous utilisez le modèle de récupération complet ou journalisé en bloc et souhaitez réduire le temps
nécessaire à la récupération des sauvegardes de journaux de transactions lors de la restauration d'une base de
données.
Voici la procédure recommandée pour l'implémentation de sauvegardes différentielles de bases de données :
1. Création de sauvegardes complètes régulières des bases de données ;
2. Création de sauvegardes différentielles de base de données périodiquement entre les sauvegardes
complètes de bases de données, par exemple toutes les quatre heures ou plus pour des systèmes trèsactifs ;
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
3. Si vous utilisez la récupération complète ou journalisée en bloc, créez plus fréquemment des sauvegardes
du journal des transactions que de base de données différentielles, toutes les 30 minutespar exemple.
La séquence de restauration de sauvegardes de bases de données différentielles est la suivante :
1. restauration de la sauvegarde complète de la base de données la plus récente ;
2. restauration de la dernière sauvegarde différentielle de la base de données ;
3. application de toutes les sauvegardes du journal des transactions après la création de la dernière
sauvegarde différentielle de la base de données, si vous utilisez la récupération complète ou journalisée en
bloc au journal.
• Création d'une sauvegarde différentielle de base de données :
A l'aide de Transact-SQL :
Important :Il est impossible de créer une sauvegarde différentielle si la base de données n'a pas été d'abord
sauvegardée.
1. Exécutez l'instruction BACKUP DATABASE pour créer une sauvegarde différentielle de base de données,
en spécifiant :
• Le nom de la base de données à sauvegarder.
• L'unité de sauvegarde où sera écrite la sauvegarde de la base de données.
• La clause DIFFERENTIAL pour spécifier que seules les parties de la base de données qui ont été modifiées
après la création de la dernière sauvegarde de la base de données sont sauvegardées.
2. Spécifiez éventuellement :
• La clause INIT pour écraser les fichiers du support de sauvegarde et écrire la sauvegarde en tant que premier
fichier sur le support de sauvegarde. S'il n'existe aucun en-tête de support, il s'en crée un automatiquement.
• Les clauses SKIP et INIT pour écraser le support de sauvegarde même s'il existe des sauvegardes qui n'ont
pas encore expiré ou si le nom du support ne correspond pas à celui du support de sauvegarde.
• La clause FORMAT lors de la première utilisation du support pour initialiser complètement le support de
sauvegarde et réécrire un en-tête de support existant.
La clause INIT n'est pas requise (nécessitée) si la clause FORMAT est spécifiée.
Important :Soyez très vigilant en utilisant les clauses FORMAT ou INIT de l'instruction BACKUP, car elles
entraînent la destruction de toutes les sauvegardes préalablement stockées sur le support de sauvegarde.
Exemple : Cet exemple crée une sauvegarde complète et une sauvegarde différentielle de la base de
donnéesMaBaseDeDonnees sur disque et dans un fichier de sauvegarde permanent.
USE master
EXEC sp_addumpdevice 'disk', 'Sauvegarde_MaBD_1',
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Sauvegarde_MaBD_1.bak'
--Création d’une sauvegarde Complète.
BACKUP DATABASE MaBaseDeDonnees TO Sauvegarde_MaBD_1
WITH INIT
Go
--Création d’une sauvegarde différentielle.
BACKUP DATABASE MaBaseDeDonnees TO Sauvegarde_MaBD_1
WITH DIFFERENTIAL
Go
• Restauration d'une sauvegarde différentielle de la base de données
A l'aide de Transact-SQL
1. Exécutez l'instruction RESTORE DATABASE, en spécifiant la clause NORECOVERY, pour restaurer la
sauvegarde de la base de données précédant la sauvegarde différentielle de la base de données.
2. Exécutez l'instruction RESTORE DATABASE pour restaurer la sauvegarde différentielle de la base de
données, en spécifiant :
• Le nom de la base de données à laquelle sera appliquée la sauvegarde différentielle de la base dedonnées.
• L'unité de sauvegarde à partir de laquelle sera restaurée la sauvegarde de la base de données.
• La clause NORECOVERY si vous devez appliquer d'autres sauvegardes du journal destransactions après la
restauration de la sauvegarde différentielle de la base de données. Dans lesautres cas, spécifiez la clause
RECOVERY.
Exemple : Cet exemple montre la sauvegarde complète et différentielle de la base de
donnéesMaBaseDeDonnees.
-- Restauration complète de la base de données
RESTORE DATABASE MaBaseDeDonnees
FROM Sauvegarde_MaBD_1
WITH NORECOVERY
GO
--Maintenant restauration différentielle de la base de la base de données
RESTORE DATABASE MaBaseDeDonnees
FROM Sauvegarde_MaBD_1
WITH FILE=2, RECOVERY
GO
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
V.7. Sauvegardes du journal des transactions
Le journal des transactions est un enregistrement en série de toutes les transactions effectuées dans une base
dedonnées depuis la dernière sauvegarde du journal des transactions. Avec les sauvegardes du journal des
transactions, vous pouvez récupérer la base de données à un point particulier dans le temps (par exemple,
avantla saisie des données non souhaitées) ou au moment de la défaillance.
Lors de la restauration d'une sauvegarde du journal des transactions, Microsoft SQL Server reprend toutes les
modifications enregistrées dans le journal des transactions. Lorsque SQL Server atteint la fin du journal des
transactions, l'état exact de la base de données au début de l'opération de sauvegarde est recréé. Si la base de
données est récupérée, SQL annule ensuite toutes les transactions incomplètes au moment où l'opération de
sauvegarde a démarré.
Les sauvegardes du journal des transactions utilisent en général moins de ressources que les sauvegardes des
bases de données. Par conséquent, vous pouvez les créer plus fréquemment que les sauvegardes de base de
données. Des sauvegardes fréquentes réduisent le risque de perdre des données.
Remarque :La sauvegarde du journal des transactions prend parfois plus d'espace qu'une sauvegarde de la
basede données. Par exemple, une base de données dont le taux de transaction est élevé entraîne une
croissancerapide du journal des transactions. Dans ce cas, créez des sauvegardes plus fréquentes du journal
destransactions.
Les sauvegardes de journal de transactions sont uniquement utilisées avec les modèles de
récupérationcomplète et journalisée en bloc.
Utilisation de sauvegardes du journal des transactions avec des sauvegardes de bases de données :
La restauration d'une base de données à l'aide de sauvegardes de base de données et de sauvegardes du
journaldes transactions ne fonctionne que si vous disposez d'une suite ininterrompue des sauvegardes du
journal destransactions après la dernière sauvegarde totale ou différentielle de la base de données. Si une
sauvegarde dejournal est manquante ou endommagée, vous devez créer une sauvegarde de base de données
ou de base dedonnées différentielle et reprendre à nouveau la sauvegarde des journaux de transactions.
Conservez lessauvegardes précédentes du journal des transactions pour restaurer la base de données à un
moment figurantdans ces sauvegardes.
Le seul moment où les sauvegardes complètes ou différentielles de la base de données doivent
êtresynchronisées avec les sauvegardes du journal des transactions est le départ d'une séquence de
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
sauvegardes dujournal des transactions. Chaque séquence de sauvegardes du journal des transactions doit
être démarrée parune sauvegarde, totale ou différentielle, de la base de données.
Généralement, l'unique fois où une nouvelle séquence de restaurations est démarrée correspond au moment
oula base de données est restaurée pour la première fois ou lorsqu'une modification a été apportée à partir du
modèle de récupération simple à celui de récupération complète ou journalisée en bloc.
V.7.1. Troncature du journal des transactions :
Lorsque SQL Server termine la sauvegarde du journal des transactions, il tronque automatiquement la partie
inactive du journal des transactions. Cette partie inactive contient des transactions terminées et n'est donc plus
utilisée pendant le processus de récupération. Inversement, la partie active du journal des transactions contient
les transactions qui fonctionnent encore et qui ne sont pas encore terminées. SQL Server utilise de nouveau
cetespace tronqué et inactif dans le journal des transactions au lieu de laisser le journal des transactions
augmenteret utiliser plus d'espace.
Remarque :Bien que le journal des transactions peut être tronqué manuellement, il est fortement recommandé
de ne pas faire cela, car la chaîne de sauvegarde du journal est alors brisée. Jusqu'à ce qu'une sauvegarde
complète de la base de données ne soit créée, la base de données n'est pas protégée contre la défaillance des
supports. N'utilisez la troncature manuelle du journal que dans des circonstances très précises, et créez une
sauvegarde complète de la base de données dès que possible.
Le point de fin de la partie inactive du journal des transactions, et par conséquent le point de
troncation,constitue le premier événement parmi les suivants :
• le point de contrôle le plus récent ;
• le départ de la transaction active la plus ancienne, qui est une transaction qui n'a pas encore été validée
ou qui n'a pas encore été annulée ;
Ceci représente le point le plus rapproché auquel SQL Server aurait à annuler des transactions lorsd'une
opération de restauration.
• le début de la transaction la plus ancienne qui implique des objets publiés à des fins de réplication, dontles
modifications n'ont pas encore été répliquées ;
Ceci représente le prochain point que SQL Server doit encore dupliquer.
Troncature du journal de transactions :
BACKUP LOG { database_name| @database_name_var}
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
}
Utilisation de l'option NO_TRUNCATE :
SQL Server :
• Enregistre le journal des transactions dans son intégralité même si la base de données est inaccessible
• Ne supprime pas les transactions validées du journal des transactions
• Permet de récupérer les données jusqu'au moment où le système a subi la défaillance
Vidage du journal des transactions :
• Utilisez l'instruction BACKUP LOG pour vider le journal des transactions.
• Si vous utilisez l'option TRUNCATE_ONLY ou NO_LOG.
-
Les modifications ne sont pas récupérées.
-
La troncature du journal des transactions n'est pas enregistrée.
• Si vous définissez l'option trunc. log on chkpt.
-
Toutes les transactions validées sont écrites.
-
Opération automatique si l'option a la valeur true.
Exemple : Dans cet exemple, l'instruction BACKUP permet de supprimer la partie inactive d'un journal des
transactions sans faire de copie de sauvegarde.
BACKUP LOG MaBaseDeDonnees WITH TRUNCATE_ONLY
V.7.2. Conditions de sauvegarde du journal des transactions :
Le journal des transactions ne peut être sauvegardé au cours d'une sauvegarde de base de données complète
oudifférentielle. Néanmoins, le journal des transactions peut être sauvegardé lors de l'exécution d'une
sauvegardede fichier.
Ne sauvegardez pas le journal des transactions :
• tant qu'une sauvegarde de la base de données ou d'un fichier n'a pas été créée car le journal destransactions
contient les modifications effectuées dans la base de données après la création de ladernière sauvegarde ;
• si le journal des transactions a été explicitement tronqué, à moins qu'une sauvegarde, complète
oudifférentielle, de la base de données ait été créée après la troncature du journal des transactions.
V.7.3. Restauration des sauvegardes du journal des transactions
L'application d'une sauvegarde du journal des transactions est impossible :
• à moins que la sauvegarde complète ou différentielle de la base de données précédant la sauvegarde du
journal des transactions ne soit restaurée en premier ;
• à moins que tous les précédents journaux des transactions créés depuis la sauvegarde de la base dedonnées
totale ou différentielle ne soient appliqués en premier ;
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
En cas de perte ou d'endommagement d'une sauvegarde précédente du journal des transactions, vouspouvez
restaurer uniquement les journaux des transactions allant jusqu'à la dernière sauvegardeprécédant celle qui
manque.
• si la base de données est déjà récupérée et que toutes les transactions en suspens ont déjà été annuléesou
ont été reprises.
Lors de l'application de sauvegardes du journal des transactions, la base de données ne doit pas êtrerécupérée
tant que le dernier journal des transactions n'a pas été appliqué. Si vous autorisez larécupération lors de
l'application de l'une des sauvegardes intermédiaires du journal des transactions,vous ne pouvez restaurer audelà de ce point sans redémarrer entièrement l'opération de restauration, encommençant par la sauvegarde de
la base de données.
V.7.4. Création d'une séquence de sauvegardes du journal des transactions
Pour créer un jeu de sauvegardes, vous devez généralement effectuer une sauvegarde de la base de données
àintervalles réguliers, par exemple tous les jours, et des sauvegardes du journal des transactions à des
intervallesplus courts, par exemple toutes les 10 minutes.
Vous devez disposer d'au moins une sauvegarde de base de données ou d'un jeu exhaustif de sauvegardes
defichiers pour réaliser efficacement les sauvegardes de journal.
L'intervalle entre les sauvegardes varie en fonction de l'importance des données et de la charge de travail
duserveur. Si votre journal des transactions est endommagé, vous perdrez les travaux réalisés depuis
lasauvegarde de journal la plus récente.
Cela suppose des sauvegardes de journal fréquentes pour les données critiques, et souligne l'importance du
placement des fichiers journaux sur un stockage à tolérance de fautes.
La séquence de sauvegardes du journal des transactions ne dépend pas des sauvegardes de la base de
données.
Vous pouvez effectuer une séquence de sauvegardes du journal des transactions, puis des sauvegardes de
basede données périodiques pour les utiliser lors du démarrage d'une opération de restauration. Supposons,
parexemple, que la séquence des événements est la suivante :
Temps
8h00
12h00
16h00
18h00
20h00
22h00
Spécialité : INFORMATIQUE
Événement
Sauvegarde complète de la base de données
Sauvegarde du journal des transactions
Sauvegarde du journal des transactions
Sauvegarde complète de la base de données
Sauvegarde du journal des transactions
Apparition d'une défaillance
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
La sauvegarde du journal des transactions créée à 20h00 contient les enregistrements du journal des
transactions effectués entre 16h00 et 20h00, ce qui couvre le moment de la création de la sauvegarde de la
basede données (18h00). La séquence de sauvegardes du journal des transactions est continue depuis la
sauvegardede la base de données initiale créée à 8h00 jusqu'à la dernière sauvegarde du journal des
transactions créée à 20h00. Vous pouvez utiliser les procédures suivantes pour restaurer la base de données
dans l'état où elle se trouvait à 22h00 (moment de la défaillance) :
Restaurez la base de données en utilisant la dernière sauvegarde de base de données créée :
1. Créez une sauvegarde du journal des transactions actif avec l'option NO_TRUNCATE,
2. Restaurez la sauvegarde complète de la base de données de 18h00 avec l'option NORECOVERY,
3. Appliquez la sauvegarde du journal des transactions de 20h00 et la sauvegarde du journal destransactions
actif.
Le processus de restauration détecte que la sauvegarde du journal des transactions de 20h00 contient des
transactions ayant eu lieu avant la dernière sauvegarde restaurée. Ainsi, l'opération de restauration balaie le
journal des transactions jusqu'au point correspondant à la fin de la sauvegarde de la base de données à 18h00
et ne reprend que les transactions terminées à partir de ce moment dans la sauvegarde du journal des
transactions. Le même processus se reproduit pour la sauvegarde du journal des transactions créée au point a.
Restaurez la base de données en utilisant une sauvegarde précédente de la base de données
(précédant la création de la sauvegarde de la base de données la plus récente).
1. Créez une sauvegarde du journal des transactions actif.
2. Restaurez la sauvegarde de la base de données de 8h00 puis restaurez dans l'ordre les quatre sauvegardes
du journal des transactions. Ne restaurez pas la sauvegarde de la base de données de 18h00. Ceci permet de
reprendre toutes les transactions terminées jusqu'à la sauvegarde créée au point a.
Ce processus prendra plus de temps que la restauration de la sauvegarde de base de données de 18h00.
La deuxième option privilégie la sécurité redondante offerte par la chaîne de sauvegardes du journal des
transactions qui peut être utilisée pour restaurer une base de données même en cas de perte de la dernière
sauvegarde de la base de données. Vous pouvez restaurer une sauvegarde antérieure de la base de données,
et puis restaurer toutes les sauvegardes du journal des transactions créées après la création de cette
sauvegarde.
Remarque :Il est important de ne pas perdre une sauvegarde du journal des transactions. Pensez à effectuer
plusieurs copies des jeux de sauvegarde du journal. Ceci peut être fait en sauvegardant le journal sur un
disque, puis en copiant le fichier du disque vers un autre périphérique, tel qu'un disque ou une bande séparée.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
V.7.5. Réduction du temps de récupération :
L'utilisation conjointe de sauvegardes complètes de base de données, de sauvegardes différentielles de base
de données et celles du journal des transactions peut réduire le temps requis par la restauration d'une base de
données dans n'importe quel état postérieur à la création de la sauvegarde de la base de données. En outre, la
création de sauvegardes différentielles de bases de données et du journal des transactions peut accroître la
solidité d'une sauvegarde dans le cas où une sauvegarde du journal des transactions ou une sauvegarde
différentielle d'une base de données n'est pas disponible, en raison d'une défaillance du support, par exemple.
Les procédures classiques utilisant les sauvegardes totales et différentielles de base de données et de journal
des transactions consistent à créer des sauvegardes totales de bases de données sur des intervalles plus
longs, des sauvegardes différentielles de base de données sur des intervalles moyens et des sauvegardes du
journal des transactions sur des intervalles plus courts. Par exemple, créez des sauvegardes de bases de
données hebdomadaires, des sauvegardes différentielles de bases de données quotidiennes et des
sauvegardes du journal des transactions toutes les dix minutes.
Lorsqu'une base de données doit être restaurée au point d'échec, par exemple en raison d'une défaillance du
système, vous devez :
1. Sauvegardez le journal des transactions actif. Cette opération échouera si le journal des transactions a été
endommagé.
2. Restaurer la dernière sauvegarde de la base de données ;
3. Restaurer la dernière sauvegarde différentielle créée depuis la sauvegarde de la base de données ;
4. Appliquer dans l'ordre toutes les sauvegardes du journal des transactions créées après la dernière
sauvegarde différentielle, en terminant par la sauvegarde du journal des transactions créée lors de l'étape 1.
Remarque :Si le journal des transactions actif ne peut pas être sauvegardé, il n'est possible de restaurer la
base de données qu'au point où la dernière sauvegarde du journal des transactions a été créée. Les
modifications apportées à la base de données depuis la dernière sauvegarde du journal des transactions sont
perdues et doivent être à nouveau implémentées manuellement.
En utilisant les sauvegardes différentielles de bases de données et du journal des transactions pour restaurer
une base de données au point d'échec, vous réduisez son temps de restauration car seules les sauvegardes du
journal des transactions créées depuis la dernière sauvegarde différentielle de base de données doivent être
appliquées.
En cas d'absence de création de sauvegarde différentielle de base de données, toutes les sauvegardes du
journal des transactions créées depuis la sauvegarde de la base de données doivent être appliquées.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Par exemple, un système de bases de données à mission critique nécessite qu'une sauvegarde de base de
données soit créée chaque nuit à minuit, une sauvegarde différentielle de base de données chaque heure et
une sauvegarde du journal des transactions toutes les 10 minutes dans la journée. Si la base de données doit
être restaurée à 5h19 le mercredi matin à son état initial :
1. Restaurez la sauvegarde de base de données créée le mardi soir.
2. Restaurez la sauvegarde différentielle de base de données créée le mercredi à 5h00 du matin.
3. Appliquez la sauvegarde du journal des transactions créée le mercredi à 5h10 du matin.
4. Appliquez la sauvegarde du journal des transactions créée le mercredi à 5h20 du matin, en précisant que la
procédure de restauration ne s'applique qu'aux transactions ayant eu lieu avant 5h19.
Puis, si la base de données doit être restaurée à son état initial à 3h04 le jeudi matin, mais que la sauvegarde
différentielle de base de données créée le jeudi à 3h00 du matin n'est pas disponible :
1. Restaurez la sauvegarde de base de données créée le mercredi soir.
2. Restaurez la sauvegarde différentielle de base de données créée le jeudi à 2h00 du matin.
3. Appliquez toutes les sauvegardes du journal des transactions créées entre 2h10 et 3h00 du matin le jeudi.
4. Appliquez la sauvegarde du journal des transactions créée le jeudi à 3h10 du matin, en précisant que la
procédure de restauration ne s'applique qu'aux transactions ayant eu lieu avant 3h04 du matin.
• Création d'une sauvegarde du journal des transactions :
A l'aide de Transact-SQL
1. Exécutez l'instruction BACKUP LOG pour sauvegarder le journal des transactions, en spécifiant :
• Le nom de la base de données à laquelle appartient le journal des transactions à sauvegarder.
• L'unité de sauvegarde dans laquelle sera écrite la sauvegarde du journal des transactions.
2. Spécifiez éventuellement :
• La clause INIT pour écraser les fichiers du support de sauvegarde et écrire la sauvegarde en tant que premier
fichier sur le support de sauvegarde. S'il n'existe aucun en-tête de support, il s'en crée un automatiquement.
• Les clauses SKIP et INIT pour écraser le support de sauvegarde même s'il existe des sauvegardes qui n'ont
pas encore expiré ou si le nom du support ne correspond pas à celui du support desauvegarde.
• La clause FORMAT lors de la première utilisation du support pour initialiser complètement le support de
sauvegarde et réécrire un en-tête de support existant.
La clause INIT n'est pas requise si la clause FORMAT est spécifiée.
Important : Soyez très vigilant en utilisant les clauses FORMAT ou INIT de l'instruction BACKUP, car elles
entraînent la destruction de toutes les sauvegardes préalablement stockées sur le support de sauvegarde.
Syntaxe :
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Sauvegarde du journal des transactions :
BACKUP LOG { database_name| @database_name_var}
{
TO <backup_device>[ ,...n ]
[ WITH
[ BLOCKSIZE= { blocksize| @blocksize_variable} ]
[ [, ] DESCRIPTION = { 'text' | @text_variable} ]
[ [,] EXPIREDATE = { date | @date_var}
| RETAINDAYS = { days| @days_var} ]
[ [, ] PASSWORD = { password| @password_variable} ]
[ [, ] FORMAT | NOFORMAT ]
[ [, ] { INIT | NOINIT } ]
[ [, ] MEDIADESCRIPTION = { 'text' | @text_variable} ]
[ [, ] MEDIANAME = { media_name| @media_name_variable} ]
[ [, ] MEDIAPASSWORD = { mediapassword| @mediapassword_variable} ]
[ [, ] NAME = { backup_set_name| @backup_set_name_var} ]
[ [, ] NO_TRUNCATE ]
[ [, ] { NORECOVERY | STANDBY = undo_file_name} ]
[ [, ] { NOREWIND | REWIND } ]
[ [, ] { NOSKIP | SKIP } ]
[ [, ] { NOUNLOAD | UNLOAD } ]
[ [, ] RESTART ]
[ [, ] STATS [ = percentage ] ]
]
}
<backup_device> ::=
{
{ logical_backup_device_name| @logical_backup_device_name_var}
| { DISK | TAPE } = { 'physical_backup_device_name' | @physical_backup_device_name_var}
}
Exemple: L'exemple suivant crée une sauvegarde du journal des transactions pour la base de données
MaBaseDeDonnees dans l'unité de sauvegarde, nommée Sauvegarde_MaBD_log1.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
USE master
EXEC sp_addumpdevice 'disk', 'Sauvegarde_MaBD_log1',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Sauvegarde_MaBD_log1.bak'
BACKUP LOG MaBaseDeDonnees
TO Sauvegarde_MaBD_log1
GO
• Création d'une Sauvegarde du journal des transactions lorsque la BD est endommagée :
A l'aide de Transact-SQL
1. Exécutez l'instruction BACKUP LOG pour sauvegarder le journal des transactions actif en cours, en
spécifiant :
• Le nom de la base de données à laquelle appartient le journal des transactions à sauvegarder.
• L'unité de sauvegarde dans laquelle sera écrite la sauvegarde du journal des transactions.
• La clause NO_TRUNCATE pour sauvegarder le journal des transactions sans en tronquer la partie inactive.
Elle permet la sauvegarde de la partie active du journal des transactions même si la base de données est
inaccessible, à condition que le ou les journaux des transactions soient accessibles et qu'ils ne soient pas
endommagés.
2. Spécifiez éventuellement :
• La clause INIT pour écraser les fichiers du support de sauvegarde et écrire la sauvegarde en tant que premier
fichier sur le support de sauvegarde. S'il n'existe aucun en-tête de support, il s'en crée un automatiquement.
• Les clauses SKIP et INIT pour écraser le support de sauvegarde même s'il existe des sauvegardes qui n'ont
pas encore expiré ou si le nom du support ne correspond pas à celui du support de sauvegarde.
• La clause FORMAT lors de la première utilisation du support pour initialiser complètement le support de
sauvegarde et réécrire un en-tête de support existant.
La clause INIT n'est pas requise si la clause FORMAT est spécifiée.
Important :Soyez très vigilant en utilisant les clauses FORMAT ou INIT de l'instruction BACKUP, car elles
entraînent la destruction de toutes les sauvegardes préalablement stockées sur le support de sauvegarde.
Exemple: L'exemple suivant crée une sauvegarde du journal des transactions actif pour la base de données
MaBaseDeDonnees
dans
l'unité
de
sauvegarde,
nommée
Sauvegarde_MaBD_log1,
même
si
MaBaseDeDonnees a été endommagée ou est inaccessible. Toutefois, le journal des transactions n'est ni
endommagé ni inaccessible.
USE master
GO
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
EXEC sp_addumpdevice 'disk', 'Sauvegarde_MaBD_log1',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Sauvegarde_MaBD_log1.bak'
BACKUP LOG MaBaseDeDonnees
TO Sauvegarde_MaBD_log1 WITH NO_TRUNCATE
• Application d'une sauvegarde du journal des transactions :
L'application d'une sauvegarde du journal des transactions est impossible :
• à moins que la sauvegarde complète ou différentielle de la base de données précédant la sauvegarde du
journal des transactions ne soit restaurée en premier ;
• À moins que tous les journaux des transactions précédents créés depuis la sauvegarde de la base de
données complète ou différentielle soient appliqués en premier.
• Si la base de données est déjà récupérée et que toutes les transactions en cours ont déjà subi l'opération
de roll back ou ont été transmises.
A l'aide de Transact-SQL :
1. Exécutez l'instruction RESTORE LOG pour appliquer la sauvegarde du journal des transactions, en
spécifiant :
• Le nom de la base de données à laquelle sera appliqué le journal des transactions.
• L'unité de sauvegarde à partir de laquelle sera restaurée la sauvegarde du journal des transactions.
• La clause NORECOVERY si vous devez appliquer une autre sauvegarde du journal des transactions après la
sauvegarde en cours. Dans les autres cas, spécifiez la clause RECOVERY.
2. Répétez l'étape 1 pour chaque sauvegarde du journal des transactions à appliquer.
Exemple: L'exemple suivant applique une seule sauvegarde du journal des transactions à la base de données
MaBaseDeDonnees.
RESTORE LOG MaBaseDeDonnees
FROM Sauvegarde_MaBD_log1
WITH RECOVERY
GO
Exemple: L'exemple suivant applique plusieurs sauvegardes du journal des transactions à la base de données
MaBaseDeDonnees, où Sauvegarde_MaBD_log1, Sauvegarde_MaBD_log2 et Sauvegarde_MaBD_log3 sont 3
fichiers de sauvegarde pour le journal de transactions.
RESTORE LOG MaBaseDeDonnees
FROM Sauvegarde_MaBD_log1
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
WITH NORECOVERY
GO
RESTORE LOG MaBaseDeDonnees
FROM Sauvegarde_MaBD_log2
WITH NORECOVERY
GO
RESTORE LOG MaBaseDeDonnees
FROM Sauvegarde_MaBD_log3
WITH RECOVERY
GO
• Restauration du journal de transaction à un point précis dans le temps :
A l'aide de Transact-SQL
1. Exécutez l'instruction RESTORE DATABASE en utilisant la clause RECOVERY.
2. Exécutez l'instruction RESTORE LOG pour appliquer chaque sauvegarde du journal des transactions, en
spécifiant :
• Le nom de la base de données à laquelle sera appliqué le journal des transactions.
• L'unité de sauvegarde à partir de laquelle sera restaurée la sauvegarde du journal des transactions.
• Les clauses RECOVERY et STOPAT. Si la sauvegarde du journal des transactions ne contient pas l'heure
demandée (par exemple, si l'heure spécifiée dépasse la dernière heure figurant dans le journal des
transactions), un avertissement est émis et la base de données n'est pas restaurée.
Exemple: L'exemple suivant restaure une base de données à son état le 1er juillet 1998 à 10 heures et illustre
une opération de restauration impliquant plusieurs journaux et plusieurs unités de sauvegarde.
-- Restaure la sauvegarde complète de la Base de données
RESTORE DATABASE MaBaseDeDonnees
FROM Sauvegarde_MaBD_1
WITH NORECOVERY
GO
RESTORE LOG MaBaseDeDonnees
FROM Sauvegarde_MaBD_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
• Etapes de restauration d'une BD endommagée :
1. Exécutez l'instruction BACKUP LOG en utilisant la clause NO_TRUNCATE pour sauvegarder le journal des
transactions actif en cours.
2. Exécutez l'instruction RESTORE DATABASE en utilisant la clause NORECOVERY pour restaurer la
sauvegarde complète de la base de données.
3. Exécutez l'instruction RESTORE LOG avec la clause NORECOVERY pour appliquer chaque sauvegarde du
journal des transactions.
4. Exécutez l'instruction RESTORE LOG avec la clause RECOVERY pour appliquer la sauvegarde du journal
des transactions créé à l'étape 1.
Exemple : L'exemple suivant sauvegarde le journal des transactions actif en cours de la base de données
MaBaseDeDonnees, même si la BD est inaccessible, puis restaure la base de données au point d'échec en
utilisant les sauvegardes préalablement créées.
-- Sauvegarde du journal de transaction actif
BACKUP LOG MaBaseDeDonnees
TO Sauvegarde_MaBD_log2
WITH NO_TRUNCATE
GO
-- Restaure la sauvegarde complete de la base de données.
RESTORE DATABASE MaBaseDeDonnees
FROM Sauvegarde_MaBD_1
WITH NORECOVERY
GO
-- Restaure la premiere sauvegarde du journal de transactions.
RESTORE LOG MaBaseDeDonnees
FROM Sauvegarde_MaBD_log1
WITH NORECOVERY
GO
-- restaure la sauvegarde du journal de transactions actif
RESTORE LOG MaBaseDeDonnees
FROM Sauvegarde_MaBD_log2
WITH RECOVERY
GO
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
V.8. Sauvegarde de fichiers ou de groupes de fichiers :
Microsoft SQL Server prend en charge la sauvegarde et la restauration de fichiers isolés ou de groupes de
fichiers dans une base de données. Il s'agit d'un processus de sauvegarde et de restauration relativement
sophistiqué qui est généralement réservé aux bases de données de très grande taille dont les exigences en
matière de disponibilité sont importantes. Si le temps disponible pour la sauvegarde ne suffit pas à prendre en
charge la sauvegarde de la totalité de la base de données, il est possible de sauvegarder des sous-ensembles
de la base de données à des moments différents.
Par exemple, si la sauvegarde d'une base de données nécessite trois heures, et si le temps de sauvegarde
quotidien autorisé est de deux heures seulement, le site peut sauvegarder la moitié des fichiers ou des groupes
de fichiers un soir et l'autre moitié le soir suivant. Si un disque contenant des fichiers ou des groupes de fichiers
de base de données tombe en panne, le site peut restaurer uniquement les fichiers ou groupes de fichiers
perdus.
Le site doit également réaliser des sauvegardes des journaux des transactions et restaurer toutes celles qui ont
été effectuées après la sauvegarde du fichier ou du groupe de fichiers.
Les restaurations de fichiers et de groupes de fichiers peuvent aussi être effectuées à partir du jeu de
sauvegarde d'une base de données complète. Ceci permet de récupérer les données plus rapidement, car
seuls les fichiers ou groupes de fichiers endommagés sont restaurés au cours de la première étape, et non la
base de données complète.
Les fichiers d'une base de données peuvent être sauvegardés et restaurés individuellement. De cette manière,
la vitesse de récupération est augmentée, car cela vous permet de restaurer uniquement les fichiers
endommagés sans restaurer le reste de la base de données. Par exemple, si une base de données est
constituée de plusieurs fichiers situés physiquement sur différents disques et qu'un disque est défaillant, seul le
fichier du disque défectueux doit être restauré.
Les opérations de sauvegarde et de restauration doivent être utilisé conjointement avec les sauvegardes de
journal des transactions. Pour cette raison, les sauvegardes de fichiers ne peuvent être utilisées qu'avec les
modèles de récupération complète et journalisée en bloc.
Les sauvegardes de fichiers offrent les avantages suivants :
• La récupération suite à des défaillances de supports isolés est plus rapide. Le ou les fichiersendommagés
peuvent être restaurés rapidement.
• Les sauvegardes de fichiers ou de journaux des transactions peuvent être créées simultanément, vous
permettant de gérer des planifications régulières de sauvegarde du journal.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
• Les sauvegardes de fichiers permettent une plus grande flexibilité dans la planification et la gestion des
supports. Par exemple, pour les très grandes bases de données, les sauvegardes de bases de données
complètes peuvent devenir ingérables.
• Sauvegarde de fichiers et groupes de fichiers :
A l'aide de Transact-SQL
Pour sauvegarder les fichiers et groupes de fichiers :
• Exécutez l'instruction BACKUP DATABASE pour créer une sauvegarde de fichier ou de groupe de fichiers, en
spécifiant :
• Le nom de la base de données à sauvegarder.
• L'unité de sauvegarde où sera écrite la sauvegarde de la base de données.
• La clause FILE pour chaque fichier à sauvegarder.
• La clause FILEGROUP pour chaque groupe de fichiers à sauvegarder.
Exemple: La base de données MaBaseDeDonnee est composée des fichiers suivants:
MaBaseDeDonnees_data_1.mdf, MaBaseDeDonnees_data_2.ndf et Les fichiers de sauvegarde suivants
existent déjà : Sauvegarde_MaBD_1 et Sauvegarde_MaBD_2.
USE Master
Go
-- Sauvegarde du fichier primaire de la base de données
BACKUP DATABASE MaBaseDeDonnees
FILE = MaBaseDeDonnees_data_1 TO Sauvegarde_MaBD_1
GO
-- Sauvegarde du fichier secondaire de la base de données
BACKUP DATABASE MaBaseDeDonnees
FILE = MaBaseDeDonnees_data_2 TO Sauvegarde_MaBD_2
GO
-- Sauvegarde du fichier journal de transactions de la base de données
BACKUP LOG MaBaseDeDonnees
TO Sauvegarde_MaBD_log1
GO
• Restauration des sauvegardes de fichiers :
Après avoir restauré des sauvegardes de fichiers, vous devez restaurer les sauvegardes du journal des
transactions effectuées depuis la création de ces sauvegardes de fichiers pour rendre à la base de données sa
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
cohérence. La sauvegarde du journal des transactions peut être reprise rapidement, car seuls les changements
qui sont appliqués aux fichiers restaurés sont appliqués.
Pour restaurer un ou plusieurs fichiers endommagés à partir de sauvegardes de fichiers :
1. Créez une sauvegarde du journal des transactions actif ; Si vous ne pouvez pas effectuer cette opération car
le journal a été endommagé , vous devez restaurer toute la base de données.
2. Restaurez chaque fichier endommagé à partir de la sauvegarde la plus récente de ce fichier ;
3. Restaurez dans l'ordre les sauvegardes du journal des transactions, en commençant par la sauvegarde
correspondant au plus ancien des fichiers restaurés ;
4. Restaurez la sauvegarde du journal des transactions actif créée à l'étape 1 ;
5. Récupérez la base de données.
Important :L'administrateur système restaurant les fichiers et groupes de fichiers doit être la seule personne
utilisant la base de données à restaurer.
1. Exécutez l'instruction RESTORE DATABASE pour restaurer la sauvegarde du fichier ou du groupe de
fichiers, en spécifiant :
• Le nom de la base de données à restaurer ;
• L'unité de sauvegarde à partir de laquelle sera restaurée la sauvegarde de la base de données.
• La clause FILE pour chaque fichier à restaurer.
• La clause FILEGROUP pour chaque groupe de fichiers à restaurer.
• La clause NORECOVERY. Si les fichiers n'ont pas été modifiés depuis la création de la sauvegarde, spécifiez
la clause RECOVERY.
2. Si les fichiers ont été modifiés depuis la création de la sauvegarde du fichier, exécutez l'instruction
RESTORE LOG pour appliquer la sauvegarde du journal des transactions, en spécifiant :
• Le nom de la base de données à laquelle sera appliqué le journal des transactions.
• L'unité de sauvegarde à partir de laquelle sera restaurée la sauvegarde du journal des transactions.
• La clause NORECOVERY si vous devez appliquer une autre sauvegarde du journal des transactions après la
sauvegarde en cours. Dans les autres cas, spécifiez la clause RECOVERY.
Les sauvegardes du journal des transactions, lorsqu'elles sont appliquées, doivent couvrir la période de
sauvegarde des fichiers et groupes de fichiers jusqu'à la fin du journal (sauf si TOUS les fichiers de base de
données sont sauvegardés).
Exemple: L'exemple suivant restaure les fichiers pour la base de données MaBaseDeDonnees. Un journal de
transactions est également appliqué pour restaurer la base de données à l'heure en cours.
USE Master
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
GO
-- Restauration du fichier primaire de la base de données
RESTORE DATABASE MaBaseDeDonnees
FILE = MaBaseDeDonnees_data_1 FROM Sauvegarde_MaBD_1
WITH NORECOVRY
GO
-- Restauration du fichier secondaire de la base de données
RESTORE DATABASE MaBaseDeDonnees
FILE = MaBaseDeDonnees_data_2 FROM Sauvegarde_MaBD_2
WITH NORECOVRY
GO
-- Application de la sauvegarde du fichier journal de transactions de la base de données
RESTORE LOG MaBaseDeDonnees
FROM Sauvegarde_MaBD_log
WITH RECOVERY
GO
Chapitre 06 :
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Surveillance des
performances
de SQL Server.
I.
Surveiller et régler les performances :
Le but de la surveillance des bases de données est d'évaluer le fonctionnement d'un serveur. Une
surveillance efficace implique la prise d'instantanés périodiques des performances actuelles afin
d'isoler les processus à l'origine des problèmes, ainsi que la collecte de données en continu pour
suivre de près les tendances des performances.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
L'évaluation continue des performances de la base de données vous permet de réduire les temps de
réponse et accélère le débit, ce qui optimise les performances. Un trafic réseau efficace, des E/S
disque et l'utilisation de l'UC sont essentiels pour maximiser les performances. Vous devez analyser
soigneusement les besoins de l'application, comprendre la structure logique et physique des données,
évaluer l'utilisation de la base de données et négocier les compromis entre des utilisations
conflictuelles telles que le traitement transactionnel en ligne par rapport à l'aide à la décision.
1. Avantages de la surveillance des bases de données et du paramétrage des
performances :
Microsoft SQL Server et le système d'exploitation Microsoft Windows fournissent des utilitaires qui
vous permettent de contrôler les conditions actuelles de la base de données et de suivre l'évolution
des performances en fonction de l'évolution de ces conditions. Il existe une multitude d'outils et de
techniques qui permettent de surveiller Microsoft SQL Server. Comprendre comment surveiller SQL
Server peut vous aider à :

Déterminer si vous pouvez améliorer les performances. Par exemple, en surveillant les temps
de réponse des requêtes les plus fréquentes, vous pouvez déterminer s'il faut modifier les
requêtes ou les index des tables.

Évaluer l'activité des utilisateurs. Par exemple, en surveillant les utilisateurs qui tentent de se
connecter à une instance de SQL Server, vous pouvez déterminer si la sécurité est
correctement configurée et tester les applications et les systèmes de développement. Par
exemple, en surveillant les requêtes SQL au fur et à mesure de leur exécution, vous pouvez
déterminer si elles sont correctement rédigées et si elles produisent les résultats attendus.

Résoudre les éventuels problèmes ou déboguer des composants d'application, comme des
procédures stockées.
2. Surveillance dans un environnement dynamique :
La surveillance est importante car SQL Server fournit un service dans un environnement
dynamique. La modification des conditions aboutit à un changement des performances.Dans vos
évaluations, vous pouvez voir les changements de performances au fur et à mesure que le nombre
d'utilisateurs augmente, que les accès des utilisateurs et les méthodes de connexion changent, que la
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
base de données se remplit, que les applications clientes changent, que les données des applications
changent, que les requêtes deviennent plus complexes et que le trafic réseau augmente. Grâce aux
outils de SQL Server permettant de surveiller les performances, vous pouvez associer certaines
modifications des performances à des modifications de conditions et des requêtes complexes. Les
scénarios suivants fournissent des exemples :

en surveillant les temps de réponse des requêtes les plus fréquentes, vous pouvez déterminer
s'il faut modifier, soit les requêtes, soit les index des tables ;

en surveillant les requêtes Transact-SQL au fur et à mesure de leur exécution, vous pouvez
déterminer si elles sont correctement rédigées et si elles produisent les résultats attendus ;

en surveillant les utilisateurs qui tentent de se connecter à une instance de SQL Server, vous
pouvez déterminer si la sécurité est correctement configurée et tester les applications et les
systèmes de développement.
Le temps de réponse est la durée requise pour le renvoi de la première ligne de l'ensemble de
résultats à l’utilisateur sous forme de confirmation visuelle qu’une requête est en cours de
traitement. Le débit mesure le nombre total de requêtes gérées par le serveur pendant une période
donnée.
La demande des ressources du serveur croît proportionnellement au nombre d'utilisateurs,
augmentant ainsi le temps de réponse et, par conséquent, diminuant le débit global.
3.
Tâches de surveillance et de paramétrage des performances :
Description de la tâche
Rubrique
Fournit les étapes nécessaires requises
pour surveiller efficacement n'importe
Surveiller les composants SQL Server
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
quel composant de SQL Server.
Répertorie les outils de surveillance et
de paramétrage de SQL Server.
Outils d'analyse et de paramétrage des
performances
Fournit des informations sur
l'établissement d'une a génération d'une
ligne de base des performances.
Établir un niveau de référence des
performances
Explique comment isoler les problèmes
de performances de base de données.
Isoler les problèmes de performance
Explique comment surveiller et suivre
les performances du serveur afin
d'identifier les goulots d'étranglement.
Identifier les goulots d'étranglement
Explique comment utiliser SQL Server
et les outils de surveillance de l'activité
et des performances Windows.
Analyse des performances et surveillance de
l'activité du serveur
Explique comment afficher et
enregistrer des plans d'exécution dans
un fichier au format XML.
Afficher et enregistrer des plans d'exécution
Fiche Technique
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Spécialité:Informatique.
Option : Base de Données.
Date :.
Enseignante:Nadjette Mouici.
Nocours : 06No séance:01.
Module:Administration à l’aide de SQL SERVER.
Temps précis : 01h 30mn.
Chapitre06:Surveillance des performances de SQL Server. Temps alloué:………h……….mn
Thème : Surveiller les composants SQL Server .
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE CITER
LES POINTS ESSSENTIELS POUR UNE SURVEILLANCE EFFICACE DE
PERFORMANCES SOUS SQL SERVER; A L’AIDE DE L’EXPLICATION DU
FORMATEUR ET SANS ERREURS.
Outils utilisés:
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
 https://msdn.microsoft.com/fr-fr/library/hh213686.aspx
Fiche Technique
Spécialité:Informatique.
Enseignante:Nadjette Mouici.
Spécialité : INFORMATIQUE
Option : Base de Données.
Date :.
Nocours : 06No séance:02.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Module:Administration à l’aide de SQL SERVER.
T.S.
Temps précis : 01h 30mn.
Chapitre06:Surveillance des performances de SQL Server. Temps alloué:………h……….mn
Thème :l’outils approprié à la surveillance.
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE
SELECTIONNER L’OUTILS APPROPRIEA LA SURVEILLANCESOUS SQL
SERVER; A L’AIDE DE MOTIFS DE LA SURVEILLANCE
CHOISIE ET
SANS ERREURS.
Outils utilisés:
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
 https://msdn.microsoft.com/fr-fr/library/hh213686.aspx
I.
Surveiller les composants SQL Server :
La surveillance est importante car SQL Server fournit un service dans un environnement dynamique. Les
données dans l'application sont fluctuantes. Le type d'accès requis par les utilisateurs peut changer. Le mode
de connexion des utilisateurs change. Les types des applications accédant à SQL Server peuvent même
changer, mais SQL Server gère automatiquement les ressources de niveau système, telles que la mémoire et
l'espace disque, pour minimiser les paramétrages manuels nécessaires au niveau système. La surveillance
permet aux administrateurs d'identifier les tendances de performances afin de déterminer si des modifications
s'imposent.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Pour surveiller efficacement un composant SQL Server :
1. Déterminer vos objectifs en matière de surveillance.
2. Sélectionner l'outil approprié.
3. Identifier les composants à surveiller.
4. Sélectionner les éléments de mesure pour ces composants.
5. Surveiller le serveur.
6. Analyser les données.
Chacune de ces étapes est décrite ci-après.
1. Déterminer vos objectifs en matière de surveillance :
Pour surveiller efficacement SQL Server, vous devez identifier clairement les motifs de surveillance. Ces motifs
peuvent être les suivants :

Établir un niveau de référence des performances.

Identifier les fluctuations de performances dans le temps.

Diagnostiquer des problèmes de performances spécifiques.

Identifier les composants ou processus à optimiser.

Comparer les effets de différentes applications clientes sur les performances.

Auditer l'activité des utilisateurs.

Tester un serveur sous différentes charges.

Tester l'architecture d'une base de données.

Tester les programmes de maintenance.

Tester les plans de sauvegarde et de restauration.

Déterminer le moment où il convient de modifier votre configuration matérielle.
2. Sélectionner l'outil approprié :
Une fois que vous avez identifié les motifs de la surveillance, vous devez sélectionner les outils correspondant
à ce type de surveillance. Le système d'exploitation Windows et SQL Server comportent un jeu complet d'outils
permettant de surveiller les serveurs dans des environnements riches en transactions. Ces outils révèlent
clairement la condition d'une instance du moteur de base de données SQL Server ou d'une instance de
SQL Server Analysis Services.
Windows fournit les outils suivants pour la surveillance d'applications s'exécutant sur un serveur :
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR

T.S.
Moniteur système, qui permet de collecter et d'afficher des données en temps réel sur des activités,
telles que l'utilisation de la mémoire, du disque et du processeur ;

Journaux et alertes de performance ;

Gestionnaire des tâches
SQL Server fournit les outils suivants pour la surveillance des composants de SQL Server :

Trace SQL

SQL Server Profiler

DistributedReplay Utility

Moniteur d'activité de SQL Server Management Studio

Plan d’exécution de requêtes graphique de SQL Server Management Studio

Procédures stockées

Commandes DBCC (Database Console Commands)

Fonctions intégrées

Indicateurs de trace
3. Identifier les composants à surveiller :
La troisième étape dans la surveillance d'une instance de SQL Server consiste à identifier les composants à
surveiller. Par exemple, si vous utilisez SQL Server Profiler pour tracer un serveur, vous définir la trace de sorte
à collecter des données concernant des événements spécifiques. Vous pouvez également exclure des
événements qui ne s'appliquent pas à votre situation.
4. Sélectionner les éléments de mesure pour les composants surveillés :
Après l'identification des composants à surveiller, déterminez les éléments de mesure à utiliser pour la
surveillance. Par exemple, après avoir sélectionné les événements à inclure dans une trace, vous pouvez
choisir d'inclure uniquement des données spécifiques concernant ces événements. La limitation de la trace aux
données pertinentes permet de réduire la quantité de ressources système requise pour effectuer le suivi.
5. Surveiller le serveur :
Pour surveiller le serveur, exécutez l'outil de surveillance que vous avez configuré pour collecter des données.
Par exemple, après avoir défini une trace, vous pouvez l’exécuter pour recueillir des données concernant les
événements qui se sont produits sur le serveur.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
6. Analyser les données :
Une fois le suivi terminé, analysez les données pour vérifier si vous avez atteint votre objectif de surveillance. Si
ce n’est pas le cas, modifiez les composants ou les éléments de mesure utilisés pour surveiller le serveur.
Le processus de capture de données d’événement et de leur exploitation est décrit ci-dessous.
1. Appliquer des filtres pour limiter les données d'événement recueillies.
Le fait de limiter les données d'événement permet de s'attacher uniquement aux événements pertinents
par rapport au scénario de surveillance en place. Par exemple, si vous souhaitez surveiller les requêtes
lentes, vous pouvez utiliser un filtre afin de ne vous intéresser qu’aux requêtes dont l'exécution par
l'application sur une base de données particulière prend plus de 30 secondes.
2. Surveiller (capturer) les événements.
Dès qu'elle est activée, la surveillance active capture des données à partir de l'application, de l'instance
de SQL Server ou du système d'exploitation spécifié. Par exemple, lorsque l'activité du disque est
analysée à l'aide du Moniteur système, ce dernier capture les données d'événement, notamment les
lectures et les écritures sur le disque et les affiche sur l'écran.
3. Enregistrer les données d'événement capturées.
L'enregistrement des données d'événement capturées vous permet de les analyser ultérieurement,
voire de les relire avec DistributedReplay Utility ou le SQL Server Profiler. Les données d'événement
capturées sont enregistrées dans un fichier pouvant être rechargé dans l'outil qui l'a créé à l'origine
pour analyse. Le SQL Server Profiler permet d'enregistrer les données d'événement dans une table
SQL Server. L'enregistrement des données d'événement capturées est essentiel lors de la création
d'un niveau de référence des performances. Les données du niveau de référence des performances
sont enregistrées et utilisées lors de la comparaison des données d'événement récemment capturées
afin de déterminer si les performances sont optimales.
4. Créer des modèles de trace contenant les paramètres spécifiés pour capturer les événements.
Ces modèles contiennent des spécifications concernant les événements proprement dits, les données
d’événement et les filtres utilisés pour la capture de données. Ils permettent de surveiller ultérieurement
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
un ensemble spécifique d'événements sans avoir à redéfinir les événements, les données d'événement
et les filtres. Par exemple, si vous voulez souvent surveiller le nombre d'inter blocages et les utilisateurs
impliqués dans ces inter blocages, vous pouvez créer un fichier définissant ces événements, données
d'événement et filtres d'événement, puis enregistrer le modèle et appliquer le filtre la prochaine fois que
vous voudrez surveiller les inter blocages. Le SQL Server Profiler utilise les modèles de trace à cet
effet.
5. Analyser les données d'événement capturées.
Les données d'événement capturées sont chargées dans l'application qui les a capturées afin d’être
analysées. Par exemple, une trace capturée depuis SQL Server Profiler peut y être rechargée en vue
d’un affichage et d'une analyse.
L'analyse des données d'événement implique l'identification des événements et de leur cause. Ces
informations vous permettent d'effectuer des modifications susceptibles d'améliorer les performances,
telles que l'ajout de mémoire, la modification d'index, la correction de problèmes de code avec des
procédures stockées et des instructions Transact-SQL, en fonction du type d'analyse effectuée. Par
exemple, vous pouvez utiliser l'Assistant Paramétrage du Moteur de base de données pour analyser
une trace capturée depuis SQL Server Profiler et créer des recommandations d'index en fonction des
résultats.
6. Relire les données d'événement capturées.
La relecture d'événements permet d'établir une copie de test de l'environnement de base de données à
partir duquel les données ont été capturées, puis de répéter les événements capturés tels qu'ils se sont
initialement produits sur le système réel. Cette fonction n'est disponible qu'avec DistributedReplayUtility
ou le SQL Server Profiler. Ces événements peuvent être relus à la vitesse à laquelle ils se sont
produits, aussi rapidement que possible (pour contraindre le système) ou, plus vraisemblablement, pas
à pas (pour analyser le système après chaque événement). L'analyse des événements exacts dans un
environnement de test empêche tout effet nuisible sur le système de production.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
II.
T.S.
Outils d'analyse et de paramétrage des performances :
Microsoft SQL Server fournit un ensemble complet d'outils permettant de surveiller les événements de SQL
Server et de paramétrer la structure physique de la base de données. Le choix de l'outil dépend du type de
surveillance ou de paramétrage à effectuer et des événements spécifiques à contrôler.
Les outils de surveillance et de paramétrage de SQL Server sont les suivants :
Outil
Description
sp_trace_setfilter Le SQL Server Profiler assure le suivi des événements de processus du moteur, notamment le
(Transact-SQL)
début d'un traitement ou d'une transaction, ce qui vous permet de surveiller l'activité du serveur
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
et de la base de données (par exemple les blocages, les erreurs irrécupérables ou les
connexions). Vous pouvez capturer les données du SQL Server Profiler dans une table ou un
fichier SQL Server en vue d'une analyse ultérieure, et relire les événements capturés sur SQL
Server, pas à pas, pour savoir ce qui s'est passé exactement.
SQL
Server Microsoft SQL Server DistributedReplay peut utiliser plusieurs ordinateurs pour relire les données
DistributedReplay de trace, en simulant mieux les charges de travail sensibles.
Le Moniteur système surveille principalement l'utilisation des ressources, notamment le nombre
de demandes de pages en cours au gestionnaire de tampons, ce qui vous permet de contrôler
les performances et l'activité du serveur à l'aide d'objets et de compteurs prédéfinis, ou de
compteurs définis par l'utilisateur pour surveiller les événements. Le Moniteur système
(l’Analyseur de performances dans Microsoft Windows NT 4.0) recueille le nombre et le taux et
non pas les données concernant les événements (par exemple, l'utilisation de la mémoire, le
Analyser
nombre de transactions actives, le nombre de verrous bloqués, ou l'activité de l'UC). Vous
l'utilisation
des
ressources
pouvez définir des seuils pour des compteurs spécifiques de manière à générer des alertes pour
avertir les opérateurs.
(Moniteur
système)
Le Moniteur système fonctionne sur les systèmes d'exploitation Microsoft Windows Server et
Windows. Il peut surveiller (à distance ou localement) une instance de SQL Server sur
Windows NT 4.0 ou version ultérieure.
La différence essentielle entre le SQL Server Profiler et le Moniteur système, est que le SQL
Server Profiler surveille les événements du moteur de base de données, tandis que le Moniteur
système surveille l'utilisation des ressources associées aux processus du serveur.
Ouvrir
le
Moniteur
d'activité
(SQL
Server
Management
Studio)
Le Moniteur d'activité de SQL Server Management Studio affiche des informations sous forme
graphique sur :

les processus s'exécutant dans une instance de SQL Server ;

les processus bloqués ;

les verrous ;

l'activité utilisateur.
Ceci est utile pour les affichages ad hoc de l'activité courante.
Les procédures stockées Transact-SQL qui créent, filtrent et définissent la trace :
Trace SQL

sp_trace_create (Transact-SQL)

sp_trace_generateevent (Transact-SQL)
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR

sp_trace_setevent (Transact-SQL)

sp_trace_setfilter (Transact-SQL)

sp_trace_setstatus (Transact-SQL)
T.S.
Le journal des événements des applications Windows fournit une image complète des
événements survenant sur les systèmes d'exploitation Windows Server et Windows dans leur
Journaux
des ensemble, ainsi que des événements survenant dans SQL Server, dans l'Agent SQL Server et
erreurs
dans la recherche en texte intégral. Il contient des informations exclusives sur les événements
qui se produisent dans SQL Server. Vous pouvez utiliser les informations du journal des erreurs
pour résoudre des problèmes liés à SQL Server.
Les procédures stockées système SQL Server ci-après fournissent une puissante alternative à
de nombreuses tâches de surveillance :
Procédure stockée Description
sp_who
(Transact-
SQL)
Procédures
sp_lock
(Transact-SQL)
SQL)
actifs de SQL Server, y compris l'exécution de l'instruction active et si
l'instruction est bloquée.
stockées
système
Renvoie des informations d'instantané sur les utilisateurs et les processus
(Transact-
Renvoie des informations d'instantané sur les verrous, y compris l'ID de
l'objet, l'ID d'index, le type de verrou et le type de ressource auquel
s'applique le verrou.
sp_spaceused
Affiche une estimation de l'espace disque actuellement utilisé par une
(Transact-SQL)
table (ou une base de données entière).
sp_monitor
Affiche des statistiques, notamment l'utilisation de l'UC, l'utilisation des
(Transact-SQL)
E/S et la durée d'inactivité depuis la dernière exécution de sp_monitor.
DBCC (Transact- Les instructions DBCC (Database Console Command) vous permettent de contrôler les
SQL)
statistiques de performances et la cohérence logique et physique d'une base de données.
Les fonctions intégrées affichent des statistiques d'instantané sur l'activité SQL Server depuis le
démarrage du serveur ; ces statistiques sont stockées dans des compteurs SQL Server
Fonctions
prédéfinis. Par exemple, @@CPU_BUSY indique pendant combien de temps l'UC a exécuté du
intégrées
(Transact-SQL)
code SQL Server ; @@CONNECTIONS indique le nombre de connexions SQL Server ou de
tentatives de connexion ; enfin, @@PACKET_ERRORS indique le nombre de paquets réseau
sur des connexions SQL Server.
Indicateurs
de Les indicateurs de trace affichent des informations sur une activité spécifique sur le serveur ; ils
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
trace (Transact- permettent de diagnostiquer les problèmes ou les causes agissant sur les performances (par
SQL)
exemple, chaînes de blocage).
Assistant
L'Assistant de Paramétrage du moteur de base de données analyse les effets des performances
Paramétrage du des instructions Transact-SQL exécutées sur des bases de données à paramétrer. Il fournit des
moteur de base recommandations pour ajouter, supprimer ou modifier des index, des vues indexées et un
de données
partitionnement.
1. Choix d'un outil de surveillance :
Le choix d'un outil de surveillance dépend de l'événement ou de l'activité à surveiller.
Événement
ou SQL
activité
Server
Profiler
Analyse
de
tendance
Relecture
DistributedReplay
Oui
ordinateur
capturés
unique)
Moniteur
Transact- Journaux
système
d'activité
SQL
des erreurs
Oui
Oui
Oui
Oui
des Oui (depuis un
événements
Moniteur
Oui (depuis plusieurs
ordinateurs)
Surveillance ad hoc Oui
Génération d'alertes
Oui
Interface graphique Oui
Oui
Oui
Oui
Utilisation dans une
application
Oui 1
Oui
personnalisée
1 Utilisation des procédures stockées système du SQL Server Profiler.
2. Outils de surveillance Windows :
Les systèmes d'exploitation Windows et Windows Server 2003 proposent également les outils de surveillance
suivants.
Outil
Description
Gestionnaire des tâches
Affiche un résumé des processus et des applications en cours d'exécution sur le
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
système.
Agent
de
surveillance
réseau
du
Surveille le trafic réseau.
3. Évaluation de la cardinalité (SQL Server) :
La logique d'estimation de la cardinalité, appelée estimateur de cardinalité, est remodelée dans SQL
Server 2014 afin d'améliorer la qualité des plans de requête, et par conséquent, améliorer les
performances des requêtes. Le nouvel estimateur de cardinalité incorpore des hypothèses et des
algorithmes qui fonctionnent sur les charges de travail OLTP et de stockage de données modernes. Il
repose sur la recherche détaillée des estimations de cardinalité sur les charges de travail modernes et
sur nos connaissances acquises au cours des 15 dernières années sur l'amélioration de l'estimateur de
cardinalité SQL Server. Les commentaires des clients indiquent que bien que la plupart des requêtes
tirent parti des modifications ou demeurent inchangées, un petit nombre d'entre elles présente des
régressions par rapport à l'estimateur de cardinalité précédent.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Remarque
Les estimations de cardinalité sont une prédiction du nombre de lignes dans le résultat de la requête.
L'optimiseur de requête utilise ces estimations pour choisir un plan d'exécution de la requête. La
qualité du plan de requête a un impact direct sur l'amélioration des performances des requêtes.
Recommandations pour le test des performances et le paramétrage :
Le nouvel estimateur de cardinalité est activé pour toutes les nouvelles bases de données créées dans
SQL Server 2014. Cependant, la mise à niveau vers SQL Server 2014 n'active pas le nouvel
estimateur de cardinalité dans les bases de données existantes.
Pour obtenir les meilleures performances des requêtes, utilisez ces recommandations pour tester
votre charge de travail avec le nouvel estimateur de cardinalité avant de l'activer sur votre système de
production.
1. Mettez à niveau toutes les bases de données existantes pour utiliser le nouvel estimateur de la
cardinalité. Pour cela, utilisez Niveau de compatibilité ALTER DATABASE (Transact-SQL)
pour définir le niveau de compatibilité de la base de données à 120.
2. Exécutez votre test de charge de travail avec le nouvel estimateur de cardinalité, puis résolvez
les nouveaux problèmes de performances de la même façon que les problèmes de
performances.
3. Une fois que votre charge de travail s'exécute avec le nouvel estimateur de cardinalité (niveau
de compatibilité de la base de données 120), et qu'une requête spécifique a régressé, exécutez
la requête avec l'indicateur de trace 9481 de façon à utiliser la version 70 (version de SQL
Server 2012) de l'estimateur de cardinalité. Pour exécuter une requête avec un indicateur de
trace, consultez l'article de la Base de connaissances Activer un plan affectant le
comportement de l'optimiseur de requête SQL Server qui peut être contrôlé par des
indicateurs de trace différents à un niveau spécifique à une requête.
4. Si vous ne pouvez pas modifier toutes les bases de données à la fois de façon à utiliser le
nouvel estimateur de cardinalité, utilisez l'estimateur de cardinalité précédent pour toutes les
bases de données à l'aide de Niveau de compatibilité ALTER DATABASE (Transact-SQL)
pour définir le niveau de compatibilité de la base de données à 110.
5. Si votre charge de travail s'exécute avec le niveau de compatibilité de la base de données 110
et vous souhaitez tester ou exécuter une requête spécifique avec le nouvel estimateur de
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
cardinalité, exécutez la requête avec l'indicateur de trace 2312 de façon à utiliser la
version 120 (nouvelle version) de l'estimateur de cardinalité. Pour exécuter une requête avec
un indicateur de trace, consultez l'article de la Base de connaissances Activer un plan
affectant le comportement de l'optimiseur de requête SQL Server qui peut être contrôlé par
des indicateurs de trace différents à un niveau spécifique à une requête.
Nouveaux XEvents :
Il y a deux nouveaux XEventsquery_optimizer_estimate_cardinality pour prendre en charge les
nouveaux plans de requête.

query_optimizer_estimate_cardinality se produit lorsque l'optimiseur de requête estime la
cardinalité sur une expression relationnelle.

query_optimizer_force_both_cardinality_estimation_behaviors se produit lorsque les deux
indicateurs de trace 2312 et 9481 sont activés, tentant de forcer l'ancien et le nouveau
comportement d'estimation de cardinalité en même temps.
Exemples :
Les exemples suivants illustrent certaines des modifications apportées aux nouvelles estimations de
cardinalité. Le code d'estimation de la cardinalité a été réécrit. La logique est complexe et il est
impossible de fournir une liste exhaustive de toutes les modifications.
Remarque
Ces exemples sont fournis en tant qu'informations conceptuelles. Aucune action n'est requise de
votre part pour modifier la manière dont vous concevez les bases de données et les requêtes.
Exemple A. Les nouvelles estimations de cardinalité utilisent une cardinalité moyenne pour les
données croissantes ajoutées récemment :
Cet exemple montre comment le nouvel estimateur de cardinalité peut améliorer les estimations de
cardinalité pour les données croissantes qui dépassent la valeur maximale dans la table pendant la
mise à jour des statistiques la plus récente.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
SELECT item, category, amount FROM dbo.Sales AS s WHERE Date = '2013-12-19';
Dans cet exemple, de nouvelles lignes sont ajoutées à la table Ventes chaque jour, la requête
demande les données des ventes qui se sont produites le 19/12/2013, et la dernière mise à jour des
statistiques a été effectuée le 18/12/2013. L'estimateur de cardinalité précédent suppose que les
valeurs du 19/12/2013 n'existent pas, car la date dépasse la date maximale et les statistiques n'ont pas
été mises à jour pour inclure les valeurs du 19/12/2013. Cette situation, appelée problème de clé
croissante, se produit lorsque vous chargez des données au cours de la journée, puis exécutez des
requêtes sur les données avant de mettre à jour les statistiques.
Ce comportement a changé. Maintenant, même si les statistiques n'ont pas été mises à jour pour les
données croissantes les plus récentes ajoutées depuis la dernière mise à jour des statistiques, le
nouvel estimateur de cardinalité suppose que les valeurs existent et utilise la cardinalité moyenne de
chaque valeur dans la colonne comme estimation de la cardinalité.
Exemple B. Les nouvelles estimations de cardinalité supposent que les prédicats filtrés sur la
même table ont une certaine corrélation :
Pour cet exemple, supposons que la table Cars contient 1 000 lignes, la colonne Make contient
200 correspondances pour « Honda », la colonne Model contient 50 correspondances pour « Civic »
et que tous les modèles Civic sont de marque Honda. Par conséquent, 20 % des valeurs de la colonne
Make correspondent à « Honda », 5 % des valeurs de la colonne Model correspondent à « Civic » et
le nombre réel de Civic honda est 50. Les estimations de cardinalité précédentes supposent que les
valeurs des colonnes Make et Model sont indépendantes. L'optimiseur de requête précédent estime
qu'il y a 10 Honda Civic (.05 * .20 * 1000 lignes = 10 lignes).
SELECT
year,
purchase_price
FROM
dbo.Cars
WHERE
Make
= 'Honda'
AND
Model =
'Civic';
Ce comportement a changé. Maintenant, les nouvelles estimations de cardinalité supposent que les
colonnes Make et Model ont une certaine corrélation. L'optimiseur de requête estime une cardinalité
plus élevée en ajoutant un composant exponentiel à l'équation d'estimation. L'optimiseur de requête
estime maintenant qu'il y a 22,36 lignes (.05 * SQRT(.20) * 1 000 lignes = 22,36 lignes) qui
correspondent au prédicat. Pour ce scénario et cette distribution de données spécifique, 22,36 lignes
est le résultat le proche des 50 lignes réelles qui sera retourné par la requête.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Notez que la logique du nouvel estimateur de cardinalité trie les sélectivités de prédicat et augmente
l'exposant. Par exemple, si les sélectivités de prédicat sont .05, .20 et .25, l'estimation de la
cardinalité est (.05 * SQRT(.20) * SQRT(SQRT(0,25)) ).
Exemple C. Les nouvelles estimations de cardinalité supposent que les prédicats filtrés sur les
tables sont indépendants :
Pour cet exemple, l'estimateur de cardinalité précédent suppose que les filtres de prédicat s.type et
r.date sont corrélés. Toutefois, les résultats du test sur les charges de travail modernes indiquent que
les filtres de prédicat sur les colonnes de différentes tables ne sont généralement pas corrélés.
SELECT s.ticket, s.customer, r.store FROM dbo.Sales AS s CROSS JOIN dbo.Returns
AS r
WHERE s.ticket = r.ticket AND s.type = 'toy' AND r.date = '2013-12-19';
Ce comportement a changé. Maintenant, la logique du nouvel estimateur de cardinalité suppose que
s.type n'est pas mis en corrélation avec r.date. En pratique, l'hypothèse est que les jouets sont
retournés tous les jours et non un seul jour spécifique. Dans ce cas, les nouvelles estimations de
cardinalité sont un nombre inférieur aux estimations de cardinalité précédentes.
III.
Établir un niveau de référence des performances :
Pour déterminer si votre système SQL Server assure des performances optimales,
mesurez les performances à intervalles réguliers, même en l'absence de problèmes,
pour établir un niveau de référence des performances du serveur. Comparez chaque
nouvel ensemble de mesures à ceux réalisés précédemment.
Les éléments suivants ont une influence sur les performances de SQL Server :

ressources système (matériel) ;

architecture réseau ;
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR

système d'exploitation ;

applications de base de données ;

applications clientes.
T.S.
Au minimum, vous devez effectuer des mesures de niveau de référence pour
déterminer :

les heures de pointe et les heures creuses d'exploitation ;

les temps de réponse des requêtes de production et des commandes de
traitement par lots ;

les temps de sauvegarde et de restauration de la base de données.
Après avoir établi le niveau de référence des performances, comparez vos statistiques
aux performances actuelles du serveur. Toute valeur très supérieure ou très inférieure à
votre niveau de référence doit donner lieu à un examen approfondi. Elle peut indiquer
la nécessité d'optimiser ou de reconfigurer certains points. Par exemple, si le temps
nécessaire à l'exécution d'un ensemble de requêtes augmente, examinez les requêtes en
question pour déterminer si vous pouvez les réécrire ou si vous devez ajouter des
colonnes de statistiques ou de nouveaux index.
IV.
Isoler les problèmes de performance :
Il est souvent plus efficace d'utiliser plusieurs outils Microsoft SQL Server ou Microsoft Windows
conjointement pour isoler les problèmes de performance de base de données que d'utiliser un seul
outil à la fois. Par exemple, la fonctionnalité graphique Plan d'exécution, également appelée plan
d'exécution de requêtes, vous aide à reconnaître rapidement les blocages dans une seule requête.
Toutefois, vous pouvez reconnaître d'autres problèmes de performance plus facilement si vous
utilisez les fonctions de surveillance de SQL Server et de Windows en même temps.
SQL Server Profiler permet de surveiller et de résoudre les problèmes liés à Transact-SQL et à
l'application. Le Moniteur système permet de surveiller les problèmes liés au matériel ou à d'autres
aspects du système.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Vous pouvez surveiller les éléments suivants pour résoudre les problèmes :

Les procédures stockées de SQL Server ou les traitements Transact-SQL envoyés par les
applications des utilisateurs.

L'activité de l'utilisateur, notamment les verrous de blocage et les blocages.

L'activité matérielle, notamment l'utilisation du disque.
Vous pouvez identifier les problèmes suivants :

des erreurs dans le développement d'une application liées à des instructions Transact-SQL
mal rédigées ;

des erreurs matérielles, notamment des erreurs de disque ou de réseau ;

un blocage excessif dû à une base de données mal conçue.
Outils pour les problèmes de performance classiques :
Il est très important aussi de sélectionner exactement le problème de performance que vous souhaitez
faire surveiller ou régler par chaque outil. L'outil et l'utilitaire dépendent du type de problème de
performance à résoudre.
Surveiller l'utilisation de la mémoire
V.
Identifier les goulots d'étranglement (passage de suppression):
L'accès simultané aux ressources partagées provoque des goulots d'étranglement. En général, les
goulots d'étranglement sont inévitables et existent dans tous les systèmes logiciels. Toutefois, des
demandes excessives sur les ressources partagées engendrent un temps de réponse médiocre, qui
impose de les identifier et de les ajuster.
Les causes des goulots d'étranglement sont notamment les suivantes :




ressources insuffisantes nécessitant l'ajout ou la mise à niveau de composants ;
répartition inégale des charges de travail entre les ressources de même type (ce qui peut être
le cas lorsqu'un disque est monopolisé) ;
ressources défectueuses ;
ressources configurées incorrectement.
Analyse des goulots d'étranglement :
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
La durée excessive de divers événements représente un indicateur des goulots d'étranglement
susceptibles d'être ajustés.
Par exemple :


un composant peut empêcher le chargement d'un autre composant, augmentant ainsi le temps
nécessaire pour terminer le chargement ;
les demandes de client peuvent prendre plus longtemps en raison d'encombrements sur le
réseau.
Voici cinq domaines clés à surveiller lors du suivi des performances du serveur pour identifier les
goulots d'étranglement.
Domaine possible de
goulet
Effets sur le serveur
d'étranglement
Utilisation
mémoire
de
Utilisation
processeur
Une quantité de mémoire insuffisante, allouée ou disponible pour Microsoft
SQL Server, dégrade les performances. Les données doivent être lues sur le
la disque plutôt que directement à partir du cache de données. Les systèmes
d'exploitation Microsoft Windows font un usage excessif des fichiers
d'échange : ils transfèrent des données en provenance et à destination du
disque chaque fois que les pages sont nécessaires.
du
Un taux d'utilisation processeur régulièrement élevé peut indiquer que les
requêtes Transact-SQL doivent être ajustées ou que l'unité centrale doit être
mise à niveau.
Entrées/Sorties
disque (E/S)
Les requêtes Transact-SQL peuvent être ajustées pour éviter les E/S
superflues, par exemple en utilisant des index.
Connexions
utilisateur
Un nombre trop important d'utilisateurs peuvent accéder au serveur en même
temps, provoquant une dégradation des performances.
Verrous bloquants
Des applications mal conçues peuvent provoquer des blocages et nuire à la
simultanéité, provoquant ainsi des temps de réponse plus longs et des débits
de transactions plus faibles.
VI.
Analyse des performances et surveillance de l'activité du serveur :
Le but de la surveillance des bases de données est d'évaluer le fonctionnement d'un serveur. Une
surveillance efficace implique la prise d'instantanés périodiques des performances actuelles afin d'isoler les
processus à l’origine des problèmes, ainsi que la collecte de données en continu pour suivre de près les
tendances des performances. Microsoft SQL Server et le système d'exploitation Microsoft Windows fournissent
des utilitaires qui permettent de contrôler les conditions actuelles de la base de données et de suivre l'évolution
des performances en fonction de l’évolution de ces conditions.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Pour effectuer une analyse des tâches à l'aide des outils Windows

Démarrer le Moniteur système (Windows)
Utilisez le Moniteur système pour surveiller l'utilisation des ressources système. Il permet de
collecter et d'afficher des données de performances en temps réel sous la forme de compteurs pour
des ressources serveur (par exemple l'utilisation du processeur et de la mémoire), ainsi que pour de
nombreuses ressources Microsoft SQL Server (telles que les verrous et les transactions).
Pour démarrer le Moniteur système de Windows

Dans le menu Démarrer, pointez sur Exécuter, tapez perfmon dans la boîte de dialogue
Exécuter, puis cliquez sur OK.

Afficher le journal des applications Windows (Windows)
Pour créer des alertes de base de données SQL Server à l'aide des outils Windows

Configurer une alerte de base de données SQL Server (Windows)
À l'aide du Moniteur système, vous pouvez créer une alerte qui se déclenche quand le seuil
d'un compteur du Moniteur système est franchi. En réponse à l'alerte, le Moniteur système
peut lancer une application, telle qu'une application personnalisée écrite pour prendre en
charge la condition d'alerte. Par exemple, vous pouvez créer une alerte déclenchée quand le
nombre de blocages dépasse une valeur spécifique.
Pour installer une alerte de base de données SQL Server
1. Dans l'arborescence de navigation de la fenêtre Performances, développez Journaux et alertes
de performance.
2. Cliquez avec le bouton droit sur Alertes, puis cliquez sur Nouveaux paramètres d'alerte.
3. Dans la boîte de dialogue Nouveaux paramètres d'alerte, entrez un nom pour la nouvelle
alerte, puis cliquez sur OK.
4. Dans l'onglet Général de la boîte de dialogue de la nouvelle alerte, ajoutez un commentaire,
puis cliquez sur Ajouter pour ajouter un compteur à l'alerte.
Chaque alerte doit avoir au moins un compteur.
5. Dans la boîte de dialogue Ajouter des compteurs, sélectionnez un objet SQL Server dans la
liste Objet de performance, puis sélectionnez un compteur dans la zone Sélectionner les
compteurs dans la liste.
6. Pour ajouter le compteur à l'alerte, cliquez sur Ajouter. Vous pouvez continuer à ajouter des
compteurs, ou bien cliquer sur Fermer pour revenir à la boîte de dialogue de la nouvelle
alerte.
7. Dans la boîte de dialogue de la nouvelle alerte, cliquez sur Supérieur à ou sur Inférieur àdans
la liste Avertir si la valeur est, puis entrez une valeur de seuil dans la zone Limite.
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
L'alerte est générée lorsque la valeur du compteur est supérieure ou inférieure à la valeur du
seuil (selon que vous avez sélectionné Supérieur à ou Inférieur à).
8. Dans les zones Période d'échantillonnage des données, définissez la fréquence
d'échantillonnage.
9. Sur l'onglet Action, définissez les actions qui doivent se produire lorsque l'alerte est
déclenchée.
10. Sur l'onglet Planification, définissez la planification de début et de fin de l'analyse d'alerte.
Pour effectuer une analyse des tâches à l'aide de SQL Server Management Studio


Afficher le journal des erreurs SQL Server (SQL Server Management Studio)
Ouvrir le Moniteur d'activité (SQL Server Management Studio)
Pour effectuer une analyse des tâches à l'aide de Trace SQL en utilisant des procédures stockées
écrites en Transact-SQL






Créer une trace (Transact-SQL)
Définir un filtre de trace (Transact-SQL)
Modifier une trace existante (Transact-SQL)
Afficher une trace enregistrée (Transact-SQL)
Afficher des informations de filtrage (Transact-SQL)
Supprimer une trace (Transact-SQL)
Pour créer et modifier des traces à l'aide du Générateur de profils SQL Server















Créer une trace (SQL Server Profiler)
Définir les options globales de trace (SQL Server Profiler)
Spécifier les événements et les colonnes de données d'un fichier de trace (SQL Server Profiler)
Créer un script Transact-SQL pour exécuter une trace (SQL Server Profiler)
Enregistrer des résultats d'une trace dans un fichier (SQL Server Profiler)
Définir la taille maximale d'un fichier de trace (SQL Server Profiler)
Enregistrer des résultats d'une trace dans une table (SQL Server Profiler)
Définir une taille maximale de table de trace (SQL Server Profiler)
Filtrer des événements dans une trace (SQL Server Profiler)
Afficher des informations de filtre (SQL Server Profiler)
Modifier un filtre (SQL Server Profiler)
Filtrer des événements en fonction de l'heure de début de l'événement (SQL Server Profiler)
Filtrer des événements en fonction de leur heure de fin (SQL Server Profiler)
Filtrer des identificateurs de processus serveur (SPID, Server Process ID) dans une trace (SQL
Server Profiler)
Organiser les colonnes affichées dans une trace (SQL Server Profiler)
Pour démarrer, suspendre et arrêter des traces à l'aide du Générateur de profils SQL Server
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR




T.S.
Démarrer automatiquement une trace après s'être connecté à un serveur (SQL Server Profiler)
Suspendre une trace (SQL Server Profiler)
Arrêter une trace (SQL Server Profiler)
Exécuter une trace après qu'elle a été suspendue ou arrêtée (SQL Server Profiler)
Pour ouvrir des traces et configurer la façon dont elles sont affichées à l'aide du Générateur de profils
SQL Server






Ouvrir un fichier de trace (SQL Server Profiler)
Ouvrir une table de trace (SQL Server Profiler)
Effacer une fenêtre de trace (SQL Server Profiler)
Fermer une fenêtre de trace (SQL Server Profiler)
Définir des paramètres par défaut de trace (SQL Server Profiler)
Définir l'affichage par défaut des traces (SQL Server Profiler)
Pour rejouer des traces à l'aide du Générateur de profils SQL Server






Relire un fichier de trace (SQL Server Profiler)
Relire une table de trace (SQL Server Profiler)
Relire un seul événement à la fois (SQL Server Profiler)
Relecture jusqu'à un point d'arrêt (SQL Server Profiler)
Relire jusqu'à un curseur (SQL Server Profiler)
Relire un script Transact-SQL (SQL Server Profiler)
Pour créer, modifier et utiliser des modèles de traces à l'aide du Générateur de profils SQL Server






Créer un modèle de trace (SQL Server Profiler)
Modifier un modèle de trace (SQL Server Profiler)
Dériver un modèle à partir d'une trace en cours d'exécution (SQL Server Profiler)
Dériver un modèle à partir d'un fichier de trace ou d'une table de trace (SQL Server Profiler)
Exporter un modèle de trace (SQL Server Profiler)
Importer un modèle de trace (SQL Server Profiler)
Pour utiliser les traces du Générateur de profils SQL Server afin de collecter et de surveiller les
performances du serveur






Retrouver une valeur ou une colonne de données pendant le suivi (SQL Server Profiler)
Enregistrer les événements Deadlock Graph (Générateur de profils SQL Server)
Enregistrer séparément des événements Showplan XML (Générateur de profils SQL Server)
Enregistrer les événements Showplan XML Statistics Profile séparément (SQL Server
Profiler)
Extraire un script d'une trace (SQL Server Profiler)
Corréler une trace aux données du journal de performances Windows (SQL Server Profiler)
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
VII.
T.S.
Afficher et enregistrer des plans d'exécution :
Les plans d'exécution affichent graphiquement les méthodes de récupération des données choisies
par l'optimiseur de requête de SQL Server. Les plans d'exécution représentent le coût d'exécution de
requêtes et d'instructions spécifiques dans SQL Server par des icônes plutôt que par la représentation
tabulaire résultant des instructions SET SHOWPLAN_ALL ou SET SHOWPLAN_TEXT. Cette
approche graphique s'avère très utile pour la compréhension des caractéristiques de performances
d'une requête.
1. Affichage du plan d'exécution estimé :
Pour utiliser cette fonctionnalité, les utilisateurs doivent disposer des autorisations adéquates pour
exécuter la requête Transact-SQL correspondant au plan d'exécution graphique à générer, et ils
doivent posséder l'autorisation SHOWPLAN pour toutes les bases de données référencées par la
requête.
Pour afficher le plan d'exécution estimé pour une requête :
1. Dans la barre d'outils, cliquez sur Requête de moteur de base de données. Vous pouvez
également ouvrir une requête existante et afficher le plan d'exécution estimé en cliquant sur le
bouton Ouvrir un fichier dans la barre d'outils puis en choisissant la requête existante.
2. Entrez la requête pour laquelle afficher le plan d'exécution estimé.
3. Dans le menu Requête, cliquez sur Afficher le plan d'exécution estimé, ou cliquez sur le
bouton de même nom dans la barre d'outils. Le plan d'exécution estimé s'affiche sous
l'onglet Plan d'exécution du volet de résultats. Pour afficher des informations
supplémentaires, placez le curseur sur les icônes des opérateurs logiques et physiques, et
consultez la description et les propriétés de l'opérateur indiquées dans l'infobulle. Il est
également possible d'afficher les propriétés des opérateurs dans la fenêtre Propriétés. Si les
propriétés ne sont pas visibles, cliquez avec le bouton droit sur un opérateur, puis cliquez
sur Propriétés. Sélectionnez l'opérateur de votre choix.
4. Pour modifier l'affichage du plan d'exécution, cliquez sur celui-ci avec le bouton droit et
sélectionnez Zoom
avant, Zoom
arrière, Zoom
personnalisé ou Zoom
pour
ajuster. Zoom avant et Zoom arrière vous permettent respectivement d'agrandir et de
réduire l'affichage du plan d'exécution suivant des pourcentages fixes. Zoom
personnalisé sert à définir votre propre pourcentage d'agrandissement (par exemple, à 80
pour cent). Enfin, l'option Zoom pour ajuster agrandit le plan de sorte que sa taille soit
ajustée en fonction de celle du volet de résultats.
2. Afficher un plan d'exécution réel :
Pour être en mesure d'utiliser cette fonction, les utilisateurs doivent bénéficier des autorisations permettant
d'exécuter les requêtes Transact-SQL pour lesquelles un plan d'exécution graphique est actuellement généré.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
Qui plus est, les utilisateurs doivent se voir accorder l'autorisation SHOWPLAN pour toutes les bases de
données référencées par la requête.
Pour inclure un plan d'exécution pour une requête durant l'exécution :
1.
Dans la barre d'outils de SQL Server Management Studio, cliquez sur Requête de moteur de base de
données. Vous pouvez également ouvrir une requête existante et afficher le plan d'exécution estimé en
cliquant sur le bouton Ouvrir un fichier de la barre d'outils, puis en recherchant la requête.
2.
Entrez la requête pour laquelle vous souhaitez afficher le plan d'exécution réel.
3.
Dans le menu Requête, cliquez sur Inclure le plan d'exécution réel ou cliquez sur le bouton de la barre
d'outils Inclure le plan d'exécution réel.
4.
Exécutez la requête en cliquant sur le bouton de la barre d'outils Exécuter. Le plan utilisé par l'optimiseur
de requête est affiché sous l'onglet Plan d'exécution dans le volet de résultats. Placez le curseur de la
souris sur les opérateurs logiques et physiques pour visualiser la description et les propriétés des
opérateurs dans l'info-bulle affichée.
Vous avez également la possibilité de visualiser les propriétés des opérateurs dans la fenêtre
Propriétés. Si Propriétés n'est pas visible, cliquez avec le bouton droit sur un opérateur et
sélectionnez Propriétés. Sélectionnez un opérateur pour afficher ses propriétés.
5.
Si vous voulez modifier l'affichage du plan d'exécution, cliquez avec le bouton droit sur le plan d'exécution
et
sélectionnez Zoom
avant, Zoom
arrière, Zoom
personnalisé
ou Zoom
pour
ajuster. Les
options Zoom avant et Zoom arrière vous permettent d'effectuer un zoom avant ou arrière sur le plan
d'exécution. Quant à l'option Zoom personnalisé, elle vous permet de définir votre propre zoom ; par
exemple, un pourcentage de zoom de 80. En dernier lieu, l'option Zoom pour ajuster agrandit le plan
d'exécution afin que ses dimensions correspondent à celles du volet de résultats.
3. Enregistrer un plan d'exécution au format XML :
Pour utiliser la fonctionnalité de plan d'exécution de Management Studio, ou les options SET de
Showplan XML, les utilisateurs doivent disposer des autorisations appropriées pour exécuter la
requête Transact-SQL pour laquelle un plan d'exécution est généré et doivent obtenir l'autorisation
SHOWPLAN pour toutes les bases de données référencées par la requête.
Pour enregistrer un plan de requête avec les options SET de Showplan XML :
1. Dans SQL Server Management Studio, ouvrez un éditeur de requête et connectez-vous au Moteur
de base de données.
2. Activez SHOWPLAN_XML avec l'instruction suivante :
SET SHOWPLAN_XML ON;
GO
Pour activer STATISTICS XML, utilisez l'instruction suivante :
SET STATISTICS XML ON;
GO
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
SHOWPLAN_XML génère des informations de plan d'exécution de requête de compilation
pour une requête, mais sans exécuter cette dernière. STATISTICS XML génère des informations
de plan d'exécution de requête à l'exécution pour une requête et exécute cette dernière.
3. Exécuter une requête. Exemple :
USE AdventureWorks2012;
GO
SET SHOWPLAN_XML ON;
GO
-- Execute a query.
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
SET SHOWPLAN_XML OFF;
4. Dans le volet Résultats, cliquez avec le bouton droit sur le Plan d'exécution XML Microsoft
SQL Server contenant le plan de requête, puis cliquez sur Enregistrer les résultats sous.
5. Dans la boîte de dialogue Enregistrer les résultats <de la grille ou du texte>, dans la zone Type de
fichier, cliquez sur Tous les fichiers (*.*).
6. Dans la zone Nom du fichier, entrez le nom de fichier selon le format <nom>.sqlplan, puis
cliquez sur Enregistrer.
Pour enregistrer un plan d'exécution avec les options de SQL Server Management Studio
1. Générez un plan d'exécution soit estimé soit réel au moyen de Management Studio. Pour plus
d'informations, consultez Affichage du plan d'exécution estimé ou Afficher un plan d'exécution
réel.
2. Dans l'onglet Plan d'exécution du volet de résultats, cliquez avec le bouton droit sur le plan
d'exécution graphique, puis cliquez sur Enregistrer le plan d'exécution en tant que.
 Ou bien, vous pouvez aussi choisir Enregistrer le plan d'exécution en tant que dans le
menu Fichier.
3. Dans la boîte de dialogue Enregistrer sous, assurez-vous que Type de fichier est défini à Fichiers
de plan d'exécution (*.sqlplan).
4. Dans la zone Nom du fichier, entrez le nom de fichier selon le format <nom>.sqlplan, puis cliquez
sur Enregistrer.
Pour ouvrir un plan de requête XML dans SQL Server Management Studio
1. Dans le menu Fichier de SQL Server Management Studio, choisissez Ouvrir, puis cliquez
sur Fichier.
2. Dans la boîte de dialogue Ouvrir un fichier, définissez Types de fichiers à Fichiers de plan
d'exécution (*.sqlplan) pour produire une liste filtrée des fichiers de plan de requête XML
enregistrés.
3. Sélectionnez le fichier de plan de requête XML que vous voulez consulter, puis cliquez sur Ouvrir.
Spécialité : INFORMATIQUE
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR

Ou bien, dans l'Explorateur Windows, double-cliquez
l'extension .sqlplan. Le plan s'ouvre dans Management Studio.
Spécialité : INFORMATIQUE
sur
un
fichier
avec
T.S.
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
BIBLIOGRAPHIES
E-BOOK :
 Module 1 : Vue d'ensemble deMicrosoft SQL Server
 Administration d'une base de données Microsoft SQL Server 2000 IFP-Birkhadem
 www.microsoft.com
 http://msdn.microsoft.com/fr-fr/library/hh213686.aspx
Fiche Technique
Spécialité: Informatique.
Option : Base de Données.
Enseignante: Nadjette Mouici.
Date : 17/11/2014.
Nocours : 04
Module: Administration à l’aide de SQL SERVER.
No séance:03.
Temps précis : 01h 30mn.
Chapitre04: Options de Base de Données. Temps alloué:………h……….mn.
Thème : Les options d’état.
Objectif Opérationnel:
A LA FIN DE LA SEANCE LE STAGIAIRE SERA CAPABLE DE CITER
LES TROIS OPTIONS D’ETAT-CONNEXION, LECTURE | ECRITURE ET
UTILISATION-DES BASES DE DONNEES SOUS SQL SERVER; A L’AIDE
DE LANGAGE TRANSACT SQL ET SANS ERREURS.
Outils utilisés:
Spécialité : INFORMATIQUE
Module : AdMINISTRATIoN à l’AIde de SQl SeRVeR
T.S.
Enseignant:Stagiaire:
-- Tableau.
-- Cahier.
-- Marqueurs.
-- stylos.
Aides Pédagogiques:

Bibliographies:
 Melle REGUIG Radia, Administration d'une base de données Microsoft SQL Server 2000 ,
IFP-Birkhadem , Mars 2005.
Spécialité : INFORMATIQUE