Exercice - Restaurer à un point dans le temps

  • 10 minutes

Dans cet exercice, vous allez découvrir comment récupérer le contrôle des opérations après une erreur courante grâce à la restauration à un instant dans le passé (PITR). Ce processus est facile à suivre dans le portail ou par programmation. Dans cet exercice, vous allez apprendre à le faire à l’aide d’Azure CLI.

Configuration : Utiliser des scripts pour déployer Azure SQL Database

Dans le terminal de droite, vous voyez Azure Cloud Shell, qui permet d’interagir avec Azure à l’aide d’un navigateur. Avant de démarrer les exercices, exécutez un script pour créer votre environnement : Azure SQL Database avec la base de données AdventureWorks. Certaines invites, dans le script, vous demandent d’entrer un mot de passe et votre adresse IP locale.

L’exécution de ces scripts prend trois à cinq minutes. Veillez à noter votre mot de passe, votre ID unique et votre région, car ils ne seront plus de nouveau affichés.

  1. Pour obtenir l’adresse IP requise, vous devez vous déconnecter des services VPN et exécuter (Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content dans une fenêtre PowerShell locale (pas dans ce navigateur). Notez l’adresse IP affichée.

  2. Dans Azure Cloud Shell à droite de cette page, exécutez le script suivant. Saisissez un mot de passe complexe et une adresse IP publique quand vous y êtes invité.

    $adminSqlLogin = "cloudadmin"
$password = Read-Host "Your username is 'cloudadmin'. Please enter a password for your Azure SQL Database server that meets the password requirements"
# Prompt for local IP address
$ipAddress = Read-Host "Disconnect your VPN, open PowerShell on your machine and run '(Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content'. Please enter the value (include periods) next to 'Address': "
# Get resource group and location and random string
$resourceGroup = Get-AzResourceGroup | Where ResourceGroupName -like "<rgn>Sandbox resource group name</rgn>"
$resourceGroupName = "<rgn>Sandbox resource group name</rgn>"
$uniqueID = Get-Random -Minimum 100000 -Maximum 1000000
$storageAccountName = "mslearnsa"+$uniqueID
$location = $resourceGroup.Location
# The logical server name has to be unique in the system
$serverName = "aw-server$($uniqueID)"

  3. Sortez et stockez (dans un fichier texte ou un emplacement similaire) les informations dont vous aurez besoin dans tout le module en exécutant le script suivant dans Azure Cloud Shell. Vous devrez probablement sélectionner Entrer après avoir collé le code, car la dernière ligne n’est pas exécutée par défaut.

    Write-Host "Please note your unique ID for future exercises in this module:"  
Write-Host $uniqueID
Write-Host "Your resource group name is:"
Write-Host $resourceGroupName
Write-Host "Your resources were deployed in the following region:"
Write-Host $location
Write-Host "Your server name is:"
Write-Host $serverName

    Important

    N’oubliez pas de noter votre mot de passe, votre ID unique et votre région. Vous aurez besoin de ces informations dans l’ensemble du module.

  4. Exécutez le script suivant pour déployer une base de données et un serveur logique Azure SQL avec l’échantillon AdventureWorks. Ce script ajoute également votre adresse IP en tant que règle de pare-feu, active Microsoft Defender pour le cloud, et crée un compte de stockage à utiliser dans les prochaines unités.

    # The logical server name has to be unique in the system
$serverName = "aw-server$($uniqueID)"
# The sample database name
$databaseName = "AdventureWorks"
# The storage account name has to be unique in the system
$storageAccountName = $("sql$($uniqueID)")
# Create a new server with a system-wide unique server name
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Create a server firewall rule that allows access from the specified IP range and all Azure services
$serverFirewallRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -FirewallRuleName "AllowedIPs" `
    -StartIpAddress $ipAddress -EndIpAddress $ipAddress
$allowAzureIpsRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -AllowAllAzureIPs
# Create a database
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -SampleName "AdventureWorksLT" `
    -Edition "GeneralPurpose" -Vcore 2 -ComputeGeneration "Gen5"
# Enable Azure Defender
$azureDefender = Enable-AzSqlServerAdvancedDataSecurity `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName
# Create a storage account
$storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroupName `
    -AccountName $storageAccountName `
    -Location $location `
    -Type "Standard_LRS"

  5. Sur votre ordinateur local, ouvrez SSMS et créez une nouvelle connexion à votre serveur logique. Pour le nom du serveur, entrez le nom de votre serveur logique Azure SQL Database. Si vous n’avez pas enregistré le nom précédemment, vous devrez peut-être faire référence au Portail Azure pour l’obtenir. Par exemple : aw-server\<unique ID>.database.windows.net.

    Azure portal

    1. Une fois dans le Portail Azure, vous pouvez entrerAdventureWorks dans la zone de recherche pour rechercher votre base de données et son serveur logique associé.

    2. Pour Authentification, entrez Authentification SQL Server. Entrez les informations de connexion et le mot de passe Administrateur du serveur correspondants (que vous avez fournis lors du déploiement dans l’exercice précédent).

    3. Cochez la case Mémoriser le mot de passe, puis sélectionnez Connexion.

    Capture d’écran montrant comment se connecter à SQL Database dans SSMS.

    Notes

    En fonction de votre configuration locale (par exemple un VPN), l’adresse IP de votre client peut différer de l’adresse IP que le Portail Azure a utilisé lors du déploiement. Si c’est le cas, le message suivant s’affiche : « Votre adresse IP cliente n’a pas accès au serveur. Connectez-vous à un compte Azure et créez une nouvelle règle de pare-feu pour activer l’accès. Si vous recevez ce message, connectez-vous avec le compte que vous utilisez pour le bac à sable (sandbox) et ajoutez une règle de pare-feu pour l’adresse IP de votre client. Vous pouvez suivre toutes ces étapes à l’aide de l’Assistant contextuel dans SSMS.

Terminer la restauration à un instant dans le passé

Avant d’aller plus loin, il est important de comprendre le processus recommandé pour effectuer une restauration à un instant dans le passé :

  1. Une table ou une base de données est supprimée accidentellement.
  2. Déterminez l’heure à laquelle vous devez revenir. Elle doit être antérieure au moment où l’erreur se produit.
  3. Effectuez la restauration à un instant dans le passé via PowerShell ou le Portail Azure pour revenir à cet instant. Ce processus déploie une nouvelle base de données et restaure une copie de votre base de données. Par exemple : AdventureWorks-copy.
  4. Vérifiez que la nouvelle base de données (par exemple AdventureWorks-copy) est dans un état approprié (l’état antérieur à l’accident).
  5. Renommer la base de données d'origine. Par exemple, renommez AdventureWorks en AdventureWorks-old.
  6. Renommez la nouvelle base de données avec le nom de la base de données d’origine. Par exemple, renommez AdventureWorks-copy en AdventureWorks.
  7. Supprimer la base de données d'origine. Par exemple : AdventureWorks-old.

Dans cet exercice, vous allez effectuer les étapes suivantes.

Simuler une suppression de données

Tout d’abord, confirmez que la table qui va être supprimée accidentellement existe et qu’elle contient des données. Examinons certaines des valeurs dans SalesLT.OrderDetail.

  1. Accédez à SSMS et vérifiez/mettez à jour votre connexion. Sélectionnez Fichier>Connecter l’Explorateur d’objets, puis sélectionnez le bouton Options.

  2. Assurez-vous que la connexion que vous utilisez se connecte au serveur logique, mais pas à une base de données spécifique. (Par exemple, utilisez <par défaut> comme indiqué par la capture d’écran suivante.) Vérifiez également que l’onglet Paramètres de connexion supplémentaires ne contient pas de texte.

    Capture d’écran montrant la connexion par défaut.

  3. Développez le dossier Bases de données, cliquez avec le bouton droit sur votre base de données AdventureWorks, puis sélectionnez Nouvelle requête. Entrez la requête suivante, et exécutez-la en sélectionnant Exécuter, puis passez en revue les résultats :

    SELECT TOP 10 * from SalesLT.SalesOrderDetail

    Capture d’écran montrant la table des détails des commandes clients.

  4. Simulez la perte de données en déposant une table dans la base de données.

    Dans la même fenêtre de requête, exécutez cette requête, sélectionnez l’onglet Messages dans la fenêtre Résultats, puis notez l’heure de fin d’exécution :

    DROP TABLE SalesLT.SalesOrderDetail

    Important

    Enregistrez l’heure de fin. Vous en aurez besoin ultérieurement. Voici un exemple : Completion time: 2020-06-22T09:20:27.1859237-07:00.

  5. Enfin, avant de démarrer les étapes de restauration de la base de données, exécutez le code suivant dans Azure Cloud Shell sur le côté droit de cette page pour configurer votre environnement :

    $resourceGroup = Get-AzResourceGroup | Where ResourceGroupName -like <rgn>Sandbox resource group name</rgn>
$server = Get-AzureRmSqlServer -ResourceGroupName $resourceGroup.ResourceGroupName
$logical_server = $server.ServerName
$resource_group = $resourceGroup.ResourceGroupName

# Specify your default resource group and Azure SQL Database logical server
az configure --defaults group=$resource_group sql-server=$logical_server

# Confirm the defaults are set
az configure --list-defaults

    Les paramètres group et sql-server retournés doivent correspondre aux noms de votre groupe de ressources Microsoft Learn et de votre serveur logique Azure SQL Database.

Identifier l’instant dans le passé auquel restaurer la base de données

La première étape consiste à déterminer l’instant dans le passé auquel restaurer la base de données. Vous devez savoir quand la dernière transaction « correcte » s’est produite avant la « mauvaise ». Vous allez procéder à une restauration avant la transaction incorrecte, mais après la dernière transaction correcte.

  1. Une des façons de déterminer l’heure d’annulation est de consulter l’heure d’exécution de l’instruction DROP, que vous avez notée lors de l’étape précédente.

    Vous pouvez également utiliser les journaux d’audit dans le portail Azure. Dans cet exercice, vous n’avez pas configuré l’audit pour Log Analytics. Découvrons ce que vous auriez pu faire si vous l’aviez configuré. Vous pourriez accéder à votre base de données Azure SQL dans le portail Azure. Dans le volet gauche, sous Sécurité, sélectionnez Audit, puis Afficher les journaux d’audit. Si vous sélectionnez ensuite Log Analytics, vous êtes redirigé vers un éditeur de requête qui vous permet d’interroger les journaux à l’aide du langage KQL (Langage de requête Kusto). Les professionnels SQL peuvent utiliser ce langage de requête pour interroger facilement les journaux de requêtes.

    Vous pouvez ensuite exécuter la requête KQL suivante :

    search database_name_s == "AdventureWorks"
| where Category == 'SQLSecurityAuditEvents' and statement_s like 'DROP'
| project format_datetime(event_time_t, 'yyyy-MM-dd hh:mm:ss.fff'), ResourceGroup, server_instance_name_s, database_name_s,  statement_s, succeeded_s,client_ip_s, server_principal_name_s, application_name_s
| sort by event_time_t desc

    Les résultats doivent ressembler aux suivants, mais avec une date et une heure différentes.

    Capture d’écran montrant les résultats Log Analytics.

    Remarque

    L’affichage des journaux ici peut prendre de 5 à 10 minutes. Nous l’avons donc omis dans le cadre de cet exercice. Vous allez utiliser à la place l’heure de fin d’exécution que vous avez notée à l’étape précédente. (Vous devez la convertir en GMT.) Dans une situation réelle, vous ne serez probablement pas en mesure d’accéder à la fenêtre avec l’heure d’exécution. C’est pourquoi l’audit peut être très utile.

  2. Dans cet exemple, la date/l’heure est 2020-07-24 08:06:24.386 pour Log Analytics et 2020-07-24T13:06:24.386-07:00 pour SSMS. Le format requis est légèrement différent. Utilisez l’exemple suivant pour déterminer le format correct. Vous pouvez également soustraire 0,001 secondes pour être sûr de restaurer à un point dans le temps avant que l’erreur se soit produite :

    • Format Log Analytics : 2020-07-24 08:06:24.386
    • Format SSMS : 2020-07-24T13:06:24.386-07:00
    • Format requis : 2020-07-24T20:06:24.385

  3. Affectez la valeur résultante à $before_error_time, en remplaçant l’heure de l’exemple par votre heure :

    $before_error_time ="2020-07-24T20:06:24.385"

Restaurer la base de données et confirmer les données manquantes

Dans cette section, vous allez utiliser az cli db restore pour restaurer la base de données jusqu’à une heure avant la suppression de la table.

  1. Exécutez le script suivant dans le terminal à droite de cette fenêtre :

    # Restore the database to a time before the database was deleted
az sql db restore --dest-name "AdventureWorks-copy" --name "AdventureWorks" --time $before_error_time --verbose

    La restauration prend entre 5 et 10 minutes environ. Quand vous effectuez une restauration, Azure déploie une nouvelle base de données Azure SQL sur votre serveur logique Azure SQL Database. La nouvelle base de données a les mêmes options de configuration que l’original. Une fois la base de données Azure SQL déployée, Azure la restaure dans la nouvelle base de données Azure SQL.

  2. Vous pouvez vérifier l’état en actualisant l’affichage des bases de données dans SSMS. Cliquez avec le bouton droit sur le dossier Bases de données, puis sélectionnez Actualiser. Une fois la base de données déployée, vous verrez que la restauration est en cours :

    Capture d’écran montrant une restauration de base de données dans SSMS.

    Une fois que vous voyez que la restauration est en cours, celle-ci doit prendre entre deux et trois minutes de plus. Vous savez que c’est fini quand la commande se termine. Vous ne voyez plus non plus « (Restauration en cours...) » à côté de la copie de la base de données quand vous lancez une actualisation.

    Si vous remarquez que la restauration prend plus de temps que les durées décrites précédemment, cela peut être dû à votre environnement Microsoft Learn. Il existe un nombre limité de requêtes de restauration pouvant être traitées/soumises simultanément pour un même abonnement. Si vous souhaitez en savoir plus sur les limites et les détails associés relatifs à la restauration à un instant dans le passé (PITR) pendant que vous patientez, consultez Restaurer une base de données à partir d’une sauvegarde dans Azure SQL Database.

  3. Confirmez que la nouvelle base de données est dans un état correct (celui qu’il avait avant que l’accident se soit produit). Cliquez avec le bouton de droite sur le serveur logique dans SSMS, puis sélectionnez Actualiser pour actualiser votre connexion au serveur logique Azure SQL Database.

  4. Cliquez avec le bouton de droite sur votre nouvelle base de données (par exemple AdventureWorks-copy) et sélectionnez Nouvelle requête.

    Capture d’écran montrant comment créer une requête.

  5. Utilisez cette requête pour confirmer que la table existe :

    SELECT TOP 10 * from SalesLT.SalesOrderDetail

    Vous devez obtenir des résultats similaires aux résultats affichés dans la capture d’écran suivante. Ce résultat confirme que votre base de données est restaurée à l’emplacement de votre choix.

    Capture d’écran montrant la table des détails des commandes clients.

Échanger les bases de données et les nettoyer

Renommez ensuite la base de données d’origine en AdventureWorks-old pour pouvoir renommer plus tard la nouvelle base de données en utilisant le nom de la base de données d’origine. Dès que vos applications utilisent une logique de nouvelle tentative, cela va fonctionner : aucune chaîne de connexion ne doit donc être modifiée.

Si, à un moment quelconque, votre base de données s’affiche comme étant non disponible (par exemple si vous ne pouvez pas vous connecter aux bases de données dans SSMS quand vous actualisez la connexion), cela peut être dû à l’exécution de mises à jour dans la table DNS. Donc, bien que la base de données n’est pas physiquement indisponible, la résolution n’est pas possible. Si vous attendez environ une minute, vous devez normalement pouvoir reprendre des activités normales.

  1. Utilisez cette commande pour modifier le nom de la base de données :

    az sql db rename --name "AdventureWorks" --new-name "AdventureWorks-old"

  2. Maintenant que le nom de la base de données d’origine n’est plus utilisé, vous pouvez renommer la copie de la base de données, en utilisant encore une fois Azure Cloud Shell :

    az sql db rename --name "AdventureWorks-copy" --new-name "AdventureWorks"

  3. Vous n’avez pas besoin de l’ancienne base de données. vous pouvez donc la supprimer à l’aide de az sql db delete :

    az sql db delete --name "AdventureWorks-old" --yes
Write-Host "Database deleted"

  4. Vous pouvez confirmer que l’ancienne base de données n’existe plus à l’aide de la commande suivante :

    az sql db list -o table

Vous avez maintenant vu comment utiliser la restauration à un instant dans le passé dans Azure SQL Database. La restauration à un instant dans le passé est également disponible dans Azure SQL Managed Instance, pour les bases de données, mais pas pour toute l’instance. Vous pouvez utiliser presque les mêmes commandes, sauf que vous devez utiliser az sql midb au lieu de az sql db.