Configurer la copie des journaux de transaction pour SQL Server sur les machines virtuelles Azure

  • Article

S’applique à :SQL Server sur la machine virtuelle Azure

Cet article vous apprend à configurer la copie des journaux de transaction entre deux serveurs SQL Server sur Azure Machines Virtuelles (machines virtuelles).

Vue d’ensemble

La copie des journaux de transaction permet d'envoyer automatiquement des sauvegardes de journaux de transactions d'une base de données primaire sur un serveur principal vers une ou plusieurs bases de données secondaires sur un serveur secondaire séparé. Les sauvegardes du journal des transactions sont appliquées individuellement à chacune des bases de données secondaires. Une troisième instance de serveur facultatif, appelée serveur moniteur, enregistre l'historique et l'état des opérations de sauvegarde et restauration, puis déclenche éventuellement des alertes si ces opérations ne sont pas effectuées selon la planification établie.

La copie des journaux de transaction est principalement utilisée comme solution de récupération d’urgence et peut être combinée à d’autres options de haute disponibilité et récupération d'urgence, notamment des groupes de disponibilité Always On.

Prérequis

Pour configurer la copie des journaux de transaction pour SQL Server sur des machines virtuelles Azure, vous devez disposer des conditions préalables suivantes :

  • Au moins deux machines virtuelles Azure jointes à un domaine avec SQL Server dans le même groupe de ressources qu’un compte de stockage Azure pour les sauvegardes de journal des transactions. Le serveur secondaire doit se trouver sur la même version ou ultérieure de SQL Server que le serveur SQL Server principal.

  • La base de données primaire doit utiliser le mode de récupération utilisant les journaux de transactions complète ou en bloc. La copie des journaux de transaction cesse de fonctionner si la base de données primaire est basculée vers un mode de récupération simple.

  • Le compte qui configure la copie des journaux de transaction doit être membre du rôle serveur fixe sysadmin.

Création d’un partage de fichiers Azure

Les sauvegardes du journal des transactions du serveur principal sont stockées dans partage de fichiers. Avant de configurer la copie des journaux de transaction, vous devez créer un partage de fichiers Azure à l’intérieur d’un compte de stockage Azure accessible à la fois par les serveurs principaux et secondaires.

Pour créer votre partage de fichiers Azure dans le portail Azure, procédez comme suit :

  1. Accédez à votre groupe de ressources dans le portail Azure et sélectionnez le compte de stockage que vous avez l'intention d'utiliser pour les sauvegardes du journal des transactions.

  2. Sous Stockage de données, sélectionnez Partages de fichiers, puis choisissez +Partage de fichiers pour créer un nouveau partage de fichiers.

    Screenshot of the File share creation option in the Azure portal.

  3. Sous l’onglet Informations de base, indiquez le nom du partage de fichiers, tel que la copie des journaux de transaction. Vous pouvez laisser le Niveau par défaut de Transaction optimisée.

  4. (Facultatif) Sous l’onglet Sauvegarde, cochez la case pour activer les sauvegardes de votre partage de fichiers dans la Sauvegarde Azure.

  5. Sélectionnez Vérifier + créer pour passer en revue vos paramètres de partage de fichiers, puis sélectionnez Créer pour créer votre nouveau partage de fichiers.

Créer des répertoires de sauvegarde

Une fois le partage de fichiers créé, vous devez créer les deux répertoires suivants :

  • Un répertoire dans lequel le primaire écrira les sauvegardes de fichier journal
  • Un répertoire pour le secondaire afin de copier et de restaurer la sauvegarde de fichier journal

Pour créer les répertoires, procédez comme suit :

  1. Une fois qu’Azure a créé le partage de fichiers, le portail vous renvoie à la page Vue d’ensemble de votre nouveau fichier SMB.

  2. Sous Parcourir, sélectionnez + Ajouter un répertoire. Indiquez le nom du nouveau répertoire, tel que les sauvegardes de journaux. Cliquez sur OK.

    Screenshot of the add directory creation option in the Azure portal.

  3. Répétez l’étape précédente pour ajouter un deuxième répertoire, tel que les sauvegardes de restauration. Cliquez sur OK.

Connectez les machines virtuelles aux partages de fichiers

Une fois les répertoires créés, connectez les machines virtuelles au partage de fichiers.

Pour déterminer les détails de connexion, sélectionnez Connecter dans la page Parcourir ou Vue d’ensemble du partage de fichiers pour ouvrir la fenêtre Connecter.

Screenshot of the Connect option for the file share in the Azure portal.

La fenêtre Connecter fournit un script pour permettre à une ressource d’accéder au partage de fichiers. Si vous le souhaitez, modifiez la lettre de lecteur pour monter le partage de fichiers sur l’ordinateur virtuel. Ce guide utilise une clé de compte de stockage pour une machine virtuelle Windows.

Sélectionnez Afficher le script pour afficher le script, le copier, puis l’exécuter sur chaque machine virtuelle SQL Server où vous prévoyez de configurer la copie des journaux de transaction.

Après avoir exécuté le script Connecter, vous pouvez utiliser l’applet de commande PowerShell suivante pour vérifier la connectivité au port 445 :

Test-NetConnection -ComputerName yourstorageaccount.file.core.windows.net -Port 445

Si le test de connexion réussit, vous voyez une sortie de TcpTestSucceeded : True.

Accorder à SQL Server l’accès au partage de fichiers

Une fois que les machines virtuelles SQL Server peuvent se connecter au partage de fichiers, accordez au compte de service SQL Server l’autorisation d’accéder au partage de fichiers en créant des informations d’identification dans SQL Server à l’aide de l’URL, du nom d’utilisateur et du mot de passe à partir du script Connecter.

Pour créer les identifiants, activez xp_cmdshell et utilisez-les pour créer les identifiants avant de désactiver xp_cmdshell une fois de plus.

Pour accorder au compte de service SQL Server l’accès au partage de fichiers, procédez comme suit sur chaque instance SQL Server que vous envisagez d’utiliser pour la copie des journaux de transaction :

  1. Connecter à la machine virtuelle SQL Server avec un compte qui fait partie du rôle sysadmin.

  2. Ouvrez SQL Server Management Studio (SSMS) et connectez-vous à votre instance SQL Server.

  3. Ouvrez une nouvelle fenêtre Requête et exécutez le code Transact-SQL suivant contenant les détails de la clé de stockage obtenus à partir de l’Portail Azure :

    EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO
EXEC xp_cmdshell 'cmdkey /add:"yourstorageaccount.file.core.windows.net" /user:"localhost\yourstorageaccount" /pass:"<yourpasskey>"';
GO
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
GO

    Après avoir exécuté la commande, SSMS confirme que vos identifiants sont ajoutées correctement :

    Screenshot of the confirmation the credential was successfully created in SSMS.

Configurer la copie des journaux de transaction

Une fois que les instances SQL Server peuvent accéder au partage de fichiers, utilisez SQL Server Management Studio (SSMS) pour configurer la copie des journaux de transaction.

Pour configurer la copie des journaux de transaction, procédez comme suit :

  1. Connectez-vous à votre instance primaire de SQL Server.

  2. Cliquez avec le bouton droit sur la base de données à utiliser en tant que base de données primaire dans la configuration de la copie des journaux de transaction, puis sélectionnez Propriétés.

  3. Sous Sélectionner une page, cliquez sur Envoi du journal des transactions.

  4. Cochez la case à côté de Activer cette base de données en tant que base de données primaire dans une configuration de la copie des journaux de transaction.

  5. Sous Sauvegardes du journal des transactions, cliquez sur Paramètres de sauvegarde.

  6. Dans la zone Chemin d'accès réseau au dossier de sauvegarde, saisissez le chemin réseau vers le partage et le répertoire que vous avez créés pour le dossier de sauvegarde du journal des transactions.

    Par exemple : \\yourstorageaccount.file.core.windows.net\log-shipping\log-backups

  7. Configurez les paramètres Supprimer les fichiers antérieurs à et Envoyer une alerte si aucune sauvegarde ne se produit dans l'espace de en fonction des besoins de votre entreprise.

    1. Notez la planification de la sauvegarde figurant dans la zone Planification sous Travail de sauvegarde. Si vous souhaitez personnaliser la planification pour votre installation, cliquez ensuite sur Planification et ajustez la planification de SQL Server Agent en fonction de vos besoins.

    2. SQL Server prend en charge la compression de la sauvegarde. Lorsque vous créez une configuration de copie des journaux de transaction, vous pouvez contrôler le comportement de compression des sauvegardes de fichiers journaux grâce à l’une des options suivantes : Utiliser le paramètre de serveur par défaut, Compresser la sauvegarde, ou Ne pas compresser la sauvegarde. Pour plus d’informations, consultez Log Shipping Transaction Log Backup Settings.

    3. Sélectionnez OK pour enregistrer vos paramètres.

  8. Sous Instances de serveurs secondaires et bases de données secondaires, cliquez sur Ajouter.

  9. Cliquez sur Se connecter et connectez-vous à l'instance de SQL Server à utiliser comme serveur secondaire.

    1. Dans la zone Base de données secondaire , choisissez une base de données dans la liste ou tapez le nom de la base de données que vous voulez créer.

    2. Dans l'onglet Initialiser la base de données secondaire , choisissez l'option à utiliser pour initialiser la base de données secondaire.

    Remarque

    Si vous choisissez de demander au SSMS d'initialiser la base de données secondaire à partir d'une sauvegarde de base de données, les fichiers de données et de journaux de la base de données secondaire sont placés au même endroit que les fichiers de données et de journaux de la base de données master. Il est probable que cet emplacement soit différent de celui des fichiers de données et des fichiers journaux de la base de données primaire.

  10. Sous l’onglet Copier des fichiers , dans le dossier de destination pour les fichiers copiés, tapez le chemin d’accès du dossier dans lequel vous souhaitez copier les sauvegardes des journaux des transactions, comme le répertoire restore-backups que vous avez créé pour votre partage de fichiers :

    \\yourstorageaccount.file.core.windows.net\log-shipping\restore-backups

    1. Notez la planification de la copie figurant dans la zone Planification sous Copier le travail. Si vous souhaitez personnaliser la planification pour votre installation, sélectionnez Planification et ajustez la planification de SQL Server Agent en fonction de vos besoins. Cette planification doit être proche de la planification de la sauvegarde.

  11. Dans l’onglet Restaurer , sous État de la base de données lors de la restauration des sauvegardes, choisissez l’option Aucun mode de récupération ou Mode veille .

    Important

    L’option Mode veille n’est disponible que lorsque la version du serveur principal et celle du serveur secondaire sont identiques. Lorsque la version principale du serveur secondaire est supérieure à celle du serveur principal, seule l’option Aucun mode de récupération est autorisée.

    1. Si vous choisissez Mode veille , déterminez si vous voulez déconnecter des utilisateurs de la base de données secondaire pendant que l'opération de restauration est en cours.

    2. Si vous voulez retarder le processus de restauration sur le serveur secondaire, choisissez un délai sous Retarder la restauration des sauvegardes d'au moins.

    3. Choisissez un seuil d'alerte sous Envoyer une alerte si aucune restauration ne se produit dans l'espace de.

    4. Notez la planification de la restauration figurant dans la zone Planification sous Restaurer le travail. Si vous souhaitez personnaliser la planification pour votre installation, sélectionnez Planification et ajustez la planification de SQL Server Agent en fonction de vos besoins. Cette planification doit être proche de la planification de la sauvegarde.

    5. Sélectionnez OK pour enregistrer vos paramètres.

  12. (Facultatif) Sous Instance du serveur moniteur, activez la case à cocher Utiliser une instance du serveur moniteur , puis cliquez sur Paramètres.

    Important

    Pour analyser cette configuration d'envoi de journaux, vous devez ajouter maintenant le serveur moniteur. Pour ajouter le serveur moniteur ultérieurement, vous devrez supprimer configuration de la copie des journaux de transaction, puis la remplacer par une nouvelle configuration qui inclut un serveur moniteur.

    1. Cliquez sur Connecter et connectez-vous à l'instance de SQL Server que vous souhaitez utiliser en tant que serveur moniteur.

    2. Sous Connexions du moniteur, choisissez la méthode de connexion que devront utiliser les travaux de sauvegarde, copie et restauration pour se connecter au serveur moniteur.

    3. Sous Rétention de l'historique, choisissez la durée pendant laquelle vous voulez conserver un enregistrement de l'historique de copie des journaux de transactions.

    4. Sélectionnez OK pour enregistrer vos paramètres.

  13. Dans la zone de dialogue Propriétés de la base de données , cliquez sur OK pour démarrer le processus de configuration.

Contenu connexe