Implémentation SQL server TS informatique/ Base de données
Introduction
SQL Server est le système de gestion de données relationnelle de Microsoft. Ce moteur est
disponible sous différentes formes (éditions) avec différents contrats de licence mais
également plus ou moins de fonctionnalités. Parmi ces éditions, ce livre va porter sur
l’édition Express de SQL Server 2008.
Cette édition de SQL Server est bien connue des développeurs car elle est intégrée à Visual
Studio depuis de nombreuses années. En effet, le terme "Express" n’est apparu qu’avec la
Version 2005 de SQL Server, auparavant ce produit s’intitulait MSDE
(Microsoft SQL Server Desktop Engines).
Les avantages de SQL Server Express sont nombreux mais le fait qu’il soit possible de
l’installer librement constitue un avantage décisif. SQL Server Express représente donc une
très bonne solution à adopter par des applications de taille modérée, qu’elles soient
professionnelles ou non. En effet, SQL Server Express, en plus d’être gratuit, est composé du
moteur SQL Server.
Adopter SQL Server Express dans le cadre d’une application offre la certitude d’héberger les
données de façon optimum. De plus, il est possible de migrer vers une autre édition de SQL
Server et ceci de façon transparente pour les applications travaillant avec les données.
Bien entendu, cette gratuité de produit s’accompagne de quelques limites :
La taille de chaque base est limitée à 4 Go maximum.
Le serveur SQL Server Express ne sait pas exploiter plus de 1 Go de mémoire vive
même si le serveur dispose d’une quantité supérieure.
Toutefois, SQL Server 2008 Express est un moteur de base de données pleinement
fonctionnel et qui bénéficie des derniers apports de SQL Server 2008 comme le Power Shell,
les types de données spatiaux, les nouveaux types de
données de gestion des dates et heures…
SQL Server Management Studio reste l’outil principal de travail, que ce soit pour
l’administrateur ou bien pour le
développeur d’applications. Il est possible d’administrer de façon graphique, toutes les tâches
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
peuvent également être
réalisées en utilisant des scripts Transact SQL. Chaque solution possède ses avantages et ses
inconvénients. C’est
pourquoi les deux solutions sont exposées de façon quasi-systématique dans ce livre. Pour les
syntaxes Transact-SQL,
seules les options les plus courantes seront précisées. L’objectif n’est pas de refaire la
documentation mais de
présenter au mieux les différentes instructions.
Le composant SQL Server Management Studio n’est pas intégré en tant que tel à l’édition
Express de SQL Server 2008.
Pour que ce produit soit installé, il est nécessaire d’utiliser l’édition Express Advanced qui
intègre la console
d’administration SQL Server Management Studio. C’est d’ailleurs le seul écart entre ces
deux éditions.
SQL Server utilise sa propre structure de base de données pour stocker toutes les
informations relatives à sa propre
gestion. Ces informations sont conservées dans les tables dites système. Toutefois, comme la
structure de ces tables
est amenée à être modifiée lors d’un changement de version, il est recommandé de ne pas
interroger directement ces
tables mais d’utiliser les vues disponibles dans le schéma Sys ou bien
INFORMATION_SCHEMA. L’utilisation de ces vues
dans des requêtes d’extraction permettra de lire les informations conservées dans
le dictionnaire des données.
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
Présentation de SQL Server
Le but de ce chapitre est d’acquérir un aperçu de SQL Server dans son ensemble, à savoir :
● comprendre la notion de SGBDR et le mode de fonctionnement client/serveur
● présenter les composants de SQL Server et les plates-formes d’exécution,
● présenter l’architecture d’administration et de programmation,
● présenter la notion de base de données et les bases installées sur le serveur SQL.
SQL Server est un SGBDR (Système de Gestion de Base de données Relationnelle)
Entièrement intégré à Windows, ce qui autorise de nombreuses simplifications
au niveau de l’administration, tout en offrant un maximum de possibilités.
Mode de fonctionnement Client/Serveur
Toutes les applications qui utilisent SQL Server pour gérer les données, s’appuient sur une
Architecture client/serveur. L’application cliente est chargée de la mise en place de
l’interface utilisateur. Cette application s’exécute généralement sur plusieurs postes clients
simultanément.
Le serveur, quant à lui, est chargé de la gestion des données, et répartit les ressources du
serveur entre les différentes demandes (requêtes) des clients. Les règles de gestion
de l’entreprise se répartissent entre le client et le serveur.
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
On peut distinguer trois cas :
Les règles sont entièrement implémentées sur le client, appelé alors client lourd.
Cette solution permet de libérer des ressources au niveau du serveur, mais les problèmes
de mise à jour des clients et de développement d’autres applications se posent.
Les règles sont entièrement définies sur le serveur, le client est alors un client léger.
Cette solution permet d’obtenir des clients qui possèdent peu de ressources matérielles, et
autorise une centralisation des règles ce qui rend plus souples les mises à jour. Cependant de
nombreuses ressources sont consommées sur le serveur et l’interaction avec l’utilisateur
risque d’être faible, puisque l’ensemble des contraintes est vérifié lorsque l’utilisateur soumet
sa demande (requête) au serveur.
Les règles d’entreprises sont définies sur une tierce machine, appelée Middle Ware,
afin de soulager les ressources du client et du serveur, tout en conservant la centralisation des
règles.
L’architecture client/serveur permet un déploiement optimum des applications clientes sur de
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
nombreux postes tout en conservant une gestion centralisée des données (le serveur), ce qui
rend possible le partage d’informations à l’intérieur de l’entreprise.
Il est bien sûr possible d’avoir plusieurs applications clientes sur le même serveur de base de
données. Cette possibilité offre de nombreuses fonctionnalités, mais il faut toutefois veiller à
ce que la charge de travail sur le serveur ne soit pas trop importante au regard des capacités
de la machine. Cette architecture client/serveur est respectée par tous les outils permettant
d’accéder à des informations contenues par le serveur SQL, donc les outils d’administration,
même s’ils sont installés sur le serveur. Toutes les demandes en provenance des clients vers
le serveur, doivent être écrites en Transact-SQL. Ce langage de requête de base de données
respecte la norme ANSI SQL92.
Le SQL fournit un ensemble de commandes pour gérer les objets et manipuler les données
dans les bases. Le Transact SQL est enrichi de nombreuses fonctionnalités, non normalisées,
afin d’étendre les possibilités du serveur. Il est ainsi possible de définir des procédures
stockées sur le serveur.
Les platesformes possibles
Il est important de distinguer deux cas : d’un côté les plateformes possibles pour le client et
de l’autre les plateformes pour le serveur.
Les plateformes clientes présentées ici sont les postes sur lesquels les outils d’administration
SQL Server peuvent être installés. Il ne s’agit pas des postes qui hébergent une application
qui se connecte à une instance SQL Server pour gérer les données.
D’une façon synthétique, les outils clients d’administrations peuvent être installés sur tous
systèmes d’exploitation Windows 2003, Windows XP Pro ou tout système plus récent.
Par contre, pour la partie serveur, les disponibilités en termes de plateformes sont fonctions
de l’édition SQL Server choisie. Néanmoins pour héberger une instance de base de données
en production, il est nécessaire de disposer d’un serveur performant et fiable. Une plateforme
Windows 2003 ou 2008 est donc recommandée. L’édition de Windows 2003 ou 2008 sera
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
choisie en fonction des contraintes imposées par l’édition SQL Server sélectionnée et des
contraintes liées à l’environnement technique. L’installation d’une instance sous Windows
XP, Vista, 7 sera réservée à des postes nomades.
Dans le cas où le client héberge une application spécifique, la gamme des plateformes est
considérablement élargie grâce, en particulier, au pilote JDBC qui permet d’accéder à une
instance SQL Server depuis une application écrite en java. La gamme est encore élargie dans
les cas d’une application ASPX qui propose une interface Internet. Un simple navigateur
Internet permet alors de lancer l’application.
Les composants de SQL Server
Le moteur de base de données de SQL Server ou Database Engine est composé de plusieurs
logiciels. Certains s’exécutent sous forme de services alors que d’autres possèdent une
interface utilisateur graphique ou en ligne de commande.
Composants Serveur
SQL Server s’exécute sous forme de services Windows. Suivant les options d’installation
choisies, il peut y avoir plus de services. Les principaux services sont :
SQL Server : c’est le serveur de base de données à proprement parlé. Si ce service
n’est pas démarré, il n’est pas possible d’accéder aux informations. C’est par
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
L’intermédiaire de ce service que SQL Server assure la gestion des requêtes
utilisateurs.
Ce service est référencé sous le nom MSSQLSERVER pour l’instance par défaut et
MSSQLSERVER $nomInstance dans le cas d’une instance nommée.
Microsoft Full Text Search : ce service propose de gérer l’indexation des documents
de type texte stockés dans SQL Server et gère également les recherches par rapport
aux mots clés. Cette fonctionnalité n’est disponible que dans l’édition SQL Server
2008 Express Advanced.
Il est possible d’installer plusieurs instances de SQL Server sur le même poste.
Connectivité Client
L’installation des composants de connectivité sur les postes clients permet de prendre en
charge la gestion du réseau, la bibliothèque SQL Native pour les programmes en accès natif,
le support OLEDB et ODBC.
Outils de gestion
Les réalisations des tâches d’administration sont possibles par l’utilisation d’outils. Ces outils
possèdent pour la plupart une interface graphique conviviale et d’utilisation intuitive.
Cependant, les tâches administratives doivent être réfléchies avant leur réalisation.
L’utilisation de certains outils suppose que le composant serveur correspondant est installé.
Ces outils sont :
SQL Server Management Studio pour réaliser toutes les opérations au niveau du serveur
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
de base de données. Seule l’édition Express Advanced permet d’installer une version
Express de SQL Server Management Studio. Toutefois, lorsque SQL Server Express est
utilisé dans le cadre de la réalisation d’une application, toutes les opérations sont bien
souvent faites directement au travers de Visual Studio.
SQL Server Configuration Manager pour gérer les services liés à SQL Server.
Tous les outils et le fonctionnement de SQL Server sont richement documentés dans la
documentation en ligne.
Les composants
Les différentes briques logicielles fournies par SQL Server s’articulent toujours autour du
moteur de base de données relationnelles qui traite de façon performante les informations
stockées au format relationnel et au format XML.
SQL Server Intégration Service (SSIS) : est un outil d’importation et d’exportation
de données facile à mettre en place tout en étant fortement paramétrable. SQL Server
Express ne bénéficie pas de l’ensemble des fonctionnalités de SSIS, il n’est en effet
pas possible de définir des lots complexes. Seules les définitions d’opérations simples
d’importation ou exportation de données sont possibles. Il est par contre tout à fait
possible d’exécuter un lot défini à l’aide d’une autre édition de SQL Server.
Reporting Services : permet de mettre en place des rapports d’analyse des données.
La réplication des données sur différentes instances permet de positionner les données
au plus près des utilisateurs et de réduire les temps de traitement.
L’intégration du CLR dans SQL Server permet de développer procédures et fonctions
en utilisant les langages VB.Net et C#. L’intégration du CLR ne vient pas se substituer
au Transact SQL mais se présente comme un complément afin de pouvoir réaliser un
codage simple et performant pour l’ensemble des fonctionnalités qui doivent être
présentes sur le serveur.
Base de données SQL Server
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
1. Objets de base de données
Les bases de données contiennent un certain nombre d’objets logiques. Il est possible de
regrouper ces objets en trois grandes catégories :
● Gestion et stockage des données : tables, type de données, contraintes d’intégrité,
valeur par défaut, règles et index.
● Accès aux données : vues et procédures stockées.
● Gestion de l’intégrité complexe : déclencheur (procédure stockée s’exécutant
automatiquement lors de l’exécution d’un ordre SQL modifiant le contenu d’une
table : INSERT, UPDATE et DELETE). Le déclencheur est toujours associé à
une table et à une instruction SQL. Il permet de mettre en place des règles
d’intégrité complexes à cheval sur plusieurs tables ou de maintenir des données
non normalisées.
Objet de base de données
Nom complet des objets
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
La règle appliquée pour nommer les objets permet une parfaite identification. Le nom
complet est composé comme suit : serveur.nomBase.propriétaire.objet. Par défaut,
seul le nom de l’objet est précisé. Cette notion sera détaillée au cours du chapitre
Gestion de la base de données.
2. Bases de données système et tables système
Pour gérer l’ensemble des données stockées, SQL Server s’utilise luimême. Il existe donc des bases de données
système et sur chaque base utilisateur, quelques tables système. L’insertion et la mise à jour de données dans
ces tables ne s’effectuent jamais directement, mais via des commandes Transact SQL ou des procédures
stockées.
Organigramme des bases de données
Les noms des bases de données et des tables système sont fixés et connus par SQL Server. Il ne faut donc
pas renommer une table ou une base système.
Master
C’est la base de données principale de SQL Server. L’ensemble des données
stratégiques pour le bon fonctionnement du serveur y est stocké (comptes de connexion,
options de configuration, l’existence des bases de données utilisateurs et les références
vers les fichiers qui composent ces bases...).
Model
Cette base contient l’ensemble des éléments inscrits dans toute nouvelle base utilisateur.
Par défaut, il n’y a que les tables système, mais il est possible de rajouter des éléments.
Tempdb
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
La base Tempdb est un espace temporaire de stockage partagé. Il permet de gérer les
tables temporaires locales ou globales, les tables de travail intermédiaires pour faire des
tris par exemple, mais aussi les jeux de résultats des curseurs. La base Tempdb est
recréée, avec sa taille initiale, lors de chaque démarrage de l’instance. Ainsi, aucune
information ne peut être conservée de façon persistante à l’intérieur de la base Tempdb.
Les objets temporaires sont, quant à eux, supprimés lors de la déconnexion de leur
propriétaire.
Bases de données utilisateur
Les bases de données utilisateurs vont héberger les données fournies par les utilisateurs.
Les bases présentes sur le schéma précédent (AdventureWorks et Gescom) sont les
bases d’exemples utilisées dans la documentation officielle de SQL Server et dans cet
ouvrage.
3. Les tables système
Les tables système sont toujours présentes dans SQL Server. Cependant, il est
recommandé de ne pas travailler directement avec ces tables. Pour rechercher
l’information, il faut passer par le schéma d’information et plus exactement les vues
définies sous le schéma de l’utilisateur sys lorsque cela est possible.
Dans le tableau cidessous, quelques tables système sont référencées.
Catalogue système (présent uniquement dans la base Master)
Table système Fonction
syslogins Une ligne pour chaque utilisateur ou groupe Windows
autorisé à se connecter au serveur SQL.
sysmessages Une ligne pour chaque message d’erreur défini et pour
chaque langue.
sysdatabases Une ligne par base de données utilisateur.
sysconfigures Une ligne pour chaque option de configuration du serveur.
sysusers Une ligne pour chaque utilisateur défini dans la base.
syscolumns Une ligne pour chaque colonne des tables, vues et pour
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
chaque paramètre des procédures stockées.
sysobjects Une ligne pour chaque objet de la base de données.
Les tables système sont utilisées directement par le moteur de SQL Server. Les
applications qui utilisent SQL Server ne doivent en aucun cas accéder directement à ces
tables, même en lecture. En effet, comme la structure de ces tables évolue avec les
versions de SQL Server, si certaines applications accèdent de façon directe aux tables
système, on peut se trouver dans l’impossibilité de migrer vers une nouvelle version de
SQL Server tant que l’application n’a pas été réécrite.
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
Déroulement de l’installation :
Configuration de Windows avant l’installation de SQL Server 2005 :
a) Installer Windows installer : installer la version 3.1 à partir de « Windows update »
Latli Hossam Eddine @C.F.P.A
Implémentation SQL server TS informatique/ Base de données
S’il n’est pas encore installé.
b) Installer le service internet (IIS) : si ce service n’est pas encore installé il faut
L’installer a partir de : « ajouter/supprimer des programmes – composants Windows »
Ou « programmes et fonctionnalités – activer ou désactiver des fonctionnalités
Windows » pour Windows 7 et plus
c) Installer .NET Framework : une installation de logiciel DotNet Framework est
nécessaire. la version 2.0, 3.0 ou 4.0
Latli Hossam Eddine @C.F.P.A
