Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor

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

Problem

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.

Solution

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:

Download Center : Microsoft SQL Server 2016 Upgrade Advisor Preview 1
(Note: this URL may change with subsequent releases, but a search should yield new versions that come along.)

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:

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

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:

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;
GO
CREATE DATABASE UpgradeNightmare;
GO
style="border:thin black solid" altER DATABASE UpgradeNightmare SET COMPATIBILITY_LEVEL = 100;
GO
USE UpgradeNightmare;
GO
CREATE TABLE dbo.BadTable
(
 x INT PRIMARY KEY,
 y NTEXT,
 ts TIMESTAMP
);
GO
CREATE INDEX ix1 ON dbo.BadTable(ts);
GO
CREATE PROCEDURE dbo.BadProcedure;1
  @x IMAGE
AS
BEGIN
  SET ANSI_NULLS OFF;
  SELECT name FROM syscolumns;
  EXEC sp_addtype N'EMail', N'nvarchar(320)';
END
GO
CREATE PROCEDURE dbo.BadProcedure;2
AS
BEGIN
  SET FMTONLY ON;
  UPDATE b SET x += 1 FROM dbo.BadTable AS b WITH (NOLOCK);
  EXEC sp_configure 'allow updates', 1;
END
GO
CREATE PROCEDURE dbo.WorseProcedure
AS
BEGIN
  SET CONCAT_NULL_YIELDS_NULL OFF;
  SELECT 'string alias' = 1;
  DROP INDEX dbo.BadTable.ix1;
END
GO

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:"

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

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:

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

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:

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:

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
SET ANSI_NULLS OFF
syscolumns
sp_addtype
SET FMTONLY ON
DML with NOLOCK
sp_configure 'allow updates'
CONCAT_NULL_YIELDS_NULL
'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.

Summary

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

Download the Upgrade Advisor Preview.
Watch for updates to this tool, hopefully that will encompass more deprecated features and changes to the database engine as the SQL Server 2016 beta continues.
See these other tips:

About the author

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, SQLPerformance.com, and also blogs at sqlblog.org.

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

View all my tips