Blog

le 21/12/2018
Arnaud
Automation and Deployment, Azure, Cloud, SQL Server

Data Migration Assistant : Générer des rapports d’Assessment en PowerShell

Data Migration Assistant : Introduction

Data Migration Assistant (anciennement SQL Server Upgrade Advisor) est l’outil indispensable pour détecter des problèmes de compatibilité lors de vos futures migrations SQL Server.

Intégrant les dernières solutions du Cloud Azure, l’outil (appelons-le DMA) supporte les quatre plateformes cibles suivantes :

SQL Server
Azure SQL Database
Azure SQL Database Managed Instance
SQL Server on Azure Virtual Machines

Si l’outil est très simple d’utilisation (quelques clics seulement suffisent à créer un project d’Assessment), cela peut s’avérer long et fastidieux d’effectuer manuellement la création de nouveaux projets pour un parc SQL assez important.

Heureusement, l’outil DMA dispose d’un mode ligne de commande que je vous propose d’utiliser pour générer un rapport de compatibilité sur projet d’Assessment (les projets de migration n’étant pas encore supportés pour le moment).

DMA en ligne de commandes : Les différentes options

L’ensemble des options disponibles s’affiche par le lancement de la commande ci-dessous (MS-DOS) :

"C:\Program Files\Microsoft Data Migration Assistant\DmaCmd.exe" /help

Voici la liste de ces options :

/?                                               Get help
/help                                            Get help
/AssessmentName                                  Name of the assessment
/AssessmentDatabases                             Space delimited connection strings
/AssessmentTargetPlatform                        Target platform for the assessment:   supported values: AzureSqlDatabase, ManagedSqlServer, SqlServer2012, SqlServer2014, SqlServer2016, SqlServerLinux2017 and SqlServerWindows2017 (default)
/AssessmentEvaluateCompatibilityIssues           Run compatibility rules
/AssessmentEvaluateRecommendations               Run feature recommendation
/AssessmentEvaluateFeatureParity                 Run feature parity rules
/AssessmentOverwriteResult                       Overwrite the result file
/AssessmentResultJson                            Full path to the JSON result file
/AssessmentResultCsv                             Full path to the CSV result file

On voit qu’il est possible de choisir précisément la plateforme cible ainsi que le type de recommandation ou d’évaluation souhaité. Les rapports générés peuvent être de deux formats différents (JSON et CSV).

Comment lancer un rapport d’Assessment DMA en ligne de commande ?

Prenons l’exemple suivant : Nous disposons d’une base de données « Ventes » hébergée dans une instance SQL nommée PRDSQL01\INS01. Nous souhaitons lancer l’Assessment de cette base de données pour une migration vers une cible de type Azure SQL Managed Instance.

Voici la commande en ligne pour générer le rapport d’Assessment de compatibilité au format CSV :

DmaCmd.exe /AssessmentName="Assessment_Ventes"  /AssessmentDatabases="Server=PRDSQL01\INS01;Initial Catalog=Ventes;Integrated Security=true" /AssessmentTargetPlatform=ManagedSqlServer /AssessmentEvaluateCompatibilityIssues /AssessmentOverwriteResult /AssessmentResultCsv="D:\DMA\Results\Assessment_Ventes.csv"

La syntaxe du DMA se révèle assez simple. Après quelques minutes, le résultat du rapport d’Assessment est généré au format CSV.

Générer un seul rapport est simple. Cependant, la complexité peut vite apparaître si l’on souhaite industrialiser la génération de rapports sur un parc SQL tout entier. Un des problèmes majeurs va être de générer la commande pour toutes les bases d’une instance. Si l’on ne dispose pas de liste des bases pour cette instance, il faudra la construire. L’utilisation du PowerShell va nous être utile pour cette tâche.

Comment automatiser un Assessment DMA sur toutes les bases d’une instance MSSQL (Multiple-Database Assessment) ?

Reprenons l’exemple de notre instance PRDSQL01\INS01. Cette fois-ci, nous allons générer un rapport d’Assessment pour l’ensemble des bases de cette instance.

Notre automatisation tient en deux parties :

Se connecter à l’instance PRDSQL01\INS01 pour récupérer la liste des bases de données.
Générer la syntaxe DMA pour générer un rapport d’Assessement à partir de la liste de ces bases.

Utilisons le PowerShell pour générer le script de rapport DMA. Dans un premier temps, il va falloir se connecter à l’instance pour récupérer la liste des bases. Diverses méthodes de connexion sont possibles en PowerShell (SMO, cmdlets, .Net). Dans notre exemple, choisissons d’utiliser les bibliothèques .NET

Voici comment déclarer la connexion à l’instance SQL PRDSQL01\INS01 :

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source = PRDSQL01\INS01; Initial Catalog = Master; Integrated Security = True;"

Il nous faut ensuite exécuter la requête (select name from sys.databases where database_id > 4;) pour récupérer la liste des noms de bases de données. Choisissons d’utiliser un DataSet pour stocker le résultat de cette requête :

$query = @"
select name from sys.databases where database_id > 4;
"@

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $query
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
$ListDatabase=$DataSet.Tables[0]

N’oublions pas de fermer la connexion lorsque la requête est terminée :

$SqlConnection.Close()

Notre Variable $ListDatabase va donc contenir le nom de toutes les bases de données de l’instance PRDSQL01\INS01. Il ne reste plus qu’à générer l’ordre pour le DMA.

La petite boucle suivante est nécessaire pour générer l’ensemble des chaînes de connexion :

foreach ($DB in $ListDatabase) {
      $AssessmentDB="Server=PRDSQL01\INS01;Initial Catalog=" + $DB.name + ";Integrated Security=true"
      $AssessmentDatabases=$AssessmentDatabases + [char]34 + $AssessmentDB + [char]34 + " "
}

Vérifions le contenu de la variable $AssessmentDatabases :

"Server=PRDSQL01\INS01;Initial Catalog=Ventes;Integrated Security=true" "Server=PRDSQL01\INS01;Initial Catalog=Produits;Integrated Security=true" "Server=PRDSQL01\INS01;Initial Catalog=References;Integrated Security=true"

La liste des chaînes de connexion est au bon format. Générons ensuite l’ordre d’exécution du DMA.

Petite Subtilité : Il n’est pas possible d’invoquer directement le binaire DmaCmd.exe en PowerShell. Il faut utiliser le cmdlet Start-process avec une liste d’arguments comme ci-dessous :

$arg1="/AssessmentName=Assessment_Ventes"
$arg2="/AssessmentDatabases=" + $AssessmentDatabases 
$arg3="/AssessmentTargetPlatform=ManagedSqlServer"
$arg4="/AssessmentEvaluateCompatibilityIssues"
$arg5="/AssessmentOverwriteResult"
$arg6="/AssessmentResultCsv=D:\DMA\Results\Assessment_Ventes.csv"

Start-Process -FilePath 'C:\Program Files\Microsoft Data Migration Assistant\DmaCmd.exe' -ArgumentList $arg1,$arg2,$arg3,$arg4,$arg5,$arg6

On remarque que le lancement du DMA va s’effectuer par l’intermédiaire d’une fenêtre MS-DOS. Cela va avoir une importance par la suite.

Comment automatiser un Assessment DMA pour une liste d’instances MSSQL ?

Bon, nous avons réussi à lancer un Assessment sur toutes les bases d’une instance. Si nous disposons d’une liste d’instances, il est possible de générer tous les Assessments depuis cette liste. Il faudra juste faire quelques petites adaptations :

Le nom du projet sera nommé « Assessment_<Nom de l’instance> ».
Le fichier CSV sera nommé « D:\DMA\Results\Assessment_<Nom de l’instance>.csv ».

Pour notre liste d’instance, nous allons juste créer un fichier texte contenant les noms de nos instances séparés par un saut de ligne.

PRDSQL01\INS01
PRDSQL02\INS02

Il y a également une autre subtilité à prendre en compte : Le lancement du DMA s’effectuant sur une autre fenêtre, il va falloir contrôler la fin de son exécution avant de lancer la prochaine occurence (Aucune exécution du DMA n’est possible en simultanée). L’utilisation du cmdlet Get-Process nous permettra de vérifier si le process DmaCmd.exe est toujours en en exécution.

En ajoutant les différents contrôles évoqués et en adaptant le script, cela nous donne le script PowerShell ci-dessous.

# ------------------------------------------------
## EXTRACTION DATA MIGRATION ASSISTANT
# ------------------------------------------------
# .\extract_DMA.ps1 -ServerList D:\servers.txt

# ------------------------------------------------
## Parametres en entree du script
# ------------------------------------------------
Param(
     [string]$ServerList=$(throw "Instance List is required.")
)

# ------------------------------------------------
## Fonction DMA_extract_report
# ------------------------------------------------
Function Get-DMA_extract_report ($Instance) {

     # ------------------------------------------------
     ## Recuperation de la liste des bases non-systeme de l'instance SQL
     # ------------------------------------------------ 
     BEGIN{
         Try{
            $query = @"
            select name from sys.databases where database_id > 4;
"@
            $ErrorActionPreference = "Stop"
            $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
            $SqlConnection.ConnectionString = "Data Source = $Instance; Initial Catalog = Master; Integrated Security = True; Connect Timeout=15;"

            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
            $SqlCmd.CommandText = $query
            $SqlCmd.Connection = $SqlConnection

            $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
            $SqlAdapter.SelectCommand = $SqlCmd

            $DataSet = New-Object System.Data.DataSet
            $SqlAdapter.Fill($DataSet) | Out-Null
            $ErrorActionPreference = "Continue"
        }
        Catch{
            $global:Error_Msg += "<li>[$instance]: Unable to connect to the SQL Instance [$instance].</li>"
            Write-Host "[$instance]: Unable to connect to the SQL Instance [$instance]." -ForegroundColor "Red"
        }
     }
     Process{
        # ------------------------------------------------------------
        ## Lancement du DMACMD sur l'ensemble des bases de l'instance.
        # ------------------------------------------------------------
        $ListDatabase=$DataSet.Tables[0] 
        if($ListDatabase){
             foreach ($DB in $ListDatabase) {
                $AssessmentDB="Server=" + $instance + ";Initial Catalog=" + $DB.name + ";Integrated Security=true"
                $AssessmentDatabases=$AssessmentDatabases + [char]34 + $AssessmentDB + [char]34 + " "
             }
        }
        else {
             # Si L'instance ne contient pas de bases de donnees
             return
        }

     # ------------------------------------------------
     ## Arguments du DMACMD
     # ------------------------------------------------
     $arg1="/AssessmentName=" + [char]34 + $AssessmentName + [char]34 
     $arg2="/AssessmentDatabases=" + $AssessmentDatabases 
     $arg3="/AssessmentTargetPlatform=ManagedSqlServer"
     $arg4="/AssessmentEvaluateCompatibilityIssues"
     $arg5="/AssessmentOverwriteResult"
     $arg6="/AssessmentResultCsv=" + [char]34 + $AssessmentResultCSV + [char]34

     # ------------------------------------------------
     ## Lancement du DMA Assessment en ligne de commande
     # ------------------------------------------------
     Start-Process -FilePath 'C:\Program Files\Microsoft Data Migration Assistant\DmaCmd.exe' -ArgumentList $arg1,$arg2,$arg3,$arg4,$arg5,$arg6 
     return 
     }
     END{
        $SqlConnection.Close()
     }
}

# ------------------------------------------------
## MAIN
# ------------------------------------------------
$Instances_list = Get-Content $ServerList

foreach ($ins in $Instances_list) {

     # Nom du serveur
     $ServerSQL=$ins.split("\")[0]
     # Nom de l'instance
     $InstanceID=$ins.split("\")[-1]
     # Generation du nom du projet 
     $AssessmentName="Assessment_" + $ServerSQL + "_" + $InstanceID
     # Generation du nom du fichier CSV
     $AssessmentResultCSV="D:\DMA\Results\" + $AssessmentName + ".csv"

     Write-Host "Collecting DMA Information on Database for SQL Instance - " $ins
     # Lancement de l'extraction DMA
     Get-DMA_extract_report $ins;
     # On attend 15s le demarrage du DmaCmd
     Start-Sleep -s 15
     # Il faut attendre la fin du DmaCmd en cours avant de lancer le suivant.
     while (get-process "DmaCmd" -ErrorAction SilentlyContinue){
          # Un process DmaCmd est toujours en cours. On attend 60s 
          Start-Sleep -s 60
     }
}

Il ne nous reste plus qu’à exécuter le script PowerShell pour générer tous nos rapports au format CSV :

.\extract_DMA.ps1 -ServerList D:\servers.txt

Les limitations

L’utilisation du DMA souffre de quelques limitations :

Les projets d’Assessments, générés en ligne de commandes, ne sont pas consultables via l’interface graphique. Malheureusement, le seul moyen de visualiser les rapports est d’ouvrir les fichiers générés au format CSV et JSON.
L’outil DMA ne dispose pas de fonctionnalité pour consolider les projets et les rapports d’Assessments. Néanmoins, les données des fichiers JSON peuvent être intégrées dans une base dédiée pour y être exploitées.