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 :

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  * * * * *