Support Cours SQL Server 2012

7/25/2019 Support Cours SQL Server 2012

1/43

Support du cours de SQL Server 2012

BOMIEN K. Narcisse Ingnieur informatique,Dveloppeur, Intgrateur de service informatique et DBA (08958820 / 04502337)

Page 1

Administration

SQL Server 2012


2/43



Page 2

Connaissances pralables

Pour comprendre et maitriser les techniques et concepts abords dans ce cours il faut avoir

une base en informatique et certaines connaissances numres ci-dessous :

Connaissances de base du systme dexploitation Windows Exprience pratique de Transact-SQL

Exprience pratique des bases de donnes relationnelles

Exprience en matire de conception de bases de donnes

Connaissance et exprience pratique du langage VB,NET


3/43



Page 3

Objectif :

Installer et configurer SQL Server 2012;

grer des fichiers de base de donnes;

sauvegarder et restaurer des bases de donnes;

grer la scurit; analyser et comprendre SQL Server 2012;

transfrer les donnes dans et partir de SQL Server 2012;

automatiser des tches dadministration;

comprendre et optimiser larchitecture de SQL Server;

rpliquer des donnes entre instances SQL Server 2012.

lecture dune base de donnes SQL server 2012 en VB.NET


4/43



Page 4

Chapitre I : Prsentation de SQL Server 2012

Lobjectif du chapitre est :

Comprendre le fonctionnement de SQL Server ;

Prsenter les diffrents composants de SQL Server ; Prsenter larchitecture globale de SQL Server

Comprendre larchitecture dune base de donnes

1.1 - Fonctionnement de SQL Server

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 donnes) vont sexcutes.

Un serveur qui va supporter beaucoup de petites transactions qui manipuleront un

petit volume de donnes.

OLAP (OnLine Analytical Processing)

Serveur dcisionnel ou Data Warehouse : cest un entrept de donnes. Un

serveur qui servira en stocker lensemble des donnes consolides. Les donnes qui

proviennent des diffrentes sources (BD Oracle, SQL Server, MySQL ). Un

serveur OLAP va donc manipuler de gros volumes de donnes en utilisant de

grosses requtes gnrant une charge importante.

Ainsi donc avant toute installation il faut connaitre le rle du serveur pour faire lebon choix du type de serveur installer.


5/43



Page 5

1.2 - Les composants de SQL Server

Le moteur de donnes est compos de plusieurs logiciels.

La plus part sexcutent sous forme de services;

Certains possdent des interfaces graphiques

Dautres sont accessibles via une ligne de commande

Les principaux composants sont :

SQL Server Instance SQL :

Cest le serveur de base de donnes (le moteur). Cest le principal composant

Il est impossible davoir une instance SQL server sans que le moteur de base de donnes ne

soit install.

Sexcute en tant que service Windows ;

Elle est rfrence sous le nom MSSQLSERVERpour une instance par

dfaut (instance dans laquelle on choisit le nom par dfaut).

Elle sera MSSQLSERVER$nomInstance dans le cas dune instance

nomme (une instance pour laquelle on choisit de donner un nom

diffrent du nom par dfaut).

Il faut savoir quil est possible dinstaller plusieurs instances sur un mme serveur.

Cependant il impossible davoir sur un mme serveur deux instances de mme nom.


6/43



Page 6

SQL Agent :

En gnral chaque instance son propre moteur de base de donnes et

possde son propre SQL Agent.

Il gre lexcution de tches planifies, la surveillance de SQL Server et

le suivi des alertes

Il sexcute en tant que service Windows

Il est directement li une instance SQL Server

Il est rfrenc dans le gestionnaire de service sous le nom SQL Server

Agent(MSSQLSERVER)pour une instance par dfaut et SQL Server

Agent(nomInstance) dans le cas dune instance nomme.

Microsoft Full Text Search :

Cest un composant utilis pour effectuer des recherches de documents de

type texte. Gre lindexation des documents de type texte

Gre les recherches par rapport aux mots

SQL Server Intgration Services (SSIS) :

Outil dimportation et dexportation de donnes

Intgre des assistants pour crer un ETL (extraction, transformation et

chargement)

Termes connaitre

Les packages = units de travail

Les conteneurs qui fournissent la structure des packages

SQL Server Analysis Services (SSAS) :

Outil danalyse OLAP et Data mining de Microsoft

Permet de construire des cubes OLAP

Idal pour des projets dcisionnels

SQL Server Reporting Services (SSRS) :

Crer des rapports pour diffrents sources de donnes Crer des rapports de types interactifs, tabulaires, graphiques ou libres

partir de source de donnes XML relationnelles et multidimensionnelles

La replication des donnes :

Permet de positionner les donnes au plus prs des utilisateurs et de

rduire les temps de traitement. (Avoir un serveur central et des serveurs

secondaires)


7/43



Page 7

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 :

Lintgration du CLR dans SQL Server permet de dvelopper des

procdures et fonctions en utilisant les langages VB.Net et C#

1.3 - Larchitecture globale de SQL Server et les outils

Larchitecture de SQL Server :

Une installation de SQL Serveur contient au minimum une instance par dfaut.

Instance par dfautElle est identifie par le nom rseau de lordinateur sur lequel elle sexcute.

Instance nommeElle est identifie par le nom rseau de lordinateur sur lequel elle sexcute

suivi dun nom dinstance.


8/43



Page 8

Les outils de SQL Server :

Les clients de SQL ServerLes composants qui permettent dattaquer un serveur de base de donnes SQL

Server.

SQL Server Management StudioPermet de raliser toutes les oprations au niveau du serveur de base de

donnes.

Gestionnaire de configuration SQL ServerPour grer les services lis SQL Server.

SQL Server ProfilerPour suivre et analyser la charge de travail dune instance SQL Server.

Assistant paramtrage du moteur de base de donnesPermet une optimisation du fonctionnement du serveur de base de donnes.

sqlcmd

Cest un outil quun DBA doit imprativement connaitre.

Outil de ligne de commande

Excuter des requtes approuves

Excuter des scripts de commandes

Etablir une connexion dadministration ddie (DAC)

1.4 - Larchitecture dune base de donnes

Base de donnes SQL Server :

Une base de donnes permet de stocker des objets logiques.

Les donnes : tables, indexes, types de donnes, contraintes dintgrit,

valeurs par dfaut, rgles.

Accs aux donnes : vues, procdures et fonctions

Gestion de lintgrit complexe : dclencheur


9/43



Page 9

Chaque base de donnes systme est

MasterCest la base mtre elle est le dictionnaire des donnes. Elle contient une

rfrence de lensemble des objets cres.

MSDB

Elle va stocker lensemble des tches planifies.


10/43



Page 10

ModelElle va permettre de dfinir le model de cration dune base de donnes.

Chaque base de donnes cre va sappuyer sur la structure de la base model.

La base de donnes cre est une copie de la base model. Une modification

apporte la base Model va impacter lensemble des bases.

TempdbElle va servir pour toutes les oprations de type temporaire.

RessourceElle permet de stocker les volutions apportes aux diffrentes versions de SQL

Server 2012 pour faciliter les migrations.

Les tables systmes:

Les bases de donnes masters contiennent des tables systmes

Utilises directement par le moteur de SQL Serveur.

Dconseill daccder directement ces tables

Quelques tables systmes :

sys.server_principals: Liste des connexions dfinies sur le serveur

sys.sysmessages: Une ligne pour chaque message ou avertissement

sys.sysdatabases: Une ligne pour chaque base de donnes

sys.sysusers: Une ligne pour chaque utilisateur dfini au niveau de la

base de donnes courante

Les procdures stockes systme :

Interroger les tables systme.

Connatre ltat du serveur, de la base

Effectuer des oprations de configuration

Quelques procdures stockes systmes:

sp_helpsp_help[nom_objet]: Information sur lobjet indiqu. sp_helpdbsp_helpdb[nom_base_donnes]: Information sur la base de

donnes indique. sp_whosp_who: Liste des utilisateurs actuellement connects

Le catalogue:

Contient des vues systmes

Toutes ces vues sont prsente dans un schma sys.

Regroupes par thmes

Objets, types et index

Serveurs lies

CLR

Service Broker


11/43



Page 11

Les fonctions systmes:

Utilisable avec des commandes Transact SQL

Quelques procdures stockes systmes:

DB_ID: Retrouve lidentification de la base de donnes.

USER_NAMEUSER_NAME : Retrouve le nom de lutilisateur partir

de son identifiant.

Schma dinformation:

Ensemble de vues

Ne fait pas directement rfrence aux tables systme


12/43



Page 12

Chapitre II : Installation et configuration de SQL Serveur 2012

Lobjectif du chapitre :

Comprendre les tapes du processus dinstallation de SQL Server 2012

Prparer linstallation avec loutil de configuration systme Ralisation dune installation manuelle et dune installation automatise

Vrifier et valider une instance

2.1 - Prparation de linstallation

Les tapes du processus dinstallation de SQL Server 2012

Analyse de la configuration systme


13/43



Page 13

2.2 - Ralisation dune Installation manuelle de SQL Server 2012

TP pratique dune installation manuelle de SQL Server 2012.

Etape 1 : Lancement du programme dinstallation

Etape 2 : Centre dinstallation de SQL Server 2012


14/43



Page 14

Le Menu de gauche du centre dinstallation nous donne les diffrentes actions que nous

pouvons effectues.

La planification :

Qui contient un certain nombre doutils. Celui qui nousintresse ici est loutil danalyse de configuration systme.

Cet outil nous permet de vrifier que notre systme est prs

pour linstallation.

Linstallation :

Le Menu Installation nous prsente quatre outils. Nous allons lancer loutil :

Nouvelle installation autonome de SQL Server ou ajout de fonctionnalits une

installation existante .


15/43



Page 15

Aprs une autre vrification des composants ncessaires pour linstallation, le programme

dinstallation va installer les fichiers dinstallation.

Aprs lajout de la cl dinstallation et lacceptation du contrat de contrat de licence nous

avons une tape importante qui est le Rle de linstallation . Nous avons 3 ou 2 options.

Nous choisissons celle qui nous permet de choisir et installer les fonctionnalits de SQLServer ncessaires pour grer notre serveur.


16/43



Page 16

Ensuite nous devons slectionner les fonctionnalits installer.


17/43



Page 17

Choix du mode dauthentification


18/43



Page 18

Fin de linstallation


19/43



Page 19

2.3 - Ralisation dune Installation automatiquede SQL Server 2012

Mise en uvre.

Cration dun fichier de configuration .ini

Dmarrage de linstallation automatise

setup.exe /CONFIGURATIONFILE=chemin daccs du fichier.ini

2.4 - SQL Server Management Studio (SSMS)

Cest loutil principal de gestion et dadministration. Il utilis par les administrateurs et

les dveloppeurs.

Un requteur avanc

Permet une gestion graphique des instances et des objets

Permet une gestion centralise des instances distantes

Intgre la compltion

2.5 - SQLCMD

Cest un outil incontournable dans ladministration de SQL Server. Un outil de gestion

et dadministration en mode ligne de commande.

Permet lexcution de requtes et des scripts

Etablir une connexion dadministration ddie (DAC)

2.6 - Configuration de SQL Server 2012

Il est question ici de voir les diffrents moyens de configuration de SQL Server 2012.

Les lments de configurations sont les services (Les Moteurs, les composants slectionns

lors de linstallation).

Les tats

Dmarr

Suspendu

Arrt

Via Gestionnaire de configuration SQL Server

Gestionnaire de service Windows

Utilisation de loutil SSMS (SQL Server Management Studio)

Inscrire un serveur

Grer la configuration dun serveur (Mot de passe de ladministrateur,

Gestion des ressources)


20/43



Page 20

Chapitre III : Les bases de donnes SQL Server 2012

3.1 - Architecture globale de SQL Server

Une base de donnes contient deux types de fichiers :

Fichiers de donnes

Fichier primaire de donnes (obligatoire, extension .mdf)

(Contient le catalogue de la base de donnes)

Fichier secondaire de donnes (facultatif, extension .ndf)

(Contient les objets et les donnes utilisateurs)

Fichiers journaux (au moins 1, extension .ldf)

Enregistre toute les modifications de la base de donnes

Fonctionnement des fichiers journaux :

Les fichiers de donnes :

Rattach une seule base de donnes

Structur en pages de 8k Les pages contiennent les enregistrements

Unit dchange entre le cache de donnes et les fichiers de donnes

La taille maximale dune ligne est de 8060 octets (hors type texte et

image)

Chaque page contient un type bien prcis de donnes


21/43



Page 21

3.2 Cration dune base de donnes

Deux mthodes :

En transact-SQL

SQL Server Management Studio (SSMS)

Les informations ncessaires :

Le nom de la base de donnes, la collation

La taille

Lespace de stockage

En Transact SQL :

Cas pratique :

Nom : gecos

Deux fichiers de donnes

gecos_data01.mdf (taille = 10MB, maxsize=100Bet autoextend OFF)

gecos_data02.ndf (taille = 10MB, maxsize=100Bet autoextend 10MB)

Stockage dans C:\MSSQL\DATA

Un fichier journal

gecos_log01.ldf(taille = 15MB, maxsize=200Bet autoextend 10%)

Stockage dans C:\MSSQL\LOGS

Collation : French, Case Sensitive, Accent Insensitive


22/43



Page 22

3.3 - Les groupes de fichiers

Un groupe de fichiers est une :

Structure logique

Permet de regrouper des fichiers de donnes et de les grer comme des

units logiques. (Unit logique auquel vont sattacher des fichiers

physiques de type .mdfou .ldf)

Nous avons :

Deux types de groupe de fichier

Un type primaire. (cre par dfaut par le systme)

Un autre type dfini par lutilisateur

Utilisation :

Rpartition des donnes par type (tables, indexes)

Rpartition des donnes pour la charge

Cration et Ajout de fichiers au groupe :

Syntaxe de cration de groupe de fichiers

ALTER DATABASE

ADD FILEGROUP ;

Syntaxe dajout de fichiers au groupe

ALTER DATABASE ADD FILE

TO FILEGROUP ;


23/43



Page 23

3.4 - Le partitionnement

Objectif

Diviser des tables volumineuses en plusieurs tables

Optimiser le stockage (Stockage sur diffrents groupes de fichiers)

Monte en charge

Un exemple

Implmentation

Dfinir une fonction de partitionnement Dfinir un schma de partitionnement

Crer les tables partitionnes

Possibilit de crer un index sur une table partitionne (lindex cre est

partitionn selon la table)

Fonction de partitionDfinir la cl de partitionnement (champ de rpartition pertinent)

Permet de rpartir les donnes entre les diffrentes partitions

Permet dorienter les donnes sur un groupe de fichierou un autre

Utilise des plages de valeurs bornes Syntaxe

CREATE PARTITION FUNCTION ( 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 frontire de chaque partition.


24/43



Page 24

Schma de partition Permet daffecter chaque partition un groupe de fichiers

Possibilit de spcifier plus de groupe de fichiers que de partitions

dfinis

Possibilit daffecter 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 schma de partitionnement.

nomFonctionPartition: nom de la fonction de partitionnement associe au schma.

groupeDeFichier: nom du ou des groupes de fichiers utiliss par les diffrentes

partitions.

Cration dune table de partitionne

Syntaxe

CREATE TABLE nomTable(

definitionColonne [,...]

) ON nomSchemaPartition(colonneUtilisePourCalculerLaPartition)[;]

Cration dun index partitionn

Syntaxe

CREATE INDEX nomIndex

ON nom Table(colonne1, )

ON nomSchemaPartition(colonneDepartition);


25/43



Page 25

3.4 - Configuration dune base de donnes

Les bases de donnes par dfaut

masterbase de donnes systme

model base de donnes modle pour les nouvelles bases de donnes

msdb base de stockage des alertes et travaux pour lagent SQL Server tempdbbase de stockage des objets et donnes temporaires

Cette base est recre chaque redmarre 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 procdures

sp_helpdb, sp_spaceused;

Modification des options de configuration

En TRANSACT-SQL

- Syntaxe

ALTER DATABASE SET option [;]


26/43



Page 26

Chapitre IV : Sauvegarde des bases de donnes SQL Server 2012

4.1 - Introduction la sauvegarde

Pourquoi sauvegarder

Panne matrielle (support) Mauvaise manipulation des utilisateurs

Panne serveur

Dplacement de base de donnes

Caractristiques

Sauvegarde chaud

sauvegarde cohrente des donnes

Aucune opration de cration ou de modification de base de donnes nest

possible pendant une opration de sauvegarde

Impossible de crer des indexes pendant la sauvegarde Excution doprations non journalises et non autorises

Les principales mthodes de sauvegardes

Sauvegarde complte

Sauvegarde diffrentielle

Sauvegarde du journal des transactions

Sauvegarde des groupes de fichiers

Partielle

Sauvegarde complte de base de donnesSauvegarde toutes les donnes de la base de donnes

Sauvegarde les modifications valides pendant la sauvegarde

Point de dpart pour toute stratgie de sauvegarde

Sauvegarde diffrentielle

Sauvegarde les parties de la base de donnes modifies depuis la sauvegarde

complte

Sauvegarde toutes les transactions intervenues pendant la sauvegarde

diffrentielle


27/43



Page 27

Sauvegarde du journal des transactions

Pr-requis : sauvegarde complte indispensable

Ncessite le mode de rcupration COMPLET

Sauvegarde partielle

Contient toutes les donnes du groupe PRIMAIREContient chaque groupe de fichiers en LECTURE/ECRITURE

Ne contient pas les groupes de fichiers en LECTURE seule

4.2 - Sauvegarde complte

Destination des sauvegardes

Disque

Les units physiques = nom complet du fichier physique auniveau de lOS

Les units logiques: sappuient sur des fichiers physiques

Bande

Les privilges ncessaires pour raliser une sauvegarde

Rles de serveur

sysadmin, db_owner, db_backupoperateur


28/43



Page 28

Linstruction BACKUP

Syntaxe

BACKUP DATABASE TO |

DISK= WITH INIT |

NOINIT | FORMAT | CHECKSUM | COMPRESSION

Sauvegarde avec mise en mirroir

Syntaxe

BACKUP DATABASE TO |

DISK= MIRROR TO

| DISK=

Vrifier lintgrit dune sauvegarde Syntaxe

RESTORE VERIFYONLY FROM | DISK=

4.3 - Sauvegarde diffrentielle

Caractristiques

Sauvegarde toutes les extensions modifies depuis la dernire sauvegarde

Complte

Permet de rduire le nombre de sauvegarde du journal des transactions Utilisable quel que soit le mode de rcupration utilis

Commande BACKUP

Syntaxe

BACKUP DATABASE TO DISK = WITH

DIFFERENTIAL

Utilisable quel que soit le mode de rcupration utilis

4.4 - Sauvegarde du journal des transactions

Caractristiques

Utilisable uniquement avec le mode de rcupration COMPLET et

JOURNALISE EN BLOC

Autorise uniquement aprs une sauvegarde complte

Dbute au numro LSN (Log Sequence Number) o a pris fin la

prcdente sauvegarde de journal

Sauvegarde toutes les transactions jusqu la transaction actuelle ouverte

complte


29/43



Page 29

Toutes les transactions sauvegards peuventt alors tre supprims du

journal des transactions

Commande BACKUP

Syntaxe

BACKUP LOG TO DISK = WITH INIT

4.5 - Sauvegarde de groupe de fichiers

Caractristiques

Stratgie de sauvegarde alternative aux sauvegardes compltes

Permet de sauvegarder des groupes de fichiers

Point de dpart: sauvegarde initiale de tous les groupes de fichiers de la

base de donnes

Mode de rcupration: COMPLET ou JOURNALISE EN BLOC

Commande BACKUP

Syntaxe

BACKUP DATABASE FILEGROUPE =

TO DISK =

BACKUP DATABASE FILEGROUPE =

TO DISK = WITH DIFFERENTIAL

4.6 - Sauvegarde partielle

Caractristiques

Utilisable avec tous les modes de rcupration

Permet dexclure des groupes de fichiers en lecture

Identique une sauvegarde complte mais ne contient pas tous les

groupes de fichiers

La sauvegarde partielle d'une base de donnes en lecture seule contient

uniquement le groupe de fichiers primaire

Pour une base de donnes accessible en lecture/criture, une sauvegarde

partielle contient toutes les donnes du groupe de fichiers primaire, de

chaque groupe de fichiers en lecture/criture

Commande BACKUP

Syntaxe

BACKUP DATABASE READ_WRITE_FILEGROUPS

TODISK =


30/43



Page 30

Chapitre V : Restauration des bases de donnes SQL Server 2012

5.1 - Les pr-requis la restauration

La vrification des sauvegardes

RESTORE HEADERONLYRESTORE 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

5.2 - Restauration des bases de donnes

Les diffrents types de restauration

Restauration dune sauvegarde complte

Restauration dune sauvegarde diffrentielle

Restauration dune sauvegarde de journal de transactions

Restauration partielle

Restauration dune page corrompue

Les options de la commande RESTORE

RECOVERY

NORECOVERY

FILE

MOVE... TO

REPLACE

STOPAT (PITR: point in time recovery)

STOPATMARK (LSN), STOPBEFOREMARK (LSN)


31/43


32/43



Page 32

Chapitre VI : Importation et exportation des bases de donnes SQLServer 2012

6.1 - Introduction aux outils dimport / export

Les outils de transfert

SSIS (SQL Server Intgration Service)

Extraction, transformation, chargement

BCP

Outil en ligne de commande

Permet dimporter et dexporter entre un fichier et SQL Server

BULK INSERT

Rplication

SELCT INTO et INSERT

6.2 - SSIS (SQL Server Integration Service)

Terminologie

Les packages (units de travail excuter)

Les tches (se chargent du travail dans les packages)

Les conteneurs (fournissent la structure des packages)


33/43



Page 33

Flux de donnes

Utilisation

Assistance

Concepteurs SSIS

Utilitaires de ligne de commande dtexec

6.3 - BCP (BULK Copy Program)

Caractristiques

Outil en ligne de commande

Permet dexporter les donnes dune table ou dune requte SQL

Permet dimporter un fichier texte dans une table SQL Server

Outil BCP

syntaxe

bcp {nom objet | requete} { in | out | queryout | format }

-S -T...

6.4 - BULCK INSERT

Caractristiques

Importer un fichier de donnes dans une table ou vue de base de donnes

dans un format spcifi par lutilisateur

Commande BULK INSERT

syntaxe

[ database_name . [ schema_name ] . | schema_name .] [ table_name | view_name ]

FROM 'data_file'[ WITH

([ [ , ] FIELDTERMINATOR = 'field_terminator' ]


34/43


35/43



Page 35

Les entits de scurits

Windows

Group Windows, Compte dutilisateur de domaine, Compte dutilisateur

local

SQL Server

Connexion SQL Server, Rle de serveur

Base de donnes

Utilisateur, Rle de base de donnes, Rle dapplication

Configuration des identifiants SQL Server

Identifiant = accs des utilisateurs SQL Server

Cration dun identifiant (authentification Windows)

Syntaxe

CREATE LOGIN [\] FROM WINDOWS

[WITH DEFAULT_DATABASE= |

DEFAULT_LANGUAGE=

Cration dun identifiant (authentification SQL Server)

Syntaxe

CREATE LOGIN WITH PASSWORD=

[MUST_CHANGED] |, DEFAULT_DATABASE= |,

DEFAULT_LANGUAGE= | , CHECK_EXPIRATION={ ON | OFF}| CHECK_POLICY={ ON | OFF} | , [CREDENTIAL=

Les vues systmes

sys.server_principals: Entits de scurit dfinis au niveau serveur

sys.sql_logins: Liste des connexions au niveau serveur

Modification

Syntaxe

ALTER LOGIN WITH

Dsactivation


36/43



Page 36

Syntaxe

ALTER LOGIN DISABLE

Suppression

Syntaxe

DROP LOGIN

DROP LOGIN

7.2 - Les credentials

Permettent des connexions en mode scurit SQL Server daccder une

ressources externe au serveur de base de donnes

Un credential = un compte Windows

Un compte SQL Server est rattach un credential

Cration dun credential

Syntaxe

CREATE CREDENTIAL

WITH IDENTITY = [, SECRET = ];

7.3 - Les utilisateurs de la base de donnes

Caractristiques

Cre au niveau base de donnes

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 donnes aux

utilisateurs de base de donnes

Les utilisateurs particuliers

Utilisateur dbo

-

Prsent dans toutes les bases donnes par dfaut

- Les membres du rle sysadmin et du compte de connexion sa sont

mapps dbo

- Ne peut tre supprim

- Tous les objets crs par un membre de sysadmin appartiennent

automatiquement dbo


37/43



Page 37

Utilisateur guest

- Prsent dans toutes les bases donnes par dfaut

- Autorise les connexions sans compte utilisateur accder la base

- Activation du compte guest

GRANT CONNECT TO guest;

-

Dsactivation du compte guest

REVOKE CONNECT FROM guest;

Cration dun utilisateur dune base donnesSyntaxe

CREATE USER FOR LOGIN WITH

DEFAULT_SCHEMA=

Modification dun utilisateur de base donnesSyntaxe

ALTER USER WITH NAME=,

DEFAULT_SCHEMA=

Suppression dun utilisateur de base donnesSyntaxe

DROP USER

Les vues systmes

Sys.database_principals

Savoir qui est connect

Procedure sp_who

7.4 - Les schmas

Caractristiques

Apparue dans MSSQL en 2005

Objet logique (enveloppe) Permet un regroupement logique des objets

Permet de grer dune manire plus optimale les droits sur les objets

Associ un utilisateur

Schma par dfaut dbo

Pour accder des objets en dehors de son schma, faut faire prcder le

nom de lobjet par le nom du schma.

Cration

SyntaxeCREATE SCHEMA AUTHORIZATION


38/43



Page 38

ModificationSyntaxe

ALTER SCHEMA TRANSFER ;

Suppression (doit tre vide)

SyntaxeDROP SCHEMA ;

7.5 - Gestion des droits sous SQL Server

Caractristiques

Plusieurs niveaux dattribution des privilges

- Au niveau serveur

-

Au niveau base

- Au niveau schma

-

Au niveau objets

Deux types de droits au niveau base

-

Droits dutilisation dinstructions

- Droits sur les objets

Gestion des privilges-

GRANT pour lattribution des privilges

- REVOKEpour retirer des privilges

- DENYpour interdir e lutilisation dun privilge

Les privilges dutilisation des instructions- CREATE DATABASE pour crer une base de donnes

- CREATE PROCEDURE pour crer une procdure stocke

-

CREATE TABLE pour crer une table

- BACKUP DATABASE pour raliser une sauvegarde

-

CREATE DEFAULT- CREATE RULE pour crer un rle

- CREATE VIEW pour crer une vue

- BACKUP LOGpour raliser une sauvegarde du journal des

transactions

La commande GRANT

GRANT [, ...] TO > [,...] [ WITH

GRANT OPTION ]

La commande REVOKE

REVOKE [ GRANT OPTION FOR] [, ...]FROM

[, ...] [CASCADE]


39/43



Page 39

La commande DENY

DENY [, ...] TO [, ...] [CASCADE]

Les privilges sur les objets

TablesSELECT, INSERT, UPDATE, DELETE

Procdures

EXECUTE

La commande GRANT

GRANT { ALL | [ (colonne [, ...] ] [, ...]) } ON

TO [, ...] [ WITH GRANT OPTION ]

La commande REVOKE

REVOKE [ GRANT OPTION FOR ] { ALL | [ (

colonne [, ...] ] [, ...] ) } ON [ ( colonne [, ...] ] [, ...] )FROM [, ...] [ CASCADE ]

La commande DENY

DENY { ALL | [ ( colonne [, ...] ] [, ...] ) } ON

[ ( colonne [, ...] ] [, ...] ) TO [, ...] [

CASCADE ]


40/43



Page 40

Chapitre VIII : Automatisation de la Gestion de SQL Server 2012

8.1 - Agent SQL Server

Caractristiques

Permet dautomatiser certaines tches.

Chaque agent est rattach une seule instance

Log les erreurs SQL Server dans lobservateur dvenement Windows

Stock les informations dans la base de donnes msdb

Terminologie

Tches planifie

Les alertes

Les oprateurs

8.2 - Configuration de la messagerie Caractristiques

Utilise le protocole SMTP pour envoyer des mails

Possde son propre processus de fonctionnement

Non actif par dfaut

8.3 - Les oprateurs

Caractristiques

Correspond une personne physique ou un groupe dans lentreprise

Permet lAgent SQL Server denvoyer des messages dalerte ou de fin

de travaux

- Messagerie

- Net send

- Radiomessagerie

Les informations sur les oprateurs sont stockes dans la base de donnes

msdb

8.4 - Alertes

Caractristiques

Permettent Davertir un oprateur lorsquun vnement survient

De raliser un traitement pour rsoudre un problme


41/43



Page 41

Quand dclencher une alerte

Erreurs SQL Server

En fonction du numro de lerreur

En fonction de la gravit

Crer ses propres messages derreur

sp_addmessage pour crer un message

sp_altermessage pour supprimer un message

sp_dropmessage pour supprimer un message

Cration dune alerte

Via SQL Server Management Studio

Via Transact SQL

sp_add_alert pour crer une alerte

sp_update_alert pour mettre jour une alertesp_delete_alert pour supprimer une alerte

8.5 - Travaux

Caractristiques

Constitu dune ou plusieurs tapes (tches)Deux tats possibles pour une tche

-

Echec

- Succs

Enchainement possible entre les tapes

Plusieurs types dtapes- Transact SQL, Commande systme, Package,

Stocks dans la table sysjobs de la base de donnes msdb


42/43



Page 42

Chapitre IX : Lecture dune base de donnes SQL Server en

VB.NET

Cas pratique


43/43


Conclusion

Documents

Support Cours SQL Server 2012