Using Microsoft Upgrade Advisor for SQL Server 2008 R2

A solution provider can use the Microsoft Upgrade Advisor to analyze SQL Server and highlight issues that may arise during a SQL Server 2008 R2 upgrade.

Solution provider takeaway: Much of the stress that results from downtime in a customer's SQL Server 2008 R2 environment can be prevented by using the Microsoft Upgrade Advisor. See the steps that the upgrade advisor takes to analyze a SQL Server and let you know early on if any issues are imminent.

Any solution provider who has worked through a server upgrade knows that things don't always go as planned. To get the kinks out of the process, Microsoft researchers have developed upgrade advisors that are designed to help solution providers assist their customers with upgrades for new products such as Windows 7 or SQL Server 2008 R2.

Using the Microsoft Upgrade Advisor for SQL Server 2008 R2 can reduce the downtime that usually occurs during a SQL Server upgrade. It contains two primary components: the Analysis Services Wizard and the Report Viewer (see Figure A). The Analysis Wizard is the component that examines a customer's existing SQL Server deployment to determine if there are any issues that may prevent a successful upgrade. The results of this analysis are displayed in the Report Viewer.

Figure A: The Microsoft Upgrade Advisor for SQL Server 2008 R2 provides an Analysis Wizard and a Report Viewer.

Although the SQL Server 2008 Upgrade Advisor may sound appealing, there are a few variables to be aware of, such as what types of issues it looks for or how thorough it is in its analysis. Microsoft most likely took a copy of the SQL Server Setup file and removed the code that copies files and makes system configuration changes. The SQL Server 2008 R2 Upgrade Advisor is built around the series of checks that remain–including hardware and software prerequisites, database structures and the installed SQL components.

Using Microsoft Upgrade Advisor to analyze SQL Server

The Microsoft Upgrade Advisor can help solution providers analyze SQL Server 2000, 2005 or 2008. To do that, run the upgrade advisor directly on the SQL Server that is being considered for an upgrade. After arriving at the welcome screen, shown in Figure A, click on the Launch Upgrade Advisor Analysis Wizard link, which takes you to the wizard's Welcome screen (see Figure B.) This screen explains that there are five steps involved in the analysis:

  • Identify the SQL Server components to analyze
  • Authenticate into the server
  • Provide any necessary additional parameters
  • Perform the actual analysis
  • View the results

Figure B: There are five steps for working through the wizard.

Clicking Next will bring you to a screen that asks you to specify the SQL Server instance that you want to analyze. This screen also requires you to select the individual components, such as analysis services, reporting services, integration services, etc. for the wizard to analyze. It is critical to ensure that you choose the appropriate set of components. If you don't, the wizard may skip some of its upgrade checks.

Click Next, and you will be prompted to authenticate into your SQL Server. After selecting the desired instance, you can provide your authentication credentials if necessary and click Next.

At this point, you will be taken to the screen shown in Figure C. You must now select the databases that you want to analyze. You will also have the option to analyze trace files and SQL batch files in this screen.

Figure C: Select the databases that you want to analyze.

After clicking Next , you will see a summary of the analytic options that you have chosen. Given the importance of running the upgrade advisor in your customer's environment, take a moment to make sure that the options that you have chosen are correct. If the summary is accurate, click the Run button.

The Microsoft Upgrade Advisor will now analyze SQL Server. The amount of time that it takes depends on the options that you have chosen. When I ran the upgrade advisor against a copy of SQL Server 2005 running on a lab SharePoint server, for example, the process took about 20 minutes to complete.

Viewing the Upgrade Advisor results

When the analysis is complete click the Launch Report button and you will be taken to the Report Viewer. As you can see in Figure D, the upgrade advisor found something with the SQL deployment that might have prevented a successful upgrade.

Figure D: Report Viewer found a possible issue with the SQL deployment.

The warning messages show something that I suspect Microsoft included to prevent it from being liable for any future problems. It essentially states that there may be unreported issues that were undetectable. This is Microsoft's way of saying that the tool may not be perfect and that you can't hold the company at fault if something unforeseen occurs during the upgrade. The second warning message is a legitimate issue, but it's not catastrophic. --Because of that, it shows as a warning, not an error.

If I expand the error, the upgrade advisor gives me the option of seeing the affected objects or of accessing documentation that discusses what the error is and how to resolve it. As you can see in Figure E, there is also an option to dismiss the error after it has been resolved.

Figure E: Microsoft provides links to documentation that tells how to fix any reported errors.

Acquiring the SQL Server 2008 R2 Upgrade Advisor

You can download the SQL Server 2008 R2 Upgrade Advisor . Although the upgrade advisor is included in the Microsoft SQL Server R2 Feature Pack, Microsoft also provides a link to a standalone version.

Upgrading a SQL Server almost always means scheduling downtime. The Microsoft Upgrade Advisor can help solution providers decrease downtime in customer environments by seeing upgrade problems before they happen, and that will help keep customers happy.

About the expert Brien M. Posey, MCSE, is a five-time recipient of Microsoft's Most Valuable Professional award for his work with Exchange Server, Windows Server, Internet Information Services (IIS), file systems and storage. Posey has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. As a freelance technical writer, he has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit Posey's personal website at www.brienposey.com.

Dig Deeper on Database software management