Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor


By:   |   Read Comments (3)   |   Related Tips: More > 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:

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:

Scenarios

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:
Feedback
  • 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:

About

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

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
  • 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


Last Update:






About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 02, 2016 - 12:28:35 PM - Aaron Bertrand Back To Top

@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 02, 2016 - 11:55:30 AM - Mircea Back To Top

 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

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

http://www.microsoft.com/en-us/download/details.aspx?id=48119


Learn more about SQL Server tools