10
As Joe describes in his post ‘Upgrading to SQL Server 2008: The Next Version of SQL Server Upgrade Assistant’, Microsoft made available a very handy utility called the Upgrade Advisor (UA) to assist customers in assessing their existing SQL Server 2000 environments for upgrade to the SQL Server 2005 platform. With the arrival of SQL Server 2008 we also have access to the updated UA utility that supports analyzing both SQL Server 2000sp4 and SQL Server 2005sp2 installations (which are the supported platforms for upgrade to SQL Server 2008). The UA utility is applicable to both the relational engine as well as the SQL Server Business Intelligence (BI) components (Integration Services|Data Transformation Services, Analysis Services, and Reporting Services).
*The UA utility requires the .Net Framework 2.0 redistributable
In our testing of this utility based on the November 2007 CTP build of Katmai (CTP5) we found that the UA will allow the analysis of both local and remote instances of the BI components excluding Reporting Services. Reporting Services instances will require the UA to be installed on the report server itself. Another discovery we found from our tests is that in the CTP5 build the Reporting Services analysis does not seem to be working as designed. Integration Services is not yet supported in UA, however the tool does note that in a future CTP/build of Katmai UA will support analyzing SSIS. In addition, SQL Server 2008 will continue to support the Data Transformation Services (DTS) runtime and thus the UA allows you to analyze DTS Packages in both a SQL Server 2000 and 2005 environment. A nice bonus is the ability to analyze DTS packages stored in the file system as well. Also note that when you analyze a SSAS 2000 instance the UA utility will require the Decision Support Objects (DSO) object model.
*DTS Packages are an official deprecated feature in SQL Server 2008. You should migrate your organization’s DTS packages as soon as possible to help support future upgrades of your SQL Server environments.
The UA utility provides you with both analysis and reports. UA reports provide you with the alerts/warnings that you should consider or take action on prior to upgrading your SQL Server environments to the 2008 platform. A nice feature of the UA utility is its ability to provide the user with links directly to its alert documentation. Some of the more important UA alerts are as follows:
SQL Server Data Transformation Services UA Key Alerts
· SQL Server DTS Packages are now a deprecated feature
· To continue running and managing your existing DTS Packages, the SQL Server 2008 Backwards Compatibility Files are required (these files were required for the same reason in SQL Server 2005 platform)
· The DTS Designer Components are a required add-on if you wish to edit DTS packages in Management Studio (these files were also required for the same reason in SQL Server 2005 platform)
SQL Server Analysis Services UA Key Alerts
· VBA functions handle NULL values and empty values differently from SQL Server Analysis Services 2005
· Custom Aggregations are not migrated
· ODBC Connections are not migrated
· MDX Changes
SQL Server Reporting Services UA Key Alerts
· Custom Reporting Services extensions (DLLs) will not be moved
· Client Certificates were detected (Reporting Services 2008 does not support them)
· Reporting Services 2000 Report Server database is on a remote 2000 instance (blocks a 2000 to 2008 SSRS upgrade)
Be aware that UA cannot analyze every aspect of your SQL Server environments and thus it cannot detect every issue that could affect an upgrade. Some examples of items not analyzed by the UA are middle-tier or front-tier code containing TSQL, encrypted stored procedures, and extended stored procedures. Regardless of the UA limitations, it is a very good tool for the BI professional to leverage for prepping their environments for SQL Server 2008.
Happy Upgrading!
Derek Comingore
