The SQL Server 2014 Upgrade Advisor (UA) is interesting tool that lets you “Upgrade Advisor reports issues when upgrading to a newer version of SQL Server.”

The GUI is pretty simple to work through but what i found interesting was when we were looking at upgrading multiple servers, instead of going through the clicky run, that is the GUI for each and every server, you can use the command line of the app.. This means we can run it in PowerShell and this means we can do a lot of the repetitive work with some simple loops!

Since the GUi side is pretty straight forward i have left that below the interesting commandline side of things.

Running the Upgrade Advisor via the Command Prompt

To run the UA via the command prompt we use UpgradeAdvisorWizardCmd.exe

More information on the syntax can be found HERE Below are a several examples of running it

To run a standard report against server “Test01” which has a default instance

$UA = "C:\Program Files (x86)\Microsoft SQL Server Upgrade Advisor\120\UpgradeAdvisorWizardCmd.exe"
& $UA -Server NWPAPPSQL03A -Instance MSSQLSERVER

Query a CMS instance to find all Instances and Report against them all


$CMSServer = "."
$CMSQuery = @'
SELECT DISTINCT
        server_name AS 'Server' ,
        CASE WHEN server_name LIKE '%\%'
             THEN SUBSTRING(server_name, CHARINDEX('\', server_name) + 1,
                            LEN(server_name))
             ELSE 'MSSQLSERVER'
        END AS 'Instance'
FROM    msdb.dbo.sysmanagement_shared_registered_servers_internal;
'@

$UA = "C:\Program Files (x86)\Microsoft SQL Server Upgrade Advisor\120\UpgradeAdvisorWizardCmd.exe"

$servers = Invoke-Sqlcmd -ServerInstance $CMSServer -Database "msdb" -Query $CMSQuery

Foreach ($s in $servers)
    {
        Write-Host "Running Against Server --" $s.Server
        & $UA -Server $s.Server -Instance $s.Instance
    }

Installation

You download the SQL Server 2014 Upgrade Adivsor as part of the “SQL Server 2014 Feature Pack” which you can find HERE.

You only need to download a single MSI which is entitled: ENU\x64\SqlUA.msi

Once downloaded follow the standard install steps:

sqluaInstall01 sqluaInstall02 sqluaInstall03 sqluaInstall04 sqluaInstall05 sqluaInstall06 sqluaInstall07

Running the Upgrade Advisor via the GUI

You can find the GUI exe now in

sqlua02

Opens up

sql2014ua01

sql2014ua02

sql2014ua03

sql2014ua04

sql2014ua05

sql2014ua06

sql2014ua07

sql2014ua08

Reviewing Results in the GUI

you can either read a report straight after completeing a server analysis via selecting Report:

Or if you wish to look at old reports you can open the AU as before and instead of running “Launch Upgrade Advisor Analysis Wizard” you click “Launch Upgrade Advisor Report Viewer”

sql2014ua01

Which opens the Report viewer

sql2014ua09

Here you can select which servers report you wish to see, which report (If youve run the AU mutliple times against the same box) filter options etc.

Advertisements