Administration
SQL Server 2012
Connaissances préalables
Pour comprendre et maitriser les techniques et concepts abordés dans ce cours il faut avoir une base en informatique et certaines connaissances énumérées ci-dessous :
§ Connaissances de base du système d’exploitation Windows
§ Expérience pratique de Transact-SQL
§ Expérience pratique des bases de données relationnelles
§ Expérience en matière de conception de bases de données
§ Connaissance et expérience pratique du langage VB,NET
Objectif :
§ Installer et configurer SQL Server 2012;
§ gérer des fichiers de base de données;
§ sauvegarder et restaurer des bases de données;
§ gérer la sécurité;
§ analyser et comprendre SQL Server 2012;
§ transférer les données dans et à partir de SQL Server 2012;
§ automatiser des tâches d’administration;
§ comprendre et optimiser l’architecture de SQL Server; § répliquer des données entre instances SQL Server 2012.
§ lecture d’une base de données SQL server 2012 en
Chapitre I:Présentation de SQL Server 2012
L’objectif du chapitre est :
§ Comprendre le fonctionnement de SQL Server ;
§ Présenter les différents composants de SQL Server ;
§ Présenter l’architecture globale de SQL Server
§ Comprendre l’architecture d’une base de données
Les types de serveur
OLDP (OnLine Transactional Processing)
Serveur transactionnel : Un serveur sous lequel un certain nombre de transactions de type LMD (Langage de Manipulation de données) vont s’exécutées. Un serveur qui va supporter beaucoup de petites transactions qui manipuleront un petit volume de données.
OLAP (OnLine Analytical Processing)
Serveur décisionnel ou Data Warehouse : c’est un entrepôt de données. Un serveur qui servira en stocker l’ensemble des données consolidées. Les données qui proviennent des différentes sources (BD Oracle, SQL Server, MySQL …). Un serveur OLAP va donc manipuler de gros volumes de données en utilisant de grosses requêtes générant une charge importante.
Ainsi donc avant toute installation il faut connaitre le rôle du serveur pour faire le bon choix du type de serveur à installer.
Le moteur de données est composé de plusieurs logiciels.
§ La plus part s’exécutent sous forme de services;
§ Certains possèdent des interfaces graphiques
§ D’autres sont accessibles via une ligne de commande Les principaux composants sont :
§ SQL Server ó Instance SQL :
C’est le serveur de base de données (le moteur). C’est le principal composant Il est impossible d’avoir une instance SQL server sans que le moteur de base de données ne soit installé.
ü S’exécute en tant que service Windows ;
ü Elle est référencée sous le nom MSSQLSERVER pour une instance par défaut (instance dans laquelle on choisit le nom par défaut).
ü Elle sera MSSQLSERVER$nomInstance dans le cas d’une instance nommée (une instance pour laquelle on choisit de donner un nom différent du nom par défaut).
Il faut savoir qu’il est possible d’installer plusieurs instances sur un même serveur. Cependant il impossible d’avoir sur un même serveur deux instances de même nom.
§ SQL Agent :
En général chaque instance à son propre moteur de base de données et possède son propre SQL Agent.
ü Il gère l’exécution de tâches planifiées, la surveillance de SQL Server et le suivi des alertes
ü Il s’exécute en tant que service Windows
ü Il est directement lié à une instance SQL Server
ü Il est référencé dans le gestionnaire de service sous le nom SQL Server Agent(MSSQLSERVER) pour une instance par défaut et SQL Server Agent(nomInstance) dans le cas d’une instance nommée.
§ Microsoft Full Text Search :
C’est un composant utilisé pour effectuer des recherches de documents de type texte.
ü Gère l’indexation des documents de type texte
ü Gère les recherches par rapport aux mots
§ SQL Server Intégration Services (SSIS) :
ü Outil d’importation et d’exportation de données
ü Intègre des assistants pour créer un ETL (extraction, transformation et chargement)
Termes à connaitre
§ Les conteneurs qui fournissent la structure des packages
§ SQL Server Analysis Services (SSAS) :
ü Outil d’analyse OLAP et Data mining de Microsoft
ü Permet de construire des cubes OLAP
ü Idéal pour des projets décisionnels
§ SQL Server Reporting Services (SSRS) :
ü Créer des rapports pour différents sources de données
ü Créer des rapports de types interactifs, tabulaires, graphiques ou libres à partir de source de données XML relationnelles et multidimensionnelles
§ La replication des données :
ü Permet de positionner les données au plus près des utilisateurs et de réduire les temps de traitement. (Avoir un serveur central et des serveurs secondaires)
§ Service Broker :
ü Permet un travail en mode asynchrone pour faciliter la gestion des pics de forte activité en stockant les demandes de travail avant de les traiter
§ CLR :
ü L’intégration du CLR dans SQL Server permet de développer des procédures et fonctions en utilisant les langages et C#
L’architecture de SQL Server :
Une installation de SQL Serveur contient au minimum une instance par défaut.
§ Instance par défaut
Elle est identifiée par le nom réseau de l’ordinateur sur lequel elle s’exécute.
§ Instance nommée
Elle est identifiée par le nom réseau de l’ordinateur sur lequel elle s’exécute suivi d’un nom d’instance.
Les outils de SQL Server :
§ Les clients de SQL Server
Les composants qui permettent d’attaquer un serveur de base de données SQL Server.
§ SQL Server Management Studio
Permet de réaliser toutes les opérations au niveau du serveur de base de données.
§ Gestionnaire de configuration SQL Server
Pour gérer les services liés à SQL Server.
§ SQL Server Profiler
Pour suivre et analyser la charge de travail d’une instance SQL Server.
§ Assistant paramétrage du moteur de base de données
Permet une optimisation du fonctionnement du serveur de base de données.
§ sqlcmd
C’est un outil qu’un DBA doit impérativement connaitre.
ü Outil de ligne de commande
ü Exécuter des requêtes approuvées
ü Exécuter des scripts de commandes
ü Etablir une connexion d’administration dédiée (DAC)
1.4 - L’architecture d’une base de donnéesBase de données SQL Server :
Une base de données permet de stocker des objets logiques.
ü Les données : tables, indexes, types de données, contraintes d’intégrité, valeurs par défaut, règles.
ü Accès aux données : vues, procédures et fonctions
ü Gestion de l’intégrité complexe : déclencheur
Chaque base de données système est
§ Master
C’est la base mètre elle est le dictionnaire des données. Elle contient une référence de l’ensemble des objets crées.
§ MSDB
Elle va stocker l’ensemble des tâches planifiées.
§ Model
Elle va permettre de définir le model de création d’une base de données. Chaque base de données créée va s’appuyer sur la structure de la base model. La base de données créée est une copie de la base model. Une modification apportée à la base Model va impacter l’ensemble des bases.
§ Tempdb
Elle va servir pour toutes les opérations de type temporaire.
§ Ressource
Elle permet de stocker les évolutions apportées aux différentes versions de SQL Server 2012 pour faciliter les migrations.
Les tables systèmes :
Les bases de données masters contiennent des tables systèmes
ü Utilisées directement par le moteur de SQL Serveur.
ü Déconseillé d’accéder directement à ces tables
Quelques tables systèmes :
ü sys.server_principals: Liste des connexions définies sur le serveur
ü sys.sysmessages: Une ligne pour chaque message ou avertissement
ü sys.sysdatabases: Une ligne pour chaque base de données
ü sys.sysusers: Une ligne pour chaque utilisateur défini au niveau de la base de données courante
Les procédures stockées système :
ü Interroger les tables système.
ü Connaître l’état du serveur, de la base …
ü Effectuer des opérations de configuration
Quelques procédures stockées systèmes:
ü sp_helpsp_help[nom_objet]: Information sur l’objet indiqué.
ü sp_helpdbsp_helpdb[nom_base_données]: Information sur la base de données indiquée.
ü sp_whosp_who: Liste des utilisateurs actuellement connectés Le catalogue:
ü Contient des vues systèmes
ü Toutes ces vues sont présente dans un schéma sys.
ü Regroupées par thèmes
Objets, types et index
Serveurs liées
CLR
Service Broker
Les fonctions systèmes:
ü Utilisable avec des commandes Transact SQL
Quelques procédures stockées systèmes:
ü DB_ID: Retrouve l’identification de la base de données.
ü USER_NAMEUSER_NAME : Retrouve le nom de l’utilisateur à partir de son identifiant.
Schéma d’information:
ü Ensemble de vues
ü Ne fait pas directement référence aux tables système
L’objectif du chapitre :
§ Comprendre les étapes du processus d’installation de SQL Server 2012
§ Préparer l’installation avec l’outil de configuration système
§ Réalisation d’une installation manuelle et d’une installation automatisée
§ Vérifier et valider une instance
Les étapes du processus d’installation de SQL Server 2012
Analyse de la configuration système
TP pratique d’une installation manuelle de SQL Server 2012.
Etape 1 : Lancement du programme d’installation
Etape 2 :Centre d’installation de SQL Server 2012
Le Menu de gauche du centre d’installation nous donne les différentes actions que nous pouvons effectuées.
L’installation :
Le Menu Installation nous présente quatre outils. Nous allons lancer l’outil :
« Nouvelle installation autonome de SQL Server ou ajout de fonctionnalités à une installation existante ».
Après une autre vérification des composants nécessaires pour l’installation, le programme d’installation va installer les fichiers d’installation.
Après l’ajout de la clé d’installation et l’acceptation du contrat de contrat de licence nous avons une étape importante qui est le « Rôle de l’installation ». Nous avons 3 ou 2 options. Nous choisissons celle qui nous permet de choisir et installer les fonctionnalités de SQL Server nécessaires pour gérer notre serveur.
Ensuite nous devons sélectionner les fonctionnalités à installer.
Choix du mode d’authentification
Fin de l’installation
Mise en œuvre.
ü Création d’un fichier de configuration .ini
ü Démarrage de l’installation automatisée
/CONFIGURATIONFILE=chemin d’accès du
C’est l’outil principal de gestion et d’administration. Il utilisé par les administrateurs et les développeurs.
ü Un requêteur avancé
ü Permet une gestion graphique des instances et des objets
ü Permet une gestion centralisée des instances distantes
ü Intègre la complétion
C’est un outil incontournable dans l’administration de SQL Server. Un outil de gestion et d’administration en mode ligne de commande.
ü Permet l’exécution de requêtes et des scripts
ü Etablir une connexion d’administration dédiée (DAC)
Il est question ici de voir les différents moyens de configuration de SQL Server 2012. Les éléments de configurations sont lesservices (Les Moteurs, …les composants sélectionnés lors de l’installation).
Les états
ü Démarré
ü Suspendu
ü Arrêté
Via
ü Gestionnaire de configuration SQL Server
ü Gestionnaire de service Windows
Utilisation de l’outil SSMS (SQL Server Management Studio)
ü Inscrire un serveur
ü Gérer la configuration d’un serveur (Mot de passe de l’administrateur, Gestion des ressources)
Une base de données contient deux types de fichiers :
§ Fichiers de données
ü Fichier primaire de données (obligatoire, extension .mdf) (Contient le catalogue de la base de données)
ü Fichier secondaire de données (facultatif, extension .ndf) (Contient les objets et les données utilisateurs)
§ Fichiers journaux (au moins 1, extension .ldf)
ü Enregistre toute les modifications de la base de données
Fonctionnement des fichiers journaux :
Les fichiers de données :
§ Rattaché à une seule base de données
§ Structuré en pages de 8k
ü Les pages contiennent les enregistrements
ü Unité d’échange entre le cache de données et les fichiers de données ü La taille maximale d’une ligne est de 8060 octets (hors type texte et image)
ü Chaque page contient un type bien précis de données
Deux méthodes :
§ En transact-SQL
§ SQL Server Management Studio (SSMS)
Les informations nécessaires :
§ Le nom de la base de données, la collation
§ La taille
§ L’espace de stockage
En Transact SQL :
Cas pratique :
§ Nom : gecos
§ Deux fichiers de données
ü (taille = 10MB, maxsize=100B et autoextend OFF)
ü (taille = 10MB, maxsize=100B et autoextend 10MB)
ü Stockage dans C:\MSSQL\DATA
§ Un fichier journal
ü (taille = 15MB, maxsize=200B et autoextend 10%) ü Stockage dans C:\MSSQL\LOGS
§ Collation : French, Case Sensitive, Accent Insensitive
Un groupe de fichiers est une :
§ Structure logique
ü Permet de regrouper des fichiers de données et de les gérer comme des unités logiques. (Unité logique auquel vont s’attacher des fichiers physiques de type .mdf ou .ldf)
Nous avons :
§ Deux types de groupe de fichier
ü Un type primaire. (crée par défaut par le système)
ü Un autre type défini par l’utilisateur
Utilisation :
ü Répartition des données par type (tables, indexes)
ü Répartition des données pour la charge
Création et Ajout de fichiers au groupe :
ü Syntaxe de création de groupe de fichiers
ALTER DATABASE <nom_base_données>
ADD FILEGROUP <nom_tablespace>;
ü Syntaxe d’ajout de fichiers au groupe
ALTER DATABASE <nom_base_données>
ADD FILE <spécification du fichier>
TO FILEGROUP <nom_tablespace>;
§ Objectif
ü Diviser des tables volumineuses en plusieurs tables
ü Optimiser le stockage (Stockage sur différents groupes de fichiers)
ü Montée en charge
Un exemple
§ Implémentation
ü Définir une fonction de partitionnement
ü Définir un schéma de partitionnement
ü Créer les tables partitionnées
Possibilité de créer un index sur une table partitionnée (l’index crée est partitionné selon la table)
§ Fonction de partition
Définir la clé de partitionnement (champ de répartition pertinent)
• Permet de répartir les données entre les différentes partitions
• Permet d’orienter les données sur un groupe de fichier ou un autre
• Utilise des plages de valeurs bornées
Syntaxe
CREATE PARTITION FUNCTION <nomfonction> ( parametre_type)
AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ valeurLimite [ , ] ]
Parametre: colonne de tous types sauf timestamp, varchar(max), nvarchar(max) et varbinary utilisé pour calculer la clé de partitionnement.
valeurLimite: valeur marquant la frontière de chaque partition.
§ Schéma de partition
• Permet d’affecter chaque partition à un groupe de fichiers
• Possibilité de spécifier plus de groupe de fichiers que de partitions définis
• Possibilité d’affecter toutes les partitions à un seul groupe de fichiers (non recommandé)
Syntaxe
CREATE PARTITION SCHEME nomSchemaPartition
AS PARTITION nomFonctionPartition
[ ALL ] TO ( { groupeDeFichier | [ PRIMARY ] } [,_])
[ ; ]
nomSchemaPartition: identifiant du schéma de partitionnement. nomFonctionPartition: nom de la fonction de partitionnement associée au schéma. groupeDeFichier: nom du ou des groupes de fichiers utilisés par les différentes partitions.
§ Création d’une table de partitionnée
Syntaxe
CREATE TABLE nomTable(
definitionColonne [, ]
) ON nomSchemaPartition(colonneUtiliséePourCalculerLaPartition)[;]
§ Création d’un index partitionné
Syntaxe
CREATE INDEX nomIndex ON nom Table(colonne1, …)
ON nomSchemaPartition(colonneDepartition);
§ Les bases de données par défaut
• master base de données système
• model base de données modèle pour les nouvelles bases de données
• msdb base de stockage des alertes et travaux pour l’agent SQL Server
• tempdb base de stockage des objets et données temporaires Cette base est recrée à chaque redémarre du SGBD
§ Les options de configuration
• AUTO_SHRINK {ON|OFF} => IsAutoShrink
• READ_ONLY, READ_WRITE => Updateability
• SINGLE_USER, RESTRICTED_ USER, MULTI_USER => UserAccess
• AUTO_CREATE_ STATISTICS { ON | OFF } => IsAutoCreateStatistics
• AUTO_UPDATE_ STATISTICS { ON | OFF} => IsAutoUpdateStatistics
• AUTO_CLOSE => IsAutoClose
• RECOVERY => recovery
• AUTRE => status, collation
§ Afficcher la valeur des options
• Fonction DATABASEPROPERTYEX
Syntaxe
SELECT DATABASEPROPERTYEX(‘NomBase',‘NomOption');
• Utilisation de sys.databases
SELECT name,collation_name,user_access_desc,state_desc FROM sys.databases;
• Les autres procédures
sp_helpdb, sp_spaceused;
§ Modification des options de configuration
En TRANSACT-SQL
- Syntaxe
ALTER DATABASE <nomBaseDeDonnees> SET option [;]
§ Pourquoi sauvegarder
• Panne matérielle (support)
• Mauvaise manipulation des utilisateurs
• Panne serveur
• Déplacement de base de données
§ Caractéristiques
• Sauvegarde à chaud
• sauvegarde cohérente des données
• Aucune opération de création ou de modification de base de données n’est possible pendant une opération de sauvegarde • Impossible de créer des indexes pendant la sauvegarde
• Exécution d’opérations non journalisées et non autorisées
§ Les principales méthodes de sauvegardes
• Sauvegarde complète
• Sauvegarde différentielle
• Sauvegarde du journal des transactions
• Sauvegarde des groupes de fichiers
• Partielle
§ Sauvegarde complète de base de données
• Sauvegarde toutes les données de la base de données
• Sauvegarde les modifications validées pendant la sauvegarde
• Point de départ pour toute stratégie de sauvegarde
§ Sauvegarde différentielle
• Sauvegarde les parties de la base de données modifiées depuis la sauvegarde complète
• Sauvegarde toutes les transactions intervenues pendant la sauvegarde différentielle
§ Sauvegarde du journal des transactions
• Pré-requis : sauvegarde complète indispensable • Nécessite le mode de récupération COMPLET
§ Sauvegarde partielle
• Contient toutes les données du groupe PRIMAIRE
• Contient chaque groupe de fichiers en LECTURE/ECRITURE
• Ne contient pas les groupes de fichiers en LECTURE seule
§ Destination des sauvegardes
ü Disque
• Les unités physiques = nom complet du fichier physique au niveau de l’OS
• Les unités logiques: s’appuient sur des fichiers physiques
ü Bande
ü
§ Les privilèges nécessaires pour réaliser une sauvegarde
ü Rôles de serveur
• sysadmin, db_owner, db_backupoperateur
§ L’instruction BACKUP
ü Syntaxe
BACKUP DATABASE <nom base> TO <unite logique> |
DISK= <chemin_nom> WITH INIT |
NOINIT | FORMAT | CHECKSUM | COMPRESSION
§ Sauvegarde avec mise en mirroir
ü Syntaxe
BACKUP DATABASE <nom base> TO <unite logique> |
DISK= <chemin_nom>MIRROR TO
<unite logique> | DISK= <chemin_nom>
§ Vérifier l’intégrité d’une sauvegarde
ü Syntaxe
RESTORE VERIFYONLY FROM <unite logique> | DISK=<chemin_nom>
§ Caractéristiques
• Sauvegarde toutes les extensions modifiées depuis la dernière sauvegarde Complète
• Permet de réduire le nombre de sauvegarde du journal des transactions
• Utilisable quel que soit le mode de récupération utilisé
§ Commande BACKUP
ü Syntaxe
BACKUP DATABASE <nom base> TO DISK = <nom_fichier> WITH DIFFERENTIAL
Utilisable quel que soit le mode de récupération utilisé
§ Caractéristiques
• Utilisable uniquement avec le mode de récupération COMPLET et JOURNALISE EN BLOC
• Autorisée uniquement après une sauvegarde complète
• Débute au numéro LSN (Log Sequence Number) oû a pris fin la précédente sauvegarde de journal
• Sauvegarde toutes les transactions jusqu’à la transaction actuelle ouverte
complète
• Toutes les transactions sauvegardés peuventt alors être supprimés du journal des transactions
§ Commande BACKUP
ü Syntaxe
BACKUP LOG <nom_base> TO DISK = <nom_fichier> WITH INIT
§ Caractéristiques
• Stratégie de sauvegarde alternative aux sauvegardes complètes
• Permet de sauvegarder des groupes de fichiers
• Point de départ: sauvegarde initiale de tous les groupes de fichiers de la base de données
• Mode de récupération: COMPLET ou JOURNALISE EN BLOC
§ Commande BACKUP
ü Syntaxe
BACKUP DATABASE <nom_base> FILEGROUPE =<nom_groupe_fichier> TO DISK = <nom_fichier>
BACKUP DATABASE <nom_base> FILEGROUPE =<nom_groupe_fichier> TO DISK = <nom_fichier> WITH DIFFERENTIAL
§ Caractéristiques
• Utilisable avec tous les modes de récupération
• Permet d’exclure des groupes de fichiers en lecture • Identique à une sauvegarde complète mais ne contient pas tous les
groupes de fichiers
• La sauvegarde partielle d'une base de données en lecture seule contient uniquement le groupe de fichiers primaire
• Pour une base de données accessible en lecture/écriture, une sauvegarde partielle contient toutes les données du groupe de fichiers primaire, de chaque groupe de fichiers en lecture/écriture
§ Commande BACKUP
ü Syntaxe
BACKUP DATABASE <nom_base> READ_WRITE_FILEGROUPS
TODISK = <fichier_nom>
§ La vérification des sauvegardes
RESTORE HEADERONLY
RESTORE FILELISTONLY
RESTORE LABELONLY
RESTORE VERIFYONLY
§ Avant de restaurer
Aucune connexion utilisateur ne doit être en cours
Interdire toute nouvelle connexion au serveur pendant la restauration Mode mono-utilisateur
§ Les différents types de restauration
• Restauration d’une sauvegarde complète
• Restauration d’une sauvegarde différentielle
• Restauration d’une sauvegarde de journal de transactions
• Restauration partielle
• Restauration d’une page corrompue
§ Les options de la commande RESTORE
• RECOVERY
• NORECOVERY
• FILE
• MOVE TO
• REPLACE
• STOPAT (PITR: point in time recovery)
• STOPATMARK (LSN), STOPBEFOREMARK (LSN)
§ Un snapshot c’est quoi ?
• Une copie en lecture seule des bases de données
• Dépourvue de journal des transactions
• Création d’un fichier épars
• Initialement aucunes données
• Possible de sauvegarder, restaurer ou détacher une capture instantanée de base de données
• Le snapshot doit se trouver dans la même instance que la base de données
source
§ Création d’une capture instantanée
CREATE DATABASE <nom_capture>
ON (NAME =<nom_fichier_logique>,
FILENAME = <nom_fichier_OS>
) [ , ]
AS SNAPSHOT OF <nom_base_source>
§ Les outils de transfert
• SSIS (SQL Server Intégration Service)
Extraction, transformation, chargement
• BCP
Outil en ligne de commande
Permet d’importer et d’exporter entre un fichier et SQL Server
• BULK INSERT
• Réplication
• SELCT INTO et INSERT
§ Terminologie
• Les packages (unités de travail à exécuter)
• Les tâches (se chargent du travail dans les packages)
§ Flux de données
§ Utilisation
• Assistance
• Concepteurs SSIS
• Utilitaires de ligne de commande dtexec
§ Caractéristiques
• Outil en ligne de commande
• Permet d’exporter les données d’une table ou d’une requête SQL
§ Outil BCP
• syntaxe bcp {nom objet | requete} { in | out | queryout | format } <fichier_de_donnees> -S -T
§ Caractéristiques
• Importer un fichier de données dans une table ou vue de base de données dans un format spécifié par l’utilisateur
§ Commande BULK INSERT
• syntaxe
[ database_name . [ schema_name ] . | schema_name .] [ table_name | view_name ] FROM 'data_file'
[ WITH
(
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
§ Introduction
• Les entités de sécurité : compte de sécurité qui dispose d’un accès au serveur de données SQL.
• Les sécurisables : objets gérés par le serveur (serveur, base, schema)
• Les autorisations : sont accordées aux entités de sécurité afin de pouvoir travailler avec les sécurisables.
§ Architecture de la sécurité d’accès
§ Les modes d’authentification
• Mode d’authentification Windows
Utilisateurs authentifiés par Windows
L’accès des utilisateurs se fait via une connexion mappée à leur compte
Windows
Mode d’authentification par défaut
• Mode d’authentification mixte (SQL Server et Windows)
Les utilisateurs connectés via une connexion Windows déclarés sous SQL Server sont validés
Les utilisateurs connectés via un compte non Windows déclarés sous SQL Server sont validés
§ Les entités de sécurités
• Windows
Group Windows, Compte d’utilisateur de domaine, Compte d’utilisateur local
• SQL Server
Connexion SQL Server, Rôle de serveur
• Base de données
Utilisateur, Rôle de base de données, Rôle d’application
• Configuration des identifiants SQL Server
Identifiant = accès des utilisateurs à SQL Server
• Création d’un identifiant (authentification Windows)
Syntaxe
CREATE LOGIN [<domaine>\<nom_connexion>] FROM WINDOWS
[WITH DEFAULT_DATABASE=<base_de_données> |
DEFAULT_LANGUAGE=<langue>
• Création d’un identifiant (authentification SQL Server)
Syntaxe
CREATE LOGIN <nom_connexion> WITH PASSWORD=<mot_de_passe>
[MUST_CHANGED] |, DEFAULT_DATABASE=<base_de_donnes> |,
DEFAULT_LANGUAGE=<langue> | , CHECK_EXPIRATION={ ON | OFF} | CHECK_POLICY={ ON | OFF} | , [CREDENTIAL=<nom_credit>
• Les vues systèmes
sys.server_principals: Entités de sécurité définis au niveau serveur sys.sql_logins: Liste des connexions au niveau serveur
• Modification
Syntaxe
ALTER LOGIN <nom_utilisateur> WITH <option>
• Désactivation
Syntaxe
ALTER LOGIN <nom_utilisateur> DISABLE
• Suppression
Syntaxe
DROP LOGIN <nom_utilisateur>
DROP LOGIN <domaine/nom_utilisateur>
§ Permettent à des connexions en mode sécurité SQL Server d’accéder à une ressources externe au serveur de base de données
§ Un credential = un compte Windows
§ Un compte SQL Server est rattaché à un credential
§ Création d’un credential
Syntaxe
CREATE CREDENTIAL <nom_du_credit>
WITH IDENTITY = <‘identité’> [, SECRET = <‘password’>];
§ Caractéristiques
• Crée au niveau base de données
• Rattaché à un login de connexion au niveau serveur
- Sauf pour les utilisateurs guest, sys et INFORMATION_SHEMA
• Les droit sur les objets (base, schema, objets) sont des données aux utilisateurs de base de données
§ Les utilisateurs particuliers
• Utilisateur dbo
- Présent dans toutes les bases données par défaut
- Les membres du rôle sysadmin et du compte de connexion sa sont mappés à dbo
- Ne peut être supprimé
- Tous les objets créés par un membre de sysadmin appartiennent automatiquement à dbo
• Utilisateur guest
- Présent dans toutes les bases données par défaut
- Autorise les connexions sans compte utilisateur à accéder à la base
- Activation du compte guest
GRANT CONNECT TO guest;
- Désactivation du compte guest
REVOKE CONNECT FROM guest;
§ Création d’un utilisateur d’une base données
Syntaxe
CREATE USER <utilisateur> FOR LOGIN <login> WITH
DEFAULT_SCHEMA=<schema>
§ Modification d’un utilisateur de base données
Syntaxe
ALTER USER <utilisateur> WITH NAME=<new_nom>, DEFAULT_SCHEMA=<schema>
§ Suppression d’un utilisateur de base données
Syntaxe
DROP USER <utilisateur>
§ Les vues systèmes
Sys.database_principals
§ Savoir qui est connecté
Proceduresp_who
§ Caractéristiques
• Apparue dans MSSQL en 2005
• Objet logique (enveloppe)
• Permet un regroupement logique des objets
• Permet de gérer d’une manière plus optimale les droits sur les objets
• Associé à un utilisateur
• Schéma par défaut dbo
• Pour accéder à des objets en dehors de son schéma, faut faire précéder le nom de l’objet par le nom du schéma.
§ Création
Syntaxe
CREATE SCHEMA <nom_schema> AUTHORIZATION <utilisateur>
§ Modification
Syntaxe
ALTER SCHEMA <nom_schema> TRANSFER <objet>;
§ Suppression (doit être vide)
Syntaxe
DROP SCHEMA <nom_schema>;
§ Caractéristiques
• Plusieurs niveaux d’attribution des privilèges
- Au niveau serveur
- Au niveau base
- Au niveau schéma
- Au niveau objets
• Deux types de droits au niveau base
- Droits d’utilisation d’instructions
- Droits sur les objets
§ Gestion des privilèges
- GRANT pour l’attribution des privilèges - REVOKE pour retirer des privilèges
- DENY pour interdire l’utilisation d’un privilège
§ Les privilèges d’utilisation des instructions
- CREATE DATABASE pour créer une base de données
- CREATE PROCEDURE pour créer une procédure stockée
- CREATE TABLE pour créer une table
- BACKUP DATABASE pour réaliser une sauvegarde
- CREATE DEFAULT
- CREATE RULE pour créer un rôle
- CREATE VIEW pour créer une vue
- BACKUP LOG pour réaliser une sauvegarde du journal des transactions
§ La commande GRANT
GRANT <nom_privilege> [, ] TO <utilisateur> > [, ] [ WITH
GRANT OPTION ]
§ La commande REVOKE
REVOKE [ GRANT OPTION FOR] <nom_privilege> [, ]FROM
<utilisateur> [, ] [CASCADE]
§ La commande DENY
DENY<nom_privilege> [, ] TO <utilisateur> [, ] [CASCADE]
§ Les privilèges sur les objets
• Tables
• Procédures
EXECUTE
§ La commande GRANT
GRANT { ALL | <nom_privilege>[ (colonne [, ] ] [, ]) } ON
<nom_objet> TO <utilisateur> [, ] [ WITH GRANT OPTION ]
§ La commande REVOKE
REVOKE [ GRANT OPTION FOR ] { ALL | <nom_privilege>[ (
colonne [, ] ] [, ] ) } ON <nom_objet> [ ( colonne [, ] ] [, ] ) FROM <utilisateur> [, ] [ CASCADE ]
§ La commande DENY
DENY { ALL | <nom_privilege>[ ( colonne [, ] ] [, ] ) } ON
<nom_objet> [ ( colonne [, ] ] [, ] ) TO <utilisateur> [, ] [ CASCADE ]
§ Caractéristiques
• Permet d’automatiser certaines tâches.
• Chaque agent est rattaché à une seule instance
• Log les erreurs SQL Server dans l’observateur d’évenement Windows
• Stock les informations dans la base de données msdb
• Terminologie Tâches planifiée
Les alertes
Les opérateurs
§ Caractéristiques
• Utilise le protocole SMTP pour envoyer des mails
• Possède son propre processus de fonctionnement
• Non actif par défaut
§ Caractéristiques
• Correspond à une personne physique ou à un groupe dans l’entreprise
• Permet à l’Agent SQL Server d’envoyer des messages d’alerte ou de fin
de travaux
- Messagerie
- Net send
- Radiomessagerie
• Les informations sur les opérateurs sont stockées dans la base de données
msdb
§ Caractéristiques
Permettent
• D’avertir un opérateur lorsqu’un évènement survient • De réaliser un traitement pour résoudre un problème § Quand déclencher une alerte
Erreurs SQL Server
• En fonction de la gravité
Créer ses propres messages d’erreur
• sp_altermessage pour supprimer un message
• sp_dropmessage pour supprimer un message
§ Création d’une alerte
• Via SQL Server Management Studio
• Via Transact SQL
sp_add_alert pour créer une alerte sp_update_alert pour mettre à jour une alerte sp_delete_alert pour supprimer une alerte
§ Caractéristiques
Constitué d’une ou plusieurs étapes (tâches)
Deux états possibles pour une tâche
- Echec
- Succès
Enchainement possible entre les étapes
Plusieurs types d’étapes
- Transact SQL, Commande système, Package, …
Stockés dans la tablesysjobs de la base de donnéesmsdb
Chapitre IX: Lecture d’une base de données SQL Server en
Cas pratique
