Types de commandes SQL Server

Fichier

Il est recommandé de séparer les deux fichiers (base de données et journaux) sur des disques différents pour améliorer les performances (I/O).

De base, les fichiers MDF et NDF sont regroupés dans un groupe PRIMARY. Il peut être cependant judicieux de séprarer ces fichiers en groupes différents pour les répartir sur des supports physiques différents.

Création d'une base de données

CREATE DATABASE DB
-- groupe de fichiers
ON PRIMARY
(
    SIZE = 5120KB,
    MAXSIZE = 15360KB,
    FILEGROWTH = 1024KB
)
LOG ON
(
    -- nom
    NAME = "DB_log",
    -- chemin physique
    FILENAME = "C:\xxxx",
    -- taille
    SIZE = 1024KB,
    -- augmentation possible
    FILEGROWTH = 10%
)

-- liste des fichiers MDF
SELECT * FROM sys.sysdatabases
-- liste de le l'ensemble des fichiers utilisés par la BDD (size en block de 8ko)
SELECT * from sys.database_files

On peut rajouter un second fichier pour les données utilisateurs pour la base. Il faut pour cela, créer d'abord un nouveau groupe de fichiers.

-- Ajout d'un nouveau groupe de fichier déjà créé
ALTER DATABASE BDD ADD FILEGROUP DATA;
-- Ajout d'un fichier créé
ALTER DATABASE BDD ADD FILE (
    NAME = "BDD2",
    FILENAME = "C:\xxxx",
    SIZE = 5120KB,
    FILEGROWTH = 1024KB
)

Cependant, un fois un fichier associé à un groupe, il ne peut plus être changer de groupe. Idem pour une table faite sur un groupe particulier.

La répartition physique se prévoit donc à la conception de la base et non après. Cela permet de répartir les tables sur plusieurs disques (amélioration des I/O).

Partitionnement

Permet de diviser une table de grande taille en plusieurs sous tables. Chacune de ces tables peut être créée sur un groupe différent enfin de profiter des avantages des groupes.

Il faut cependant que les collones qui seront partitionnées soient membres de la clé primaire de la table.

1. Créer les groupes
2. Fonction de partitionnement (connaitre les valeurs des bornes, right ou left, type de donnée)
3. Schéma de partitionnement (répartir les informations dans les groupes)
4. Créer les tables : CREATE TABLE table () ON schema(collone)

Authentification

Mappage possible :

• Explicite : CREATE USER
• Implicite : avec guest/dbo

GRANT

Donne une autorisation pour un utilisateur

GRANT INSERT ON dbname TO user;

REVOKE

Révoque une autorisation d'un utilisateur

REVOKE INSERT ON dbname TO user;

DENY

Interdit une action pour un utilisateur

DENY INSERT ON dbname TO user;

Rôles

Ils sont définis sur trois niveau :

• Instance
• Base de données
• Applications

Rôles d'instances

Rôles de base de données

membre de sysadmin, db_owner ou db_securityadmin

Regroupe les différentes autoristations ou refus. Concerne les bases de données.

USER bdd
CREATE ROLE name
GRANT INSERT,UPDATE,DELETE TO name
ALTER ROLE name ADD MEMBER user
-- schéma
GRANT SELECT ON schema::dbo TO user

Le compte d'utilisateur dbo est fréquemment confondu avec le rôle de base de données fixe db_owner. La portée de db_owner est une base de données, tandis qeue la portée de sysadmin est le serveur dans son intégralité. L'appartenance au rôle db_owner ne confère pas les privilèges d'utilisateur dbo.

Rôles d'applications

De même que pour les rôles de base de données mais au niveau des tables. Aucun utilisateur ne peut y être ajouté et il est protégé par un mot de passe. Ces rôles prennent le pas sur les autres rôles qui peuvent être défini.

sp_setapprole 'role', 'password'

Vues

Une vue peut être utilisée aux fins suivantes :

• pour affiner, simplifier et personnaliser la perception de la base de données par chaque utilisateur.
• comme mécanisme de sécurité en permettant aux utilisateurs d'accéder aux données par le biais de la vue, sans leur accorder d'autorisations qui leur permettraient d'accéder directement aux tables de base sous-jacentes de la vue.
• pour fournir une interface à compatibilité descendante pour émuler une table dont le schéma a été modifié.

CREATE VIEW Employee AS
SELECT Name, BirthDate, Salary, BuildingName
FROM Employee2 e, Department d
WHERE e.DeptId = d.DeptId

Informations systèmes

-- Connexion
USE master
SELECT * FROM sys.sql_logins --seulement sql server
SELECT * FROM sys.server_principals
SELECT * FROM sys.server_permissions -- GRANTEE action/GRANTOR qui
SELECT * FROM sys.server_role_members

SELECT name, permission_name FROM sys.server_principals who INNER JOIN
    sys.server_permissions what ON who.principal_id = what.grantee_principal_id

-- Utilisateur
USE bddnamer
SELECT * FROM sys.database_principals
SELECT * FROM sys.atabase_permissions -- GRANTEE action/GRANTOR qui
SELECT * FROM sys.atabase_role_members

Planification des tâches

L'ensemble des données sont stockées dans la base MSDB.

• Travail : automatisation de tâches d'administration ou répétitives. Il peut être planifié ou exécuter manuellement. Ils sont stockés dans la table sysjobs. C'est l'agent SQL Server qui est en charge de leur exécution.
• Alerte : déclenchement d'un traitement automatique pour corriger un problème et/ou avertir un opérateur. Elle peut être liée à un numéro d'erreur ou une gravité de message d'erreur. Elle est valide pour une ou toutes les bases de données d'une instance.
• Opérateur : personne physique ou rôle averti lors de la fin d'exécution d'un travail ou lors d'un déclenchement d'une alerte pour informer de la gravité de la situation. Trois moyens possible : mail, radiomessage ou message réseau (net send). Il n'a aucun lien avec un utilisateur de BDD.

Sauvegardes

• Complète : (données et journaux) obligatoire - long et volumineux en sauvegarde mais restauration rapide
• Différentielle : (données) différences depuis la dernière complète
• Journaux : (journaux) rapide, vide le journal des instructions avant le dernier checkpoint - long car rejoue toutes les insctructions

-- COMPLETE
-- La sauvegarde sera répartie sur deux fichiers et non mirroir
BACKUP DATABASE name
    TO DISK='C:\backups\name.bak',
        DISK='C:\backups\name_2.bak'
    WITH INIT,
        -- nom de la sauvegarde
        MEDIANAME='Backup Full',
        -- compression du fichier
        COMPRESSION
-- DIFERENCIELLE
BACKUP DATABASE name
    TO DISK='C:\backups\name.bak'
    WITH DIFFERENTIAL,
        MEDIANAME='Backup Full'
-- JOURNAUX
BACKUP LOG name 
  TO <backup_device>

Mode de récupération

• Simple : le journal est utilisé seulement pour garentir la persistance des opérations. Il est vidé lors de chaque point de synchronisation (CHECKPOINT).
• Complet : toutes les actions sont consignées dans le journal et y restent même après un point de synchronisation.
• Journalisé en bloc : les informations relatives aux transactions mais également certaines opérations affectant les données (création d'index).

ALTER DATABASE name
SET RECOVERY { SIMPLE | FULL | BULK_LOGGED }

Optimisation

Espace disque

SHRINKDATABASE

Permet de compacter l'ensemble des fichiers constituans la base de données (journaux et données).

SHRINKFLE

Permet de compacter suelement un des fichiers de données .