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

Ce billet traite de la mise-en-place d’une solution de log shipping via T-SQL. Il concerne les versions supérieures ou égales à SQL Server 2005.

Pour comprendre le fonctionnement du log shipping, référez-vous au billet [SQL Server] Un peu de théorie sur le log shipping. Et pour assimiler la mise-en-place du log shipping via SQL Server Management Studio (SSMS), vous pouvez jeter un coup d’œil ici.

Prérequis général du log shipping

Pour mener à bien la mise-en-place du log shipping, voici un rappel des prérequis généraux déjà évoqués ici :

  • 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), EnterpriseStandard, 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 en T-SQL

Pour la mise-en-place du log shipping via T-SQL, suivez les étapes suivantes :

  • Sur le serveur primaire (SRV-SQL01), réalisez les sous-étapes suivantes :
    • Pour la configuration d’IFC_SUITE comme base de données primaire :
DECLARE @LS_BackupJobId AS UNIQUEIDENTIFIER
DECLARE @LS_PrimaryId AS UNIQUEIDENTIFIER

EXEC master.dbo.sp_add_log_shipping_primary_database
@database = N'IFC_SUITE'
,@backup_directory = N'S:\SQL Backups\TRN\IFC_SUITE\LSBackup_IFC_SUITE'
,@backup_share = N'\\SRV-SQL01\LSBackup_IFC_SUITE'
,@backup_job_name = N'LSBackup_IFC_SUITE'
,@backup_retention_period = 2880 –- 2 jours
,@monitor_server = N'SRV-SQL02'
,@monitor_server_security_mode = 0 –- mode d'authentification SQL,@monitor_server_login = N'pluginrtms',@monitor_server_password = N'MyP455!'
,@backup_threshold = 90 –- erreur levée si pas de backup durant 90 minutes
,@threshold_alert_enabled = 1
,@history_retention_period = 7200 –- 5 jours
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
,@backup_compression = 0
GO

Notez que les périodes (periods) et seuils (thresholds) sont en minutes. Et notez également que @backup_compression n’est fonctionnel qu’à partir de la version 2008 de SQL Server.

Pour comprendre le fonctionnement de la procédure stockée sp_add_log_shipping_primary_database, référez-vous au lien suivant : http://technet.microsoft.com/en-us/library/ms182718.aspx.
    • Pour la planification des sauvegardes transactionnelles (tous les jours, toutes les 30 minutes, entre minuit et 23h59):
EXEC msdb.dbo.sp_add_jobschedule
@job_name = N'LSBackup_IFC_SUITE'
,@name = N'Sauvegarde transactionnelle base principale',@freq_type = 4 –- tous les jours,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 30 –- toutes les 30 minutes
–-,@active_start_date = 20110920
–-,@active_end_date = 99991231
,@active_start_time = 000000 –- début à 00:00:00
,@active_end_time = 235959
GO

Notez que si vous souhaitez planifier le job pour un autre jour, vous pouvez décommenter @active_start_date et fournir les 8 chiffres numériques adéquats. Si , par exemple, vous souhaitez lancer le job à partir du 20 septembre 2011, vous devrez dans ce cas spécifier la valeur 20110920.

Pour comprendre le fonctionnement de la procédure stockée sp_add_jobschedule, référez-vous au lien suivant : http://technet.microsoft.com/en-us/library/ms366342.aspx.
  • Sur le serveur moniteur (SRV-SQL02), ajoutez le job de monitoring et d’alerte du log shipping :
USE master
GO
EXEC sp_add_log_shipping_alert_job
GO

Notez que par défaut, il se lancera toutes les 2 minutes pour auditer la session de log shipping. N’hésitez pas à le replanifier de façon à ce qu’il puisse se lancer toutes les heures, par exemple.

  • Sur le serveur primaire, activez le job de sauvegardes transactionnelles créé plus haut) :
EXEC msdb..sp_update_job
@job_name = 'LSBackup_IFC_SUITE',@enabled = 1
GO
  • Sur le serveur secondaire (SRV-SQL03), suivez les sous-étapes suivantes :
    • Pour le référencement du serveur primaire et de sa base de données primaire :
DECLARE @LS_Secondary__CopyJobId AS UNIQUEIDENTIFIER
DECLARE @LS_Secondary__RestoreJobId AS UNIQUEIDENTIFIER
DECLARE @LS_Secondary__SecondaryId AS UNIQUEIDENTIFIER
DECLARE @LS_Add_RetCode AS INT

EXEC @LS_add_retcode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N'SRV-SQL01'
,@primary_database = N'IFC_SUITE'
,@backup_source_directory = N'\\SRV-SQL01\LSBackup_IFC_SUITE'
,@backup_destination_directory = N'S:\SQL Backups\TRN\IFC_SUITE\LSCopy_IFC_SUITE' –- sur le second serveur
,@copy_job_name = N'LSCopy_SRV-SQL01_IFC_SUITE'
,@restore_job_name = N'LSRestore_SRV-SQL01_IFC_SUITE'
,@file_retention_period = 2880 –- 2 jours
,@monitor_server = N'SRV-SQL02'
,@monitor_server_security_mode = 0 –- mode d'authentification SQL
,@monitor_server_login = N'pluginrtms'
,@monitor_server_password = N'MyP455!'
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
SELECT @LS_Secondary__CopyJobId,@LS_Secondary__RestoreJobId,@LS_Secondary__SecondaryId
GO
Pour comprendre le fonctionnement de la procédure stockée sp_add_log_shipping_secondary_primary, référez -vous au lien suivant : http://technet.microsoft.com/en-us/library/ms188382.aspx.
    • Pour la planification de la copie et de la restauration des sauvegardes transactionnelles de la base de données primaire sur le serveur secondaire :
–- Job de copie
EXEC msdb.dbo.sp_add_jobschedule
@job_name=N'LSCopy_SRV-SQL01_IFC_SUITE'
,@name=N'Copie de sauvegardes transactionnelles base principale',@freq_type=4 –- tous les jours,@freq_interval=1
,@freq_subday_type=4
,@freq_subday_interval=30 –- toutes les 30 minutes
--,@active_start_date=20110920
--,@active_end_date=99991231
,@active_start_time=000000 –- début à 00:00:00
,@active_end_time=235959
GO
–- Job de restauration
EXEC msdb.dbo.sp_add_jobschedule
@job_name=N'LSRestore_SRV-SQL01_IFC_SUITE'
,@name=N'Restauration transactionnelle base secondaire'
,@freq_type=4 –- tous les jours
,@freq_interval=1
,@freq_subday_type=4
,@freq_subday_interval=30 –- toutes les 30 minutes
–-,@active_start_date=20110920
–-,@active_end_date=99991231
,@active_start_time=000000 –- début à 00:00:00
,@active_end_time=235959
GO
    • Pour la configuration d’IFC_SUITE comme base de données secondaire :
EXEC master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N'IFC_SUITE'
,@primary_server = N'SRV-SQL01'
,@primary_database = N'IFC_SUITE'
,@restore_delay = 0
,@restore_mode = 1 –- STANDBY mode (lecture seule)
,@disconnect_users = 1 –- recommandé
,@restore_threshold = 90
,@threshold_alert_enabled = 1
,@history_retention_period = 7200 –- 5 jours
GO
Pour comprendre le fonctionnement de la procédure stockée sp_add_log_shipping_secondary_database, référez-vous au lien suivant : http://technet.microsoft.com/en-us/library/ms188916.aspx.
  • Sur le serveur primaire, fournissez les informations relatives à la base de données secondaire du serveur secondaire :
EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'IFC_SUITE'
,@secondary_server = N'SRV-SQL03'
,@secondary_database = N'IFC_SUITE'
GO
Pour comprendre le fonctionnement de la procédure stockée sp_add_log_shipping_secondary_primary, référez-vous au lien suivant : http://technet.microsoft.com/en-us/library/ms175025.aspx.
  • Sur le serveur secondaire, activez les jobs de copie et de restauration des sauvegardes transactionnelles :
–- Job de copie
EXEC msdb..sp_update_job
@job_name =N'LSCopy_SRV-SQL01_IFC_SUITE', @enabled = 1
GO
–- Job de restauration
EXEC msdb..sp_update_job
@job_name = N'LSRestore_SRV-SQL01_IFC_SUITE', @enabled = 1
GO

Pour aller plus loin…

Dans ce billet, nous avons vu à quel point la mise-en-oeuvre du log shipping en T-SQL était relativement simple. Dans le futur, nous aborderons quelques éléments d’audit du log shipping, ainsi que le failover.

Publicités

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