sql server 2005

SQL SERVER 2005
I – Installation et configuration de SQL Server 2005
II - Gestion des bases de données et fichiers
III – Gestion de la sécurité
IV – Sauvegarde et restauration
1
I - Préparation à L’installation de SQL Server 2005
Les SQL Server offre plusieurs éditions :
o Edition Enterprise : Cette version ne s’installe que sur des serveurs. Elle donne la
passibilité de faire du business Intelligence.
o Standard : Version souvent installé dans des succursales de sortes à être piloté par
Enterprise 2005 au niveau de la maison mère.
o Work Group 2005 : Cette version est aux groupes de travail.
o Express : Elle remplace MSDE
o Mobile : pour Windows CE…
o Developper : C’est une copie de la version Enterprise.Elle n’est pas destiné à faire de
la production.Cette édition a toutes les fonctionnalité de Enterprise.On peut l’installer
sur XP avec une poste locale. Elle est utilisée pour faire des tests
o Evaluation : Elle ressemble à l’édition developper.Elle ne s’installe pas sur XP
 Configuration matérielle
Il faut disposer d’un processeur compris entre 600Mhz et 1Ghz
2
Une mémoire minimale de 512Mo (Enterprise, Standard, Work Group…)
112Mo pour express.
Il faut aussi disposer d’une espace disk minimale de 1 Go
 Configuration logicielle
Avant l’installation, Il faut avoir :
o OS
o Internet Explorer 6
o IIS 5
o Framework 3.0
o Réseau TCP/IP
 Les instances
Les instances sont l’ensemble des programmes et des données.Elle se définit par :
Nom_ordinateur\Nom_instance ;
SQL Server permet la création de 15 instances.
 Considération de sécurité
Elle est liée aux types de compte :
o Compte utilisateur local
o Compte de données
o Compte de système local
o Moteur de base de données
o Composant (Analysis, broker)
o Outil (Management studio)
3
 Option de licence
Il y a trois types de licences :
o Licences par processeur : Chaque processeur sera taxé. Les clients n’auront pas
besoin de licences
o Licence d’accès client/serveur plus périphérique : taxé par serveur et par
périphérique
o Accès client serveur plus utilisateur : Chaque utilisateur qui se connecte
occasionne une taxation
4
 Paramètre de classement
Ce paramètre permet les opérations de tri.
o Vu d’ensemble du processus d’installation
La première phase consiste à l’installation de mise à jour des composants L.NET,
Framework 2.0 Windows installer 3.1 et du package msi d’installation qui nécessite
Windows installer 3.1
 Les options d’installation des composants
o Moteur de base de données :
Réplication
Recherche de texte intégral
o Analysis Services (OLAP)
o Reportings services
o Intégration Services (Transfert des données)
o Composants station de travail (ensemble d’outils : documentation, base de données
test)
5
 Les opérateurs
o Arithmétiques : +,-,*, /, %
o Logique : AND, NOT, OR, XOR
o Comparaison : >, <, >=, <=, =
 Variables
DECLARE SET
Use Northwind
Declare @Empid int, @Name char (20)
SET @Empid=101
Commentaire
/*
*/
II – Gestion de base de données et des fichiers
6
Une base de données sert à stocker un ensemble de données. Les objets d’une base de
données nous aide à structurer les données d’une base de données et à définir les mécanismes
de sécurité, d’améliorer la performance et le contrôle d’intégrité des données.
Parmi ces objets, nous avons :
o Table : Elles représentent un ensemble de lignes associées à des colonnes
o Contraintes : elles définissent les règles relatives aux autorisées dans les colonnes
et vont constituer les mécanismes standard pour garantir l’intégrité des données.
o Valeurs par défaut : elles définissent les valeurs stockées dans une colonne si
aucune valeur n’est fournie au moment de l’ajout
o Index : Ils constituent des structures de stockage offrant un accès rapide pour
l’extraction des données
o Vue : elles permettent de visualiser les données venant d’une ou de plusieurs tables
d’une même base de données
o Fonctions définies par l’utilisateur : elles vont permettre de renvoyer soit une
valeur scalaire, soit une table
o Procédures stockées : elles constituent un ensemble d’instruction nommées en
TSQL pré compiler devant être exécutées ensemble
o Déclencheur : ils représentent une forme spéciale de procédure stockées et
exécutées automatiquement lorsqu’un utilisateur modifie les données d’une table
ou d’une vue.
 Outils de configuration de la surface d’exploitation
Il permet de démarrer, arrêter, autoriser, activer, désactiver les fonctionnalités, les services et
les connections distantes de votre installation.
On peut utiliser ces fonctionnalités, il y a le CLR (Common Language Runtime), la
messagerie de base de données, SQL Mail et assistant Web qui ont été retiré
Server de base de
données
API
Langage.Net
7
 Gestionnaire de configuration SQL 2005
Il permet de gérer les services associés SQL Server, configurer les protocoles utilisés par SQL
Server et gérer la configuration de connexion à partir des ordinateurs client de Sqls.
 SQL Server Management Studio
SQL Management Server est un outil qui permet d’exécuter la quasi-totalité des taches
d’administration.
 Configuration de la messagerie de base de données
La fonctionnalité de messagerie est utilisée pour expédier des messages de type email à partir
du moteur de base de données.Il est possible d’envoyer des messages pour notifier à
l’administrateur que la mémoire n’est pas suffisant.On a 2 principaux objets dans cette
fonctionnalité.
o Compte de messagerie base de données : contient les informations utilisées par
SQL Server pour envoyer des messages email par SMTP
o Profil de messagerie de la base de données : c’est un ensemble de compte de
messagerie.
Les profils peuvent-etre publique ou privé :
o Publique : lorsqu’il est alloué au groupe (Database Mail User Role).Les profils
publiques sont disponibles pour tous les membres du role contenu dans la base de
données système MSBD.
o Privé : défini pour les responsables de sécurité.
 Création et gestion de base de données
Fichier de la base de donnée
o Fichier de donnée primaire (.mdf) : contient des infos sur les démarrages de la
base de donnée et d’autres informations user
o Fichiers de données secondaires (.ndf) : Ils ne sont pas obligatoire, mais ils
peuvent être créée pour pouvoir stocker les données utilisateur.Ils portent
l’extension .ndf
8
o Fichiers journaux (.ldf) : Ils permettent de récupérer les détails sur toutes les
transactions de la base de données et enregistrer toutes les modifications apportées
à la base de données.
Groupes de fichiers
o Groupe de fichiers primaires : C’est un ensemble de fichiers primaires et au besoin
les fichiers secondaires et autres.
o Groupe de fichiers définis par l’utilisateur : Il est crée dans l’optique de regrouper
les fichiers qui ne font pas parti du groupe de fichier primaire, secondaire ou
autres.
 Création d’une base de données
o Transact_SQL :
Create database _nombd
On
(name=_bambadb,
Filename=’…\Data\_bambadb_dat.mdf’,
Size=
,
Maxsize= ,
Filegrowth= )
Log on
(name=_bambadb_lob,
Filename=’…\Data\_bambadb.ldf’,
Size= ,
Maxsize= ,
Filegrowth= )
9
 Propriétés de la base de données
o Les schémas :Ils représentent un espace de nommage pour les objets de la base de
données.
Nom_base_de_données.nom_schema.nom_objet_bdd
Create schema Myscles
Create table Myscles.salesdat
(SalesId int not null PRIMARY KEY, Sales Amount float not null)
 Gestion des bases de données
La gestion d’une base de données implique des activités comme l’ajout, la suppression de
fichiers et de groupes de fichiers ainsi que la modification de la taille de fichiers et de
groupes.La base de données peut également être supprimée si elle n’est plus utilisée.
10
Les secondes opérations sont relatives au déplacement d’une base de données d’un serveur
vers un autre ou d’une instance à une autre sur le même serveur.En plus des taches de
maintenances.Il arrive parfois que l’administrateur de base de données ait à faire d’autres
taches comme le contrôle de performance d’exécution des requête ou le contrôle de la
quantité d’espace disque libre.Une telle analyse permet d’améliorer les performances de la
base de données.L’administrateur est également responsable pour l’obtention des
informations concernant l’efficacité des index crées.
L’une des taches fondamentales de l’administrateur est de maintenir la taille de la base de
données.Une des méthodes pour y arriver est de réduire la taille de la base de données pour
libérer l’espace non utilisée.
Cet espace libre peut alors être utilisé pour enregistrer d’autres objets de la base de données.Il
faut aussi effectuer des contrôles d’intégrité de la base de données pour vérifier que les
données sont fiables et consistantes.
 Modification d’une base de données
11
Après avoir crée une base de données, il est peut-être nécessaire d’ajouter ou de modifier un
fichier ou un groupe de fichiers ou encore de supprimer après une création de base de
données.Pour effectuer ces opérations,on utilise :
ALTER DATABASE
 Ajout d’un groupe de fichier
Alter database _espdb
Add filegroup EmpFG
Alter database _espdb
Addfile(name=EmpFGfile1,
Filename=’…\Data\EmpFGFile1.ndf’,
Size=10,
Maxsize=100,
Filegrowth=10)
To filegroup.EmpFG
 Suppression
DROP
12
 Migration d’une base de données
o Il y a 15 instances (ensemble de programmes et de données dans une base de
données) dans un serveur de base de données.
Sp_detach_db’_espdb’, true
 Gestion des statistiques
Ils peuvent être créer pour des donnes spécifiques d’une table ou d’une vue
o Create statistics Contact Email : (on person.contact(contactID,EmailAddress)
o Sp_helpstats’Person.contact’,’All’.
o Dbcc show_statistics(‘Person.contact,contactEmail)
13
 Mise à jour de statistiques
On utilise la commande update statistics Person.contact ContactEmail
 Reduction d’une base de données
Il devient essentiel de verifier la taille d’une base de données lorsque le volume de
données s’accroit.L’administrateur de base de données doit controler la taille de la base de
données pour améliorer les performances de la base de données.
Dbcc shrink file
Shrink database
14
 Extension de la base de données
Avec l’accroissement du volume de données dans la base, les fichiers d’enregistrement
peuvent atteindre leur taille maximale, il est necessaire d’augmenter la taille de la base de
données pour admettre plus de données.
 Réalisation
L’instruction dbcc permet en plus de réduire la taille de la base de données, de contrôler
l’intégrité d’une base de données.
La première instruction est : dbcc checkdb
Cet instruction va nous permettre de contrôler la cohérence et l’intégrité de la base de
données.
Dbcc check alloc permet de contrôler la cohérence de l’allocation de l’espace disk d’une base
de données particulière.
Ddbc check table : cette instruction est identique à l’instruction ddbc check db qu’elle
contrôle une seule table ou une vue indexée ou même un index particulier
 Automatisation d’une base de données avec un agent SQL Server
Un administrateur de base de données assure la cohérence, la fiabilité et l’actualisation des
données d’une base de données.Pour ce faire, certaines taches administratives doivent se faire
périodiquement. Dans cette optique, d’automatiser leur exécution à l’aide de SQL Server
Agent.
III - Sécurité de la base de données
SQL Server 2005 fait appel à 3 mécanismes pour la sécurité :
o Authentification
o Autorisation
o Validation
Tois composants interviennent dans la sécurité au sein de SQL Server 2005.Ce sont les entités
de sécurité, les sécurisables et les autorisations
 Les entités de sécurité
Ce sont les entités logiques qui doivent accéder aux ressources de SQL Server
Sur Windows : ce sont les comptes utilisateur locaux, les comptes utilisateur à distances, les
comptes groupe d’utilisateur.
 Niveau SQL Server
On a les identifiants d’accès d’ensemble des utilisateurs enregistrés et authentifiées par SQL
Server
 Entités de sécurité au niveau base de données
15
Nous avons les utilisateurs base de données.Ce sont les comptes d’accès ayant l’autorisation
d’accès base de données.
 Les sécurisables
Ce sont des objets auxquels accèdent les entités de sécurité, et d’une base de données ou
encore un service de base de données.Au niveau des encodeurs, il s’agit de fichiers utilisés
par SQL Server.Ce sont des hiérarchies imbriquées appelées portée.
Une portée serveur : Il inclut une connexion d’accès d’une base de données et des points
de terminaison crées au niveau du serveur.
o La portée base de données : elle inclut des objets tels que utilisateurs, role
d’application, assemblage
o Portée schéma : Elles inclut des objets tels que Tables, vues, fonctions, procédures
et les types contenues dans un schéma.
 Les autorisations
L’accès aux sécurisables par des entités de sécurité peut-être activé ou désactivé en
configurant les autorisations. Celles-ci conditionnent les entités de sécurité qui ont accès aux
sécurisables.
 Création des connexions d’accès et d’utilisateurs
On utilise l’instruction : create login.
 Affectation des rôles
Un rôle est un objet de base de données auquel un ensemble d’autorisation sont octroyés.Les
types de rôle :
 Rôle de base de données fixe
 Rôle de base de données défini par l’utilisateur
 Rôle d’application
 Role de base de données
Elles sont définis au niveau de la base de données et existent dans chaque base de données
Role de la base de données
Db_accessadmin
Db_backupoperator
Db_datareader
Db_datawriter
Db_ddladmin
Autorisations
Sauvegarder les opérations
Ajoute, modifie ou supprime les données
d’une tables quelconque
Ajoute, modifie ou supprime les objets de la
16
Db_denydatareader
Db.datawriter
Db_owner
Db_securityadmin
Public
base de données
Restreint la lecture des données d’une table
quelconque
Restreint la modification des données d’une
table quelconque
Réalise une activité quelconque de base de
données
Il crée des schémas, charge des rôles de base
de données et les rôles d’application
Maintient les autorisations par défaut
 Rôle de base de données
Il est possible de créer ses propres rôles de base de données pour grouper plusieurs utilisateurs
et les assigner un ensemble commun d’autorisations. Ceci permet de configurer un ensemble
commun à assigner à des utilisateurs de base de données.
 La commande : create role
 Rôle d’application
Un role d’application est un ensemble de sécurité qui permet à une application de fonctionner
avec ses propres privilèges.

Create application role
 Assignation de rôle aux utilisateurs base de données
Chaque role se voit assigner un ensemble d’autorisation .Pour assigner une autorisation à
un utilisateur, un role peut-être assigner à un utilisateur ou celui-ci peut être autorisé à
devenir un membre du role.
 Affectation d’autorisation
Après avoir créer les entités de sécurité, les entités doivent être sécurisable
 Octroie d’autorisation au niveau serveur
Create table
Create function
Create view
Create procedure
Delete
Execute
Insert
Select
Update
Alter any user
17
View database state
 Octroie d’autorisation au niveau du serveur
Use Adventureworks
Grant create procedure
To boubacarz
 Révocation d’autorisation
Il est parfois nécessaire de supprimer les autorisations octroyées aux utilisateurs
Revoke alter
On schema ::sales
From boubacarz
En plus restreindre l’accès à la base de données, il est possible d’ajouter une couche de
sécurité supplémentaire.
Si les données sont chiffrées, elles restent sécurisé même si un utilisateur non autorisé accède
à la base de données. Les données et objets de base de données peuvent-etre chiffrer à l’aide
de clé et de certificat.
 Création de clé
Une clé est une valeur qui peut-être appliqué à une première fonction cryptographique pour
déchiffrer une valeur de données sécurisée. On distingue les données symétriques ou
asymétriques
Clé asymétrique
Create symetric Key Symkey
With algorithmTRIPLES_DES
Encryption by password=’mot_de_passe’
 Creation de certificat
Un certificat peut-être utilise dans une base de données SQL Server pour ajouter un objet de
base de données tel qu’une procédure stockée ou une fonction.
Use Adventureworks
Create certificate awCustCert
Encryption by password= ‘Mot_de_passe’
With subject =’Cert for Adventureworks custumers’,
Star_date=’01/04/2008’,
Expiry date=’09/04/2008’
18