Using SQL Server 2016 Upgrade Advisor

INTRODUCTION

SQL Server is designed to allow multiple databases on a single instance. With this model it is possible to have databases sitting on an instance which are not the same version as the instance itself. You can think of this as running the database in the “backward compatibility mode.” To break it down further, we are saying that you can have a 2008 database deployed on an SQL Server 2016 instance. In such a scenario, the database is, for instance, allowed to use certain constructs that belong to a previous version of SQL Server.

There are cases where you would like to upgrade all databases in your instance to a more recent version for security reasons or to align with your organization’s policies. When doing this it may not be safe to assume that such a database will automatically work when upgraded. Using Upgrade Advisor, you can determine whether your existing databases are suitable for upgrade to SQL Server 2016 or any other higher version.

PREREQUISITES

Download Upgrade Advisor 2016 from Microsoft’s website. In this article, we have focused on Upgrade Advisor 2016; however, the latest version of Upgrade Advisor is called Data Migration Assistant and can be downloaded here. Once downloaded, you can install Upgrade Advisor 2016 on the computer that you will execute the analysis from. Note that this need not be the computer where SQL Server is installed.

PROCEDURE

1. Launch Upgrade Advisor and select the option to ANALYZE AND MIGRATE TO SQL SERVER

Figure 1: Upgrade Advisor Splash Screen

Figure 1 shows the splash screen that is displayed when you launch Upgrade Advisor. We see that the advisor offers four options for the analysis:

Migrate to Azure SQL Database
Run in-memory OLTP and ColumnStore advisor
Run stretch database advisor
Analyze and Migrate to SQL Server

The tool offers the database administrator an opportunity to properly prepare for any of these listed steps. It is better to predict what might happen if you embark on any of the listed tasks than to simply attempt them, fail and then realize why.

In the current scenario, we are focusing on number (4) which is Analyze and Migrate to SQL Server. We want to know whether existing databases can be migrated to SQL Server 2016, what we may need to do before embarking on such a migration, and what we may need to do as additional steps after the migration.

We select the option Analyze and Migrate to SQL Server and then click the Run button.

2. Connect to the desired instance

Figure 2: Select Instance

We must then connect to the instance we wish to analyze (Figure 2) and select the databases we want to analyze (See Figure 3). Type the instance name in the field shown and click Connect. In this scenario, we are running Upgrade Advisor on the same server where our SQL Server instance is hosted, so the tool can enumerate the installed instances and allow us to select from a menu. Recall that this behavior for tools that connect to SQL Server instance is made possible by SQL Server Browser.