One of the first and important steps to perform before performing upgrade or migration of SQL Server instance from lower version to a higher version is to run the Upgrade Advisor which generates a detailed report with objects and list of issues that could arise after upgrading to the higher version of SQL Server instance. Once we know the objects and the issues we can take corrective actions before or after the upgrade is complete which will keep the new upgraded SQL Server instance stable and consistent.
What is SQL server upgrade advisor?
SQL Server Upgrade Advisor analyzes the legacy instances and produces reports detailing upgrade issues by SQL Server component. The resulting reports will show the detected issues or problems and also provide some guidance on how to fix the reported issues or some work around for the reported issues. These reports can be reviewed by using Upgrade Advisor or can be exported for further analysis, for example to Excel document. In addition to analyzing data and database objects, Upgrade Advisor can also analyze Transact-SQL scripts and SQL Server Profiler or SQL Trace files. Upgrade Advisor can be run from either a local server or from a remote server. Report generation and analysis is CPU intensive, so it is better to always try to run it remotely when working with production database servers.
SQL Server upgrade can analyze Database Services, Analysis Services, Integration Services, Reporting Services, DTS packages. Report will also contain deprecated items that won’t run on the higher version of SQL server and which MUST be fixed.
Advisor can be freely downloaded from Microsoft site and we should download the upgrade advisor version to which you want to upgrade to. Example, if you have SQL Server 2005 instance installed and want to upgrade to SQL Server 2008 R2 instance, then you must download SQL Server 2008 R2 upgrade advisor.
This is how upgrade advisor looks like and some options to choose and the final report generated.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.