During the read of a blog of my colleague Nathan Courtine about Stretch database, I installed the version of SQL Server 2016 Upgrade Advisor and Oh surprise! it looks really NEW! “A new generation of advisor tool for SQL Server” aka Microsoft.
To download and install it, click here.

This new version has a dual role:

  • Check the compatibility of old versions of SQL Server compared to SQL Server 2016
  • Help adoption of new SQL Server feature like for example the Stretch Database (Stretch Database Advisor)

The main screen shows two selection menu which are Scenarios and Active:

UpgradeAdvisor1

Active shows scenario currently running, here a Database Upgrade Analyzer:

UpgradeAdvisor13
For scenario, five are for the moment available:

UpgradeAdvisor2

  • Run Stretch Database Advisor
  • Run Database Upgrade Advisor
  • Analysis Services Upgrade Advisor
  • Integration Services Upgrade Advisor
  • Reporting Services Upgrade Advisor

Let’s have a look at one of those possibilities…

Stretch Database Advisor

First of all, we have to configure the stretch database advisor, to do this step we have to click under the link “Configure required settings”:

UpgradeAdvisor3

A new column is created asking for SQL Instance settings, click on the link “Configure required settings”:

UpgradeAdvisor4

Enter your server and instance name, select if you use a Windows Authentication (which is my case) and click on “Using default settings” to see advanced settings:

UpgradeAdvisor5

I used the default port number so I have nothing to change, if it is not your case change the port number and click the Select button on the bottom of the screen:

UpgradeAdvisor6

Click now the Connect button at the bottom of the Connect SQL Instance column.
You have a list of database bellowing to your SQL Server instance. Here I have just one database named GestionCom. Click on each database on which you want to run the Stretch database advisor.
To finish click on the Select button.

UpgradeAdvisor7

We have selected the database to analyze, so click the Run button:

UpgradeAdvisor8

The Advisor will analyzed the database tables and advised about which table could be part of a stretch database. The result is shown in a grid similar to the one used in the AMR tool (used to advise about with table or SP will give you the best performance gain after in-memory migration: see my blog here).

UpgradeAdvisor9
In my example the recommended tables are:

  • command_queue: will give low benefit but with low cost of migration
  • Employe: will give low benefit with a high migration cost

The best here should be to have High benefit with low cost 😉
If table dbo.employe is ready to be part of a stretch database:

UpgradeAdvisor10

The table dbo.command_queue has two compatibility problem:

UpgradeAdvisor11

To have more details, it is possible to click on each result:

UpgradeAdvisor12

That’s all for the Stretch database advisor.
It was to quickly present you this new SQL Server 2016 Advisor.
I let you tested the others scenario 😉


Thumbnail [60x60]
by
Stéphane Savorgnano