Most of the DBAs must be familiar with SQL Server upgrade advisor, which is used to run during planning phase of upgrading to higher SQL Server versions inorder to identify any objects or features that will get affected during or after the instance or database upgrade. With every new release of SQL Server, some old features may be deprecated or retired and behavior of some features might change in new versions. So, running upgrade advisor during planning phase on a test server where the production database is restored, will give us good idea on what objects are going to break during or after upgrading to higher version of SQL Server. In previous releases of SQL Server, the Upgrade Advisor tool was made available only few days before the release of the new SQL Server version, but with SQL Server 2016, the upgrade advisor has been released way early giving options to test the instances or databases and then to proceed with testing on RC releases of the SQL Server. Although, we may be very familiar with using the upgrade advisors for SQL Server 2005, 2008 R2, 2012 and 2014, but there have been significant changes to SQL Server 2016 Upgrade Advisor in look and feel and its usage.
We should be able to download the latest version of SQL Server 2016 Upgrade advisor from official Microsoft link here.
As per Microsoft documentation “SQL Server 2016 Upgrade Advisor Preview is a standalone tool that enables users of prior versions to run a set of upgrade rules against their SQL Server database to pinpoint breaking and behavior changes and deprecated features as well as providing help with the adoption of new features such as Stretch Database.”
Once we download the SQL Server 2016 Upgrade Advisor, we will find SqlAdvisor.msi in the downloaded location. Run this msi installer by choosing run as administrator and you will need to have local administrator permission on the server where you are installing this tool. It is always recommended to install Upgrade Advisor on a test server and restore a copy of production database on the test server for analysis.
Once the installation of SQL Server 2016 upgrade advisor is completed successfully, launch the program and you will first see a What’s New screen which has some information regarding What’s new in SQL Server 2016 Upgrade Advisor. After that we will find a title bar with SQL Server 2016 Upgrade Advisor and next to it, you will see two main options “Scenarios” and “Active”. Apart from these two options, there are Notifications, Send Feedback and Settings which allows us to see new notifications for the product and to send any feedback to Microsoft for any bugs or new feature requests. Settings option has a gear icon which is used to choose any settings, as of now there is not much you can do here.
Inorder to start the upgrade analysis process, we need to choose “Scenarios” menu -> Run Database Upgrade Advisor -> Select databases to analyze -> Select Instance -> Select the instance from the drop down list if any available or create a new connection -> If we need to specify port number as well inorder to connect to the instance, then we can specify the port number by clicking on advanced options -> Once we successfully made connection to the SQL Server instance, next we will see list of databases to choose for the upgrade analysis -> Select one or more databases based on your requirement -> Run the upgrade analysis by clicking on Run button. It will take a while for the analysis to be performed. Once analysis is complete, you will be presented with a summary page. We will not only see the analysis for SQL Server 2016 instance, but we will find it for other versions as well based on what is the SQL Server version where upgrade advisor was run against, like if you run it against SQL Server 2008 R2 instance, then you will see the analysis for SQL Server 2012, 2014 and 2016. We can drill down for each of the versions and see the affected objects and the details about the impact and recommendation.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings