Cours 1 : Gestion des Bases de donnes SQL Server

Cours 1 : Gestion des Bases de donnes SQL Server

I. Bases de donnes SQL Server1. Objets de base de donnesLes bases de donnes contiennent un certain nombre dobjets logiques. Il est possible de regrouper ces objets en trois grandes catgories : Gestion et stockage des donnes : tables, type de donnes, contraintes dintgrit, valeur par dfaut, rgles et index. Accs aux donnes : vues et procdures stockes. Gestion de lintgrit complexe : dclencheur (procdure stocke sexcutant automatiquement lors de lexcution dun ordre SQL modifiant le contenu dune table : INSERT, UPDATE et DELETE). Le dclencheur est toujours associ une table et une instruction SQL. Il permet de mettre en place des rgles dintgrit complexes cheval sur plusieurs tables ou de maintenir des donnes non normalises.

Nom complet des objetsLa rgle applique pour nommer les objets permet une parfaite identification. Le nom complet est compos comme suit : serveur.nomBase.propritaire.objet. Par dfaut, seul le nom de lobjet est prcis. Cette notion sera dtaille au cours du chapitre Gestion de la base de donnes.

2. Bases de donnes systme et tables systmePour grer lensemble des donnes stockes, SQL Server sutilise lui mme. Il existe donc des bases de donnes systme et sur chaque base utilisateur, quelques tables systme. Linsertion et la mise jour de donnes dans ces tables ne seffectuent jamais directement, mais via des commandes Transact SQL ou des procdures stockes.

MasterCest la base de donnes principale de SQL Server. Lensemble des donnes stratgiques pour le bon fonctionnement du serveur y est stock (comptes de connexion, options de configuration, lexistence des bases de donnes utilisateurs et les rfrences vers les fichiers qui composent ces bases...).

ModelCette base contient lensemble des lments inscrits dans toute nouvelle base utilisateur. Par dfaut, il ny a que les tables systme, mais il est possible de rajouter des lments.

TempdbLa base Tempdb est un espace temporaire de stockage partag. Il permet de grer les tables temporaires locales ou globales, les tables de travail intermdiaires pour faire des tris par exemple, mais aussi les jeux de rsultats des curseurs. La base Tempdb est recre, avec sa taille initiale, lors de chaque dmarrage de linstance. Ainsi, aucune information ne peut tre conserve de faon persistante lintrieur de la base Tempdb. Les objets temporaires sont, quant eux, supprims lors de la dconnexion de leur propritaire.

Bases de donnes utilisateurLes bases de donnes utilisateurs vont hberger les donnes fournies par les utilisateurs. Les bases prsentes sur le schma prcdent (AdventureWorks et Gescom) sont les bases dexemples utilises dans la documentation officielle de SQL Server et dans cet ouvrage.

3. Les tables systmeLes tables systme sont toujours prsentes dans SQL Server. Cependant, il est recommand de ne pas travailler directement avec ces tables. Pour rechercher linformation, il faut passer par le schma dinformation et plus exactement les vues dfinies sous le schma de lutilisateur sys lorsque cela est possible.Dans le tableau ci-dessous, quelques tables systme sont rfrences.

Les tables systme sont utilises directement par le moteur de SQL Server. Les applications qui utilisent SQL Server ne doivent en aucun cas accder directement ces tables, mme en lecture. En effet, comme la structure de ces tables volue avec les versions de SQL Server, si certaines applications accdent de faon directe aux tables systme, on peut se trouver dans limpossibilit de migrer vers une nouvelle version de SQL Server tant que lapplication na pas t rcrite.

4. Liens entre base de donnes et organisation physiqueLors de la cration dune base de donnes, il est ncessaire de prciser au moins deux fichiers. Le premier servira stocker les donnes, le deuxime sera utilis par le journal afin de stocker les images avant et aprs modification des donnes. Ces deux fichiers sont obligatoires et sont propres chaque base. Dans SQL Server, il nest pas possible de partager un fichier de donnes ou le journal entre plusieurs bases.

5. Les fichiers journauxa. Le rleLes fichiers journaux permettent de stocker les images avant et aprs modification des donnes contenues dans la base. Seules les oprations du DML (Langage de Manipulation de Donnes), soit les ordres SQL INSERT, UPDATE et DELETE, provoquent une journalisation des oprations quelles effectuent sur les donnes de la base. Les oprations de grande envergure, comme la cration dun index, sont mentionnes dans le journal. Le journal sera utilis principalement lors des oprations de restauration automatique suite un arrt brutal du serveur, ou bien lors des oprations de sauvegardes lorsque ces dernires sappuient sur le journal. Il sera galement utilis lorsque la base participe la rplication.

b. Le fonctionnement

Lorsquun ordre SQL est transmis au serveur SQL, ce dernier va chercher lexcuter le plus rapidement possible. Aprs analyse de lordre et mise en place du plan dexcution, si les donnes concernes par lordre ne sont pas dj prsentes en mmoire, alors le moteur SQL va lire les fichiers sur le disque dur afin dy trouver les informations ncessaires. Une fois prsentes en mmoire, les modifications peuvent tre apportes aux donnes. La modification est toujours enregistre dans le journal avant dtre rellement effectue sur les donnes de la base. Un tel journal est appel journal criture anticipe.

Les fichiers journaux sont situs, par dfaut, dans le rpertoire C:\Program Files\Microsoft SQLServer\MSSQL10.SQLEXPRESS\MSSQL\Data, et portent lextension *.ldf. Il sagit dune extension recommande qui nest nullement obligatoire. Le journal peut tre constitu dun ou plusieurs fichiers, la taille de ces derniers pouvant tre fixe ou variable automatiquement ou de faon manuelle.

6. Les fichiers de donnes

a. Leur rleChaque base de donnes possde au moins un fichier de donnes. Ce fichier va contenir lensemble des donnes stockes dans la base. Chaque fichier de donnes ne peut contenir que des donnes en provenance dune seule base, il y a donc spcialisation des fichiers par rapport la base de donnes.

b. La structure des fichiers de donnesLes fichiers de donnes sont structurs pour rpondre de manire optimum toutes les sollicitations de la part du moteur et surtout pour tre capables de stocker plus de donnes en optimisant lespace disque utilis. Pour optimiser lespace dont il dispose, le serveur va formater les fichiers de donnes de faon matriser leur structure. Le travail ralis par SQL Server sur ces fichiers de donnes est semblable au travail fait par le systme dexploitation sur les disques disponibles sur la machine. Il est tout fait admis que le systme dexploitation formate lespace disque dont il dispose afin de le diviser en blocs. Par la suite ce sont ces blocs qui vont tre accords aux fichiers. SQL Server ralise le mme type de travail sur les fichiers de donnes, puis accorde lespace disponible aux diffrentes tables et index.

II. Crer, grer et supprimer une base de donnesUne base de donnes gre un ensemble de tables systme et des tables utilisateurs. Les informations contenues dans ces tables systme reprsentent, entre autres, la dfinition des vues, des index, des procdures, des fonctions, des utilisateurs et des privilges. Les tables utilisateurs vont contenir les informations saisies par les utilisateurs.

1. Crer une base de donnesLa cration dune base de donnes est une tape ponctuelle, ralise par un administrateur SQL Server. Avant de tenter de crer une base de donnes, il est important de dfinir un certain nombre dlments de faon prcise : le nom de la base de donnes qui doit tre unique sur le serveur SQL, la taille de la base de donnes, les fichiers utiliss pour le stockage des donnes.

Pour crer une nouvelle base de donnes, SQL Server sappuie sur la base Model. Cette base Model contient tous les lments qui vont tre dfinis dans les bases utilisateurs. Par dfaut, cette base Model contient les tables systme. Il est cependant tout fait possible dajouter des lments dans cette base. Toutes les bases utilisateurs cres par la suite disposeront de ces lments supplmentaires.Une base peut tre cre de deux faons diffrentes : par lintermdiaire de linstruction Transact SQL CREATE DATABASE par lintermdiaire de SQL Server Management Studio.

Une base de donnes est toujours compose au minimum dun fichier de donnes principal (extension mdf) et dun fichier journal (extension ldf). Des fichiers de donnes secondaires (ndf) peuvent tre dfinis lors de la cration de la base ou bien ultrieurement.

Cette opration de cration de base de donnes affecte la base Master. Une sauvegarde de cette base systme savre donc ncessaire pour tre en mesure de travailler avec la nouvelle base suite une restauration.

Les informations concernant les fichiers de donnes sont enregistres dans la base Master ainsi que dans le fichier primaire de la base de donnes.

a. La syntaxe Transact SQL

CREATE DATABASE nomBaseDeDonnes[ ON[PRIMARY] [ [,n]][LOG ON < spcificationFichier > [,n]]]

Avec pour spcificationFichier les lments de syntaxe suivants :

(NAME = nomLogique,FILENAME = cheminEtNomFichier[,SIZE = taille [KB|MB|GB|TB]][,MAXSIZE={tailleMaximum[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH = pasIncrement [KB|MB|GB|TB|%]])

PRIMARYPermet de prciser le premier groupe de fichiers de la base. Ce groupe est obligatoire, car lensemble des tables systme est obligatoirement cr dans le groupe primary. Si le mot cl PRIMARY est omis, le premier fichier de donnes prcis dans la commande CREATE DATABASE correspond obligatoirement au fichier primaire, il porte normalement lextension mdf.

NAMECet attribut, obligatoire, permet de prciser le nom logique du fichier. Ce nom sera utilis pour faire des manipulations sur le fichier via des commandes Transact SQL on pense notamment aux commandes DBCC ou la gestion de la taille des fichiers.

FILENAMESpcification du nom et emplacement physique du fichier sur le disque dur. Le fichier de donnes est toujours cr sur un disque local du serveur.

SIZEAttribut optionnel qui permet de prciser la taille du fichier de donnes. La taille par dfaut est de 1 Mo et la taille minimale dun fichier est 512 Ko aussi bien pour le journal que pour les fichiers de donnes. La taille peut tre prcise en kilooctets (Kb) ou en mgaoctets (Mb, par dfaut). La taille du premier fichier de donnes doit tre au moins gale celle de la base Model.

MAXSIZECet attribut optionnel, permet de prciser la taille maximale en Mgaoctets (par dfaut) ou en kilooctets que peut prendre le fichier. Si rien nest prcis, le fichier grossira jusqu saturation du disque dur.

FILEGROWTHIl sagit de prciser le facteur dextension du fichier. La taille de chacune des extensions peut correspondre un pourcentage (%), une taille en Mgaoctets (par dfaut) ou en Kilo octets. Si on prcise la valeur zro, le facteur dextension automatique du fichier est nul et donc la taille du fichier ne change pas automatiquement. Si le critre FILEGROWTH est omis, la valeur par dfaut est de 1 Mo pour les fichiers de donnes et de 10 % pour les fichiers journaux. La taille des extensions est toujours arrondie au multiple de 64 Ko (8 blocs) le plus proche.

b. Utilisation de SQL Server Management StudioIl est galement possible de crer une base de donnes de faon graphique depuis SQL Server Management Studio. Il faut alors slectionner Nouvelle base de donnes depuis le menu contextuel associ au noeud Bases de donnes depuis lexplorateur dobjets, comme illustr cidessous.

SQL Server Management Studio prsente alors la bote de dialogue des proprits de la base en mode cration. Aprs avoir complt les options comme le nom de la base et le nom et lemplacement des fichiers, il est possible de demander la cration de la base.

2. Modifier une base de donnesLors de la gestion dune base de donnes, plusieurs critres sont prendre en compte. Dans cette section, la gestion de lespace utilis par les fichiers physiques qui constituent la base de donnes est aborde. Les points principaux qui concernent la gestion des fichiers sont : laccroissement dynamique ou manuel des fichiers, lajout de nouveaux fichiers, la rduction de la taille des fichiers.

Modifier un fichier en Transact SQLCest linstruction ALTER DATABASE qui permet deffectuer toutes les oprations relatives aux manipulations des fichiers de base de donnes, aussi bien pour les fichiers de donnes que les fichiers du journal de transaction. Toutes les caractristiques des fichiers peuvent tre modifies, mais les modifications apportes doivent suivre certaines rgles comme, par exemple, la nouvelle taille du fichier qui doit tre suprieure la taille initiale.

ALTER DATABASE nomBaseDeDonnesMODIFY FILE (spcificationFichier)[;]

Avec pour spcificationFichier les lments de syntaxe suivants :

(NAME = nomLogique,NEWNAME = nouveauNomlogique,FILENAME = cheminEtNomFichier[,SIZE = taille [KB|MB|GB|TB]][,MAXSIZE={tailleMaximum[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH = pasIncrement [KB|MB|GB|TB|%]])

Ajouter un fichier en Transact SQLCest la commande ALTER DATABASE associe loption ADD FILE qui va permettre de rajouter un fichier de donnes ou un fichier journal.

ALTER DATABASE nomBaseDeDonnesADD FILE spcificationFichier[;]

3. Supprimer une base de donnesLa suppression dune base de donnes utilisateur est une opration ponctuelle qui peut tre ralise, comme la plupart des oprations dadministration soit par SQL Server Management Studio, soit en Transact SQL. La suppression de la base de donnes a pour consquence de supprimer tous les fichiers qui correspondent cette base ainsi que toutes les donnes contenues dans cette base. Cette opration est irrversible et en cas de mauvaise manipulation, il est ncessaire de remonter une sauvegarde.

Les limitesIl nest bien sr pas toujours possible de supprimer une base, les principales limites sont : lorsquelle est en cours de restauration, lorsquelle est ouverte par un utilisateur, en lecture ou en criture, lorsquelle participe une publication dans le cadre de la rplication. Il nest pas possible de supprimer les bases de donnes systme.

Avec Transact SQLCest par lintermdiaire de la commande DROP DATABASE que seront supprimes les bases de donnes.

DROP DATABASE [,, ]

Avec SQL Server Management StudioPar un simple clic droit sur la base de donnes, vous avez accs au menu Supprimer dans le menu

AM1