Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor
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:
- 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.
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
- SET FMTONLY ON
- 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.
- 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:
- Overview of Microsoft SQL Server 2008 Upgrade Advisor
- SQL Server 2008 Upgrade Plan for DBAs
- Version and Edition Upgrades with SQL Server 2008 R2
- SQL Server 2005 Upgrade Considerations for DBAs and Developers
- Identify deprecated SQL Server code with a server side trace
- Identify Deprecated SQL Server Code with Extended Events
- Upgrades and Migration Tips
Last Updated: 2015-07-29
About the author
View all my tips