Microsoft SQL Server

Administration

Par : Mohamed ElEmam

[Link]@[Link]
 Administration de SQL Server

• Comment les données sont stockées : Pages et extensions

Pages

• 8 Ko en taille ou 8192 octets

• L'en-tête fait 96 octets

• 128 pages = 1 mégaoctet | 128 000 = 1 gigaoctet

• Unité d'allocation la plus petite ; il n'y a pas de demi-pages

Étendue

• Un ensemble de 8 pages

• 1 Page = 8 Ko | 8 Pages = 64 Ko

• 16 étendues par Mo | 16 000 par Go

Pages de bitmap d'allocation : Le "répertoire" interne d'informations sur les pages

• GAM (Carte d'allocation mondiale)

• Quelles étendues sont disponibles pour allocation

• Couvre un intervalle de données de 4 Go

• SGAM (Carte d'allocation globale partagée)

• Quelles étendues de mélange ont au moins une page à allouer

• Couvre un intervalle de données de 4 Go

• Espace libre de page (PFS)

• Utilisé pour suivre combien d'espace libre est disponible sur les pages

• Suit également d'autres attributs

• Couvre un intervalle de données de 64 Mo

Carte d'attribution d'index (IAM)

• Page interne spéciale sur un fichier de données qui suit toutes les allocations d'extent pour les tables, les index,
et partitions

• En gros, faisons savoir à SQL Server quelle étendue appartient à quelle entité spécifique.

• Couvre un intervalle de données de 4 Go

-----------------

• PFS, GAM et SGAM aident SQL Server à déterminer où se trouve l'espace libre et combien il y en a.
qu'il peut l'allouer de manière appropriée sans avoir à scanner toutes les pages

• Les pages et index IAM aident SQL Server à exécuter des requêtes

• L'essentiel est que ces quelques pages aident à maintenir un "répertoire" interne pour SQL Server.
être en mesure de trouver ce dont il a besoin rapidement et de revenir en arrière avec le moins de pages possible

• Il s'agit de réduire les coûts pour le système

----------------
Fichiers de données

• Stocke des données à partir de tables et d'index dans des pages

• L'extension de fichier est .Mdf pour le premier fichier et .Ndf pour les fichiers supplémentaires.

• Utilisez des disques physiques séparés lorsque cela est disponible/approprié

• Séparer les fichiers Log et TempDB ; considérez cela comme une exigence

• Assurez-vous que les fichiers de données ont une taille égale.

• Assurez-vous de pré-dimensionner vos fichiers de données à l'avance

• L'extension de fichier journal est .Ldf

Groupes de fichiers

• Conteneur logique pour fichiers de base de données

• Permet la séparation des données et des index

• Utilisé pour le partitionnement de table

Journaux des transactions

• (insérer, mettre à jour, supprimer)

• Transaction engagée : (Transaction terminée)

• Transaction non engagée : (transaction en phase de traitement)

• (Transaction incomplète)

• Doit mettre le fichier journal sur un disque séparé et avec de bonnes performances comme RAID 10 ou 5
Pourquoi ? Parce que toute transaction entre d'abord dans la RAM, puis dans le fichier journal, et si elle est validée.

la transaction sera écrite dans le fichier de données donc le fichier journal est le traitement le
transactions
 2 Types

• Explicite

• Implicite : le défaut de SQL Server

TempDB

• Plusieurs fichiers permettent à plusieurs threads d'utiliser TempDB

• Réduit la contention de page "Système" (GAM, SGAM, PFS)

• Ces pages internes aident SQL Server à allouer/suivre l'espace pour des objets tels que
tables. Par conséquent, lorsque la contention est réduite, la performance est augmentée.

• Commencez avec 4 à 8 fichiers, ne dépassez pas 8 sauf si nécessaire.

• Pré-dimensionnez-les également en fonction de votre charge de travail

Initialisation Instantanée des Fichiers - IIF

• Empêche les fichiers de base de données d'être "réinitialisés" ou "remplis de zéros" lors de leur création et de leur croissance

Pourquoi est-ce utile ?

• Rend la création et la croissance des fichiers de base de données plus rapides

• Créer des fichiers de base de données plus rapidement signifie que la reconstruction de TempDB est plus rapide.

• Se produit avec le redémarrage du serveur ou le redémarrage des services SQL, potentiellement

accélérer les basculements de cluster

• augmentation de performance

• N'affecte pas les fichiers journaux, s'applique uniquement aux fichiers de données

• Ne fonctionne pas avec les bases de données qui utilisent le chiffrement des données transparent (TDE) et quelques autres.

autres règles

DML et DDL

• DML–Langage de Manipulation de Données

• SÉLECTIONNER

• DDL – Langage de Définition de Données

• CRÉER
Principes fondamentaux de la sauvegarde et de la restauration

Modèles de récupération

• Complet - permet la récupération à un instant donné pour toutes les transactions et nécessite T-Log
maintenance
• Opérations enregistrées au minimum - simples
• Enregistré en masse - Les opérations en masse ne sont pas enregistrées, mais les autres le sont, récupération à un instant donné
sauf pour les opérations de chargement en bulk, nécessite un entretien du journal de transactions

• La meilleure pratique est de réaliser une sauvegarde du journal des transactions lors du passage entre le chargement en masse et la sauvegarde complète.

modèles de récupération

Types de sauvegarde

• Complet - effectue une sauvegarde de toutes les pages de la base de données et les "marque" comme sauvegardées.

• Différentiel - sauvegarde de toutes les pages de base de données qui ont changé depuis la dernière sauvegarde complète

sauvegarde
• T-Log - effectue une sauvegarde du journal des transactions ; nécessite qu'au moins une sauvegarde complète ait été effectuée.

fait à l'avance
• FileGroup - effectue une sauvegarde de groupes de fichiers spécifiques

• Fichier - sauvegarde un fichier de base de données spécifique

Options de sauvegarde

• COMPRESSION
• COPIE_UNIQUE (à utiliser lorsque vous avez besoin d'une sauvegarde pour le développement - pas dans la chaîne-)

• DIFFERENTIEL
• MIROIR À
• X
• NOINIT | INIT (Ajoute aux sauvegardes - Écrase)
• CRYPTAGE (SQL 2014+ et supérieur)
Options de restauration

• AVEC RÉCUPÉRATION
• VÉRIFIER UNIQUEMENT - vérifie que la sauvegarde est correcte
• HEADERONLY - affiche une liste de sauvegardes sur votre fichier de sauvegarde
• FILELISTONLY - affiche la liste des fichiers de base de données et des fichiers journaux sur votre fichier de sauvegarde (Logique &
Nom physique, groupe de fichiers et d'autres choses formidables)

Restauration de Séquence

1. Prenez une sauvegarde de votre journal actif/de suivi

2. Restaurez votre sauvegarde complète (NoRecovery)

3. Restaurer votre dernier Diff (si disponible - Pas de récupération)

4. Restaurez tous vos journaux de transactions (NoRecovery)

5. Restaurez votre dernier T-Log le "Tail" (Récupération)

RTO, RPO et votre CIO

• RTO - Objectif de Temps de Récupération

• Combien de temps vous faudra-t-il pour vous remettre après une catastrophe ?

• RPO - Objectif de Point de Récupération

• Combien de données êtes-vous prêt à perdre ? Ou combien pouvez-vous récupérer ? Ou à quelle fréquence ?
Vos données changent-elles ?
• CIO - La personne qui vous donnera le licenciement si vous échouez dans cela !

DR contre HA

• Récupération après sinistre

• Par exemple :
• Miroir, expédition de journaux vs. sauvegardes
• Groupes de disponibilité vs. Sauvegardes
• Regroupement vs. Sauvegardes

Code de sauvegarde de base

Sauvegarder la base de données [DatabaseName]

VERS [Disque/Dispositif] = N'NomOuChemin'
AVEC [OPTIONS]

-------------------------------
Sauvegarde de la base de données DBTest1

VERS Disque = N'C:\Sauvegardes\DBTest1_01012013.bak'

AVEC COMPRESSION, COPIE_SEULE

--------------------------------
Sauvegarder la base de données DBTest1

À disque = N'C:\Backups\DBTest1_DIFF01012013.bak'
AVEC DIFFÉRENTIEL
Code de sauvegarde des journaux

Journal de sauvegarde DBTest1

À Disk = N'C:\Backups\DBTest1_Log01012013.trn'

Code de restauration de base

RESTORE DATABASE [DBName]

DE [Disque/Appareil] = N'NomOuChemin'
----------------------------------------

RESTORE DATABASE DBTest1

DE DISQUE = 'C:\Backups\DBTest1_01012013.bak‘
----------------------------------------

RESTORE DATABASE DBTest1

DE DISQUE = 'C:\Sauvegardes\DBTest1_01012013.bak'
SANS RÉCUPÉRATION

RESTORE LOG DBTest1

DE DISQUE = 'C:\Backups\DBTest1_Log01012013.trn'

Sécurité SQL Server

Aperçu de la sécurité de SQL Server

• Principes - Objet qui est authentifié et auquel on accorde l'accès

• Système d'exploitation - Connexions Windows

• Instance
• Base de données
• Objets sécurisables - objets qui se voient accorder un accès à

• OS - AUCUN
• Instance - DBs
• Base de données - Tables, vues, schémas, procédures, etc.

Les responsables ont accès à ce qui peut être sécurisé

Ports
Géré avec Configuration Manager
• Moteur de base de données SQL - 1433 TCP

• ADMIN dédié (DAC) : 1434 TCP

• SQL Server Browser 1434 UDP
• SSAS–2383 TCP
• MSDTC et SSIS via SSMS - 135 TCP
• Miroir/Groupes de disponibilité 5022
 • Également nécessaire pour le clustering de basculement

Les instances nommées de SQL Server utilisent par défaut des ports dynamiques, mais cela peut être modifié.

Connexions au serveur

• Sécurité au niveau du moteur de base de données ou de l'instance

• Peut être de deux sortes :

• Connexion SQL Server (Fonctionne uniquement dans SQL)

• Connexion Windows

Rôles de serveur

• Rôles de serveur personnalisés - SQL 2012+ (octroi=accès autorisé, refus=aucun accès, avec
peut donner accès à un autre utilisateur
• Les connexions de serveur sont associées aux rôles de serveur pour les autorisations

• Les rôles permettent de donner des permissions en fonction de la fonction d'emploi.

Déclencheurs de connexion

• Déclencheur de serveur qui se déclenche lors de la connexion

• Pour les restrictions de temps d'audit ou de connexion

Schémas de base de données

• Conteneur logique et propriétaire d'objets tels que des tables, des fonctions, des stockés
procédures
• Peut créer des schémas pour regrouper des objets similaires qui appartiennent à différents
départements par exemple RessourcesHumaines, Ventes, etc

• Peut donner des autorisations au niveau du schéma, ce qui aide à séparer les autorisations.
davantage
• Les schémas sont également utiles dans les environnements d'entrepôt de données où vous voulez
garder
Utilisateurs de la base de données

• Une connexion au niveau de la base de données

• Doit être mappé à un identifiant de serveur sinon l'utilisateur DB est considéré

Orphelin
Rôles de base de données

• Identique aux rôles de serveur mais au niveau de la base de données

• Vous pouvez toujours créer des rôles personnalisés

Langage de Contrôle des Données (DCL)

• GRANT–donne à un principal accès à un objet sécurisé

• REVOQUER – retire les autorisations accordées à un principal sur un élément sécurisable

• REFUSER – refuse l'accès à un principal sur un sécurisable ; cela annule tout GRANT

• AVEC OCTROI - permet au titulaire d'accéder à un objet sécurisé et permet au titulaire

permissions pour ACCORDER l'accès à d'autres principaux. Soyez prudent avec cela !

Orphelinat SQL
• Les identifiants SQL Server ont des SIDs (Identifiant de Sécurité)

• Ce SID est créé lorsque le compte est créé et est unique sur chaque serveur
parce qu'il est généré aléatoirement

• Des problèmes surviennent lorsque vous déplacez des bases de données vers d'autres serveurs car elles peuvent

a déjà le même identifiant SQL, ou s'il est créé, il aura un SID différent
• Donc, l'utilisateur de la base de données n'est plus associé à la connexion

Correction des utilisateurs orphelins

• Utiliser sp_change_users_login

• Utilisez sp_help_revlogin - pour de nombreux connexions qui doivent être corrigées, particulièrement utile
pour les migrations

Chaînage de propriété croisée de base de données

• Désactivé par défaut

• Peut être activé au niveau de la base de données ou de l'instance

• Lorsqu'un objet est accédé par le biais d'une chaîne, SQL Server compare d'abord le
propriétaire de l'objet au propriétaire de l'objet appelant. C'est le lien précédent dans
la chaîne. Si les deux objets ont le même propriétaire, les autorisations sur l'objet référencé
les objets ne sont pas évalués.

Sécurité de la base de données contenue

• L'utilisateur de la base de données n'a pas de connexion sur le serveur et ne peut donc pas avoir d'utilisateurs orphelins.
 • Une base de données est considérée comme une entité de travail indépendante qui est "contenue" et
séparer du serveur
**Avertissement**
• Les utilisateurs de la DB contenue auront accès aux bases de données avec l'utilisateur invité activé

Haute Disponibilité et Récupération après Sinistre

• Réplication (Niveau de table)

• Expédition de journaux (niveau DB)
• Miroir de base de données (niveau de la base de données)

• Clustering de basculement toujours actif (Niveau serveur)

• Groupes de disponibilité Always-On (groupe de niveaux de bases de données)

• SQL Azure (niveau Azure)

[Link]

Types de réplication
• APERÇU : réplique l'ensemble des données
• TRANSACTIONNEL : commence à partir d'une réplication instantanée et au fil du temps
réplique uniquement les données modifiées

• LA FUSION : commence par une réplication instantanée, puis chaque base de données est mise à jour.
séparément jusqu'à ce que les bases de données soient comparées et que les différences entre elles soient

transféré

Réplication Transactionnelle (niveau de table)

La réplication transactionnelle est la distribution périodique automatisée des changements entre
bases de données. Les données sont copiées en (ou près de) temps réel à partir du serveur principal (éditeur) vers
la base de données de destination (abonné). Ainsi, la réplication transactionnelle offre une excellente
sauvegarde pour les modifications fréquentes des bases de données quotidiennes.

Article : unité de base de la réplication SQL Server. L'article peut se composer de tables, de procédures stockées.
procédures et vues.
Publications : Une publication est une collection logique d'articles.
L'éditeur : est une instance de base de données qui rend les données disponibles à d'autres emplacements via
Réplication SQL Server à répliquer
Distributeur : transfère une publication.

Agent distributeur :
Abonné : Une instance de base de données qui reçoit des données d'une publication.

Réplikation par poussée : le distributeur pousse la publication au souscripteur

Réplication par tirage : Le souscripteur tire la publication du distributeur

Types de publication :
Publication instantanée :
L'éditeur envoie un instantané des données publiées aux abonnés à des intervalles programmés.

Publication transactionnelle :
L'Éditeur diffuse des transactions aux Abonnés après qu'ils ont reçu un instantané initial de
les données publiées.

Publication Pair-à-Pair
La publication Peer-Peer permet une réplication multi-maître. L'éditeur diffuse des transactions à tous
les pairs dans la topologie. Tous les nœuds pairs peuvent lire et écrire des modifications et les modifications sont
propagé à tous les nœuds de la topologie.

Fusionner la publication :
Le Publisher et les Abonnés peuvent mettre à jour les données publiées indépendamment après le
Les abonnés reçoivent un aperçu initial des données publiées. Les modifications sont fusionnées périodiquement.
Microsoft SQL Server Compact Edition ne peut s'abonner qu'aux publications de fusion.

Miroir de base de données

La mise en miroir de bases de données SQL Server est une technique de récupération après sinistre et de haute disponibilité qui implique
deux instances SQL Server sur la même machine ou sur des machines différentes. Une instance SQL Server agit comme un
L'instance principale est appelée le principal, tandis que l'autre est une instance miroir appelée le miroir.
Dans des cas spéciaux, il peut y avoir une troisième instance SQL Server qui agit en tant que témoin.
L'utilisation de la mise en miroir de base de données SQL Server présente plusieurs avantages : une fonctionnalité intégrée de SQL Server,

relativement facile à configurer, peut offrir un basculement automatique en mode haute sécurité, etc. Base de données
la mise en miroir peut être combinée avec d'autres options de récupération après sinistre telles que le clustering, le transport de journaux,

et réplication

Concept Toujours Actif

• En termes simples, c'est un terme marketing qui englobe deux technologies similaires :

• Clustering de basculement

• Groupes de disponibilité

Groupes de disponibilité toujours actifs

• Permet une haute disponibilité / récupération après sinistre d'un groupe de bases de données définies comme
un « Groupe de Disponibilité »

• Permet l'extension des bases de données dans l'AG

En quoi cela diffère-t-il du clustering de basculement ?

• Ne nécessite pas de stockage partagé

• Les bases de données sont dupliquées, donc l'empreinte de stockage augmente ; mais aussi
fournit une autre copie de la base de données

• Chaque serveur a des bases de données système séparées et une sécurité différente.

• Chaque serveur peut être accessible séparément

• Les répliques peuvent être rendues "Lecture-Seule" pour décharger les demandes de lecture (proche de la charge)

Équilibre
• Réparation automatique de la page

Pré-requis
• Édition Entreprise SQL - cela peut changer avec 2016

• Le port TCP 5022 doit être ouvert

 • Les serveurs doivent être dans un domaine

• Les serveurs doivent faire partie d'un WSFC

• SQL 2012+ et les groupes de disponibilité doivent être activés dans le gestionnaire de configuration SQL

• Toute base de données participante doit être en mode de récupération intégrale

• Doit passer la validation de cluster en exécutant TOUS les tests **

Clustering de basculement toujours actif

• WSFC–Clustering de basculement Windows Server

• Nœud – Serveur physique/virtuel faisant partie d'un cluster

• Fondamentalement, un morceau de "matériel" qui alimente les services

• FCI–Instance de Cluster de Basculement (SQL installé en tant que service dans le cluster)

• Option de haute disponibilité pour les services

• Les services sont hébergés dans un WSFC et un noeud particulier du cluster fournit
les ressources matérielles pour ces services

• Nécessite un stockage partagé

• SQL 2012+ permet de placer TempDB sur un stockage local

• Permet une protection au niveau de l'instance (Haute Disponibilité)

• Peut être fait entre les sites (Multi-Subnet)

• Peut héberger plusieurs FCI, peut être combiné avec des groupes de disponibilité

• Les bases de données ne sont pas protégées, elles sont sur un stockage partagé et utilisées par le FCI.

• Une instance ne peut être hébergée que sur un seul nœud à la fois ; ce n'est pas du répartition de charge.

Utilisations populaires du regroupement

• Virtualisation avec Hyper-V

• Rendre le stockage hautement disponible

• SQL Server