Using SQL Server 2016 Upgrade Advisor

{coding}Sight
4 min readNov 18, 2020

--

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 that 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 an 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:

  1. Migrate to Azure SQL Database
  2. Run in-memory OLTP and ColumnStore advisor
  3. Run stretch database advisor
  4. 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.

3. Select the desired database(s)

Figure 3: Select Database

We select the desired database(s) (See Figure 3) and click the Select button. Once the database is selected (See Figure 4), click Run.

4. Run the analyzer

Figure 4: Analyze Database

5. Analysis is complete

Figure 5: Complete Analysis

The Advisor analyzes the database(s) selected and returns any issues of concern about the database that may hinder the upgrade or pose a problem in the future if you do proceed with the upgrade. Click Issues to see the analysis results (Figure 6).

6. Review the results and export the report

Figure 6: Review Issues

The results show us that we can safely upgrade to SQL Server 2016 but warns us about compatibility issues with earlier releases and future releases (beyond SQL Server 2016) which need to be addressed. You do realize that the future versions being spoken of here are current versions at the time of this article such as SQL Server 2017 and SQL Server 2019. At this point, we can choose to migrate to SQL Server 2016, and we can also choose to save the results and review them in greater detail. We click the Save icon highlighted in orange.

7. Choose your preferred file type and click EXPORT

Figure 7: Export Results

Upgrade Advisor 2016 allows us to save the output either as HTML or CSV in a path we choose. Once the file is downloaded, we can review it.

Continue reading at 👉 https://codingsight.com/using-sql-server-2016-upgrade-advisor/.

Join a community of database specialists. Subscribe here to get more industry insides 👋

--

--

{coding}Sight

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/