Le thème du jour est cette fois orienté vers le "Disaster Recovery".
En gros, cela concerne les stratégies de backup/restore de DB ainsi que quelques outils.
Integrity Check
Avant de parler des nouveautés concernant les backups/restores, il faut ici signaler l'importance des checks d'intégrité.
Ceux-ci doivent être exécutés à interval régulier sur la DB afin de détecter au plus tôt les problèmes d'intégrités physiques (liés au stockage sur les disques).
DBCC CheckDB
Cette commande exécute les phases suivantes:
- dbcc checkalloc: le stockage physique des pages de données et des indexes.
- dbcc checktable: vérification des tables (et des contraintes comme les foreign keys).
- dbcc checkcatalog: vérification des méta-data.
Réparation d'une table:
alter database [dbx] set SINGLE_USER with rollback immediate
go
DBCC checkDB( 'DBX', REPAIR_REBUILD )
-- l'option REPAIR_ALLOW_DATA_LOSS peut également être utilisé.
go
alter database [dbx] set MULTI_USER
go
Backups
Sujet déjà bien connu, je ne vais seulement prendre note de quelques spécificités.
Stratégie de backup minimale pour un environnement de production
D'une façon générale, en production, il est préférable d'utiliser le recovery model FULL et d'effectuer un full backup la nuit et des transaction log backup à interval régulier (interval de 3 à 1 heure).
Pour information, il semblerait qu'un backup du log toutes les 5 minutes semble être excessif et pourrait même affecter les performances. Il faut déjà avoir un système sacrément charger pour en arriver là (en effet, la taille du transaction log dépend de l'activité).
Ce qu'il faut aussi backuper
Il semble évident de backuper les bases de données utilisateurs.
Cependant il est également
primordial de backuper certaines DB systèmes de façon régulière.
Par exemple:
- MASTER: (impératif) cette dernière contient toutes les informations relative à la sécurité/logins, la configuration du serveur sql, les linked server, backup devices, triggers serveur, end-points. Cet également cette db qui maintien un enregistrement par base de donnée disponible sur le système.
Pour restaurer une master db, il faut démarrer l'instance du service sql serveur en 'single user mode'.
- MSDB: (impératif) contient le detail et historique des backups, les backups set, les jobs automatisés, etc. Cette DB est donc importante si l'on désire restorer rapidement une DB (surtout si la stratégie de backup est plus complexe... incluant des backups de filegroup ou transaction log ou encore differential). Donc, a ne pas négliger.
- MODEL: (optionnel) seulement si l'on définit des modèles.
- DISTRIBUTION: (optionnel) contient la configuration et l'état des différents processus de réplication. Le backup de cette DB n'est nécessaire que si l'on utilise la réplication.
Nouveauté du full backup
Depuis SQL 2005, le Full backup inclus un backup du transaction log (en fin de processus de backup).
Cela permet d'avoir un backup englobant les transactions éffectuées durant l'exécution du backup lui-même.
Backup with COPY_ONLY
Lorsque l'on restore une DB, sql serveur lit l'historique des backups. A partir de cet historique, sql serveur décidera quel média sera nécessaire pour restauré la DB. C'est pratique mais require d'avoir ces médias sous la main.
Cette situation est par contre génante si un full backup doit être fournir à une tierce parti... car en effet, ce backup est également repris dans la liste des backups mais plus à disposition physique.
Les transactions log backups se référent au dernier full backup, le full backup en question sera réclamé lors d'une restoration de DB. Mais quid si ce full backup est chez une tierce partie!!!
La solution est de faire un full backup with COPY_ONLY. Dans ce cas, le full backup n'apparaît pas dans la liste/historique des backups.
Differential backup
Enregistre dans le backup devise les data pages (de 8 kb) qui ont changées depuis le dernier full backup.
Un différential backup ne se réfère jamais à un autre différential backup mais toujours à un full backup.
Pour information, un transaction log backup se réfère toujours au dernier full ou différential backup connu.
Vérification des backups
- Mirrored Device: il est maintenant possible de dupliquer directement (en cours de backup) un backup sur deux médias distincts. Les médias doivent être identiques d'un point de vue matériel et performance. Cela assure une redondance des backups.
- Compressed backup: sql serveur 2008 permet de compresser les backups au vol.
- Checksum: une option permet d'écrire un checksum sur les pages écrites dans le backup. Cela permet de vérifier l'intégrité du backup.
- Backup verification: un option permet de vérifier si un backup donné peut être restoré sur le serveur sql (place disponible sur le disque, disponibilité des répertoires de destination, disponibilité des backups sur les devices, vérification de sécurité).
- Integrity of backup: la commande RESTORE VERIFY_ONLY permet de vérifier l'intégrité physique d'un backup (et donc de s'assurer qu'il puisse être restauré).
Restore
Aussi un sujet connu, il y a cependant du nouveau pour moi.
Online Restore
par défaut les opérations de restoration sont ONLINE (autant que cela est possible).
La DB reste donc disponible aux utilisateurs. Lors d'opérations de restoration se faisant en plusieurs opérations (Full+TrnLogs restore), il faut indiquer l'option
WITH NORECOVERY aux étapes intermédiaires.
Restore d'une data page (**)
La partie la plus intéressante de cette section fut certainement la restoration d'une seule data page corrompue dans une table (sans la restoration complète de la DB).
Une data page relative a une table était corrompue empêchant l'accès à cette seule table (le restant de la DB étant opérationnelle).
Dans ce cas, la restoration de cette seule data page (ou quelques autres en plus) permet de garder la db de production en ligne sans devoir la mettre offline pour entamer une longue phase de restoration complète (pouvant prendre plusieurs heures).
Par ailleurs, la restoration de la page corrompue durant le training n'a pris que quelques minutes.
La restoration d'une data page se fait avec les étapes suivantes:
- Identifier les pages suspectes à l'aide DBCC checktable ou encore
Select * from MSDB.dbo.suspect_pages
- Restorer les pages suspected
restore database AdventureWorks
Page = '1:610' -- can be a comma separated list
from disk = 'c:\...\myBackup.bak'
with NORECOVERY
- Backup des dernières opérations du transaction log (Tail-Log)
backup log AdventureWorks
to disk = 'c:\..myLog.bak'
- Restorer le log applicable à la page restaurée (sql serveur filtre automatiquement les operations dans le transaction log pour ne retenir que les opérations relatives à la data page restorée).
restore log AdventureWorks
from disk = 'c:\...\awlog.bak'
with recovery -- put back the DB online
- Update the suspect_pages table to remove the entries (this is not automatically done by Sql Serveur)
delete from MSDB.dbo.suspect_pages
where .... -- identification of the pages
Quelques requêtes utiles
-- Faire un mirrored backup sur deux devices.
-- Stats 10: permet de recevoir une info tous les 10%
-- checksum: Inclus un checksum dans les data pages du backup
backup database
to BackupDev
Mirror to MirrorDev
with format, checksum, stats 10
-- Afficher le contenu d'un backup
restore filelistonly from BackupDev
-- Vérifie si la db peut être restorée
-- Vérifie le checksum, la présence des fichiers de backup,
-- target directories, l'espace disponible pour la restoration
restore verifyonly from BackupDev with checksum
Des informations complémentaires concernant les backups peuvent être lus directement depuis des table de la MSDB.
select * from MSDB.dbo.backupset
-- endroit ou les fichiers de backups ont étés stockés
select * from MSDB.dbo.backupfile
security
Pas grand chose a noter concernant la création des login et users et privilèges sur les DB.
Si le management console de sql permet d'assigner facilement des privilèges à un utilisateur, cette même console ne permet pas d'en obtenir une liste via la "table valuated function"
fn_my_permissions.
Voici donc une méthode TSql permettant d'en tirer la liste.
-- obtenir mes privilèges au niveau sql serveur
select * from fn_my_permissions( NULL, 'SERVER' )
-- mes privilèges sur l'objet LOGIN pour le login SA
select * from fn_my_permissions( 'SA', 'LOGIN' )
-- pour connaitre les privilèges d'autres utilisateurs
-- modifier le contexte d'impersonification pour
-- executer fn_my_permissions sous le "login" de cet
-- utilisateur (réservé a SA).
execute as login = 'miami\testuser'
-- voir les privilèges pour un object particulier
select * from fn_my_permissions( 'HumanResources.Employee', 'OBJECT' )
order by subentity_name, permission_name
-- annuler l'impersonnification
revert
Encryption DB
Sql serveur 2008 dispose d'une fonctionnalité permettant d'encrypter une base de donnée de façon transparente.
Authentification avec certificat
Il est également possible de faire générer des certificat par un serveur sql S1.
Ce certificat servant a signer, par exemple, une stored procedure usp1 lisant le contenu d'une table Tl2 sur le serveur sql S2.
Ce certificat peut est exporté de S1 et importé dans S2. Cela permet de définir dans S2 un login UsrS2 basé sur ce certficat. Cela permettra a S1 d'authentifier des appels/connections en direction du serveur S2 (même via internet).
Lorsque UsrS2 disposera du privilège d'authentification (att: c'est important) et de l'accès sur la table Tbl2. La stored procedure usp1 de Sql1 pourra accéder librement aux données de la table T2 sur le serveur S2 (en étant authentifié sur S2 comme UsrS2).
