V. Création et gestion de base de données

Une base de données permet de stocker un ensemble de données. Les objets de base de données permettent de structurer vos données et à définir les mécanismes de sécurité, d’améliorer et de contrôler l’intégrité des données.

Parmi ces objets nous avons :

• Les tables : ensembles de lignes associées à des colonnes
• Les contraintes : permettent de définir des valeurs relatives autorisées dans les colonnes et vont constituer le mécanisme standard pour garantir l’intégrité des données.
• Les valeurs par défaut définissent une valeur stockée dans une colonne si aucune autre valeur n’est fournie au moment de l’ajout.
• Les index constituent des structures de stockage offrant un accès rapide pour l’extraction des données.
• Les vues permettent de visualiser des données provenant d’une ou de plusieurs tables de la même base de données.
• Les fonctions définies par l’utilisateur vont permettre de renvoyer soit une valeur scalaire soit une table.
• Les procédures stockées sont un ensemble d’instructions nommées en Transact-SQL pré-compilées devant être exécutées ensemble.
• Les délencheurs représentent une forme spéciale de procédure stockée exécutée automatiquement lorsqu’un utilisateur modifie les données d’une table ou d’une vue.

A. Création de base de données

1. Fichiers de la base de données

Une base de données est un espace de stockage. Il y a deux fichiers importants :

a) Fichiers de données primaires (.mdf)

Ils contiennent des informations sur le démarrage de la base de données (objets de base de données et les données intégrées dans la base de données).

b) Fichiers de données secondaires (.ndf)

Ils ne sont pas obligatoires mais peuvent intervenir pour pouvoir stocker les données utilisateur.

c) Fichiers journaux (.ldf)

Ils servent à récupérer les détails sur toutes les transactions effectuées sur les base et fournissent les modifications apportées à votre base de données.

2. Groupes de fichiers

C’est un ensemble de fichiers. Il y a 2 groupes :

a) Groupes de fichiers primaires

Ils contiennent des fichiers de données primaires. Le groupe de fichiers secondaires contient des données secondaires .

b) Groupes de fichiers définis par l’utilisateur

Ce groupe de fichiers est créé dans l’optique de rassembler ou pour regrouper des fichiers qui ne font pas partie du groupe de fichiers primaires ou secondaires.

Remarque : Lors de la création d’une base de données, il y a un fichiers de données primaires et un journal de transaction qui sont créés par défaut.

3. Création d’une base de données

La création d’une base de données peut se faire via l’interface graphique de Management Studio ou directement par Transact-SQL.

a) Management Studio

Lancer Management Studio

Faire un clic droit sur Base de données – Nouvelle base de données

Renseigner le champ nom : _espdb

Cliquer sur OK

b) Transact-SQL

Cliquer sur Nouvelle Requête

Taper le code suivant :

Create database _espdb

On

(name=_espdb_dat,

Filename= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\_espdb_dat.mdf',

Size=10,

Maxsize=50,

Filegrowth=5)

Log on

(name=_espdb_log,

Filename= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\_espdb_log.ldf',

Size=5MB,

Maxsize=25MB,

Filegrowth=5MB)

Cliquer sur l’icône Analyser pour vérifier la syntaxe

Si aucune erreur n’est signalée cliquer sur Exécuter

Faire un clic droit sur la base de données _espdb – Propriétés

4. Les schémas

Le schéma représente un espace de nommage pour les objets de la base de données. Toutes les bases de données créées ont un shéma, le schéma par défaut s’appelle dbo.

Pour accéder à un objet de la base de données il faut mettre nomschéma.nomobjetBaseDeDonnées ou nomBaseDeDonnées.nomschéma.nomObjetBaseDeDonnées

Pour créer un schéma il faut faire un clic droit sur _espdb -- Nouvelle Requête et saisir ce qui suit :

create schema MySales

create table MySales.Salesdat

(SalesId int not null Primary Key,

SalesAmount float not null)

B. Gestion de base de données

La gestion d’une base de données inclut des activités telles que l’ajout, la suppression de fichiers et de groupes de fichiers ainsi que la modification de la taille de fichiers et de groupe de fichiers. La base de données peut être supprimée si elle n’est plus utilisée.

Les secondes opérations de gestion sont relatives au déplacement d’un serveur de base de données à une autre ou d’une instance SQL SERVER vers une autre instance sur le même serveur.

En plus des tâches de maintenance régulière il arrive parfois qu’un administrateur de base de données doit accomplir d’autres tâches telles que le contrôle de la performance d’exécution des requêtes ou le contrôle de la quantité d’espace de disque libre.

Une telle analyse permet d’améliorer les performances de la base de données. L’administrateur de base de données est également responsable pour l’obtention des infos sur l’efficacité des index créés.

L’une des tâches fondamentales est de pouvoir maintenir la taille de la base de données. Une des méthodes utilisées pour y parvenir est de libérer l’espace non utilisé. Cette espace libre peut alors être utilisée 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 assurer que les données sont fiables et consistantes.

1. Modification d’une base de données

Il est nécessaire d’ajouter ou de modifier un fichier ou un groupe de fichiers ou encore supprimer un fichier de la base de données. Pour effectuer ces opérations on utilise l’instruction Alter database.

Ajout d’un groupe de fichiers :

Alter database _espdb

add filegroup EmpFG

Ajouter un ficher au groupe de fichers créé :

Alter database _espdb

add file (

name=EmpFGFile1,

filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\EmpFGFile1.ndf',

size=10,

maxsize=100,

filegrowth=10)

Ajout d' un fichier à EmpFG

Alter database _espdb

add file (

name=EmpFGFile2,

filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\EmpFGFile2.ndf',

size=10,

maxsize=100,

filegrowth=10)

to filegroup EmpFG

Remarque : Il ne peut y avoir qu’un seul fichier de données primaires .mdf par base de données.

Actualiser la base de données et faire un clic droit dessus – Propriétés – Fichiers

2. Suppression de base de données

Taper la commande : drop database nomBaseDeDonnées

3. Migration d’une base de données

a) Création d’une seconde instance

Lancer le setup.exe

Sélectionner SQL Server Database Services

Cliquer sur Suivant

Sélectionner Instance nommée

Saisir ESP22

Cliquer sur Suivant

Sélectionner le compte système par défaut

Cliquer sur Suivant

Sélectionner le mode mixte

Saisir « esp » comme mot de passe

Cliquer sur Suivant

Cliquer sur Suivant

Cliquer sur Installer

Cliquer sur Suivant

Cliquer sur Terminer

Démarrer Management Studio

Cliquer sur Connexion – Parcourir – Moteur de base de données

Choisir la nouvelle instance

Cliquer sur OK

b) Migration d’une base de données

Nous allons détacher la base de données _espdb sur l’instance ESP2 et le faire migrer sur la nouvelle instance ESP22. Pour ce faire :

Faire un clic droit sur _espdb – Propriétés – Options

Aller sur Restreindre l’accès et choisir l’option Single (Ceci permet de bloquer les accès à la base de données)

Cliquer sur OK -- Oui

Faire un clic droit sur _espdb – Tâches – Détacher

Activer mettre à jour

Décocher conserver les catalogues

Cliquer sur OK

Aller sur la nouvelle instance

Faire un clic droit sur Base de données – Joindre—Ajouter

Aller dans MSSQL – Data

Sélectionner _espdb_dat.mdf

Cliquer sur OK

Pour réaliser cette tâche avec Transact-SQL il faut faire :

Sp_detach_db ‘_espdb’ , true

4. Gestion des statistiques

a) Création de statistiques

Les statistiques peuvent être créés pour des colonnes spécifiques d’une table ou d’une vue. Pour créer une statistique il faut taper la commande create statistics.

REMARQUE : A partir de Management Studio aller dans Aide puis Index et taper create statistics. Vous verrez toutes les fonctionnalités de cette commande.

Création de groupe de statistiques dans la table AdventureWorks

Cliquer sur Nouvelle Requête

Taper le code qui suit :

create statistics ContactEmail

on Person.Contact(ContactID,EmailAddress)

Renvoyer l’ensemble des groupes de statistiques de la table Person.Contact

Taper Sp_helpstats'Person.Contact','All'

Voir le contenu du groupe de statistiques

Taper dbcc show_statistics('Person.Contact','ContactEmail')

b) Mise à jour des statistiques

On utilise la commande

update statistics Person.Contact ContactEmail

5. Réduction d’une base de données

Il devient essentiel de vérifier la taille de la taille de la base de données lorsque le volume de données s’accroît. L’administrateur de base de données doit contrôler la taille de la base de données pour améliorer les performances.

Pour réduire la taille d’un fichier il faut taper la commande

dbcc shrinkfile

Pour réduire la taille d’une base de données il faut taper la commande

dbcc shrinkdatabase

EXEMPLE : faire un clic droit sur AdventureWorks – Tâches – Réduire – Fichier

Cocher l’option Réorganiser les pages avant de libérer l’espace inutilisé

Mettre 1Mo

Cliquer sur OK

6. Expansion de la base de données

Avec l’accroissement du volume des données dans la base de données, les fichiers d’enregistrement peuvent atteindre leur taille maximale, il est donc nécessaire d’augmenter la taille de la base de données pour admettre plus de données.

Faire un clic droit sur AdventureWorks – Propriétés -- Fichiers

7. Réalisation de vérification d’intégrité de la base de données

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.

dbcc checkdb permet de contrôler la cohérence et l’intégrité de la base de données.

dbcc checkalloc permet de contrôler la cohérence de l’allocation de l’espace disque d’une base de données particulière

dbcc table est identique à dbcc checkdb à la différence qu’elle contrôle une seule vue indexée ou un index particulier.

8. Automatisation de la base de données avec un agent SQL Server

Un administrateur de base de données est responsable d’assurer la cohérence, la fiabilité et l’actualisation des données d’une base de données. Pour ce faire certaines tâches administratives doivent être effectuées périodiquement. Dans cette optique il est possible d’automatiser leur exécution grâce à SQL Server Agent.

Etude de cas : En tant qu’administrateur de base de données vous devez souvent démarrer ou arrêter le service de texte intégral sur votre serveur de base de données. Pour ce faire, vous avez décidé de créer un travail avec SQL Server Agent. Quand le travail est exécuté, vous devez envoyer une notification à l’administrateur système de votre entreprise à l’aide du service de messagerie de windows. L’adresse IP de la machine de l’administrateur système est 10.10.10.142.

Pour résoudre ce problème, des tâches suivantes devront être effectuées

a) Création d’un opérateur

La création d’un travail pour expédier une notification à l’administrateur quand le service de texte intégral démarre nécessite la création d’un opérateur.

Pour ce faire il faut :

Aller dans l’explorateur d’objets de Management Studio

Faire un clic droit sur Agent SQL Server

Cliquer sur Démarrer puis sur OUI

Dérouler Agent SQL Server

Faire un clic droit sur Opérateurs

Cliquer sur Nouvel opérateur

Renseigner les informations suivantes :

Nom : kiki

Adresse : Net Send

NB : Il faut que le service Windows Messenger soit configuré préalablement.

b) Création d’un travail

Faire un clic droit sur Travaux – Nouveau Travail

Renseigner les informations suivantes :

Nom : Démarrer service de recherche de texte intégral

Propriétaire : DEV1\sgedd

Description : Travail de service de recherche de texte intégral

Cliquer sur Etapes – Nouveau

Renseigner les informations suivantes :

Nom de l’étape: Démarrer service de recherche de texte intégral

Type : Système d’exploitation

Commande : Net Start msftesql$ESP2

Cliquer sur OK puis Notifications

Choisir l’opérateur kiki

Choisir l’option « Lorsque le travail est terminé »

Faire un clic droit sur Démarrer service de recherche de texte intégral

Cliquer sur Afficher l’historique -- Fermer

Etude de cas : En tant qu’administrateur, vous voulez accomplir une activité impliquant l’insertion en vrac dans la base de données AdventureWorks. Quand vous l’accomplissez elle échoue par manque d’espace dans la base de données tempdb.Vous souhaitez donc une alerte dans la base de données tmpdb. Cette alerte doit être lancée dès que la base de données tempdb n’a que 10Mo d’espace libre. L’alerte doit délivrer le message « Augumenter la taille de la base de données tempdb pour résoudre le problème ».

c) Création d’une alerte

Faire un clic droit sur Agent SQL Server

Cliquer sur Alerte – Nouvelle alerte

Nom : tempdbalerte

Type : Alerte de condition de performance SQL Server

Objet : MSSQL$ESP2 : Databases

Compteur : Data File(s) Size(KB)

Instance : tempdb

Alerte : devient égal à

Valeur : 100 000

Aller sur l’onglet Réponses

Cocher « Notifier les opérateurs »

Cocher Net Send

Aller sur l’onglet Options

Cocher Net Send

Noter « Augumenter la taille de la base de données tempdb »

Cliquer sur OK