azuresqldb-assessment-sku-using-ps.md

Assessment and SKU recommendation for Azure SQL Database using PowerShell

Assess your SQL Server databases for Azure SQL Database readiness or to identify any migration blockers before migrating them to Azure SQL Database.

The Azure SQL migration extension for Azure Data Studio enables you to assess, get Azure recommendations and migrate your SQL Server databases to Azure SQL.

In addition, the Azure CLI command az datamigration can be used to manage data migration at scale.

Prerequisites

• SQL Server with Windows authentication or SQL authentication access
• .Net Core 3.1 (Already installed)
• Az.DataMigration PowerShell module

Getting Started

Caution

• Connect to the Jump Box VM
• VM name: jb-migration
• Use the credentials provided on the deploy page.

Open a Terminal. It is already installed in the VM and by default it uses PowerShell.

Run the assessment

1. Run a SQL Server assessment using the Get-AzDataMigrationAssessment command.

   Get-AzDataMigrationAssessment `
   -ConnectionString "Data Source=10.0.0.4,1433;Initial Catalog=master;User Id=sqladmin;Password=My`$upp3r`$ecret" `
   -OutputFolder "C:\temp\output" `
   -Overwrite

2. Assessment at scale using config file

   You can also create a config file to use as a parameter to run assessment on SQL servers. The config file has the following structure:

   {
       "action": "Assess",
       "outputFolder": "C:\\temp\\output",
       "overwrite":  "True",
       "sqlConnectionStrings": [
           "Data Source=Server1.database.net;Initial Catalog=master;Integrated Security=True;",
           "Data Source=Server2.database.net;Initial Catalog=master;Integrated Security=True;"
       ]
   }

   The config file can be passed to the cmdlet in the following way

   Get-AzDataMigrationAssessment -ConfigFilePath "C:\Users\user\document\config.json"

   [!TIP]

   To view the report, go to C:\temp\output folder and check the json file.

   Learn more about using PowerShell to assess SQL Server

SKU Recommendation

Performance data collection

This step is optional. An Azure SQL DB has been already provisioned.

1. Run a SQL Server performance data collection using the Get-AzDataMigrationPerformanceDataCollection command.

   Get-AzDataMigrationPerformanceDataCollection `
   -SqlConnectionStrings "Data Source=10.0.0.4,1433;Initial Catalog=master;User Id=sqladmin;Password=My`$upp3r`$ecret" `
   -OutputFolder "C:\temp\output" `
   -PerfQueryInterval 10 `
   -NumberOfIterations 5 `
   -StaticQueryInterval 120

   [!TIP]

   Collect as much data as you want, then stop the process. You can look into the output folder (C:\temp\output) to find a CSV file that also gives the details of the performance data collected.

2. Running performance data collection at scale using a config file

   You can also create a config file to use as a parameter to run performance data collection on SQL servers. The config file has the following structure:

   {
       "action": "PerfDataCollection",
       "outputFolder": "C:\\temp\\output",
       "perfQueryIntervalInSec": 20,
       "staticQueryIntervalInSec": 120,
       "numberOfIterations": 7,
       "sqlConnectionStrings": [
           "Data Source=Server1.database.net;Initial Catalog=master;Integrated Security=True;",
           "Data Source=Server2.database.net;Initial Catalog=master;Integrated Security=True;"
       ]
   }

   The config file can be passed to the cmdlet in the following way.

   Get-AzDataMigrationPerformanceDataCollection -ConfigFilePath "C:\Users\user\document\config.json" 

   [!TIP] Collect as much data as you want, then stop the process. You can look into the output folder (C:\temp\output) to find a CSV file that also gives the details of the performance data collected.

   Learn more about using PowerShell to perform data collection

Get SKU Recommendation

This step is optional. An Azure SQL DB has been already provisioned.

1. Get SKU recommendation using the az datamigration get-sku-recommendation command.

   Get-AzDataMigrationSkuRecommendation `
   -OutputFolder "C:\temp\output" `
   -DisplayResult `
   -Overwrite `
   -TargetPlatform "AzureSqlDatabase"

   All results will be displayed after the command finishes.

   

2. Get SKU recommendations at scale using a config file.

   You can also create a config file to use as a parameter to get SKU recommendations on SQL servers. The config file has the following structure:

   {
       "action": "GetSKURecommendation",
       "outputFolder": "C:\\temp\\Output",
       "overwrite":  "True",
       "displayResult": "True",
       "targetPlatform": "any",
       "scalingFactor": 1000
   }

   Learn more about using PowerShell to get SKU recommendation

View Assessment & SKU Recommendation

1. HTML recommendations result

   You can look into the output folder (C:\temp\output) to find an HTML file that also gives the details of the SKU being recommended.

   

Page Navigator

• SQL Server migration one-click PoC to Azure SQL
• One-click PoC to Azure SQL DB
• One-click PoC to Azure SQL MI