Overview of Microsoft SQL Server 2008 Upgrade Advisor

By:   |   Comments (4)   |   Related: > Upgrades and Migrations


Problem

Like most organizations, we are planning to upgrade our database server from SQL Server 2005 to SQL Server 2008. I would like to know is there an easy way to know in advance what kind of issues one may encounter when upgrading to a newer version of SQL Server? One way of doing this is to use the Microsoft SQL Server 2008 Upgrade Advisor to plan for upgrades from SQL Server 2000 or SQL Server 2005. In this tip we will take a look at how one can use the SQL Server 2008 Upgrade Advisor to identify potential issues before the upgrade.

Solution

SQL Server 2008 Upgrade Advisor is a free tool designed by Microsoft to identify potential issues before upgrading your environment to a newer version of SQL Server. Below are prerequisites which need to be installed before installing the Microsoft SQL Server 2008 Upgrade Advisor.

Prerequisites for Microsoft SQL Server 2008 Upgrade Advisor

  • .Net Framework 2.0 or a higher version
  • Windows Installer 4.5 or a higher version
  • Windows Server 2003 SP 1 or a higher version, Windows Server 2008, Windows XP SP2 or a higher version, Windows Vista

Download SQL Server 2008 Upgrade Advisor

You can download SQL Server 2008 Upgrade Advisor from the following link. Once you have successfully installed Upgrade Advisor follow the below steps to see how you can use this tool to identify potential issues before upgrading your environment.

1. Click Start -> Programs -> Microsoft SQL Server 2008 -> SQL Server 2008 Upgrade Advisor.

2. Click Launch Upgrade Advisor Analysis Wizard as highlighted below to open the wizard.

SQL Server 2008 Upgrade Advisor

2. On the wizard welcome screen click Next to continue.

sql server upgrade advisor analysis wizard

3. In SQL Server Components screen, enter the Server Name and click the Detect button to identify components which need to be analyzed and then click Next to continue with the wizard.

sql server components screen

4. In Connection Parameters screen choose Instance Name, Authentication and then click Next to continue with the wizard.

In Connection Parameters screen choose Instance Name and Authentication

5. In SQL Server Parameters wizard screen select the Databases which you want to analysis, trace files if any and SQL batch files if any. Then click Next to continue with the wizard.

Databases which you want to analysis, trace files if any and SQL batch files if any

6. In Reporting Services Parameters screen you can specify the Reporting Server Instance name and then click next to continue with the wizard.

specify the Reporting Server Instance name

7. In Analysis Services Parameters screen you can specify an Analysis Server Instance name and then click Next to continue with the wizard.

specify an Analysis Server Instance name

8. In Confirm Upgrade Advisor Settings screen you will be able to see a quick summary of the options which you have selected so far. Click Run to start the analysis.

Confirm Upgrade Advisor Settings

9. In Upgrade Advisor Progress screen you will be able to see the progress of the analysis. Basically, the upgrade advisor runs predefined rules which will help to identify potential issues that can affect your environment once you upgrade your server from a lower version of SQL Server to SQL Server 2008.

 upgrade advisor runs predefined rules which will help to identify potential issues that can affect your environment once you upgrade your server from a lower version of SQL Server to SQL Server 2008

10. In the below snippet you can see that Upgrade Advisor has completed the analysis of SQL Server, Analysis Services and Reporting Services. To see the output click the Launch Report button at the bottom of the wizard screen.

you can see that Upgrade Advisor has completed the analysis of SQL Server, Analysis Services and Reporting Services

11. In View Report screen you can see a summary of issues which can affect you once you upgrade. To learn more about each issue you can expand the issue and read the detailed description as shown in the below snippet.

sql server 2008 upgrade advisor view report screen

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, February 1, 2016 - 5:55:05 AM - babita Back To Top (40563)

 Hi, I have to run the Upgrade advisor on SQL server 2005 which is on remote server. We connect to that server via SQL server 2005 management studio using IP of that server and port and instance. But I am not able to connect to that server via Upgrade Advisor. Please assist me how can I proceed. Thanks in advance

 


Thursday, February 13, 2014 - 9:37:22 AM - Arvind Yadav Back To Top (29429)

Nice Article. Very helpful with respect to Migration of SQL Server from 2000 to 2008. Really Appreciated and very thankful.

 


Thursday, February 13, 2014 - 9:35:44 AM - nitin kadam Back To Top (29428)

Good Article.... very informative.


Tuesday, May 15, 2012 - 7:46:05 AM - Dinesh Vishe Back To Top (17460)

I want mssql to oracle migration...what should precaution should taken ?????















get free sql tips
agree to terms