Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor

By:   |   Comments (3)   |   Related: > SQL Server 2016


When you are planning an upgrade to a new version of SQL Server, it is not always obvious what obstacles you might encounter during the actual upgrade. In previous major releases of SQL Server, the Upgrade Advisor tool has been made available only shortly before, or along with, the release of the new version, making it hard to use this resource to plan an upgrade very far in advance.


As with previous releases, there is once again an Upgrade Advisor for SQL Server 2016. However, this version is completely different from its predecessors, and is available in preview from today - long before the release of SQL Server 2016! In addition, there is a new feature available here, called the Stretch Database Advisor, which will help provide guidance about tables that might be good candidates for this new SQL Server 2016 feature.

In this tip, I'm going to show you around the new user interface, and demonstrate the tool in action, finding issues with an intentionally problematic database.

First, you can download the Upgrade Advisor Preview 1 from here:

This will download an MSI, which is a standard installer that will load the application onto your machine. As this is beta software, I suggest testing this in a virtual machine or non-production, non-critical workstation.

Once installed, you will find "Microsoft SQL Server 2016 Upgrade Advisor" in your Start menu. When you launch it, you will see a "What's New" screen:

What's New

Next, you'll notice the title bar (I've added numbers that correspond to the list below):

Title bar

The options in the title bar are:

Scenarios - this shows the available tasks you can perform with the Upgrade Advisor:


Active - this shows any current or recent analyses and other windows:

Active windows

I'd love to see a tooltip over those, since all database upgrade analyzers look the same, even though they may have been run against different instances or databases

  • Notifications - This presents a notification area where you will be able to see recent alerts (such as the completion of a database analysis).
  • Send Feedback - This is a simple text box that allows you to send feedback to the development team. I used it already to send a note about the tooltips I mentioned above:
  • Settings - the gear icon in the top right corner will eventually be used for settings, I imagine, but during the preview, it just brings up the About and other notices:


If that's intended to only ever go to Help > About, the icon should probably be a question mark, not a gear.

Database Analysis

Let's run a quick database analysis. I've created a database that uses a number of features or syntax elements that should be caught by the upgrade advisor; random selections from the documentation:

USE master;
CREATE DATABASE UpgradeNightmare;
style="border:thin black solid" altER DATABASE UpgradeNightmare SET COMPATIBILITY_LEVEL = 100;
USE UpgradeNightmare;
CREATE INDEX ix1 ON dbo.BadTable(ts);
CREATE PROCEDURE dbo.BadProcedure;1
  @x IMAGE
  SELECT name FROM syscolumns;
  EXEC sp_addtype N'EMail', N'nvarchar(320)';
CREATE PROCEDURE dbo.BadProcedure;2
  UPDATE b SET x += 1 FROM dbo.BadTable AS b WITH (NOLOCK);
  EXEC sp_configure 'allow updates', 1;
CREATE PROCEDURE dbo.WorseProcedure
  SELECT 'string alias' = 1;
  DROP INDEX dbo.BadTable.ix1;

So in the Upgrade Advisor, we go to Scenarios, and click on the "Run Database Upgrade Advisor" option. The first screen is "Select databases to analyze:"

Select Databases to Analyze

Then you'll have to click again (these two steps could be combined, I think):

Select Databases

Then you'll select your instance - and there's a handy MRU list here, so it will remember previous connections. Advanced settings allows you to specify the port, whether to encrypt the connection (off by default)and any additional connection string parameters:

Connect to SQL Instance

Once you've typed or selected the desired instance, click Connect at the bottom. This will bring about the following screen:

Select Databases

I've selected only my "UpgradeNightmare" database, and then you press Select at the bottom, and then Run. This will start the analysis, and when it is done, you will see this summary screen:

Analysis Results

I like that it shows a breakdown for all compatibility levels above the database's current level. Surprisingly, though, it only found one issue in my intentionally problematic database; the use of the NTEXT data type:

Detailed Results

It did not notice any of the following:

  • the compatibility level (I'm assuming that once SQL Server 2016 is released, the 100 compatibility level will be retired, but I can understand that won't happen yet)
  • TIMESTAMP (which should be ROWVERSION)
  • numbered procedures
  • an IMAGE parameter
  • syscolumns
  • sp_addtype
  • DML with NOLOCK
  • sp_configure 'allow updates'
  • 'string alias' = expression syntax
  • DROP INDEX table.index syntax

These are all things that are deprecated and that should be setting off alarms for an upgrade to SQL Server 2016 and beyond. Whether they all explicitly break in current builds of SQL Server 2016 is a different issue (in fact, they all work; I suspect the actual implementation of deprecated/discontinued features in the engine has simply not been done yet). This seems to be the point of the "Future" analysis.


The new Upgrade Advisor certainly looks promising, but I do hope that it is enhanced to catch more than just the usage of TEXT/NTEXT, which still doesn't even actually break in SQL Server 2016. Technically, these types should have stopped working by SQL Server 2012, since they were first announced for deprecation in SQL Server 2005 - but they're still used by SQL Trace.

The tool should provide at least warnings for all of the items in the official documentation. If more coverage isn't supplied in future versions, you will still be left to your own devices to assess the risk and effort that will be involved in an upgrade (the links below might help with some of that).

In a future tip, I'll take a look at the new feature found in this preview, the Stretch Database Advisor.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, June 2, 2016 - 12:28:35 PM - Aaron Bertrand Back To Top (41596)

@Mircea, I don't think upgrade from CTP to RTM is supported, usually this is restricted to RCs (and sometimes only the last one or two RCs). Even if it were supported, I still wouldn't try this for a production system, or even a workstation or VM.

Suggest, instead, just installing a new named instance, backing up your databases, and restoring them on the new instance.

If instance name is a deal-breaker, then back up your databases, uninstall the CTP, install RTM, then restore your databases.

Thursday, June 2, 2016 - 11:55:30 AM - Mircea Back To Top (41595)

 Can you please provide some info how can I upgrade from SQL Server 2016 CP3 (installed last month) to the official version released in June?


Saturday, August 29, 2015 - 12:37:44 AM - Aaron Bertrand Back To Top (38563)

FYI on 2015-08-28, Microsoft released Preview 1.1 of the 2016 Upgrade Advisor:

get free sql tips
agree to terms