Installation de SQL SERVER

CLIENT/SERVEUR
SQL SERVER 2000
Yonel Grusson
1
SQL SERVER 7
PLAN
•
•
•
•
Présentation
Installation
Résultat de l'installation
L'administration
– Par le SQL
– Par l'interface
• Création d'une base et d'une table
• Ajout de données
• La Sécurité
Yonel Grusson
2
SQL SERVER - Présentation
SQL-Server est un SGBD Client-Serveur (cf. le cours)
qui utilise TRANSACT-SQL dans ses transactions .
Client
Requête (Transact-SQL)
Résultats
(seulement les données)
Application CLIENTE
Yonel Grusson
Serveur
Base de
Données
Application SERVEUR :
SQL-SERVER
(SGBD)
3
SQL SERVER - Présentation
• L’application cliente s'occupe du traitement et
de la présentation des données (en d’autres
termes « ce qu’il faut faire avec les données »).
• SQL-Server :
– Gère et répartit éventuellement les bases de
données (gérer les relations entre les
données, assurer leur stockage)
– Assure la sécurité (sécurité des accès aux
bases et aux objets, récupération des
données en cas de panne).
Yonel Grusson
4
Installation de SQL SERVER
Configuration requise :
• Processeur : Intel ou compatible.
• Mémoire : 64 Mo et plus (128 Mo ou plus recommandé).
• Système d'exploitation :
 NT 4 (Server et WS) avec SP 5 ou ultérieur,
2000 (Server et Professionnel),
2003 Server (avec SQL-Server SP3 ou ultérieur)
• Système de fichiers : NTFS (peut fonctionner avec FAT).
• Internet Explorer 5.0 (nécessaire pour l'affichage de
l'Aide en ligne).
• Internet Information Services (IIS) s'il existe des
applications XML.
Yonel Grusson
5
Installation de SQL SERVER
Attention : SQL-SERVER 2000 n'est pas
supporté par Windows 2003 Server :
Continuer l'installation à la suite du message :
"SQL Server 2000 n'est pas pris en charge par
cette version de Windows." apparaissant au
début de l'installation.
Installer immédiatement après l'installation du
SGBD le service pack 3 ou ultérieur de SQLServer.
Yonel Grusson
6
Installation de SQL SERVER
Vérifier les composants requis
avant de lancer l'installation
Yonel Grusson
7
Installation de SQL SERVER
Installation locale
L'installation peut
se
faire
sur une
Il est
possible
d'installer plusieurs
machine
distante
instances de
SQL sur le même serveur.
Yonel Grusson
8
Installation de SQL SERVER
Yonel Grusson
9
Installation de SQL SERVER
Yonel Grusson
10
Installation de SQL SERVER
Personnalisation de l'installation
Yonel Grusson
11
Installation de SQL SERVER
Désignation des comptes sous lesquels sont exécutés les services
de SQL-Server
Ce compte doit exister
dans l'Active Directory.
Il doit appartenir au
groupe «Administrateurs».
Il permet l'exécution des
deux services : SQL Server
et Agent SQL Server
Yonel Grusson
12
Installation de SQL SERVER
Variante : Il est possible de différencier les comptes entre les 2
services.
Yonel Grusson
13
Installation de SQL SERVER
SQL-SERVER peut authentifier les utilisateurs :
• En s'appuyant sur l'authentification Windows
Yonel Grusson
14
Installation de SQL SERVER
SQL-SERVER peut authentifier les utilisateurs :
• En complétant la précédente par une authentification
supplémentaire propre au SGBD.
Attention : La connexion "SA" (System
Administrator) peut être créée sans mot de
passe – A éviter
Yonel Grusson
15
Installation de SQL SERVER
Yonel Grusson
Choix important car irréversible sans une
reconstruction complète de TOUTES les bases.
16
Installation de SQL SERVER
Mécanisme
interprocessus
(IPC) que de
SQL
Le port serade
icicommunication
attribué dynamiquement
au démarrage
Server
utilise
pour assurer
la communication
entreUDP
les clients
l'instance
du SGBD.
SQL-SERVER
utilise le port
1434 et
Yonel Grusson
17
les
serveurs
(cf. cours)
pour
la connexion
depuis les clients puis le port IP 1433.
Installation de SQL SERVER
Yonel Grusson
18
Installation de SQL SERVER
Installation du service Pack 3 ou ultérieur
Yonel Grusson
19
Installation de SQL SERVER
Yonel Grusson
20
Installation de SQL SERVER
Yonel Grusson
21
Résultat de l’installation
Le répertoire système (SQL Server proprement dit)
BINN : Pour les
exécutables
BOOKS : Pour l'aide en
ligne
DEVTOOLS : Outils
d'aide au développement
HTML : Pour MMC
(console) et SQL Server
SCRIPTS : Scripts
générés à l'installation
TEMPLATES :
Yonel Grusson
22
Résultat de l’installation
Les bases de données (1)
BACKUP : Pour les
fichiers de sauvegarde
DATA : Pour les bases
de données
JOBS : Pour les fichiers
temporaires de sorties
des travaux
LOG : Pour les fichiers
journaux des erreurs
REPLDATA :
Répertoire de travail des
tâches de réplication
Yonel Grusson
23
Résultat de l’installation
Les bases de données (2)
Yonel Grusson
24
Résultat de l’installation
Les bases de données (stockage physique)
Les bases de données SQL-Server sont constituées de 3
types de fichiers :
• Les fichier de données primaires (.mdf)
Point de départ de toutes les bases. Chaque base
comprend un et un seul fichier primaire qui point sur
les autres fichiers de la base de données.
• Les fichiers de données secondaires (.ndf)
• Les fichiers journaux (.ldf)
Ils contiennent toutes les informations de suivi
nécessaires à la récupération de dla base de données.
Yonel Grusson
25
Résultat de l’installation
Les bases de données (stockage physique)
Base de données
Fichier de données principal (.mdf)
Eventt un ou plusieurs fichiers secondaires (.ndf)
Un ou plusieurs fichiers
journaux des transactions (.ldf)
UNE ETENDUE = 8 Pages de 8 Ko
UNE Page = 8 Ko
Yonel Grusson
Les tables et les index sont stockés dans
des ETENDUES .
Une ETENDUE = 8 PAGES de 8 Ko
Une ligne ne peut pas s’étendre sur
plusieurs pages et sa taille maximum est de
26
8060 octets.
Résultat de l’installation
Dans l'Active Directory
Ce compte d’administration existait dans l'Active Directory avant
l'installation de SQL-Server. Il permet l'exécution des deux services : SQL
YonelServer
Grusson
et Agent SQL Server.
27
Résultat de l’installation
Les services
MSDTC s’exécute sous le compte : NT AUTHORITY\NetworkService
Ce service s’exécute sous le compte syteme local
Yonel Grusson
Ce service s’exécute sous le compte syteme local
28
Résultat de l’installation
Les services
Ces 2 services s’exécutent sous le compte d’administration défini à l’installation
Yonel Grusson
29
Résultat de l’installation
Les principaux services installés sont :
MS SQL Server : Ce service traite les requêtes en
provenance des clients. Il assure la gestion des données
et garantit l'intégrité de la base.
MS DTC : (Distributed Transaction Coordinator) Ce
service permet à une application cliente de faire appel à
plusieurs bases avec une seule transaction (Base de
données répartie – cf. cours).
SQLServerAgent : Ce service gère les tâches planifiées,
les alertes et la réplication des bases de données.
Yonel Grusson
30
Résultat de l’installation
Démarrage et arrêts des services :
• Automatiquement au démarrage du serveur
• Par l’intermédiaire du bureau
Yonel Grusson
31
Résultat de l’installation
Administration de SQL Server ( par l’interface)
SQL
Gestion des
services
Yonel Grusson
Administration de SQL Server
(Mode Interface)
32
L’administration de SQL Server
L'administrateur doit :
• Installer et configurer les serveurs
• Gérer le stockage (espace disque initial, surveillance de
cet espace et son agrandissement éventuellement
physique).
• Coordonner la création des bases de données et des
utilisateurs (la création peut être déléguée).
• Gérer la sécurité (accès à SQL et permissions des
utilisateurs sur les bases et leurs objets).
• Maintenir le système (gestion des sauvegardes,
planification des tâches, surveillance des serveurs et des
bases, prévention et réaction aux pannes…).
• Gérer les données distribuées.
33
Yonel Grusson
L’administration de SQL Server
L’administration peut se faire :
• Soit par l’intermédiaire du SQL
• Soit par l’intermédiaire d’une interface
(Enterprise Manager)
Yonel Grusson
34
Utilisation du SQL
Aperçu
Yonel Grusson
Pour une étude
plus approndie,
voir cours SQL
35
Utilisation du SQL
3 – Enregistrez votre
Choisir
requêtes
la baseSQL
de données
2 – Exécutez votre requête SQL
1 – Tapez votre requête SQL
Yonel Grusson
36
Utilisation de Enterprise Manager
Rappel : Les manipulations montrées ici avec l’interface
graphique peuvent évidemment se faire avec le SQL.
Yonel Grusson
37
Utilisation de Enterprise Manager
Yonel Grusson
38
Création d’une base de données
Yonel Grusson
39
Création d’une base de données
Création de la
base de données :
Le fichier des
données (.mdf)
Yonel Grusson
40
Création d’une base de données
Création de la
base de données :
Création du
journal des
transactions (.ldf)
Yonel Grusson
41
Création d’une base de données
Yonel Grusson
Résultat dans l’interface
42
Création d’une base de données
Résultat
sur le disque
Plus mise à jour des tables
système (base master)
Yonel Grusson
43
Création d’une table
Yonel Grusson
44
Création d’une table
Enregistrer
Création de l’index
Yonel Grusson
45
Création d’une table
Définir une contrainte d’intégrité référencielle
Yonel Grusson
46
Création d’une table
Yonel Grusson
47
Création d’une table
Yonel Grusson
48
Création d’une table
 Les clés regroupent :
• Les clés primaires
(identifiant)
• Les clés externes
Les index correspondent
à des propriétés
fréquemment utilisés et
permettent d’optimiser
l’accès celles-ci.
Yonel Grusson
49
Création d’une table
Les contraintes (CHECK
en SQL) permettent de
définir des règles sur les
propriétés d’une tables.
Par exemple définir un
domaine de validité pour
une valeur numérique.
Yonel Grusson
50
Création d’une table
Yonel Grusson
51
Création d’une table
Yonel Grusson
52
Création d’une table
Les types "CARACTERE" reconnus par SQL-Server sont :
CHAR , CHAR(n) : Chaîne de caractères – 1 octet par caractère – n <= 8000
– L'espace de stockage sera toujours de n octets (ajout d'espace
éventuellement).
VARCHAR, VARCHAR (n) : L'espace de stockage varie selon la longueur de
la chaîne – L'espace sera au plus égal à n octets.
NCHAR, NCHAR(n), NVARCHAR et NVARCHAR(n) : idem avec des
caractères UNICODE – 8000 octets maximum donc 4000 caractères
maximum.
TEXT et NTEXT : permet de stocker de grande quantité de texte (résumé de
Yonel
Grusson par exemple). Jusqu'à 2 147 483 647 octets.
livre
53
Création d’une table
Les types "NUMERIQUE ENTIER" reconnus par
SQL-Server sont :
INT (ou INTEGER) : Entier compris entre –231 et + 231 soit sur
4 octets.
SMALLINT : Entier compris entre –32768 et +32767 soit sur 2
octets.
TINYINT : Entier compris entre 0 et 255 soit sur 1 octet.
Les types "BINAIRE" sont :
BINARY, BINARY(n), VARBINARY, VARBINARY(n) :
Ensemble de bits (n<=8000).
Yonel Grusson
54
Création d’une table
Les types "NUMERIQUE APPROCHE" (en Virgule
Flottante) reconnus par SQL-Server sont :
REAL : Compris entre 3,4E-38 et 3,4E+38 avec une précision
de 7 chiffres (sur 4 octets).
FLOAT : Compris entre 1,7E-308 et 1,7E+308 avec une
précision de 15 chiffres (sur 8 octets).
FLOAT(n) : Le nombre d'octets alloué égal n donc augmente
ou diminue la précision.
Yonel Grusson
55
Création d’une table
Les types "NUMERIQUE EXACT" reconnus par
SQL-Server sont :
DECIMAL(p,s) ou NUMERIC(p,s) : avec "p" représente le
nombre de chiffres total et "s" le nombre de chiffres après la
virgule.
Les types "DATE" reconnus par SQL-Server sont :
DATETIME : Du 1/1/1753 au 31/12/9999. Sur 8 octets
SMALLDATETIME : Du 1/1/1900 au 6/6/2079. Sur 4 octets
Yonel Grusson
56
Création d’une table
Les types "MONETAIRE" reconnus par SQL-Server
sont :
MONEY : Pour les valeurs entre –922337203685477,5808 et
+922337203685477,5807.
SMALLMONEY : Pour les valeurs entre –214748,3648 et
+214748,36487.
MONEY occupe 2 fois plus d'espace que SMALLMONEY.
Yonel Grusson
57
Ajout de données
Yonel Grusson
58
Ajout de données
Yonel Grusson
59
La SECURITE
1 - La sécurité sous SQL-Server se situe à trois
niveaux
• Le rattachement au domaine dans lequel se
situe SQL-Server qui repose sur un ID de
connexion (login et un mot de passe),
• La possibilité d’utiliser SQL-Server, on
parlera ici de CONNEXION.
• L'utilisation d'une base de données qui
permet de restreindre l'accès ou l'utilisation
des objets de la base. SQL-Server parlera ici
d’UTILISATEUR.
Yonel Grusson
60
La SECURITE
2 – L'authentification de la CONNEXION peut
se faire de 2 façons :
• SQL-Server + Windows : SQL-Server
authentifie les connexions correspondant à
des utilisateurs Windows et à celles définies
au sein de SQL-Server.
• Windows uniquement : SQL-Server
authenfie seulement les utilisateurs Windows.
Le choix se fait a l'installation mais peut être
modifié par la suite.
Yonel Grusson
61
La SECURITE
Modification de la
sécurité à l'aide de :
"Propriétés de
l’instance de SQL
Server"
Yonel Grusson
62
La SECURITE
UNE Connexion Windows
UNE Connexion spécifique
SQL-Server
OU
Reprise de la
Connexion Windows
SQL-Server
Yonel Grusson
............
Base1
Base2
UtilisateurA
UtilisateurB
BaseN
UtilisateurX
63
La SECURITE
Ainsi ….
Au niveau d'une base de données :
• Un utilisateur est unique
• Un utilisateur est associé à une et une seule
connexion
Au niveau du SGBD, une connexion quant à elle :
• Est unique
• Peut être rattachée plusieurs utilisateurs
Connexion
Utilisateur
0,n
Yonel Grusson
1,1
Base
1,1
1,n
64
La SECURITE
Nouvelle connexion (état initial):
"CLAPTON/Eric_SQL" s'appuie sur une authentification Windows
"sa" s'appuie sur une authentification SQL Server
Yonel Grusson
65
La SECURITE
L'utilisateur doit
déjà exiter
Nouvelle connexion Windows uniquement :
Yonel Grusson
Création d'un
utilisateur
"JOLIE" sur la
base "Comix"
66
La SECURITE
Nouvelle connexion Windows
Yonel Grusson
Création d'un
utilisateur
"MONNOT" sur la
SQL-Server:
base "Comix"
67
La SECURITE
A la suite de sa connexion Windows l'utilisateur "JOLIE"
("CLAPTON/JOLIE") doit avoir le droit d'accéder à la base
"Comix:
Yonel Grusson
68
La SECURITE
Résultat sur la base de données "Comix"
Yonel Grusson
69
La SECURITE
Il est également
possible, lors de la
création d'une
connexion, de
rattacher cette
connexion à
plusieurs bases de
données.
Yonel Grusson
70
La SECURITE
Laisser la base
"master"
Nouvelle connexion (sans création d'un utilisateur) :
Yonel Grusson
71
La SECURITE
Nouvel Utilisateur: Une fois une connexion créée, si aucun
utilisateur ne lui a été reliée, il sera possible d'y rattacher des
utilisateurs.
Nom de l'utilisateur, par
défault ce nom est
Liste des connexionsidentique
disponibles
à celui de la
pour la base "Comix"
connexion
Yonel Grusson
72
La SECURITE
Il faut bien comprendre que la notion
d'utilisateur d'une base de SQL-Server est
transparente pour l'utilisateur de la base ("la
personne qui utilise la base").
L'utilisateur ("la personne") se connecte sur
SQL-Server et demande à utiliser telle base de
données. De ce fait il devient l'utilisateur SQL
(correspondant à la connexion) de cette base.
Rappel : Sur une base, une connexion
correspond un et un seul utilisateur.
Yonel Grusson
73
La SECURITE
Relation : Authentification /
Connexion (1)
L'utilisateur "LeChef" s'est
connecté sur le domaine
"Clapton".
Soit la configuration
Ici seule
suivante…
l'authentification
Windows
acceptée.
Par est
contre
…
"Monnot" est une
connexion SQL-Server
Yonel Grusson
74
La SECURITE
Relation : Authentification /
Connexion (2)
Soit la configuration
Les deux
suivante…
types de connexion
Et …
sont ici acceptés.
"Monnot" est une
connexion SQL-Server
Yonel Grusson
75
La SECURITE
Le droit accordé à un utilisateur pour accéder à
une base de données ne permet pas à cette
utilisateur de manipuler cette base
L'administrateur de la base doit donc accorder
sur les objets de la base des permissions aux
utilisateurs.
Les objets regroupent les tables (et les
opérations sur elles), différentes procédures
stockées et des vues.
Yonel Grusson
76
La SECURITE
L'utilisateur a néamoins quelques autorisations
sur une base de données.
Il obtient ces autorisation par son appatenane a
un ROLE (cf. plus loin).
Un utilisateur appartient par défaut au rôle
PUBLIC (cf. plus loin).
Attention : Si une autorisation n'est pas
spécifiquement définie au niveau de
l'utilisateur ; ce dernier subit l'autorisation
définie au niveau du rôle.
Yonel Grusson
77
La SECURITE
Permissions sur les objets d'une base pour un utilisateur nouvellement créer
Yonel Grusson
78
La SECURITE
Permissions d'un utilisateur nouvellement créer sur les objets d'une base
Attention : Par défaut l'utilisateur nouvellement
crée n'a AUCUNE autorisation sur les objets de
la base.
Il subit donc les autorisations du ou des rôle(s)
auquel il appartient
Yonel Grusson
79
La SECURITE
Permissions d'un utilisateur nouvellement créer sur les objets d'une base
Ces autorisations ne
sont pas définies.
Application du rôle
Le SELECT
Aucune autorisation
n'est est
spécifiquement
spécifiquement définie
refusé
Yonel Grusson
Le UPDATE est
spécifiquement
autorisé
80
La SECURITE
Liste des procédures stockées utilisables par les utilisateurs de la base
Yonel Grusson
81
La SECURITE
Les ROLES :
Il est évident que plusieurs utilisateurs d'une
base de données auront les mêmes permissions
sur cette base. Comme pour des permissions
sur les fichiers et les répertoires, il sera
possible de regrouper ces utilisateurs au sein
de groupes.
SQL-Server ne parlera pas de groupe mais de
rôle et distinguera différents rôles définis sur
deux niveaux (le SGBD et la base).
Yonel Grusson
82
La SECURITE
Les ROLES :
• Au niveau du serveur : Ils permettent à des
connexions d'avoir des droits administratifs
(en particulier par l'intermédiaire des
procédures stockées)
• Au niveau des bases de données : Ils
permettent d'affecter des droits aux
utilisateurs des bases de données
• Il est possible de créer des rôles.
Yonel Grusson
83
La SECURITE
Les ROLES : Au niveau d'une base de données
Aucune autorisation
Aucune autorisation
donnée par défaut sur la donnée par défaut sur la
base de données
base de données
Quelques autorisations Quelques autorisations
sont définies sur les
sont définies sur les
procédures, les tables procédures, les tables
système
et les vues.
Tout
utilisateur
est affecté
par
système
et nouvel
les vues.
défaut au rôle "public"
Yonel Grusson
84
La SECURITE
Les ROLES : Création d'un rôle
Yonel Grusson
85
La SECURITE
Les ROLES : Création d'un rôle
Pour finir, il reste à donner
des autorisations aux actuels
et futurs utilisateurs de la
base qui sont ou seront
rattachés à ce rôle.
Yonel Grusson
86
La SECURITE
Rôle prédéfinis sur une base de données :
Rôle de base de
données
Description
db_owner
Possède toutes les autorisations de la base
de données.
db_accessadmin
Peut ajouter ou supprimer des utilisateurs.
db_securityadmin
Peut gérer toutes les autorisations, les
propriétés d'objet, les rôles et les membres
des rôles
Yonel Grusson
87
La SECURITE
Rôle prédéfinis sur une base de données :
Rôle de base de
données
Description
Peut lancer l'instruction ALL DDL mais
db_ddladmin
pas les instructions GRANT, REVOKE
ou DENY.
Peut lancer les instructions DBCC,
db_backupoperator
CHECKPOINT et BACKUP.
Peut sélectionner toutes les données de
db_datareader
toutes les tables utilisateur dans la base
de données.
Yonel Grusson
88
La SECURITE
Rôle prédéfinis sur une base de données :
Rôle de base de
données
db_datawriter
Description
Peut modifier les données de toutes les
tables utilisateur dans la base de données.
Ne peut sélectionner des données
db_denydatareader d'aucune table utilisateur dans la base de
données.
Ne peut modifier les données d'aucune
db_denydatawriter
table utilisateur dans la base de données.
Yonel Grusson
89
La SECURITE
Les ROLES : Au niveau du serveur
Yonel Grusson
90
La SECURITE
Rôles au niveau du serveur SQL :
Rôle de serveur
Description
sysadmin
Exécute n'importe quelle opération dans SQL
Server. Les autorisations de ce rôle s'étendent à
tous les autres rôles de serveur fixes.
serveradmin
Configure les paramètres au niveau du serveur.
setupadmin
Ajoute et supprime les serveurs liés et exécute
certaines procédures système stockées telles que
sp_serveroption.
securityadmin
Gère les connexions d'accès au serveur.
Yonel Grusson
91
La SECURITE
Rôles au niveau du serveur SQL :
Rôle de serveur
Description
processadmin
Gère les traitements s'exécutant dans une instance
de SQL Server.
dbcreator
Crée et modifie les bases de données.
diskadmin
Gère les fichiers sur disque.
bulkadmin
Exécute l'instruction BULK INSERT.
Yonel Grusson
92
La SECURITE
Exemple de l'impact des permissions au travers d'une requête
SQL :
Yonel Grusson
93
La SECURITE
Les autorisations sont contradictoires
Yonel Grusson
94
La SECURITE
L'autorisation la plus restrictive
l'emporte (c'est une règle générale).
Yonel Grusson
95
La SECURITE
Yonel Grusson
96
La SECURITE
Les autorisations du rôle s'imposent.
Yonel Grusson
97
La SECURITE
Si plusieurs utilisateurs Windows sont
susceptibles de se connecter sur SQL-Server,
l'administrateur à la possibilité d'associer une
connexion SQL à un groupe Windows
User1
Connexion1
User2
Connexion2
User3
Connexion3
User4
Connexion4
SQL-Server
User1
User2
User3
User4
Groupe Windows
Connexion
SQL-Server
Yonel Grusson
98
La SECURITE
Soit un groupe Windows créer dans l'active Directory
Yonel Grusson
99
La SECURITE
Nouvelle connexion :
Etat initial
Etat final
Yonel Grusson
100
La SECURITE
Nouvel Utilisateur : Tous les membres du groupe "UserSQL"
pourront utiliser la base "Comix" sous l'utilisateur "Lecteur"
Yonel Grusson
101
La SECURITE
Application : Soit la connexion sur SQL-Server de l'utilisateur
"PIERRE" qui appartient au groupe Windows "UserSQL",
depuis un poste du réseau.
La connexion de "Pierre" est acceptée
du fait qu'il appartient au groupe
"UserSQL" sur lequel repose la
connexion SQL.
Yonel Grusson
102