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: -
Server,Database
OFFICE,AdventureWorks2012
OFFICE,DBA
OFFICE\SQLEXPRESS,AdventureWorks2014
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
- Migrate to Azure SQL Database
- Run In-Memory OLTP and Columnstore Advisor
- Run Stretch Database Advisor
- Analyze and Migrate to SQL Server
Unfortunately, only the latter two of these options are available via the command line interface via the /Scenario switch, namely
- Run Stretch Database Advisor, and
- Analyze and Migrate to SQL Server
