If you currently do not have a requirement of upgrading your Analysis Services 2000 to Analysis Services 2005 or you are a first time user of Analysis Services then you can jump to the next section. The upgrade process in general is not a seamless process, and not without its share of gotchas. This is especially true when much of the product has been redesigned, such as you are faced with going from Analysis Services 2000 to Analysis Services 2005. Fortunately, Analysis Services 2005 provides you with a tool called Upgrade Advisor to prepare you to upgrade databases from Analysis Services 2000. Upgrade Advisor is available as a redistributable package with SQL Server 2005. You need to install Upgrade Advisor from the Servers\ redist\Upgrade Advisor folder on your CD/DVD. When you run Upgrade Advisor on your existing Analysis Services 2000 instance, Upgrade Advisor informs you whether your database(s) will be upgraded successfully without any known issues. Warnings are provided by Upgrade Advisor in cases where there might be changes in the names of the dimensions or cubes due to the Analysis Services 2005 architecture. Once you have reviewed all the information from Upgrade Advisor, you are ready to start the upgrade. Follow the steps below to use Upgrade Advisor for analyzing the effects of upgrading your Analysis Services 2000 to Analysis Services 2005.
Go to "Program Files\Microsoft SQL Server 2005 Upgrade Advisor" folder on your machine and click the UpgradeAdvisorWizard.exe file to start the wizard. The welcome screen appears, as shown in Figure 2-1. Click the Next button to continue.
In the SQL Server 2000 Component selection page, shown in Figure 2-2, enter the name of a machine that contains SQL Server 2000 products. If you click the Detect button then Upgrade Advisor will populate the SQL Server Components page with the services running on the server name provided. If you know the services available on the server machine you can enable the check boxes corresponding to the services available in this page. Select Analysis Services and click Next.
In the Confirm Upgrade Advisor Settings page, as shown in Figure 2-3, you can review your selections. If your selections are not correct, go back to the previous page and make the appropriate changes. Click the Run button for upgrade analysis.
In the next screen you see the Upgrade Advisor analyzing the databases on your Analysis Services 2000 server. At the end of the analysis you see the errors and warnings reported by the Upgrade Advisor, as shown in Figure 2-4.
Click the Launch Report button to see the detailed report of the analysis and the actions you need to take for a smooth migration of your databases, as shown in Figure 2-5.
We strongly recommend that you run the Upgrade Advisor utility, analyze all the errors and warnings reported, and take the necessary actions. In certain cases you might have to perform some operations on your existing Analysis Services 2000 database. For example, if you have a writeback partition in Analysis Services 2000 that contains data, the recommended approach is to convert the writeback partition to a MOLAP partition, upgrade the database to Analysis Services 2005, reprocess the partition, and then re-create a new writeback partition. Similarly, you might have to perform several steps either before or after the upgrade on your Analysis Services database to ensure your existing applications will work correctly.
Once you have analyzed the Upgrade Advisor report on your Analysis Services 2000 databases you are ready for upgrade. Install the product and select the option to upgrade your Analysis Services 2000 OLAP databases. Analysis Services 2005 only upgrades the metadata of your OLAP databases. So, you will need your relational data source available so that source data can be populated once again into your cubes. You need to process all the databases that have been upgraded from your Analysis Services 2000 installation as well. Once this is completed, all your cubes and dimensions will be available for querying. If warnings in Upgrade Advisor indicate that names of dimensions or hierarchies would be changed and if you have invested a lot in building applications, then your applications might also have to be updated accordingly. Please plan to spend time to ensure all your applications are working for your customers after the upgrade process. We also have an experienced-based general recommendation — test the entire upgrade process on a test machine. In this way, you can verify if your existing applications are working as expected using the Analysis Services 2005 instance. Finally, with confidence you can perform the upgrade on your production machine.
If you do not have a test machine we recommend the following approach: Install Analysis Services 2005 as a named instance. Analysis Services 2005 provides you with a wizard to migrate your databases from an Analysis Services 2000 server to an Analysis Services 2005 instance. Analysis Services 2005 provides you with an integrated environment to manage all SQL Server 2005 products using SQL Server Management Studio (SSMS). SSMS is the newer version of the famous Query Analyzer, which is available in SQL Server 2000.
In the following short tutorial, we will reference Foodmart2000 as a sample database and you can use your own databases where appropriate. To migrate your Analysis Services 2000 databases to an Analysis Services 2005 instance, follow these steps:
Launch SQL Server Management Studio, which comes with Analysis Services 2005, by choosing from the Start Menu All Programs Microsoft SQL Server2005 SQL Server Management Studio. Connect to the Analysis Services 2005 instance using SQL Server Management Studio's Object Explorer. Right-click the server name and select Migrate Database as shown in Figure 2-6. This takes you to the welcome screen of the wizard. If someone else had used this wizard and disabled the welcome page you might not see the welcome page. If you are in the welcome page click the next button to proceed to step 2.
In the Specify Source and Destination page, the wizard pre-populates the name of your Analysis Services 2005 instance. Enter the machine name of your Analysis Services 2000 as shown in Figure 2-7 and click the Next button.
In the Select Databases to Migrate pages you will see the list of databases on your Analysis Services 2000 itemized and pre-selected for migration as shown in Figure 2-8. A column on the right side provides you with the name of the database on your Analysis Services 2005 instance. You have the option of selecting all the databases or just a few databases on your Analysis Services 2000 to migrate. Deselect all the databases and select the Foodmart 2000 database; this is the sample database that is shipped with Analysis Services 2000.
The Migration Wizard now validates the selected databases for migration. As the Migration Wizard validates the objects within a database for migration, it provides you a report including warnings of objects that will be changed during the migration process, as shown in Figure 2-9. You can save the logs to a file for future reference. Once you have analyzed the entire report, click Next to deploy the migrated database to your Analysis Services 2005 instance.
The Migration Wizard now sends the metadata of the migrated database to the Analysis Services 2005 instance. The new database with migrated objects is created on your Analysis Services 2005 instance and the Migration Wizard reports the status, as shown in Figure 2-10. Once the migration process is complete, click the Next button.
In the completion page the Migration Wizard shows the new databases that have been migrated in a tree view. Click Finish to complete the migration.
You should be aware that the migration wizard will only migrate the metadata of an Analysis Services database and not the data. Hence the migrated cubes and dimensions are not accessible for querying unless you reprocess the databases. Process all the databases that have been migrated, and test your applications against the migrated databases on your Analysis Services 2005 instance. You need to direct your applications to hit the new Analysis Services 2005 instance name. Once you have verified that all applications are working as expected, you can uninstall Analysis Services 2000 and then re-name your Analysis Services 2005 named instance to the default instance using the instance rename utility ASInstanceRename.exe that can be found in the directory \Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\ Common7\IDE.