OBJECTIF
L’objectif du log Shipping est de copier et maintenir à jour une base de données SQL Server afin que la copie soit accessible en lecture seule pour y effectuer les interrogations massives liées au reporting.
Ce document décrit une manière de procéder afin de mettre en place cette fonctionnalité.
Toute la procédure décrite s’effectue a l’aide de Microsoft SQL Server Management Studio (MSSMS) connecté aux serveurs Primaire et secondaire en Sysadmin (soit identification NT avec un administrateur local des machines, soit avec le compte SA).
LES PRE-REQUIS
Vous devez impérativement veiller à ce que les 4 étapes de cette partie soient validées afin d’être sûr que votre environnement LOG SHIPPING soit opérationnel.
Configuration
Vous devez disposer de 2 instances SQL servers distinctes.
Le but étant de déporter la consommation de ressources due aux interrogations de type reporting, 2 machines différentes sont recommandées.
Sur chacune des machines devra être installé une instance SQL Server 2005. Ces instances doivent être aussi identiques que possible :
- Même classement
- Mêmes options
- Même langages
Sur chacune des 2 instances le SQL Agent doit être installé et démarré car ce sont eux qui vont gérer la sauvegarde des Logs, leurs transferts et leurs restaurations sur la base secondaire.
Il ne peut en aucun cas y avoir de Log Shipping entre 2 bases d’une même instance.
Si les 2 instances sont installées sur la même machine elles entreront en concurrence pour les ressources mémoire et CPU.
La base de données qui sera recopiée via le mécanisme du log shipping doit être en mode de restauration Bulk-load ou Complet. Le mode simple est totalement incompatible avec le log shipping.
Vérification des noms de serveurs
La première étape est de vous connecter aux 2 instances et de vérifier leurs noms internes.
Par défaut ces noms internes sont du type <nom machine>\<nom instance>. Il s’agit toutefois d’être vigilant car si la machine a été renommée après l’installation de SQL Server, le nom interne du serveur n’a cependant pas changé et cela peut être un point bloquant lors de la mise en œuvre du Log Shipping. De plus si l’instance a été installée avec son nom par défaut (MSSQLSERVER) celui-ci n’est pas reporté dans le nom interne du serveur SQL qui ne contient plus alors que <nom machine>.
Dans MSSMS connectez-vous aux serveurs et pour chacun d’eux ouvrez une nouvelle fenêtre de requête.
Pour chacun des serveurs exécutez la requête :
SELECT @@SERVERNAME ;
Si comme ci-dessous le nom renvoyé par la requête diffère du nom indiqué dans l’explorateur d’objet (a gauche dans MSSMS) :
Vous devrez alors changer le nom interne de votre SQL Server.
Pour changer le nom interne du serveur SQL exécutez les 2 requêtes suivantes :
Sp_dropserver N’<ancien nom>’ ;
Go
Sp_addserver N’<nouveau nom>’, ‘LOCAL’ ;
Go
Vous devez ensuite impérativement redémarrer l’instance SQL Server (via arrêt/relance du service correspondant) pour que ce changement soit pris en compte.
Les noms correspondent maintenant bien :
Création du partage réseau pour les transferts de fichiers log
Pour être transféré de la base principale vers la base secondaire les fichiers de log doivent transiter par un disque physique. Il faut donc que les 2 serveurs aient accès à un même répertoire. Ce répertoire devra donc être soit local pour l’un et partagé pour l’autre, soit partagé pour les 2 sur une machine tierce.
Comme expliqué ci-dessus au paragraphe 2.1, ce sont les Agents SQL des 2 instances qui effectuent tous les traitements utilisés pour le Log Shipping. Il faut donc absolument que les comptes de lancement des services SQL Agent des 2 serveurs aient accès au répertoire partagé en lecture et en écriture.
Par défaut le compte de lancement d’un SQL Agent est le compte « Service Réseau », vous pouvez toutefois le changer (attentions aux droits du compte, ils pourraient fortement limiter les fonctionnalités de l’agent). Dans notre exemple nous gardons le compte par défaut et créons le répertoire partagé avec les droits suivants :
Ce répertoire peut être créé indifféremment sur le serveur primaire ou le serveur secondaire, voir même sur un serveur tiers. C’est avant tout un problème d’espace disponible sur vos disques. Toutefois il peut être plus performant de le créer sur le serveur primaire.
Accessibilité de la base Cible
De par son principe de fonctionnement la base cible ne sera accessible qu’en lecture seule. C’est un avantage car cela nous permet de garantir la totale cohérence des données lues dans la base cible qui est une image fidèle de la base source à un instant T.
Cela pose toutefois un problème de taille.
Les « connexions » sont une notion « Serveur » et les « utilisateurs » une notion « Base », les premières étant liées aux seconds par un identifiant unique. Les connexions sont stockées dans la base master du serveur alors que les utilisateurs et leur mappage aux connexions sont stockés dans la base SOURCE.
A ce titre les utilisateurs et le mappage sont copiés mais pas les connexions. La base étant en lecture seule il est par ailleurs impossible de modifier le mappage a posteriori.
Par défaut les seuls utilisateurs à pouvoir lire la base Cible sont les utilisateurs Sysadmin du serveur secondaire.
La solution consiste à activer sur la base Source l’utilisateur Guest avant de mettre en œuvre le log shipping et de lui attribuer les droits de lecture uniquement sur cette base.
Cet utilisateur est très particulier. Il n’est lié à aucune connexion et propose des droits « par défaut » pour toutes les connexions déclarées sur le serveur n’ayant pas de droit explicite sur la base SOURCE. En temps normal cet utilisateur n’a aucun droit et n’est même pas actif, ce qui empêche toute connexion n’ayant pas de droit explicite sur la base d’y accéder.
Nous allons donc activer cet utilisateur sur la base source du serveur primaire de la manière suivante :
Exécutez ces instructions sur le serveur Primaire.
L’instruction USE nous positionne sur la base SOURCE.
L’instruction GRANT autorise l’utilisateur GUEST à être actif sur la base SOURCE.
L’instruction EXEC donne à l’utilisateur GUEST le droit de lire la base dans son intégralité.
Cette configuration particulière sera répliquée sur la base CIBLE et nous pourrons alors créer sur le serveur SECONDAIRE une connexion dont la base par défaut sera la base CIBLE (dans notre exemple LOGSHIPING_SAMPLE) et qui n’aura aucun droit spécifique.
Exécutez ces instructions sur le serveur Secondaire.
En utilisant cette connexion sur le serveur secondaire nous ne pourrons donc que lire la base CIBLE et rien faire d’autre.
Il est possible d’être plus fin dans la gestion des droits de l’utilisateur GUEST si nous voulons restreindre ses lectures à seulement certaines tables.
MISE EN ŒUVRE DU LOG SHIPPING
Principes
Le principe du Log Shipping est assez simple :
La base SOURCE du serveur PRIMAIRE supporte une activité applicative transactionnelle. Afin de ne pas perturber cette activité nous souhaitons la dupliquer sur un serveur SECONDAIRE dans une base CIBLE qui pourra supporter une activité décisionnelle sans perturber les performances du serveur PRIMAIRE.
Pour atteindre ce but nous allons devoir effectuer les étapes suivantes :
- Copier la base SOURCE du serveur PRIMAIRE dans la base CIBLE du serveur SECONDAIRE.
- Sauvegarder périodiquement les logs de la base SOURCE dans un répertoire partagé.
- Restaurer périodiquement sur la base cibles les sauvegarde des logs, depuis le répertoire partagé.
C’est cette double périodicité qui définit le delta de données entre les deux bases.
Il convient de définir cette périodicité selon le besoin des utilisateurs en sachant que plus les sauvegardes/restaurations de fichier log seront fréquentes, moins bonnes seront les performances mais que moins elles seront fréquentes plus grand sera le retard des données de la base cible par rapport à la base source.
Par ailleurs, plus le temps est long entre 2 sauvegardes de logs plus le temps de la restauration du fichier log sur le serveur secondaire / la base cible risque d’être grand. Or pendant la restauration des fichiers logs la base cible est gelée et les requêtes des utilisateurs sont en attente.
Le temps entre 2 sauvegardes/restaurations doit donc être étudié en fonction de ces facteurs.
Procédure
Dans l’explorateur d’objet de MSSMS sélectionnez la base SOURCE du log shipping et effectuez un clic droit pour accéder au menu contextuel.
Choisissez l’option Propriétés.
L’écran suivant s’affiche :
Positionnez vous sur la page « Envoi des journaux de transaction » :
Etape 1
Cochez la case « activer en tant que base de données primaire dans une configuration de la copie des journaux de transactions ». Puis cliquez sur le bouton « Paramètres de sauvegarde… »
Dans la zone « Chemin d’accès réseau au dossier de sauvegarde » indiquez le chemin UNC du partage que vous avez configuré pour recevoir les fichiers de log.
Si (et seulement si) ce répertoire est sur le même serveur que votre instance SQL Primaire donnez le chemin local de ce même répertoire dans la zone « Si le dossier de sauvegarde se trouve sur le serveur principale, tapez un chemin d’accès local au dossier ». Cela peut aider aux performances mais ne dispense en aucun cas de renseigner le chemin UNC du partage.
Paramétrez la rétention des fichiers de log dans ce répertoire avec l’option « Supprimer les fichiers antérieurs à : »
Paramétrez l’émission d’une alerte si aucun backup ne s’est effectué correctement depuis un certain temps via l’option « Envoyez une alerte si aucune sauvegarde ne se produit en l’espace de : »
Cliquez sur le bouton « Planification… » pour définir le rythme de vos sauvegarde de fichier log :
Vous ne devriez avoir qu’à modifier la fréquence quotidienne qui est par défaut à 15 minutes. Remplacez cette valeur par celle que vous avez estimé la meilleur pour votre contexte applicatif.
Cliquez sur OK pour revenir a l’écran précédent, puis une deuxième fois sur OK pour terminer la configuration des sauvegardes des fichiers log.
Etape 2
Cliquez sur Ajouter pour configurer un (ou plusieurs) serveur(s) secondaire / base(s) cible :
Cliquez sur le bouton « Se connecter… » pour vous connecter à votre serveur secondaire.
La base Cible portera le même nom que la base Source.
Nous recommandons d’utiliser la première option de cet onglet pour initialiser la base cible sans toucher aux options de restauration. Ce faisant vous génèrerez une sauvegarde complète de votre base de données dans le répertoire destiné à recevoir vous fichiers logs. Méfiez vous des problèmes d’espace disque.
Positionnez vous sur l’onglet « Copier les fichiers » :
Cette partie vous permet de configurer une copie des fichiers situés dans votre répertoire partagé vers un autre répertoire (sur un autre disque/machine) moins chargé ou plus protégé afin d’en garder une trace après qu’ils aient été appliqués sur la base cible.
Indiquez le chemin du répertoire de délestage dans la zone « Dossier de destination des fichiers copiés »
Indiquez le délai de rétention des fichiers avec l’option « Supprimer les fichiers copiés après le : »
Cliquez sur « Planification… » pour définir la périodicité de votre job de copie (écran similaire a celui de la planification du job de sauvegarde des fichiers logs).
Positionnez vous sur l’onglet : « Restaurer le journal des transactions » :
Choisissez le mode veille pour que votre base soit accessible en lecture seule.
La base CIBLE conserve les options de la base SOURCE, notamment son mode de récupération "complet", mais ne génère aucune activité et n'est pas "backupable".
Indiquez un délai de retardement de la restauration des sauvegarde si vous en avez besoin avec l’option « Retarder la restauration des sauvegardes d’au moins : »
Précisez le délai passé lequel vous déclenchez une alerte si aucune restauration n’a pu s’effectuer avec l’option « Envoyer une alerte si aucune restauration ne se produit en l’espace de : »
Cliquez sur « Planification… » pour définir la périodicité de votre job de restauration (écran similaire a celui de la planification du job de sauvegarde des fichiers logs).
Cliquez sur OK pour terminer la configuration de votre serveur Secondaire.
Recommencez l’opération si vous souhaitez configurer un serveur secondaire supplémentaire.
Vous pouvez définir une instance moniteur si vous le souhaitez.
Nous n’avons pas souhaité entrer dans le détail de cette fonctionnalité. Elle n’a pas d’apport fonctionnel vital. Il s’agit juste d’un confort d’administration du log shipping, puisqu’elle centralise les 2 Alertes (pas de sauvegarde depuis X minutes ET pas de restauration depuis X minutes) sur une seule instance SQL.
Cliquez sur OK pour finir l’activation de votre log shipping.
A ce moment là vous générez une sauvegarde complète de votre base source sur le serveur Primaire dans le répertoire partagé du log shipping (attention aux problèmes de place disque) via l’agent SQL du serveur Primaire.
Puis vous restaurez cette sauvegarde sur le serveur secondaire dans la base Cible via l’agent SQL du serveur secondaire.
Enfin les 2 agents SQL enregistrent leurs tâches planifiées de sauvegardes et de restauration des fichiers logs de votre base Source vers votre base Cible.
Cliquez sur « Fermer » pour finir.
Dans l’explorateur d’objet de votre serveur Secondaire votre base apparaît en GRIS (et non en jaune).
L’agent SQL de votre serveur Primaire s’est vu confier 2 nouveaux jobs.
LSBackup_<nom bases> est le job de sauvegarde de vos fichiers log vers le partage.
LSAlerte_<nom serveur> est le job alerte que vous avez configuré à l’étape 1 il se déclenche a la fréquence que vous avez indiqué et écrit une alarme dans la log des applications Windows de votre serveur si au terme du délai que vous avez fixé aucune sauvegarde n’a pu être effectuée.
L’agent SQL de votre serveur Secondaire s’est vu confier 3 nouveaux jobs.
LSCopy_<nom serveur primaire>_<nom base source> est le job de copie que vous avez configuré a l’étape 2
LSRestore_<nom serveur primaire>_<nom base source> est le job de restauration de vos fichiers log sur la base cible que vous avez configuré à l’étape 2.
LSAlerte_<nom serveur secondaire> est le job alerte que vous avez configuré à l’étape 2 il se déclenche a la fréquence que vous avez indiqué et écrit une alarme dans la log des application Windows de votre serveur si au terme du délai que vous avez fixé aucune restauration n’a pu être effectuée.