To ensure that you have a successful upgrade to SQL Server 2005, here are a few housekeeping tips. Ensure that all system databases are configured to autogrow, and ensure that they have adequate hard-disk space. Make certain that all startup stored procedures are disabled, as the upgrade process will stop and start services on the SQL Server instance being upgraded. Now that we have covered the strategies for upgrading to SQL Server 2005, we can discuss the upgrade tools available to assist in this process.
If you don't like to be first to adopt a technology, the SQL Server 2005 Upgrade Advisor is the tool for you. This tool is based on early adopters' feedback and internal lab-testing feedback. The SQL Server 2005 Upgrade Advisor is a free download available at www.microsoft.com/sql and is also available as part of the SQL Server 2005 installs media for all editions. The purpose of this utility is to identify known upgrade issues and provide guidance for workarounds or fixes for the identified issues on a per-server components basis. Microsoft worked hard on this tool as a risk-mitigation effort to empower SQL Server 7.0 (SP4) and SQL Server 2000 (SP3 or higher) users to upgrade to SQL Server 2005. So, whether you are running Analysis Services, DTS packages, Notification Services, Reporting Services components, or a combination of components, the Upgrade Advisor tool can help.
The Upgrade Advisor is a relatively simple tool to use. The tool can be found in the "Prepare" section of the default screen of the install CD/DVD. It can also be found at www.microsoft.com/sql. Select the download section on the left side of the page, and then select SQL Server 2005. As shown in Figure 3-2, be sure to select "check for updates," as upgraded versions of the tool are available online.
Figure 3-2
The tool is constantly being updated to reflect the lessons learned by the DBAs who updated before you. Although the SQLUASetup.msi file itself is a little over 4 MB; it will require the installation of the .NET Framework 2.0, which can be downloaded through Windows Update service or from MSDN. There are also X64 and Itanium versions of the tool and .Net framework that you can choose to install. Or you can choose to install a single instance and version of the tool to test servers across your enterprise. This option supports a zero-footprint interrogation with read-only access to servers.
Note | This tool is read-intensive and should be tested on a test server to evaluate the potential impact on your systems. |
The installation process is straightforward; the only option is to select the location where you would like to install the tool. The default install path is C:\Program Files\Microsoft SQL Server 2005 Upgrade Advisor. At the time of this writing, there are four incremental releases of this Upgrade Advisor with a total of 20 additional rules from the original release, for a grand total of 103 rules. These rules represent conditions, situations, or known errors that could affect your upgrade to SQL Server 2005. I also recommend checking "readme file" and q-article 905693 for the latest updates.
Once installed, the Upgrade Advisor presents you with two choices: Launch the Upgrade Advisor Analysis Wizard or Upgrade Advisor Report Viewer. Launch the Upgrade Advisor Analysis Wizard to run the tool. As shown in Figure 3-3, you simply select a server and the component to analyze for upgrade, or you can click the Detect button, which kicks off an inspection process that selects the components installed on your system.
Figure 3-3
After you select the components for testing, the next decision is to select the databases that you would like to have evaluated for upgrade, as shown in Figure 3-4. The best part of this process is that you have the option to analyze trace and batch files to help make this a comprehensive analysis. That is, by adding these files to the evaluation process, Upgrade Advisor is not only evaluating the database but its workload and job scripts as well. All you have to do is to select the path to the directory where your trace file(s) or your batch file(s) are located.
Figure 3-4
After you have completed the configuration of the components that you want to evaluate, you will be prompted to begin the analysis. If you have any questions during the configuration steps, the Help button brings up an Upgrade Advisor-specific book online (UABOL) that is rich in information and guides you through the options. As the component-level analysis completes, a green, yellow, or red dialog box will indicate the outcome of the test.
Once completed, you can view the discovered issues via the Upgrade Advisor Report Viewer. The reports themselves, as shown in Figure 3-5, are presented in an interface similar to a Web browser. The information can be analyzed by filtering the report presented by Server, Instance, component, or issue type. How to interpret the results of this report is discussed later in this chapter.
Figure 3-5
If you have a server farm or if you just prefer scripting, a command-line utility is also available. With the UpgradeAdvisorWizardCmd utility, you can configure the tool via an XML configuration file and receive results as XML files. The following arguments can be passed to the UpgradeAdvisorWizardCmd utility:
ConfigFile (config file path)
SQLUser
SqlPassword
Servername and/or InstanceName
CSV (where to deliver the XML results doc)
All capabilities and parameters discussed in the wizard section are exposed through the configuration file. The results from a command-style interrogation can still be viewed in the report viewer, via XML documents or Excel if you used the CSV option.
You can also install or remove the Upgrade Advisor application via the command prompt. From there you can control the install process with or without the UI. You can also configure the install path and process-logging options.
The Upgrade Advisor's report contains a wealth of information. The key is to understand how this information is presented, what needs to be resolved, and when. As you see in Figure 3-5, the first column shows the importance of a finding or a recommendation, the second column tells you when it needs to be addressed, and the Description column tells you about the issue. The recommended way to approach this is to first categorize the information by "importance" and "when to fix" the items. Specifically, the sum of the indicators should dictate whether issues should be addressed before or after the upgrade process. The following table provides our recommendations of when to address these issues.
Importance | When to Fix | Our Recommendation |
---|---|---|
Red | Before | Resolve Before Upgrade |
Red | Anytime | Resolve Before Upgrade |
Red | After | Resolve After Upgrade |
Yellow | Anytime | Resolve After Upgrade |
Yellow | After | Resolve After Upgrade |
Yellow | Advisory | Resolve After Upgrade |
Issues that have been flagged with an "Importance" of red and "When to Fix" of "Before" or "Anytime" should be addressed before an upgrade process. Typically, these issues require remediation because of SQL Server 2005 functionality changes. The remaining issues can usually be resolved after the upgrade process, because they either have a workaround within the upgrade process or will not affect it at all.
If you expand the error in question, additional information appears, as shown in Figure 3-6.
Figure 3-6
The "Show affected object" link shows the exact objects flagged by the Upgrade Advisor process as affected, while the "Tell me more about this issue and how to resolve it" link takes you to the corresponding section of the Upgrade Advisor BOL. The UABOL describes the conditions and provides guidance regarding corrective action to address the issue. The Upgrade Advisor BOL is a true gem, as it provides guidance for problem resolution in areas beyond the scope of the tools (such as replication, SQL Server Agent, and Full-Text search).
The "This issue has been resolved" checkmark is for your personal tracking of the issues that have been resolved. This metadata checkmark is in place to support remediation processes by allowing the report to be viewed by filtered status of resolved issues or preupgrade (unresolved) issues.
If you that prefer scripting, the viewer is nothing more than a XSLT transformation applied to the XML result file located in your "My Documents\SQL Server 2005 Upgrade Advisor Reports\" directory. Individual component results and configuration files can be found in each server name-based directories. You can even export viewer-based reports to other output formats such as CSV or text.