Plan de relève en cas de désastre pour les bases de données sur SQL Serveur

La haute disponibilité est une mesure du temps que cela prendra pour revenir à vos opérations régulières suite à un incident imprévu causant un désastre. Une planification stratégique et une documentation complète sont requises afin de surmonter ce défit. Si votre plan de relève échoue, cet incident pourrait devenir une catastrophe pour votre entreprise. Hugo Shebbeare discute de tous ces détails avec un exemple typique fournissant aussi tout le code et toute la documentation nécessaire pour éviter que cela arrive à vos opérations respectives.

Dans cet article, j’explique les enjeux techniques pour l’implantation d’un plan de relève en cas de désastre (PRD) pour vos systèmes de production. Le but de ce plan est de vous fournir du code générique ainsi qu’un gabarit de documentation afin que vous puissiez créer votre propre stratégie de plan de relève.

J’étale les étapes à suivre après un désastre dans vos environnements de production, et note les démarches tout au long du processus.  Ces étapes sont basées sur un plan de relève que j’ai dû créer récemment (disponible pour téléchargement afin que vous puissiez le personnaliser pour vos systèmes). De plus, ces étapes sont décrites dans le style d’un document d’affaires tactique. Je vous concède que ce sujet peut être considéré légèrement ardu et/ou inintéressant, mais je vous promets qu’il vaut la peine d’être lu.

La première partie décrit les étapes de base nécessaires afin de préparer un serveur de relève (instance en mode standby chaud ou bien, ‘hot standby’). La deuxième partie fournira un plan de relève annoté, incluant les étapes à prendre lors d’un désastre ainsi que de l’information pour les administrateurs chanceux qui auront la responsabilité d’exécuter le plan de relève afin de se sortir du dit désastre.:

1e partie – La restitution automatique des fichiers de sauvegarde vers un serveur de relève

Le mode de recouvrement de SQL Server norecovery conserve la base de données stable et prête à appliquer constamment les changements récents venant du processus automatisé de sauvegarde. Cela implique qu’il est nécessaire d’appliquer uniquement  la plus récente copie de sauvegarde différentielle ou du journal de transactions (log file) afin de rendre la base de données disponible à vos utilisateurs et vos applications.

La méthodologie de relève utilisée est de se servir d’une instance en mode standby, dorénavant appelée SQL2, qui est déjà installée, stable et une copie exacte de la configuration en production.  Le serveur de relève, SQL2, devrait déjà avoir les bases de données opérationnelles critiques restaurées au complet en mode de recouvrement norecovery.

L’Implantation du serveur de relève

Suite à l’installation de SQL Server sur l’instance de relève, vous devez premièrement vous assurez que Robocopy existe dans le dossier sysroot\Windows\system32. Deuxièmement, assurez-vous que le logiciel SQL Backup de Red Gate soit correctement installé et connecté au serveur de relève en cliquant sur le petit bouton gris à coté du serveur dans la liste des serveurs à gauche de la fenêtre du logiciel.  Cliquer sur ce petit bouton installera et configurera automatiquement SQL Backup si cela n’est pas déjà fait.

791-SQB_installingservercomponents.gif

Image 1 – le système automatisé de configuration de SQL Backup.

Robocopy est le meilleur outil intégré utilisé par Windows, car Xcopy disparaîtra sous peu, confirmé par le fait que depuis Windows Server 2003, Robocopy est l’outil de copie recommandé.  Selon ce que je comprends de ces gestes, Xcopy ne sera plus disponible dans les prochaines versions du système d’exploitation Windows.

Ensuite, en ce qui concerne les procédures stockées qui exploitent Robocopy (nous allons placer ces procédures dans une base de donnée qui s’appel DBA_outils), nous devons permettre la configuration avancée xp_cmdshell à exécuté :

Afin de faire la copie des fichiers de sauvegarde, chaque base de données sur le serveur de relève (SQL2) devrait avoir son propre processus automatisé (local SQL Server Agent Job) qui roule Robocopy à l’heure convenue, et qui copie la sauvegarde au complet ou différentielle du serveur en production (SQL1) vers SQL2.  La cédule de ces processus automatisés peut être exécutée à la fréquence désirée et/ou étalée selon vos besoins.

L’exécution de Robocopy est la première étape à exécutée dans le processus automatisé par l’agent de SQL, à moins que vous désiriez ajouter une étape de validation de l’existence des fichiers de sauvegarde courants avant que Robocopy effectue sa copie. Voici un exemple qui démontre comment copier des sauvegardes de SQL1 vers SQL2.

La restitution des fichiers de sauvegarde sur le serveur de relève se fait par l’agent SQL avec un processus mis en place pour chacune des bases de données qui appelle les procédures stockées suivantes qui ont été crées spécifiquement pour ce plan de relève, tel que :

  • usp_DB_Restore_Master ou usp_DB_Restore_Master_Multi
  • usp_DB_Restore
  • usp_DB_Restore_NoRecovery
  • usp_DB_Restore_differential
  • usp_DB_Restore_Log

Considérations à prendre par l’analyste de la base de données (DBA) concernant le niveau de recouvrement
Si vous utiliser actuellement le mode de recouvrement Simple, et que vous faites régulièrement vos sauvegardes des fichiers de journaux de transactions et différentielles (tel que plusieurs fois par jour), vous pouvez changer le mode de recouvrement à Bulk-Logged en production afin de permettre la restitution jusqu’à un point spécifique dans le passé. Cette modification minimisera la perte de données lors de désastre. Les vrais DBAs vous diront qu’il faut aimer les fichiers de journaux de transactions pour exécuter un plan de relève comme le mien.

Le modèle de recouvrement FULL [recovery] est recommandé pour vos bases de données les plus critiques, et qui requièrent une attestation d’audit.

Dans le cas d’un désastre, le plus récent fichier du journal des transactions (Transaction Log File) ou de sauvegarde différentielle est prêt à être appliqué sur la base de données en relève, restée en mode de recouvrement NORECOVERY, et voilà vous êtes prêts à revenir à vos opérations normales, avec un temps de panne minimal.

Une autre méthode pour une base de données en petite taille, c’est-à-dire avec un temps de restauration de moins de cinq minutes, est d’appliquer la restitution complète chaque heure vers le serveur de relève. Dans cet exemple, la nécessité de garder le mode norecovery n’est pas avantageux.

2e partie – Les étapes à suivre lors d’un désastre en production

  1. Contactez le DBA du support en première ligne (insérez numéro de téléphone/pagette/mobile) ou le DBA secondaire à (insérez numéro).
  2. Suite à la panne du serveur en production principal (SQL1), le serveur de relève (SQL2) devient le serveur principal de base de données. Avisez toutes les personnes impliquées de ce changement par courriel (départements internes ainsi que les clients externes si nécessaire). Ces personnes devraient déjà y être préparées
  3. Dès que le serveur de relève (SQL2) est prêt à accepter les connexions, et que SQL1 (ancien serveur principal) est bel et bien mort, changez tous les liens applicatifs vers SQL2.  Si vos applications ont un fichier de configuration pour effectuer ce genre de changements vers le serveur SQL2, il est recommandé de tester cette configuration, par exemple pendant une fin de semaine, avant de terminer l’implantation de ce plan.
  4. Désactivez vos processus automatisés de restitution des sauvegardes sur SQL2.
  5. Désactivez vos processus automatisés de sauvegardes sur SQL1 (si possible).
  6. Activez tous les processus automatisés de maintien et de sauvegarde (essential Jobs) sur le serveur de relève qui héberge les bases de données principaux.

Please note that restoring a log backup is not possible if the production database recovery model is set to Simple. For fine-grained restoration, the database needs to have been using the Full recovery model – Heureusement, le mode par défaut est Full Recovery. Lorsque l’espace de disque dur devient serré et qu’il faut réduire l’utilisation de fichiers journaux, le mode minimal pour revenir vers un moment spécifique est le mode Bulk-Logged.

Comment la restauration automatique des sauvegardes compressées peut-être bénéfique pour votre environnement de production. Idéalement, il faudrait conserver deux sauvegardes complètes, la première sur votre serveur de Rodage/Test et la deuxième sur serveur de relève.  L’existence de cette copie de production en relève permet d’effectuer des vérifications utiles et/ou des tâches intensives mais à éviter en production, comme par exemple une commande console DBCC CheckDB qui vérifie l’intégrité de la base de données en question.

La liste des fichiers restaurés devrait se trouver dans le répertoire suivant :

\\ServeurDeBD\Disque$\DirSauvegardesProd\DBlog\

Dès que la restauration a été complétée, nous devrions avoir un nettoyage automatique des vieux fichiers de sauvegarde grâce à un script PowerShell ou un fichier de commandes batch (Ce nettoyage devrait être fait au moins une fois par semaine en utilisant les plans de maintenance de SQL Server).

Afin de s’assurer un processus de restauration en douceur, nous devrions lire les paramètres de la restauration directement dans les tables méta données dans la base de données MSDB, tel que BackupHistory, BackupSet, BackupFile or BackupLog – à moins qu’une table BackupLog ne soit déjà explicitement créée régulièrement dans la base de données DBA_Tools (où les autres objets ci-dessous sont situés).  Ceci est toujours nécessaire en raison du besoin des paramètres essentiels disponibles (nom de la sauvegarde, et localisation sur disque dur) pour rouler ces procédures stockées avec succès.

Lors de la création des étapes dans les processus automatisées, j’écris assez souvent les paramètres manuellement durant le rodage et je les laisse tel quel – mais il est certain que la meilleure façon est de retirer les paramètres directement des tables méta données du système décrit ci-haut, car si on déplace les fichiers MDF, NDF ou LDF, il se peut très bien qu’on oublie de mettre à jour les étapes (en Anglais on l’appelle le futureproofing du script).

Configuration Ãquipement et de Serveurs Production et Relève (SQL1 & SQL2)

Voici une configuration typique pour les serveurs pour un projet de relève. Dans le cas qui nous intéresse, je ne me souviens pas exactement de toutes les spécifications des systèmes, mais cela ne veut pas dire qu’on ne devrait pas les enregistrer. Veuillez donc mettre à jour ces tables de spécifications selon vos propres équipements utilisés.

SQL1 (instance en production)

1.1

Système d’exploitation

Windows 2008 (standard ou édition x64)

1.2

Modèle de système

[Numéro de système, type de produit]

1.3

Mémoire physique

8 Giga-octets

1.4

Nombre de CPU physiques

2

1.5

CPU & vitesse

AMD (x64)

Disques durs

Espace disque

C(#Go); D(#Go)

 

SQL2 (serveur de relève)

1.1

Système d’exploitation

Windows 2008 (standard ou édition x64) – doit être le même que SQL1

1.2

Modèle de système

[Numéro de système, type de produit]

1.3

Mémoire physique

8 Giga-octets

1.4

Nombre de CPU physiques

2

1.5

CPU & vitesse

AMD (x64) Opteron Processor 280

Disques durs

Espace disque

C(#Go); D(#Go); F(2To); G(250Go); H (1.5To); Z(20Go)

Il faut que ce serveur soit bien préparé au niveau de l’espace disque, à moins que vos bases de données ne soient pas très volumineuses.

Configuration de l’instance SQL Serveur

Nos serveurs exploitent la version 64-bit de moteur de la base de données SQL Serveur 2005, avec la deuxième mise à jour applicative majeure appliquée (Service Pack 2) et la troisième mise à jour cumulatif aussi appliqué même si en ce moment SP3 et CU5 sont disponibles, nous avons réussi avec ses versions antérieures en production. Le type de collation est configuré à Latin1_General_CI_AS ou bien, SQL_Latin1_General_CP1_CI_AS – dans les deux cas, nous suggérons porter attention aux accents (AS=Accent Sensitive).  Bien sûr, si on refait cet environnement de relève maintenant, on profitera de Service Pack 3 avec la cinquième mise à jour accumulé installé sur nos instances en production – ce qui est fortement recommandé surement, est de mettre les derniers correctifs en production suite à son roulement avec succès en développement et test/pre-prod.

Les renseignements détaillés à propos de chacun des serveurs (SQL1 & SQL2) de la base de données inclus dans ce plan de relève est disponible dans un fichier aide compilé sur disque dur local. Par exemple:

D:\DRP\NomduServeur.chm (i.e.soit très facile à trouver)

Détails critiques à propos des bases de données usagers

1. Liste des bases de données

BD1

BD2

à noter: nous n’allons pas toucher les bases de données systèmes (master, msdb, model, ou temp), qui sont sauvegardées sur une base régulière et seront copiés par Robocopy, mais pas restaurés sur le serveur de relève directement.

2. Plan de maintien de la base de données et restitution automatique

En général, notre plan va reflété exactement la cédule des sauvegardes.

3. Plan de maintien de la base de données en production

Nom du processus d’automatisés

Description de processus

fréq.

heure cédulé

SauvgardeComplet_BD1

sauvegarde BD complet1

Hebdo

dimanche 6h

SauvgardeComplet_BD2

sauvegarde BD complet2

Hebdo

dimanche 6h30

4. Processus automatique sur le serveur de relève

Nom du processus d’automatisés

Description de processus

fréq.

heure cédulé

SauvgardeComplet_BD1

sauvegarde BD complet1

W

dimanche 6h

SauvgardeComplet_BD1

sauvegarde BD complet2

W

dimanche 6h30

Procédures et code critiques pour le bon fonctionnement de ce plan de relève

Ce qui suit est tout le code nécessaire pour vous puissiez profiter de ce plan de relève de SQL1 vers SQL2.

usp_DB_Restore_Master

usp_DB_Backup & usp_DB_Restore

usp_DB_Restore_NoRecovery

usp_DB_Restore_Differential

usp_DB_Restore_Log

usp_RoboCopy

usp_KillConnections

Sauvegardes des bases de donnés systèmes

Sur le serveur de relève lui-même les sauvegardes de MSDB, DBA_tools (ou se situe ce code de relève), qui sont critiques pour ce plan se retrouvent ici:

\\ServeurDeReleve:\DossierReleveSauvegardes\Complet

Il devrait y avoir toujours un dossier de sauvegarde local facultatif pour les bases de données systèmes, tel que sur votre serveur de pré-production/rodage finale.  Ceci dit, il est fortement recommandé de placer sur votre serveur de rodage les B.D. systèmes suivants:

\\ServeurDeRodage:\DossierReleveSauvegardes\Complet

L’exemple suivant a été rodé sur un serveur de test, et est présent sur le serveur de restauration. La procédure stockée usp_DB_restoreX reçoit 6 paramètres.  Afin de s’aligner avec les métadonnées de journal de sauvegardes, nous allons nous accorder avec le nom de la base de données par date et les paramètres appropriés, ensuite nous pousserons ces paramètres à la procédure stockée usp_DB_restoreX approprié.  Ces procédures centralisées sont divisés en deux types: celle qui reçoit les paramètres reliés aux métadonnées simples, et celle prête pour les BDs qui exploitent de multiples fichiers de données ou journaux de transactions. Employez toutes les procédures dépendantes pour faire la vraie restitution.

Il faut comprendre que usp_DB_RestoreX est dépendante sur usp_KillConnections qui aide le processus de restitution en fermant par force les connexions à la base de données (mais attention aux usagers systems SPID<50)

e.g.

La procédure stockée en question, usp_DB_restore_norecovery, est la même que usp_DB_restore, et elle est destinée pour les bases de données qui vont demeurer en mode norecovery (expliqué ci-haut déjà)

Veuillez lire l’historique (Activity History) de Red Gate SQL Backup en ce qui concerne les rapports de sauvegardes, puisque le scope de ce document n’est seulement qu’au niveau du processus de restitution.  Malgré le fait que les renseignements reliés aux sauvegardes sont retirés des métadonnées afin d’être mise dans les scripts automatisés à l’intérieur des processus (SQL Agent Jobs), nous n’allons pas créer (à cette étape, au moins) des rapports automatisés.

791-SQB_activityhistory.gif

Figure 2 – SQL Backup Activity Log

Méthode de restauration de la base de données quand vous appliquez les sauvegardes différentielles

Veuillez  noter que nous allons utiliser usp_restore_db_norecovery à charger la sauvegarde de production copiée localement par Robocopy.  Alors, si roulé par le DBA sur le serveur de relève en DBA_Tools:

Ceci est l’essentielle de ce qui roule lors de deuxième étape du processus (job) automatisé qui laisse la base de données en mode norecovery, et par la suite, on appellera la procédure ups_RestoreDiff_dbx et finalement, appliquer les fichiers de transaction  par  usp­RestoreLog_dbx.

Suite à la restauration, il faut s’assurer qu’une vérification (DBCC CHECKDB) se passe sans errreurs afin de préparer pour vos opérations normales sur la base de données.


Résumée du plan de relève

Est-ce que ce plan en cas de désastre, cette méthodologie, vas vraiment réduire l’intervention manuelle lors d’une catastrophe ?  Est-ce qu’on peut l’améliorer ? Oui, sûrement, il est toujours possible de parfaire et remanier un plan pour qu’il soit encore plus efficace.  N’hésitez-pas à me laisser vos commentaires et contributions. Ce qui est important, c’est de comprendre que cette méthode n’est pas la solution pour tous les environnements.  Avant de faire un copie/collé du code ci-haut pour vos opérations les yeux fermés, je vous recommande fortement de lire la grille Choix au niveau de la Haute Disponibilité ici-bas afin de bien saisir vos besoins individuels.  Naturellement, pour choisir la voie appropriée, il faudrait aussi faire une analyse profonde des attentes de vos clients.

Choix au niveau de la Haute Disponibilité

Solution

Coût

Complexité

Mise en Relève

Retour  

Hardware Clustering

Elevé

Elevé

Rapide

Rapide

Software Clustering

Elevé

Elevé

Rapide

Rapide

Replication

Moyen

Moyen

Moyen-avec procedure manuelle

Lent -avec procedure manuelle

Continuous Data
Protection

Moyen

Moyen

Moyen

Lent

Log Shipping

Faible

Faible

Moyen

Lent

Backup and
Restore

Faible

Faible

Lent

Lent

Database Mirroring

Faible

Faible

Rapide, restriction par BD individuelle

Rapide, restriction par BD individuelle

Personne ne veut être confronté à une situation de désastre sans la préparation nécessaire.

Lorsque la plus grande entreprise gestionnaire de fonds institutionnels au Canada, m’a demandé de préparer un plan de relève, j’ai pris cette tâche au sérieux, l’abondance de détails de ce document en est la preuve.

Nous avons faits nos propres essais durant une fin de semaine afin de simuler un désastre, et tout s’est déroulé sans problème (Dieu merci!).