Automatistion du « Log Shipping » pour toutes les bases d’un serveur

Voici un ensemble de fonctions, procédures et tables pour automatiser le « log shipping » d’une ou de toutes vos bases d’un serveur à un autre.

Cet ensemble de routines est constitué d’une table, 6 fonctions et 8 procédures :

TABLE S_LOGSHIP.T_LOG_SHIPPING_OPERATION_LSO;
 
FUNCTION S_LOGSHIP.F_CLEAN_SQLID
FUNCTION S_LOGSHIP.F_DECODE_FILEDATE
FUNCTION S_LOGSHIP.F_EXTRACT_ENDCHARS
FUNCTION S_LOGSHIP.F_FILE_EXTRACT
FUNCTION S_LOGSHIP.F_GET_DATETIME_ID
FUNCTION S_LOGSHIP.F_PATH_HAS_SLASH
 
PROCEDURE S_LOGSHIP.P_ACTIVE_TARGET_DATABASE
PROCEDURE S_LOGSHIP.P_CLEANUP_FILES
PROCEDURE S_LOGSHIP.P_DISABLE_SOURCE_DATABASE
PROCEDURE S_LOGSHIP.P_RECEIVE_INITIATE
PROCEDURE S_LOGSHIP.P_RECEIVE_REGULAR
PROCEDURE S_LOGSHIP.P_SEND_INITIATE
PROCEDURE S_LOGSHIP.P_SEND_REGULAR
PROCEDURE S_LOGSHIP.P_SET_RECOVERY_FULL

Elle seront créées dans un schéma particulier intitulé S_LOGSHIP de la base msdb.

LIMITES :

Voici les restrictions concernant cet outil

Ce code ne marche que pour SQL Server version 2008 et suivantes.
Il peut néanmoins être corrigé pour fonctionner sous 2005 sans grande modification. Les modification à apporter le sont à la définition des variables tables @SHEADER et @SFILE (par exemple pour la compression des backups)

L’utilisation de la procédure xp_cmdshell est obligatoire.

La base ne doit pas comporter de FILESTREAM.

IMPORTANT :

Vous devez être au niveau de privilège sysadmin pour effectuer l’ensemble de ces tâches sur les deux serveurs.

Vous devez créer un répertoire partagé pour stocker temporairement les sauvegardes.
Les comptes système d’exécution des servces SQL Server et/ou Agent SQL devront avoir accès en lecture et écriture sur ce partage.

Dans notre exemples ce sera : « \\SQLpro\C_transfert »

Vous ne devez plus lancer aucune sauvegarde d’aucune sorte (à l’exception de sauvegardes fantômes avec l’option COPY ONLY) sinon, cela brise le mécanisme de log shipping en rompant le chaînage des segments de journaux (LSN).
Pour sauvegarder votre base, copiez les sauvegardes qui seront placées régulièrement dans le répertoire partagé, ou utilisez l’option COPY ONLY de BACKUP DATABASE.

Vous devez conserver au moins tous les derniers fichier du journal de transaction du partage (*.TRN, *.TRN_OK) afin de pouvoir basculer votre base en production.

Nous vous conseillons de vider régulièrement ce partage afin de ne pas encombrer votre serveur. Par exemple avec une échéance de plus de 24 h et en ne prenant que les fichier ayant une extension renommée selon votre souhait (dans notre exemple .BAK_OK et .TRN_OK).

L’ensemble du code est le suivant :

Script de log shipping pour toutes les bases d’un même serveur

1 – MISE EN PLACE :

1.1 – Création des objets

Lancez le script ci- avant sur le serveur source et le serveur de cible. Ceci créé tous les objets nécessaires

1.2 – mise en RECOVERY FULL des bases sources

Sur le serveur source, lancez la commande :
EXEC S_LOGSHIP.P_SET_RECOVERY_FULL;
Pour que toutes les bases passent en mode de journalisation FULL, ou bien
EXEC S_LOGSHIP.P_SET_RECOVERY_FULL 'maBase';
pour que seule la base ‘maBase’ passe en mode de journalisation FULL

1.3 – Initialiser le log shipping côté source
Sur le serveur source, lancez la commande suivante :


EXEC S_LOGSHIP.P_SEND_INITIATE  
     @PATHSAVE = '\\SQLpro\C_transfert',  --> nom du répertoire partagé des sauvegardes
     @DBNAME   = NULL,                    --> toutes les bases sont concernées, sinon mettez le nom de la base
     @VERIFY   = 1;                       --> 1 la sauvegarde est vérifié, 0 elle ne l'est pas

Ceci envoi une sauvegarde initiale complète des bases logshippées.

1.4 – Initialiser le log shipping côté cible

Sur le service cible. Une fois l’étape 1.3 accomplie, lancez la commande suivante :


EXEC S_LOGSHIP.P_RECEIVE_INITIATE
     @PATHSAVE = '\\SQLpro\C_transfert',  --> nom du répertoire partagé des sauvegardes
     @PATHDATA = 'D:\SQLDATA\TABLES',     --> répertoire des données des bases logshippées
     @PATHTRAN = 'D:\SQLDATA\TRANS',      --> répertoire du journal de transaction des bases logshippées (peut être le même)
     @KEEPFILE = 1,                       --> les fichiers de sauvegarde sont conservés, si 0 ils sont supprimés
     @RENAME_EXT = 'BAK_OK';              --> renommage des extensions après prise en compte (si NULL, renommage sans extension)

Ceci créé les bases logshippées depuis les sauvegardes complètes et les place en état d’attente et renomme l’extension des sauvegardes de base en BAK_OK.

1.5 – Planification de l’envoi des journaux

Sur le serveur source, créez une tâche planifiée de l’agent SQL, à la périodicité souhaitée, (par exemple 20 minutes) avec la commande suivante :


EXEC S_LOGSHIP.P_SEND_REGULAR
     @PATHSAVE = '\\SQLpro\C_transfert',  --> nom du répertoire partagé des sauvegardes
     @DBNAME   = NULL,                    --> toutes les bases sont concernées, sinon mettez le nom de la base
     @VERIFY   = 1;                       --> 1 la sauvegarde est vérifié, 0 elle ne l'est pas

Ceci envoi une sauvegarde du journal de transaction pour les bases logshippées.

1.6 – Planification de la réception des journaux

Sur le serveur cible, créez une tâche planifiée de l’agent SQL, à la périodicité souhaitée, (par exemple 20 minutes) avec la commande suivante :


EXEC S_LOGSHIP.P_RECEIVE_REGULAR
@PATHSAVE = '\\SQLpro\C_transfert',       --> nom du répertoire partagé des sauvegardes
@KEEPFILE = 1,                            --> les fichiers de sauvegarde sont conservés, si 0 ils sont supprimés
@RENAME_EXT = 'TRN_OK'                    --> renommage des extensions après prise en compte (si NULL, renommage sans extension)

Ceci alimente les bases logshippées avec les journaux de transaction et renomme l’extension des sauvegardes de JT en TRN_OK.

1.7 – basculement en production

Vous devez faire deux actions dans l’ordre :

1.7.1 – désactiver les bases sources

Sur le serveur source, lancez la procédure :


EXEC S_LOGSHIP.P_DISABLE_SOURCE_DATABASE  
     @DBNAME = NULL;                      --> toutes les bases sont concernées, sinon mettez le nom de la base

Ceci place les bases logshippées en READ ONLY.

1.7.2 – activer les bases cibles

Sur le serveur cible, lancez la procédure :


EXEC S_LOGSHIP.P_ACTIVE_TARGET_DATABASE
     @PATHSAVE   = '\\SQLpro\C_transfert',  --> nom du répertoire partagé des sauvegardes
     @DBNAME     = NULL,                    --> toutes les bases sont concernées, sinon mettez le nom de la base
     @KEEPFILE   = 1,                       --> les fichiers de sauvegarde sont conservés, si 0 ils sont supprimés  
     @RENAME_EXT = 'BAK_FINAL'              --> renommage des extensions après prise en compte (si NULL, renommage sans extension)

Ceci place les bases cible en état de production (READ WRITE), à condition que le dernier journal de transaction n’ai pas été supprimé !

2 – MONITORING

Pour savoir à tout moment ce qui se passe, vous pouvez aller dans la table msdb.S_LOGSHIP.T_LOG_SHIPPING_OPERATION_LSO voir quelles sont les opérations effectuées dans l’un comme dans l’autre serveur.

Pour connaître les incidents graves que ce système peut connaître, lisez les messages d’erreur dans la table msdb.S_LOGSHIP.T_LOG_SHIPPING_OPERATION_LSO et les entrées du journal d’événement de SQL Server.

CONCLUSION

N’hésitez pas à me faire part de vos expériences à l’utilisation de cette mécanique !


--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

4 réflexions au sujet de « Automatistion du « Log Shipping » pour toutes les bases d’un serveur »

  1. Avatar de sqlprosqlpro Auteur de l’article

    Les opérations transactionnelle sont marquées dans le journal par des LSN (Log Segment Number). Toute interruption du chainage des LSN rend caduc la restauration. Autrement dit le système est définitivement perdu et il faut recommancer depuis le début en recréant la base fille avec une sauvegarde complète.

  2. Avatar de zoltar35zoltar35

    Bonjour,

    Que se passe t’il si l’un des fichiers de log n’est pas copié sur l’autre serveur? Les journaux sont-ils numérotés?

    Je suis dans un contexte multi bâtiments donc les journaux doivent être transférés sur un serveur distant et non un répertoire partagé. Est-ce aussi secure?

    Merci pour vos réponses et vos articles.

  3. Avatar de sqlprosqlpro Auteur de l’article

    C’est tout à fait cela, car vous aller rompre la chaine transactionnelle des différenst journaux sauvegardées qui se suivent par leur LSN.
    Donc il vous faut :
    – soit copier les sauvegardes des journaux
    – soit faire un BACKUP FULL … WITH COPY ONLY
    – soit les deux

    A +

  4. Avatar de rodbeckrodbeck

    Bonjour,

    Lorsque vous dites : « Vous ne devez plus lancer aucune sauvegarde d’aucune sorte », vous voulez dire que si j’effectue un backup full journalier et du lot shipping entre chaque backup full, je ne serais pas capable de restaure ma base ?

    Merci.

Laisser un commentaire