Qu'est ce que le log-shipping ?

Le Log-shipping  est un processus à faible coût et relativement facile à mettre en place qui va vous permettre de synchroniser le contenu de 2 bases de données. Sa facilité vient de son mode de fonctionnement qui n'est autre qu'une sauvegarde et de la restauration. C'est l'une des solutions de haute disponibilité intégré à la plate forme SQL Server.

Le Log-shipping  est officiellement supporté par Microsoft dans l'édition Enterprise de SQL Server 2000, ainsi que dans les éditions WorkGroup, Standard et Enterprise de SQL Server 2005. Ce support se traduit par le fait que vous avez accès à un assistant spécifique et des procédures stockées qui vous permettent de le mettre en place très rapidement et à limiter la maintenance associée, il y a aussi un certain nombre de procédures stockées disponible pour la mise en place de ce procédé.

Cependant sur toutes les versions/éditions de SQL Server il est possible de mettre en place un processus similaire, que se soit sur SQL Server 7 ou même sur une édition Express de SQL Server 2005.

J'insiste sur le fait que tout ce qui va suivre n'est là que pour illustrer le fonctionnement du log-shipping et je n'en assure pas le support, les scripts T-SQL sont livré tel que, à vous de les adapter et les maintenir sur vos serveurs.

Le processus.

Phase d'initialisation du log-shipping

La base de données A est dans un premier temps sauvegardé à l'aide d'un backup complet, puis d'une sauvegarde du journal (étape 1). Le ou les fichiers ainsi obtenus sont copié sur le serveur secondaire (étape 2) pour y créer une nouvelle base restaurée à partir de ceux-ci, avec une petite différence la base de données restaurée (étape 3) est laissé en mode NO RECOVERY (mode dans lequel la base de données est inaccessible) ou STANDBY (mode dans lequel la base de données est en lecture seule).

 

1.      Backup complet de la base de données sur le serveur principal

2.      Copie du fichier de sauvegarde entre les serveurs

3.      Restauration complète de la base de données sur le serveur secondaire (NORECOVERY)

Phase de synchronisation du log-shipping

L'initialisation de notre nouvelle base étant faite il nous fait maintenant synchroniser les données entre notre base source sur notre serveur principal et notre base destination sur notre serveur secondaire. Il va se produire une succession de sauvegarde des journaux de la base de données du serveur principal, puis la copie de ces fichiers, ainsi que leur restauration toujours en NO RECOVERY ou STANDBY.

 

1.      Sauvegarde du journal sur le serveur principal

2.      Copie du fichier de sauvegarde (généralement extension .TRN)

3.      Restauration du journal sur le serveur secondaire (avec option NORECOVERY)

Phase de défaillance / basculement

Dernière phase, en cas de défaillance, il faudra passer la base de données secondaire en lecture / écriture.

 

-          Restauration éventuelle des dernières sauvegardes de journaux

-          Restauration du journal (RECOVERY)

Les différences des versions / éditions de SQL Server

En fonction des versions / éditions de SQL Server le log shipping sera plus ou moins supporté, voici les détails.

 

Cas 1 : Support du Log-shipping intégré

Versions / éditions concernés

-          SQL Server 2000 édition Enterprise

-          SQL Server 2005 éditions WorkGroup, Standard, Enterprise

 

Dans le cas de ces versions il est recommandé d'utiliser le support intégré du Log-shipping qui est fourni par Microsoft. Cependant dans de rares cas si vous avez besoin de personnaliser le fonctionnement et arrivez donc au limite du produit vous pourriez jeter un coup d''il à ce qui suit.

 

Ce cas étant le plus simple je vous invite à y jeter un coup d'oeil ici :

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx

J'espère avoir le temps d'écrire un tutoriel sur le sujet très bientôt.

 

Le principe, dans ce cas est que tout est géré par le moteur, les procédures stockées nécessaires à la mise en oeuvre du log-shipping. De plus un assistant est fournis (accessible via l'assistant de plan de maintenance).

Par contre rien ne vous oblige à utiliser la  solution fournie et vous pouvez donc allez au cas n°2.

Cas 2 : Pas de support du Log-shipping intégré, Agent SQL présent

Versions / éditions concernés

-          SQL Server 7, toutes éditions sauf MSDE

-          SQL Server 2000 éditions Personal, Standard

 

Dans le cas présent, il est possible de faire du log-shipping par le biais de l'agent SQL qui sera notre planificateur de tâches, ce qui nous facilite la tâche étant donné l'intégration forte entre le service SQL et l'agent.

 

Dans ce cas nous allons utiliser les scripts disponibles avec cet article

 

Dans les éditions non MSDE et non Express, nous avons la chance d'avoir à notre disposition l'agent SQL qui va nous servir de planificateur de tâches. Sans aller dans les détails de la configuration de la sécurité de celui-ci il faut si possible avoir le compte de service de l'agent dans le domaine (simple invité du domaine, mais avec des droits d'administration du serveur sur lequel est installé SQL Server), et cela pour permettre la copie des fichiers entre les serveurs.

 

Vérifiez bien que l'agent SQL est bien démarré pour votre instance (Allez dans « Outils d'administration » / « Services »), et vérifié que celui-ci démarre automatiquement.

 

Dans cette solution nous allons créer des tâches SQL, dans lesquels l'on mettra le contenu des scripts SQL, et des tâches de commandes où l'on mettra le contenu des scripts de commande (.cmd).

Cas 3 : Pas de support du Log-shipping intégré, Agent SQL absent

Versions / éditions concernés

-          SQL Server 7 édition MSDE

-          SQL Server 2000 édition MSDE 2000

-          SQL Server 2005 édition Express

 

Ici en l'absence de planificateur tel que l'agent SQL il va falloir trouver une alternative pour réaliser le log-shipping entre les serveurs.

 

Dans ce cas nous allons utiliser nos scripts (disponible en sources avec ce tutoriel)

 

Dans cette situation, pas d'agent installé, résultat pour effectuer les tâches planifiées nécessaire au Log-shipping nous allons utiliser le planificateur de tâches de Windows, qui va nous permettre l'exécution de lignes de commandes, ou de code Transact-SQL.

 

Voir le détail dans l'article suivant :

http://www.technos-sources.com/tutorial-creation-tache-planifiee-sous-windows-15.aspx

 

Dans notre cas la ligne de commande utilisé sera la suivante pour exécuter les scripts (.sql) fournis ici.

 

Pour SQL Server 2005 :

sqlcmd -S NOMSERVEUR\NOMINSTANCE -E -i FICHIERSCRIPT.SQL

 

Pour SQL Server 2000 :

osql -S NOMSERVEUR\NOMINSTANCE -E -i FICHIERSCRIPT.SQL

 

Pour SQL Server 7.0 :

isql -S NOMSERVEUR\NOMINSTANCE -E -i FICHIERSCRIPT.SQL

 

Voici la description des options :

• -S : nom du serveur et éventuellement nom de l'instance
• -E : authentification de type Windows
• -i : fichier de script SQL à exécuter sur le serveur

Le point faible de cette solution est la faible intégration avec SQL Server, en effet il n'est pas possible d'avoir des mêmes fonctionnalités que l'agent (historique, tâches multi serveur), etc.

Gérer le basculement

Le basculement est l'action de passer du serveur principal comme source des données pour le client au serveur secondaire.

 

Cette situation dans un fonctionnement à base de logshipping génère forcément une perte de données, cette perte de données dépend de l'intervalle de sauvegarde de la base de données et de la copie des sauvegardes du serveur principal, elle au maximum la somme de ces 2 critères.

 

Le basculement quant à lui est en général manuel, puisqu'il s'agit de router les connexions des clients sur un autre serveur. De plus même si la reconnexion était automatique sur le nouveau serveur, la base de données du serveur secondaire n'est pas accessible ou est au mieux en lecture seule.

 

 

Pour rendre accessible la base de données du serveur secondaire, il faut restaurer les dernières sauvegardes du journal et passer la base de données en RECOVERY :

 

RESTORE LOG A FROM DISK = "fichierdesauvegarde" WITH RECOVERY

 

RESTORE LOG A WITH RECOVERY

 

A cet instant la base de données est à nouveau en lecture / écriture et il est dès lors possible de router les connexions clientes vers le serveur secondaire.

Les options

Basculement automatique

Une nouveauté de la couche cliente de SQL Server 2005 (qui d'ailleurs fonctionne aussi avec un serveur version 2000) est la possibilité d'inclure dans la chaîne de connexion le paramètre FailoverPartner qui est le serveur sur lequel on tentera de se connecter si le serveur indiqué dans DataSource est inaccessible.

Cette fonctionnalité disponible à l'origine pour le mirroring permet au client de connecter sur le serveur qui est disponible (celui des 2, en commençant par celui indiqué dans DataSource et en testant successivement celui des 2 qui accepte la connexion).

Quand le basculement se produit, la connexion est tout de même perdu, c'est à la reconnexion que le client SQL va chercher à se connecter au serveur secondaire. A vous donc de gérer les tentatives de connexion en cas de déconnexion d'un serveur. A noter que cette fonctionnalité peut être utilisé avec comme serveur client SQL Server 2005 ou 2000, ainsi qu'en utilisant le client natif directement, OLEDB ou ODBC.

Serveur « witness »

Autre possibilité, utiliser un serveur tiers qui va servir à identifier la défaillance du serveur principal. C'est lui qui va se charger d'exécuter le script de basculement sur le serveur secondaire. Ce n'est pas un serveur de witness comme celui de SQL Server 2005 pour le mirroring,  mais il a une fonction similaire.

Je ne fourni pas de scripts pour cela, mais vous pourrez facilement en créer un.

Attention dans cette configuration, malgré le basculement automatique, la perte de données est systématique.

Le serveur secondaire en lecture seule

Possibilité d'avoir le serveur secondaire partiellement disponible, la base de données est en effet dans ce cas en lecture seule. Cela est possible en remplaçant l'option NORECOVERY des scripts de restauration, par l'option STANDBY = "fichier" (en commentaire dans le script). L'avantage de cette solution est de pouvoir utiliser les 2 serveurs, cependant quand des utilisateurs sont connectés sur cette base de données, les opérations de restauration du journal sont en attente.

La mise en oeuvre

Avant toutes choses il faut que les 2 serveurs possèdent la même configuration de sécurité (même comptes de connexion / login) et cela pour éviter tout problèmes de connexion.

Voir cet article du support Microsoft pour la manière de le faire :

http://support.microsoft.com/kb/246133/fr

 

Il faut prévoir sur le serveur secondaire un partage de fichier sur lequel le compte qui sera en charge d'exécuter le script de déplacement des fichiers de sauvegarde aura les droits nécessaire (c'est en général le compte de service de l'agent SQL ou celui qui vous aurez spécifié dans la tâche planifié).

 

Puis ensuite, voici la liste des scripts ainsi que le serveur sur lesquels les exécuter.

 

Phase d'initialisation

-          SAV_COMPLET.sql (SRV_P)

-          DEPL_FICHIERS.cmd (SRV_P)

-          RST_COMPLET.sql (SRV_S)

 

Phase de synchronisation

-          SAV_JOURNAL.sql (SRV_P)

-          DEPL_FICHIERS.cmd (SRV_P)

-          RST_JOURNAL.sql (SRV_S)

 

Phase de basculement

-          RST_RECOVERY.sql (SRV_S)

 

La phase d'initialisation est à exécuter une seule fois, elle ne sert qu'à effectuer la copie de la base de données.

Dans la phase de synchronisation tous les scripts sont à la planifier sur l'un ou l'autre des serveurs comme indiqué. Attention dans cette phase, le nom des fichiers de sauvegarde est important, le script de restauration devant respecter l'ordre de restauration des fichiers, c'est pour cette raison que j'inclus systématiquement la date et l'heure dans ces fichiers.

La phase de basculement peut s'exécuter manuellement sur le serveur secondaire.

La configuration du log-shipping

Voici la liste des paramètres à prendre à compte dans la configuration du logshipping

-          Intervalle de sauvegarde des logs

o       Temps laissé entre 2 sauvegardes du journal de transaction (script SAV_JOURNAL.sql)

-          Intervalle de copie des fichiers de sauvegarde

o       Temps laissé entre 2 déplacements des fichiers de sauvegarde du journal (script DEPL_FICHIERS.cmd)

-          Intervalle de restauration des fichiers de sauvegarde

o       Temps laissé entre 2 restaurations des fichiers de sauvegarde du journal, qui ont été copiés de l'autre serveur. (script RST_JOURNAL.sql)

-          Nom de la base de données et du serveur principal

o       Nom du serveur principal (avec éventuellement nom de l'instance)

o       Nom de la base de données à synchroniser (celle-ci est en lecture écriture)

-          Nom de la base de données et du serveur secondaire

o       Nom du serveur secondaire (avec éventuellement nom de l'instance)

o       Nom de la base de données cible des restaurations (celle-ci porte en général le même nom que la base du serveur principal, elle est non accessible ou en lecture seule)

-          Répertoire partagé où copier les fichiers de sauvegarde

o       Partage dans lequel les scripts de déplacement des fichiers de sauvegarde du serveur principal déplacent les fichiers de sauvegarde

-          Base de donnée secondaire laissé en lecture ou indisponible

o       Critère pris en compte lors de la restauration du journal (NORECOVERY ou STANDBY dans le script RST_JOURNAL.sql)

A vous de jouer

Malgré l'aspect simple du système il vous faudra vérifier son fonctionnement au jour le jour... Je fourni cet exemple pour monter qu'il est possible d'aller hors des sentiers battus et de réaliser des choses à priori impossible y compris avec des éditions gratuites.

En espérant que ces scripts vous serviront de base ajouter plein de fonctionnalités à vos serveurs.