Microsoft's SQL Server 2005 Upgrade Advisor helps you analyze your customer's SQL Server databases and servers before performing a server upgrade to SQL Server 2005 -- so you have fair warning of potential problems.
The Upgrade Advisor is an analysis and reporting engine that is available on the SQL Server 2005 DVD or as a download from Microsoft's Web site (click on the link above). Once installed on a client machine, the tool can be used to analyze databases or components on any server the client has access to, allowing you to track server upgrades to the entire enterprise from a single point.
Installing the Upgrade Advisor, simply run the MSI file from the CD or the download site, then specify an installation path. Once installed the tool will expose an icon in the root of the Microsoft SQL Server 2005 folder in the Start menu. Keep in mind that prerequisites for installing the tool include both operating system and .NET framework minimums. Your client must be running Windows 2000 SP4 or later, Windows XP SP2 or later or Windows Server 2003 SP1 or later. The tool also requires .NET Framework 2.0.
The first option you're presented with upon launching the tool is whether to analyze or view reports from previous analysis sessions. The tool is designed for iterative use; once you've detected issues in servers and databases, you can track them through the tool, rerunning the analysis once you believe all issues have been corrected in order to verify your progress.
Selecting "Launch Upgrade Advisor Analysis Wizard" brings up a menu of possible analysis options. The tool can detect problems with virtually all components present in SQL Server 7 or SQL Server 2000 installations.
Once you've indicated which components you'd like to analyze, you will be given options to help the tool pinpoint or broaden analysis to meet your requirements. For instance, if you're analyzing database engine installations, you can select one or more live databases, or keep the tool from interfering with live systems by analyzing trace or SQL batch files. The latter choices may not detect as many possible errors, but they will help in situations where you cannot analyze the live databases due to security or other restrictions.
After options are selected, the actual analysis begins, when the Upgrade Advisor interrogates your systems and processes what it finds through its rules engine.
For the database engine alone, the tool can detect almost 50 conditions. These include everything from esoteric problems, such as "0xFFFF character is invalid in an identifier," to serious and, unfortunately, all-too-common issues like the use of deprecated DBCC commands and code that drops system tables. Documentation for the tool also lists 21 database engine conditions that are known but can't detected. You'll want to read through this list and resolve any issues you find in your databases.
When the analysis is complete you will be presented with a summary pane that presents a high-level overview of what work was done, and whether the analysis was successful.
Of course, analysis is not helpful without the accompanying report! Upon launching the report viewer you will be shown a list of issues, which will appear similar to the following, which was generated from an analysis of the Pubs sample database:
Important things to notice are that the report does not merely list the issue, it also explains what was affected and offers suggestions to help you work through the problem.
Once a problem has been resolved, be sure to check the box labeled "This issue has been resolved." This feature provides tracking capabilities so that you do not need to use another tool in order to manage your upgrade process. If you do want to employ another tool, the Upgrade Advisor's Report Viewer includes an Export option that allows exportation of the report to a comma-delimited file.
The SQL Server 2005 Upgrade Advisor is an invaluable tool in battling with stressful upgrade situations. Rather than walking into unknown territory, this tool can arm you with the necessary knowledge to fix any potential problems before they manifest as downtime -- which will greatly support your reputation as a superstar to your customers.
This tip originally appeared on SearchSQLServer.com.
About the author: Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of Pro SQL Server 2005, published by Apress.
