Join Our Team
Contact
Insights Running the Data Migration Assistant – Part Two – PowerShell
Insights

Running the Data Migration Assistant – Part Two – PowerShell

In the previous post, I introduced the Data Migration Assistant (DMA) from Microsoft.  In this post I will introduce PowerShell modules from Microsoft that can be used to run the DMA against an inventory of instances and databases.

A .zip file containing the PowerShell modules can be downloaded from this page.  Once the modules are downloaded and extracted, they need to be copied to path

C:\Program Files\WindowsPowerShell\Modules\DataMigrationAssistant

There are four modules:

  • dmaProcessor – Executes the DMA against an inventory of instances and databases.  The module can be run from any server if it can access the instances to be assessed.  This inventory can be stored in a SQL Server database or in a .csv file.  The DMA results are written out to a .json file.
  • dmaCollector – Ingests the .json results file and inserts it into a SQL Server database, data warehouse, or both.  The database or data warehouse can be on the same server running the DMA or on a different instance.
  • createDMAReporting – Creates the SQL Server database to hold the .json results.
  • createDMAWarehouse – Creates the SQL Server data warehouse to hold the .json results.

In this example, the assessment inventory is stored in a SQL Server database.  Create a database on the instance you will run the dmaProcessor module from.  The module requires a table named DatabaseInventory with the following schema:

If required, additional columns can be added to the table if those four columns exist.  These columns are described below”:

  • ServerName – The server running the SQL Server instance being assessed.
  • InstanceName – The instance name being assessed.  If it is a default instance, use MSSQLSERVER.
  • DatabaseName – The database being assessed.
  • AssessmentFlag – Set to Y if the databased is to be assessed.  Set to N to exclude the database from the assessment.

The AssessmentFlag column allow an entire inventory to be inserted into the DatabaseInventory table and then run in batches.  This can be used to categorize and assess databases separately.  Once the DatabaseInventory table is populated and the AssessmentFlag column is set, you are ready to run the dmaProcessor.  A sample execution is shown below:

  • getServerListFrom – Valid values are:
    • SQLServer
    • csv
  • ServerName – The server\instance hosting the inventory database.
  • DatabaseName – The name of the inventory database.
  • AssessmentName – The name of the assessment.
  • TargetPlatform – The target of the assessment.  Valid values are:
    • SqlServer2012
    • SqlServer2014
    • SqlServer2016
    • SqlServerWindows2017
    • SqlServerLinux2017
    • SqlServerWindows2019
    • SqlServerLinux2019
    • AzureSqlDatabase
    • ManagedSqlServer
  • OutputLocation – The file path to write the .json results to.
  • AuthenticationMethod – Valid values are:
    • SqlAuth
    • WindowsAuth

Once the dmaDataCollector completes, a .json file containing the results for all databases will be written to the OutputLocation.  The dmaProcessor module loads these results into a SQL Server database or data warehouse.  A sample execution is shown below:

  • processTo – The destination for the results.  Valid values are:
    • SqlServer
    • AzureSqlDatabase
  • serverName – The server to write the results to.
  • databaseName – The name of the database to be created.
  • warehouseName – The name of the data warehouse to be created.
  • jsonDirectory – The file path containing the .json results files.
  • CreateDMAReporting – Create a results database. 
  • CreateDataWarehouse – Create a results data warehouse.

You can create a database and a data warehouse from one run of dmaProcessor by setting both CreateDMAReporting and CreateDataWarehouse to 1.  Sample queries for database and data warehouse are listed below.  The WHERE clauses exclude several false positive results.

In the last post in this series, I will show how to use a Jupyter notebook to combine and automate the DMA process.