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.
2. On the wizard welcome screen click Next to continue.
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.
4. In Connection Parameters screen choose Instance Name, Authentication and then click Next to continue with the wizard.
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.
6. In Reporting Services Parameters screen you can specify the Reporting Server Instance name and then click next to continue with the wizard.
7. In Analysis Services Parameters screen you can specify an Analysis Server Instance name and then click Next to continue with the wizard.
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.
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.
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.
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.
