Pre-Upgrade Checks


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.

SQL Server Upgrade Advisor

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.

Installing the SQL Server 2005 Upgrade Advisor

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.

image from book
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.

Using the Upgrade Advisor

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.

image from book
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.

image from book
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.

image from book
Figure 3-5

Scripting the Upgrade Advisor

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.

Resolving Upgrade Issues

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.

Open table as spreadsheet

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.

image from book
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.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net