+44 (0)20 3051 3595 | info@coeo.com | Client portal login

SQL Server 2016 Upgrade Advisor Automation

Andy Jones

The SQL Server 2016 Upgrade Advisor is a great graphical user interface for analysing your existing SQL Server database to discover any features or syntax you are using that are not compatible with a 2016 upgrade.

This tool produces a great HTML report that lists all the items you will need to fix prior to upgrading. This works fine, but the issue is running this tool manually can become onerous if your requirements are to analyse tens or hundreds of databases. This is where the command line interface for the tool, SqlAdvisorCmd.exe can greatly assist.

The following switches are available

  • /Scenario:Upgrade|StretchDB Specifies the scenario. Supported scenarios are Upgrade and StretchDB (short form /S)
  • /ConnectionStrings:<string>[,<string>,<string>,...] ConnectionStrings (short form /C)
  • /Server:<string> Specifies server (short form /s)
  • /Database:<string> Specifies database to run analysis on (short form /d)
  • /UserId:<string> UserId for server (short form /u)
  • /Password:<string> Password for server (short form /p)
  • /IntegratedAuthentication[+|-|:true|:false] Integrated Authentication option (short form /I)
  • /ExportType:Html|Csv Specify format to export results to: CSV or HTML (short form /E)
  • /Individual[+|-|:true|:false] Specify individual reports instead of an aggregated report
  • /exportFolderPath:<string> Folder path to export results to (short form /FolderPath)
  • /MicrosoftAssessmentAndPlanningToolkit[+|-|:true|:false] Run analysis on databases inventoried by Microsoft Assessment and Planning Toolkit (MAP). UserID, password, and MAP inventory database must also be
    given when using this option (short form /MAP)
  • /MicrosoftAssessmentandPlanningToolkitDatabase:<string> Database with results from running Microsoft Assessment and Planning Toolkit (MAP) (short form /MAPdatabase)
  • @<file> Read response file for more options

As we are able to script the upgrade analysis, we can take advantage of Powershell to automate this process for any amount of databases we desire. Firstly, I created a CSV file at C:\UpgradeAdvisor\Servers.csv with two columns for the servers and databases to be included in the analysis: -


Then run the following Powershell script to produce an upgrade report for each database within the CSV

 Import-CSV "C:\UpgradeAdvisor\Servers.csv"  | Foreach-Object{

    $Server = $_ . Server
    $Database = $_ . Database
    $Path = "C:\UpgradeAdvisor\ $Database "
    Write-Host $Database
    New-Item -ItemType Directory -Path $Path -Force
    Start-Process -FilePath 'C:\Program Files\Microsoft SQL Server 2016 Upgrade Advisor\SqlAdvisorCmd.exe' -ArgumentList "/Scenario:Upgrade /Server: $Server /Database: $Database /IntegratedAuthentication:true /ExportType:Html /exportFolderPath: $Path "


This will write the report to a separate sub-directory of C:\UpgradeAdvisor\ per database.

One limitation of the command line interface appears in the /Scenario switch. The GUI exposes four options as shown in the following graphic

  1. Migrate to Azure SQL Database
  2. Run In-Memory OLTP and Columnstore Advisor
  3. Run Stretch Database Advisor
  4. Analyze and Migrate to SQL Server


SQL Server 2016 Upgrade Advisor


Unfortunately, only the latter two of these options are available via the command line interface via the /Scenario switch, namely

  1. Run Stretch Database Advisor, and
  2. Analyze and Migrate to SQL Server


Subscribe to Email Updates

Back to top