Mise à jour de bases Azure SQL depuis production vers environnement de test avec un Azure Automation

En déployant divers environnements d'exécution de nos applications : test/pré-production/validation/développement... Il est parfois nécessaire de travailler sur des données issues du monde réel : la production.
Pour faire ça manuellement la procédure suivante était lancée :

1. On demande au DBA de remonter une base de donnée
2. Il se connecte sur le serveur
3. Il lance une sauvegarde ou récupère une sauvegarde existante
4. Il télécharge la sauvegarde sur son poste (des fois avec une étape de compression/décompression)
5. Il bloque les utilisateurs sur le serveur cible
6. Il restaure la sauvegarde sur le serveur cible
7. Il exécute certaines requête SQL (anonymisation par exemple) 
8. Il dit au développeur que tout est OK, qui derrière peuvent avoir à faire certaines tâches : restart application, vidage de caches...

Nombreuses sont les erreurs possibles pendant ce processus :

• Sauvegarde du mauvais serveur / mauvaise base de donnée
• Restauration sur le mauvais serveur / mauvaise base de donnée
• Oubli des requêtes SQL
• Exécution des requêtes sur le mauvais serveur 
• Oublie d'une étape par le développeur
• ...

Donc il convient d'automatiser cette procédure pour être plus stable, libérer du temps au DBA et donner plus d'indépendance aux développeurs.

Sur notre dernier projet, la base de donnée est une base Azure SQL (version PaaS de SQL Server 2016).

Nous allons donc automatiser cette remontée grâce aux outils de tâches planifiées fourni dans Azure : Azure Automation.

Azure Automation

Il faut commencer par créer le compte d'automation :

1. Dans le portail Azure, click + vert
2. "Automation"
3. Clicker sur le premier item "Automation Microsoft Developer Tools"
4. "Create"
5. Donnez lui un nom (unique)
6. Un abonnement
7. Un groupe de ressource
8. Run as account "Yes"

Credentials

Pour accéder à vos ressources Azure depuis vos scripts vous allez devoir vous authentifier. Au lieu de mettre des identifiants en clair dans le code, les comptes d'automatisations permettent de stocker des identifiants et de les récupérer via PowerShell.

1. Dans le compte d'automation
2. Colonne de gauche => Credentials
3. "Add a credential"
4. Dans "Name" entrez le nom que vous allez utiliser pour récupérer ces credentials
5. "user name" le nom d'utilisateur (dans notre cas il doit avoir accès aux deux bases SQL en administrateur)
6. "Password" ....

Runbooks

Un "runbook" est une tâche planifiée. Donc il faut en ajouter une

1. "+ Add a runbook" (Microsoft fourni toute une galerie de runbook, mais évidement pas ce qu'on recherche)
2. "Quick create"
3. "Name" : le nom ...
4. "Runbook type" : "Powershell"
5. "Create"

Une fois sur le runbook cliquez sur le bouton "Edit"

La une fenêtre d'édition de texte apparaît, voici notre script

workflow RemonterData
{
 #nom du serveur source Azure SQL, sans "database.windows.net"
    $sourceServer = "source-server" 
 # nom de la bdd sur le serveur source
    $sourceDatabase = "a-database"
 nom du serveur source Azure SQL, sans "database.windows.net"
    $targetServer = "target-server"
 # nom de la bdd sur le serveur cible
    $targetDatabaseName = "a-database"
 # nom du groupe de ressource ou sont situées ces ressource, si ils sont différent, il faudra les dédoubler
    $ressourceGroupName = "AGroup"
 #on récupère les credentials permettant de se logger au compte Azure
    $AzureCredentials = Get-AutomationPSCredential -Name "AzureAdmin"
    Write-Output "Login"
 #on s'authentifie sur Azure
    Add-AzureAccount –Credential $AzureCredentials 
    Login-AzureRmAccount –Credential $AzureCredentials   
 #id de l'abonnement Azure, nécessaire que si vous avez plusieurs abonnements
    $subscriptionId  = {guid}  
    Select-AzureRmSubscription -SubscriptionId $subscriptionId
 #On tente de supprimer la base cible
    Write-Output "Delete"
    Try{
        Remove-AzureRmSqlDatabase  -ResourceGroupName $ressourceGroupName -ServerName $targetServer -DatabaseName $targetDatabaseName -Force
    }
    Catch{}
 #on restore la source sur la cible
    Write-Output "Restore"
    New-AzureRmSqlDatabaseCopy -ResourceGroupName $ressourceGroupName -ServerName $sourceServer -DatabaseName $sourceDatabase -CopyServerName $targetServer -CopyDatabaseName $targetDatabaseName
 
 #on passe la cible en niveau de tarification "basic" vu que c'est un serveur de test
    Write-Output "Passage en Basic"
    Set-AzureRmSqlDatabase  -ResourceGroupName $ressourceGroupName -ServerName $targetServer -DatabaseName $targetDatabaseName -Edition "Basic"
 
 
    Write-Output "Scripts SQL"    
 #on récupère les identifiants de l'admin du serveur SQL
    $SQLCredentials = Get-AutomationPSCredential -Name "AdminSQL"
    #on execute un script SQL sur la cible pour anonymiser les données
    inlinescript {
        $UserId = $Using:SQLCredentials.UserName
        $Password = ($Using:SQLCredentials).GetNetworkCredential().Password
        $targetServerFullName = "$using:targetServer.database.windows.net"
        Write-Output "Connection a  : $targetServerFullName"     
        $Conn = New-Object System.Data.SqlClient.SqlConnection
        $Conn.ConnectionString = "server=$targetServerFullName; database = $using:targetDatabaseName; user = $UserId; password = $Password;"
        $Conn.Open();        
        $Cmd=new-object system.Data.SqlClient.SqlCommand("{SQL}", $Conn)
        $Cmd.CommandTimeout=120
        $Cmd.ExecuteNonQuery()
        $Conn.Close()
    }
 #lance diverse commandes nécessaire : webjob / restart appli
    $webHookCrdentials = Get-AutomationPSCredential -Name "WebJob"
    Invoke-WebRequest -Uri 'https://{scm-site-web}/api/triggeredwebjobs/ProdversPreProd/run' -Method 'POST' -Credential $webHookCrdentials -UseBasicParsing   -ContentType "application/json" -UserAgent "myuseragent"

    #redemarre l'applicaiton web pour vider les caches/appliquer les migrations EF
    Restart-​Azure​Rm​Web​App -ResourceGroupName $ressourceGroupName -Name "{site-web}"
    
}

La partie finale est intéressante, elle va nous permettre de lancer d'autres commandes relative à cette remontée de donnée : restart web app, lancement web jobs, email ...

Désormais un utilisateur qui a accès à ce runbook peut déclencher une remontée des données facilement sans avoir besoin des accès aux ressources, ni des compétences nécessaires et avec une marge d'erreur réduite.

Documentations

https://docs.microsoft.com/en-us/azure/sql-database/scripts/sql-database-copy-database-to-new-server-powershell

https://docs.microsoft.com/en-us/azure/automation/automation-intro