Migrating Existing Analysis Services Databases
You can use the Analysis Services Migration Wizard to upgrade Microsoft SQL Server 2000 Analysis Services databases to Microsoft SQL Server 2008 Analysis Services. During migration, the wizard copies SQL Server 2000 Analysis Services database objects and then re-creates them on an instance of SQL Server 2008 Analysis Services. The source databases are left intact and are not modified. After you verify that the new databases are fully operational, you can manually delete the old databases.
As a best practice, you should migrate your databases one at a time, or in small batches. This will allow you to verify that each database object appears as expected on the destination server, before you migrate additional objects. When you use the Migration Wizard, the MSSQLServerOLAPService service must be running on both the source and the destination server. (If you are migrating a named instance, the **MSOLAP$**instancename service must be running on both servers.)
After you migrate your databases, you must process them from the original data source before you can run queries against them. For more information, see Processing Analysis Services Objects.
Using the Migration Wizard
You can start the Migration Wizard from an Analysis Services server node in the Object Browser in SQL Server Management Studio. You can also start the wizard at the command prompt, by running the program MigrationWizard.exe.
Specifying a Source and a Destination
After the initial Welcome page in the wizard, the Specify Source and Destination page appears. On this page, you specify the source server and the destination server for the databases that you are migrating. The destination instance of Analysis Services can be either local or remote. If you specify a destination computer that is running more than one instance of SQL Server 2008 Analysis Services, use the format computername\instancename.
You can also decide to save the database schema to a script file and complete the migration later by using an Analysis Services Execute DDL task in a SQL Server 2008 Integration Services package. For more information about the Analysis Services Execute DDL task, see Analysis Services Execute DDL Task.
Selecting Databases to Migrate
The next page of the wizard, Select Databases to Migrate, displays a list of all SQL Server 2000 Analysis Services databases and the corresponding SQL Server 2008 Analysis Services databases to be created. By default, all databases are selected, but you can clear the check box next to the name of any database that you do not want to migrate.
By default, the names of a source database and its corresponding destination database are identical. However, if another database that uses the same name already exists on the destination server, the Migration Wizard assigns a unique name for the new database. (For example, a second copy of AdventureWorks becomes AdventureWorks 1.) You can use the name provided in the wizard, type a new name in the wizard, or rename the database after you complete the wizard.
For more information about how SQL Server 2000 Analysis Services objects correspond to SQL Server 2008 Analysis Services objects, see Migration Considerations (Analysis Services).
Note
The Migration Wizard does not support the migration of Microsoft SQL Server 7.0 Analysis Services databases.
Validating Databases
On the Validating Databases page, the Migration Wizard validates the structure of the databases to be migrated. The wizard generates a log as the databases are validated. To view the log or a subset of the log entries, click View Log, and then select from the following options:
Show All
Show Errors
Show Warnings
Show Successes
Validation stops if an error occurs. To continue the migration process, fix the database that caused the error, and then click Restart.
You can stop the validation process at any time by clicking Stop. The wizard displays a message that states the number of databases that have not been validated.
Migrating Databases
On the Migrating Databases page, the wizard migrates SQL Server 2000 Analysis Services objects to SQL Server 2008 Analysis Services format. You can stop the process and roll back the whole transaction by clicking Stop and then Cancel.
After migration is complete, you can change selections you made earlier by clicking Back to return to earlier pages of the wizard. However, if you do this, you must run the migration process again. Running the migration process again deletes the databases that have already been migrated.
Completing the Wizard
The Completing the Wizard page displays a summary of the SQL Server 2008 Analysis Services databases that have been created. Click Finish to complete the wizard.
After you migrate a database, you must process the database from the original data source before you can query the database. For more information, see Processing Analysis Services Objects.