Préparer manuellement une base de données secondaire pour un groupe de disponibilité (SQL Server)

  • Article

Cette rubrique explique comment préparer une base de données secondaire pour un groupe de disponibilité AlwaysOn dans SQL Server 2014 à l’aide de SQL Server Management Studio, Transact-SQL ou PowerShell. La préparation d'une base de données secondaire s'effectue en deux étapes : (1) restauration d'une sauvegarde de base de données récente de la base de données primaire et des sauvegardes de journaux suivantes sur chaque instance de serveur qui héberge le réplica secondaire, à l'aide de RESTORE WITH NORECOVERY, et (2) attachement de la base de données restaurée au groupe de disponibilité.

Conseil

Si vous disposez d'une configuration de copie des journaux de transaction, vous pouvez peut-être convertir la base de données principale pour la copie des journaux de transaction et une ou plusieurs de ses bases de données secondaires en base de données principale AlwaysOn et une ou plusieurs bases de données secondaires AlwaysOn. Pour plus d’informations, consultez Conditions préalables à la migration de la copie des journaux de transaction vers des groupes de disponibilité AlwaysOn (SQL Server).

Avant de commencer

Conditions préalables requises et restrictions

  • Assurez-vous que le système dans lequel vous envisagez de placer la base de données possède un lecteur de disque avec suffisamment d'espace pour les bases de données secondaires.

  • Le nom de la base de données secondaire doit être identique au nom de la base de données primaire.

  • Utilisez RESTORE WITH NORECOVERY pour chaque opération de restauration.

  • Si la base de données secondaire doit résider sur un chemin d'accès de fichier différent (lettre de lecteur incluse) de celui de la base de données primaire, la commande de restauration doit également utiliser l'option WITH MOVE pour chacun des fichiers de base de données afin de les spécifier au chemin d'accès de la base de données secondaire.

  • Si vous restaurez le groupe de fichiers de base de données par groupe de fichiers, veillez à restaurer l'intégralité de la base de données.

  • Après la restauration de la base de données, vous devez restaurer (WITH NORECOVERY) chaque sauvegarde de journal créée depuis la dernière sauvegarde de données restaurée.

Recommandations

  • Sur les instances autonomes de SQL Server, nous recommandons, si possible, que le chemin d'accès au fichier (y compris la lettre de lecteur) d'une base de données secondaire particulière soit identique au chemin d'accès de la base de données primaire correspondante. Cela est dû au fait que, si vous déplacez les fichiers de base de données lors de la création d'une base de données secondaire, une opération ultérieure d'ajout de fichier peut échouer sur la base de données secondaire et provoquer l'interruption de la base de données secondaire.

  • Avant de préparer vos bases de données secondaires, il est fortement recommandé d'interrompre les sauvegardes de fichiers journaux planifiées sur les bases de données dans le groupe de disponibilité jusqu'à ce que l'initialisation des réplicas secondaires soit terminée.

Sécurité

Lorsqu'une base de données est sauvegardée, la valeur OFF est attribuée à la propriété TRUSTWORTHY de la base de données. Par conséquent, la propriété TRUSTWORTHY d'une base de données nouvellement restaurée a toujours la valeur OFF.

Autorisations

Les autorisations BACKUP DATABASE et BACKUP LOG reviennent par défaut aux membres du rôle serveur fixe sysadmin et des rôles de base de données fixes db_owner et db_backupoperator . Pour plus d’informations, consultez BACKUP (Transact-SQL).

Lorsque la base de données en cours de restauration n'existe pas dans l'instance de serveur, l'instruction RESTORE nécessite des autorisations CREATE DATABASE. Pour plus d’informations, consultez RESTORE (Transact-SQL).

Utilisation de SQL Server Management Studio

Remarque

Si les chemins de fichier de sauvegarde et de restauration sont identiques entre l’instance de serveur qui héberge le réplica principal et chaque instance qui héberge un réplica secondaire, vous devez pouvoir créer des bases de données secondaires à l’aide de l’ Assistant Nouveau groupe de disponibilité, de l’ Assistant Ajouter un réplica au groupe de disponibilitéou de l’ Assistant Ajouter une base de données au groupe de disponibilité.

Pour préparer une base de données secondaire

  1. À moins d'avoir déjà une sauvegarde de base de données récente de la base de données primaire, créez une nouvelle sauvegarde complète ou différentielle de base de données. À titre de recommandation, placez cette sauvegarde et toutes les sauvegardes du journal réalisées ultérieurement sur le partage réseau recommandé.

  2. Créez au moins une nouvelle sauvegarde du journal de la base de données primaire.

  3. Sur l'instance de serveur qui héberge le réplica secondaire, restaurez la sauvegarde complète de la base de données primaire (et éventuellement une sauvegarde différentielle) suivies de toutes les sauvegardes de journaux suivantes.

    Dans la page Options de RESTORE DATABASE , sélectionnez Laisser la base de données non opérationnelle, et ne pas restaurer les transactions non validées. Les journaux des transactions supplémentaires peuvent être restaurés. (RESTORE WITH NORECOVERY).

    Si les chemins d'accès de fichier de la base de données primaire et de la base de données secondaire diffèrent, par exemple, si la base de données primaire se trouve sur le lecteur « F: », mais que l'instance de serveur qui héberge le réplica secondaire ne dispose pas de lecteur F:, incluez l'option MOVE dans votre clause WITH.

  4. Pour terminer la configuration de la base de données secondaire, vous devez attacher la base de données secondaire au groupe de disponibilité. Pour plus d’informations, consultez Joindre une base de données secondaire à un groupe de disponibilité (SQL Server).

Notes

Pour plus d'informations sur l'exécution de ces opérations de sauvegarde et de restauration, consultez Tâches connexes de sauvegarde et de restauration, plus loin dans cette section.

Tâches connexes de sauvegarde et de restauration

Pour créer une sauvegarde de base de données

Pour créer une sauvegarde du journal

Pour restaurer des sauvegardes

Utilisation de Transact-SQL

Pour préparer une base de données secondaire

Notes

Pour obtenir un exemple de cette procédure, consultez Exemple (Transact-SQL), plus haut dans cette rubrique.

  1. À moins de disposer d'une sauvegarde complète récente de la base de données primaire, connectez-vous à l'instance de serveur qui héberge le réplica principal et créez une sauvegarde complète de base de données. À titre de recommandation, placez cette sauvegarde et toutes les sauvegardes du journal réalisées ultérieurement sur le partage réseau recommandé.

  2. Sur l'instance de serveur qui héberge le réplica secondaire, restaurez la sauvegarde complète de la base de données primaire (et éventuellement une sauvegarde différentielle) suivies de toutes les sauvegardes de journaux suivantes. Utilisez WITH NORECOVERY pour chaque opération de restauration.

    Si les chemins d'accès de fichier de la base de données primaire et de la base de données secondaire diffèrent, par exemple, si la base de données primaire se trouve sur le lecteur « F: », mais que l'instance de serveur qui héberge le réplica secondaire ne dispose pas de lecteur F:, incluez l'option MOVE dans votre clause WITH.

  3. Si des sauvegardes de journal ont été effectuées sur la base de données primaire après la sauvegarde du journal requise, vous devez également les copier sur l'instance de serveur qui héberge le réplica secondaire et appliquer chacune de ces sauvegardes de journal à la base de données secondaire, en commençant par la première et en utilisant systématiquement RESTORE WITH NORECOVERY.

    Notes

    Une sauvegarde du journal n'existerait pas si la base de données primaire venait d'être créée et qu'aucune sauvegarde du journal n'avait encore été réalisée ou que le mode de récupération venait d'être modifié de simple à complet.

  4. Pour terminer la configuration de la base de données secondaire, vous devez attacher la base de données secondaire au groupe de disponibilité. Pour plus d’informations, consultez Joindre une base de données secondaire à un groupe de disponibilité (SQL Server).

Notes

Pour plus d'informations sur l'exécution de ces opérations de sauvegarde et de restauration, consultez Tâches connexes de sauvegarde et de restauration, plus loin dans cette rubrique.

Exemple Transact-SQL

L'exemple suivant prépare une base de données secondaire. Cet exemple utilise l’exemple de base de données AdventureWorks2012 , qui utilise le modèle de récupération simple par défaut.

  1. Pour utiliser la base de données AdventureWorks2012, modifiez-la pour utiliser le modèle de récupération complète :

    USE master;  
GO  
ALTER DATABASE MyDB1   
SET RECOVERY FULL;  
GO

  2. Après avoir changé le mode de récupération de la base de données de SIMPLE à FULL, créez une sauvegarde complète qui pourra être utilisée pour créer la base de données secondaire. Dans la mesure où le mode de récupération vient d'être modifié, l'option WITH FORMAT est spécifiée pour créer un nouveau support de sauvegarde. Il peut se révéler utile de séparer les sauvegardes effectuées en mode de récupération complète des sauvegardes préalablement effectuées en mode de récupération simple. Pour les besoins de cet exemple, le fichier de sauvegarde (C:\AdventureWorks2012.bak) est créé sur le même lecteur que la base de données.

    Remarque

    Dans le cas d'une base de données de production, il est conseillé de toujours effectuer les sauvegardes sur une unité distincte.

    Sur l'instance de serveur qui héberge le réplica principal (INSTANCE01), créez une sauvegarde complète de la base de données primaire comme suit :

    BACKUP DATABASE MyDB1   
    TO DISK = 'C:\MyDB1.bak'   
    WITH FORMAT  
GO

  3. Copiez la sauvegarde complète sur l'instance de serveur qui héberge le réplica secondaire.

  4. Restaurez la sauvegarde complète, à l'aide de RESTORE WITH NORECOVERY, sur l'instance de serveur qui héberge le réplica secondaire. La commande de restauration varie selon que les chemins d'accès des bases de données primaire et secondaire sont identiques ou non.

    • Si les chemins d'accès sont identiques :

      Sur l'ordinateur qui héberge le réplica secondaire, restaurez la sauvegarde complète comme suit :

      RESTORE DATABASE MyDB1   
    FROM DISK = 'C:\MyDB1.bak'   
    WITH NORECOVERY  
GO

    • Si les chemins d'accès sont différents :

      Si le chemin d'accès de la base de données secondaire n'est pas le même que celui de la base de données primaire (lettres de lecteurs différentes, par exemple), la création de la base de données secondaire requiert l'intégration d'une clause MOVE dans l'opération de restauration.

      Important

      Si les chemins d'accès des bases de données primaire et secondaire diffèrent, vous ne pouvez pas ajouter de fichier. La raison tient à la réception du journal pour l'opération d'ajout de fichier, puisque l'instance de serveur du réplica secondaire tente de placer le nouveau fichier dans le même chemin d'accès que celui utilisé par la base de données primaire.

      Par exemple, la commande suivante restaure une sauvegarde d’une base de données primaire qui réside dans le répertoire de données de l’instance par défaut de SQL Server 2014, C :\Program Files\Microsoft SQL Server\MSSQL12. MSSQLSERVER\MSSQL\DATA. L’opération de base de données de restauration doit déplacer la base de données vers le répertoire de données d’une instance distante de SQL Server 2014 nommée (AlwaysOn1), qui héberge le réplica secondaire sur un autre nœud de cluster. Là, les données et les fichiers journaux sont restaurés dans le répertoire C:\Program Files\Microsoft SQL Server\MSSQL12.ALWAYSON1\MSSQL\DATA . L'opération de restauration utilise WITH NORECOVERY, afin de laisser la base de données secondaire dans la base de données de restauration.

      RESTORE DATABASE MyDB1  
  FROM DISK='C:\MyDB1.bak'  
 WITH NORECOVERY,   
    MOVE 'MyDB1_Data' TO   
     'C:\Program Files\Microsoft SQL Server\MSSQL12.ALWAYSON1\MSSQL\DATA\MyDB1_Data.mdf',   
    MOVE 'MyDB1_Log' TO  
     'C:\Program Files\Microsoft SQL Server\MSSQL12.ALWAYSON1\MSSQL\DATA\MyDB1_Data.ldf';  
GO

  5. Après avoir restauré la sauvegarde complète, vous devez créer une sauvegarde du journal sur la base de données primaire. Par exemple, l’instruction Transact-SQL suivante sauvegarde le journal dans un fichier de sauvegarde nommé E :\MyDB1_log.bak :

    BACKUP LOG MyDB1   
  TO DISK = 'E:\MyDB1_log.bak'   
GO

  6. Avant de pouvoir joindre la base de données au réplica secondaire, vous devez appliquer la sauvegarde du journal requise (et toutes les sauvegardes de journal ultérieures).

    Par exemple, l’instruction Transact-SQL suivante restaure le premier journal à partir de C :\MyDB1.bak :

    RESTORE LOG MyDB1   
  FROM DISK = 'E:\MyDB1_log.bak'   
    WITH FILE=1, NORECOVERY  
GO

  7. Si des sauvegardes de journal supplémentaires se produisent avant la jointure de la base de données au réplica secondaire, vous devez également restaurer toutes ces sauvegardes de journal, dans l'ordre, dans l'instance de serveur qui héberge le réplica secondaire à l'aide de RESTORE WITH NORECOVERY.

    Par exemple, l’instruction Transact-SQL suivante restaure deux journaux supplémentaires à partir d’E  :\MyDB1_log.bak :

    RESTORE LOG MyDB1   
  FROM DISK = 'E:\MyDB1_log.bak'   
    WITH FILE=2, NORECOVERY  
GO  
RESTORE LOG MyDB1   
  FROM DISK = 'E:\MyDB1_log.bak'   
    WITH FILE=3, NORECOVERY  
GO

Utilisation de PowerShell

Pour préparer une base de données secondaire

  1. Si vous devez créer une sauvegarde récente de la base de données primaire, accédez au répertoire (cd) de l'instance de serveur qui héberge le réplica principal.

  2. Utilisez l'applet de commande Backup-SqlDatabase pour créer chacune des sauvegardes.

  3. Remplacez le répertoire (cd) par l'instance de serveur qui héberge le réplica secondaire.

  4. Pour restaurer la base de données et les sauvegardes de journaux de chaque base de données primaire, utilisez l'applet de commande restore-SqlDatabase, en spécifiant le paramètre de restauration NoRecovery. Si les chemins d'accès de fichier diffèrent entre les ordinateurs qui hébergent le réplica principal et le réplica secondaire cible, utilisez également le paramètre de restauration RelocateFile.

    Remarque

    Pour afficher la syntaxe d’une applet de commande, utilisez l’applet Get-Help de commande dans l’environnement SQL Server PowerShell. Pour en savoir plus, voir Get Help SQL Server PowerShell.

  5. Pour terminer la configuration de la base de données secondaire, vous devez l'attacher au groupe de disponibilité. Pour plus d’informations, consultez Joindre une base de données secondaire à un groupe de disponibilité (SQL Server).

Pour configurer et utiliser le fournisseur SQL Server PowerShell

Exemple de commande et de script de sauvegarde et restauration

Les commandes PowerShell suivantes effectuent une sauvegarde complète de base de données et de son journal des transactions dans un partage réseau et restaurent ces sauvegardes depuis ce partage. Dans cet exemple, on suppose que le chemin d'accès de fichier dans lequel la base de données est restaurée est identique au chemin d'accès de fichier dans lequel la base de données a été sauvegardée.

# Create database backup  
Backup-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.bak" -ServerInstance "SourceMachine\Instance"  
# Create log backup  
Backup-SqlDatabase -Database "MyDB1" -BackupAction "Log" -BackupFile "\\share\backups\MyDB1.trn" -ServerInstance "SourceMachine\Instance"  
# Restore database backup
Restore-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.bak" -NoRecovery -ServerInstance "DestinationMachine\Instance"  
# Restore log backup
Restore-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.trn" -RestoreAction "Log" -NoRecovery -ServerInstance "DestinationMachine\Instance"

Suivi : Après avoir préparé une base de données secondaire

Pour terminer la configuration de la base de données secondaire, attachez la base de données nouvellement restaurée au groupe de disponibilité. Pour plus d’informations, consultez Joindre une base de données secondaire à un groupe de disponibilité (SQL Server).

Voir aussi

Vue d’ensemble des groupes de disponibilité AlwaysOn (SQL Server)
BACKUP (Transact-SQL)
Arguments RESTORE (Transact-SQL)
RESTORE (Transact-SQL)
Résoudre les problèmes d’une opération d’ajout de fichier ayant échoué (groupes de disponibilité AlwaysOn)