[SQL Server] Mise-en-place du log shipping (via SSMS)

Ce billet traite de la mise-en-place d’une solution de log shipping via SQL Server Management Studio (SSMS).

Pour comprendre le fonctionnement du log shipping, référez-vous au billet [SQL Server] Un peu de théorie sur le log shipping.

Prérequis général du log shipping

Pour mener à bien la mise-en-place du log shipping, il faut surtout que :

  • Les comptes exécutant le service SQL Server (que ce soit du côté du serveur source et de celui (ceux) de destination) doivent pouvoir avoir accès au dossier de partage (à créer et) dédié aux sauvegardes transactionnelles copiées.
  • Le SQL Agent doit être en cours d’exécution sur les serveurs contenant les bases concernées par une session de log shipping. En effet, c’est le SQL Agent qui exécutera les jobs de sauvegardes transactionnelles, de copie (envoi) et de restauration.
  • Les bases de données concernées par le log shipping doivent avoir le mode de récupération FULL ou BULK-LOGGED.
  • Désactivez tout job de sauvegarde transactionnelle pour éviter toute rédondance susceptible de perturber le log shipping.
  • La base principale et sa (ses) base(s) secondaire(s) aient l’édition Datacenter (à partir de SQL Server 2008 R2), Enterprise, Standard, Web ou Workgroup de SQL Server.

Modus operandi de mise-en-place du log shipping

Contexte et opérations préliminaires

Supposons :

  • Qu’on possède 3 serveurs :
    • SRV-SQL01, comme serveur principal.
    • SRV-SQL02, comme serveur de monitoring du log shipping.
    • SRV-SQL03, comme serveur secondaire.
  • Qu’on ait pour nom de domaine, IFC.
  • Qu’on veuille envoyer les journaux de transactions d’une base de données nommée IFC_SUITE située sur le serveur SRV-SQL01 vers une base secondaire homonyme située sur SRV-SQL03.
  • Qu’on souhaite rafraîchir la base de données secondaire toutes les 30 minutes à partir des derniers journaux de logs de la base de données principale.
  • Qu’on souhaite supprimer les journaux transactionnels vieux de plus de 2 jours, aussi bien du côté du serveur de la base principale que celui de la base secondaire.
  • Qu’on souhaite laisser la base de données en mode veille (stand by) de façon à ce qu’elle puisse être accessible en lecture.
  • Qu’on dispose déjà d’un login SQL de connexion appelé pluginrtms sur le futur serveur moniteur (SRV-SQL02), et qui possède un mot-de-passe qui n’expire jamais.

Avant toute configuration du log shipping effectuez les opérations préliminaires suivantes :

  • Création et partage de dossiers :
    • Sur le serveur principal (SRV-SQL01), créez un dossier LSBackup_IFC_SUITE et partagez-le en attribuant, notamment les droits nécessaires (lecture/écriture) au compte de service de l’agent SQL des instances SRV-SQL01 et SRV-SQL03. Ce dossier sera destiné à accueillir les sauvegardes transactionnelles de la base principale IFC_SUITE.
    • Sur le serveur secondaire (SRV-SQL03), créez un dossier LSCopy_IFC_SUITE et attribuez-lui les droits nécessaires pour le compte de service de SRV-SQL01 si ce n’est déjà fait autrement ou non. Ce dossier sera destiné à accueillir les sauvegardes transactionnelles copiées à partir de LSBackup_IFC_SUITE.
  • Sauvegarde complète (i.e., génération d’un fichier ifc_full_backup.bak)
    de la base principale IFC_SUITE sur le serveur principal SRV-SQL01 (supposons, sur S:\SQL Backups) :
USE MASTER
GO
BACKUP DATABASE IFC_SUITE
TO DISK=N’S:\SQL Backups\ifc_full_backup.bak’
GO
  • Copie (manuelle) de la sauvegarde complète de la base principale sur le serveur SRV-SQL03 (supposons, sur : S:\SQL Backups)
  • Sur le serveur secondaire, création d’une base vide IFC_SUITE.
  • Sur le serveur secondaire, restauration de la base secondaire en mode veille et à partir de la sauvegarde complète copiée :
USE MASTER
GO
RESTORE DATABASE IFC_SUITE
FROM DISK=N’S:\SQL Backups\ifc_full_backup.bak’
WITH REPLACE,STANDBY=N’S:\SQL Backups\ifc_full_backup.bak’
GO

Sur le serveur secondaire, vous pouvez vérifier l’état de la base secondaire restaurée en mode veille :

Configuration du log shipping

Après avoir réalisé les différentes opérations préliminaires, respectez les étapes suivantes sur le serveur principal pour la mise-en-place du log shipping :

  • Accès au menu contextuel relatif au log shipping pour la base principale :

  • Au sein de la fenêtre de configuration du log shipping, accès aux paramètres de sauvegarde :

  • Configuration des paramètres de sauvegarde des journaux de transaction de la base principale (avec 2 jours de rétention) :

    Il s’agit, ici, de fournir le chemin d’accès réseau du dossier de partage des sauvegardes transactionnelles et qui est présent sur le serveur principal, ainsi que son chemin d’accès local sur ledit serveur.

    Pour les alertes en cas de non-sauvegarde, il est recommandé de paramétrer une valeur valant 3 fois celle relative à la fréquence des sauvegardes afin de laisser une bonne marge au job d’alerte.

  • Avant validation, planification de la fréquence des sauvegardes (toutes les 30 minutes) :

  • Accès aux paramètres de la base secondaire (bouton Ajouter dans la fenêtre principale) :

  • Après connexion à l’instance de la base secondaire, choix du mode d’initialisation de cette dernière :

    Comme la base secondaire a déjà été restaurée, on laisse Non, la base de données secondaire est initialisée sélectionnée.

  • Configuration de la copie des sauvegardes transactionnelles de la base principale vers le serveur de la base secondaire (onglet Copier les fichiers) :

    On prend bien le soin de choisir le chemin d’accès destiné à accueillir les sauvegardes transactionnelles de la base principale à copier. Et on prend également bien le soin de paramétrer la durée de vie des fichiers à 48 heures (ou 2 jours).

    Pour la fréquence de la copie, on peut se permettre de la laisser telle quelle (i.e., toutes les 15 minutes) de façon à ce qu’une certaine marge de manœuvre (15 minutes) soit laissée entre le moment du lancement d’une sauvegarde transactionnelle sur le serveur principal, la copie de ladite sauvegarde transactionnelle et, enfin, sa restauration.

  • Configuration de la restauration des sauvegardes transactionnelles sur la base secondaire (onglet Restaurer le journal des transactions) :

    Choisissez le mode En veille (Standby mode) afin de rendre la base secondaire accessible en lecture, et cochez la case relative à la déconnexion des utilisateurs lors de la restauration. Pour les alertes en cas de non-restauration, il est recommandé de paramétrer une valeur valant 3 fois celle relative à la fréquence des sauvegardes afin de laisser une bonne marge au job d’alerte.

    Sélectionnez ensuite Planification afin de planifier la fréquence de la restauration de la base secondaire (i.e., toutes les 30 minutes) :

  • Ajoutez un serveur de monitoring :
    • Accédez à la fenêtre de configuration du serveur de monitoring via clic sur le bouton Paramètres… après avoir coché sur Utiliser une instance du serveur moniteur :

    • Configurez le serveur moniteur en indiquant les informations d’authentification (un login SQL est plus recommandé), la durée de vie de l’historique de chaque travail de monitoring (5 jours), le tout, après vous être connecté à l’instance du serveur moniteur concernée (SRV-SQL02) :

    Le job d’alerte préviendra, à chacune de ses exécutions, si une sauvegarde ou une restauration n’a plus eu lieu depuis plus de 90 minutes (seuils configurés lors des étapes 3 et 8).

  • Validez définitivement les configurations effectuées pour le log shipping :

    Notez qu’avant toute validation, vous pouvez générer un script T-SQL contenant la configuration du log shipping effectué de façon à ce qu’en cas de nécessité (bouton Créer un script de configuration), vous puissiez l’exécuter pour le recréer rapidement :

    Un clic sur OK lance une boîte de dialogue de progression de la mise-en-place du log shipping. Et si tout va bien, le résultat sera le suivant :

Le job d’alerte LSAlert_SQL02 du serveur moniteur s’exécutant toutes les 2 minutes, vous pouvez le replanifier (via l’onglet Planification dans ses propriétés) de façon à ce qu’il puisse s’exécuter, par exemple, toutes les heures, ce qui serait plus correct.

Etat des jobs associés au log shipping

Du côté du serveur principal, il est possible de vérifier si le job de sauvegardes transactionnelles s’est bien déroulé en accédant notamment au moniteur d’activité des jobs du SQL Agent (ou en effectuant un clic-droit sur le job en question afin d’accéder à son historique de travaux):

Du côté du serveur secondaire (SRV-SQL03), 2 jobs sont créés : LSCopy_IFC_SUITE pour la copie des sauvegardes transactionnelles à partir du serveur principal SRV-SQL01 et LSRestore_IFC_SUITE pour la restauration des copies sur la base secondaire :

Au sein du serveur moniteur SRV-SQL02 se trouve un job qui vérifie le bon déroulement du log shipping dans son ensemble, en s’assurant que la dernière version de la base secondaire correspond contient les derniers enregistrements transactionnels restaurés via la dernière sauvegarde transactionnelle qui a été copiée sur SRV-SQL03 à partir d’SRV-SQL01 :

Ce job est réellement utile pour savoir si le log shipping se passe bien. En effet, ce n’est pas parce qu’une sauvegarde transactionnelle, sa copie ou même sa restauration se soit bien passée (du point de vue des jobs associés : LSBackup_IFC_SUITE, LSCopy_SRV-SQL01_IFC_SUITE et LSRestore_SRV-SQL01_IFC_SUITE) que tout va bien.

Par ailleurs, voici un petit exemple de message d’erreur affiché si LSAlert_SRV-SQL02 constate que la base de données secondaire n’a plus été restaurée depuis plus de 90 minutes (seuil défini durant la mise-en-place du log shipping vue plus haut, pour rappel) :

Exécuté en tant qu »utilisateur : IFC\SQL_Service. La base de données secondaire d’envoi de journaux SRV-SQL03.IFC_SUITE a un seuil de restauration de 90 minutes et n’est pas synchronisée. Aucune restauration n’a été effectuée depuis 1574 minutes. La latence de la restauration est de 30 minutes. Vérifiez les informations du journal de l’agent et du moniteur d’envoi de journaux. [SQLSTATE 42000] (…).

Pour plus de contexte :

Publicités

4 commentaires sur “[SQL Server] Mise-en-place du log shipping (via SSMS)

  1. Yannick dit :

    Bonjour !
    Déjà bravo pour ce post, très intéressant.

    J’ai juste une petite question : Une fois le log shipping mis en place, seul des sauvegardes des fichiers journaux sont créés.

    Ou et comment mettre une sauvegarde totale ? sur le serveur secondaire ?

    merci

    • Bonjour Yannick,

      Merci pour ton commentaire.

      Pour te répondre, une fois le log shipping mis en place, tu peux créer un job (ou un plan) de sauvegardes, via le SQL Agent, qui va lancer une sauvegarde complète. Ledit job doit être activé seulement pour la base principale, et donc sur le serveur primaire, vu que les bases en restauration (cas d’une base secondaire concernée par le log shipping) ne peuvent pas être sauvegardées.

      Au cas où tu souhaites effectuer une restauration à terme, il faudra noter que toute sauvegarde complète recrée un nouveau jeu de sauvegardes (à cause de la réinitialisation de la chaîne LSN). Donc toutes tes sauvegardes transactionnelles issues du log shipping pré-sauvegarde complète N+1 (supposons, la sauvegarde complète que tu viens de lancer) deviendront obsolètes, et si jamais tu voudrais les réutiliser, il te faudra exploiter, au préalable, la sauvegarde complète N, c’est-à-dire la dernière sauvegarde complète à laquelle elles dépendent.

      Je rédigerai bientôt un billet sur comment implémenter un plan de maintenance générique incluant optimisations et sauvegardes, et adapté à un très grand nombre de contextes.

      @+ !

      M.

  2. Yannick dit :

    merci pour ta réponse, elle est plus que claire, je dirais limpide !!!

    Par contre, autre chose toujours compliquée le licensing… Pour l’utilisation d’un log shipping il faut 2 serveurs au moins avec 2 instances. Est ce que cela signifie forcement l’achat de eux licences serveur ?

    Merci.

    PS : D’ailleurs, est ce que l’option « log shipping » est présente sur toute les édition SQLserver ? (encore merci !)

    • Yannick,

      Dans le cadre du log shipping, si ta base secondaire n’est pas destinée à être utilisée pour une quelconque opération (reporting, etc…), pas besoin de l’achat d’une licence supplémentaire. En revanche, si ta base secondaire est en mode stand by, cela signifierait que tu envisages de faire en sorte qu’elle soit utilisable (les requêtes de consultation sont des opérations actives), ce qui, par conséquent, nécessitera l’achat d’une seconde licence.

      Concernant les éditions supportées par le log shipping, comme indiqué dans le billet, tu peux utiliser l’Enterprise, la Standard, la Web ou la Workgroup Edition. A ceux là, tu peux rajouter la Business Intelligence Edition à partir de la version 2012 de SQL Server.

      @+ !

      M.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s