Migrate a SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster

Article translations Article translations
Article ID: 910233 - View products that this article applies to.
SQL Server Support Voice Column

Migrating a SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster

To customize this column to your needs, we want to invite you to submit your ideas about topics that interest you and the issues that you want to see addressed in future Knowledge Base articles and Support Voice columns. You can submit your ideas and feedback using the Ask For It form. There's also a link to the form at the bottom of this column.
Expand all | Collapse all

On This Page

Welcome back! I am Sandy Yakob with the Microsoft SQL Server Content team. I am your hostess for the SQL Server Support Voice columns. A quick note about me: I have been with Microsoft for 14 years. For the past three years, I have been working with the SQL Server Content team.

We continue to provide a new column every other month. For this month's column, we will discuss how to migrate your SQL Server 2000 Analysis Services cluster to SQL Server 2005.

This article was written by Lan Lewis-Beven. Lan has been with Microsoft for over five years where she has been supporting SQL Server clustering and Analysis Services. Before joining Microsoft, Lan was the Lead Network Administrator for the University of North Carolina - Chapel Hill School of Medicine.

Migrate a SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster

The following shows a typical Analysis Services 2000 cluster. Analysis Services 2000 was installed as a generic service in the cluster group, and its repository is on a clustered file share.
Collapse this imageExpand this image
This graphic illustrates a typical Analysis Services Cluster installation.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
308023 How to cluster SQL Server 2000 Analysis Services in Windows 2000 and Windows Server 2003
We want to upgrade a SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster. In this article, we will cover the following topics:
  • Plan the upgrade
  • Perform the upgrade
  • Verify the upgrade

Plan the upgrade

Upgrade options

You can upgrade the SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster by using one of the following options:
  • Allocate a temporary server on the network. To do this, follow these steps:
    1. Back up the SQL Server 2000 Analysis Services cluster server, and then restore the backup to the temporary server as a stand-alone server.
    2. Upgrade the stand-alone SQL Server 2000 Analysis Services server to a stand-alone SQL Server 2005 Analysis Services server.
    3. Remove the SQL Server 2000 Analysis Services cluster.
    4. Install the SQL Server 2005 Analysis Services cluster.
    5. Back up the temporary SQL Server 2005 Analysis Services stand-alone server, and then restore the backup to the SQL Server 2005 Analysis Services cluster.
  • Allocate an extra cluster shared drive, and then install the SQL Server 2005 Analysis Services cluster side-by-side with your existing SQL Server 2000 Analysis Services cluster. Migrate the SQL Server 2000 Analysis Services cluster directly to the SQL Server 2005 Analysis Services cluster, and then remove the SQL Server 2000 Analysis Services cluster.
  • Uncluster the SQL Server 2000 Analysis Services cluster to make the cluster a stand-alone server on the cluster nodes in Microsoft Windows. Install a SQL Server 2005 Analysis Services cluster on the same shared drive. Migrate from the stand-alone SQL Server 2000 Analysis Services server to the SQL Server 2005 Analysis Services cluster. Remove the SQL Server 2000 Analysis Services stand-alone server.
The first option is a stand-alone Analysis Services server upgrade, and we will not cover the details of this option in this article. This article focuses on the second option and the third option.

Additional considerations

The existing SQL Server 2000 Analysis Services cluster uses a Windows cluster name and IP for client access, but the new SQL Server 2005 Analysis Services cluster will have its own name and IP. If you want the SQL Server 2005 Analysis Services cluster to use the same name as your existing SQL Server 2000 Analysis Services cluster to keep the client application connection string intact, consider the following:
  • On a Windows cluster, you cannot have two resources that use the same network name. You must rename the cluster to enable the SQL Server 2005 Analysis Services resource to keep the SQL Server 2000 Analysis Services name.
  • You can easily rename the Windows cluster network, and you do not have to restart the computer. However, if you have other applications or services that rely on the cluster name, you must deal with each application or service individually, depending on the nature of the application or service. For example, if you have a cluster file share, you must move the drive to the new SQL Server 2005 Analysis Services cluster group. After you do this, the cluster file share can continue to use the same network name for access.
  • The clustered SQL Server 2005 Analysis Services server can use the virtual server name only for client access, even if the server is a named instance. For example, if the SQL Server 2005 Analysis Services cluster is named VirtualAS/Instance1, the client can access the cluster by using VirtualAS as the server name.

Prepare for the upgrade

After you decide which option that you will use for the upgrade, you have to run SQL Server 2005 Upgrade Advisor. Upgrade Advisor checks for any problems in the existing SQL Server 2000 Analysis Services cluster, and then fixes those problems. Upgrade Advisor is a stand-alone tool that has a dedicated analyzer. Upgrade Advisor runs for each SQL Server component, including Analysis Services. Upgrade Advisor can be used to identify potential problems before you run SQL Server Setup. Each analyzer produces an XML report for that component. To download Upgrade Advisor, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
The installation of this tool is straightforward:
Collapse this imageExpand this image
This graphic shows the first screen of the Microsoft SQL Server 2005 Upgrade Advisor Setup program, including the installation path.
After you install Upgrade Advisor, you can start it on the Start menu. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2005 Upgrade Advisor, and then click Upgrade Advisor.
Collapse this imageExpand this image
This graphic shows the SQL Server 2005 Upgrade Advisor Welcome screen.
Click Launch Upgrade Advisor Analysis Wizard, and then click Analysis Services. Enter the virtual SQL Server 2000 Analysis Services name (cluster name).
Collapse this imageExpand this image
This graphic shows the first screen of the Upgrade Advisor Analysis Wizard. Analysis Services is the checked box under Components.
Click Run, and then note the report files location.
Collapse this imageExpand this image
This graphic shows the settings that are selected for the Upgrade Advisor Analysis  Wizard. It also shows the path of the report files.
When Upgrade Advisor is finished, click Launch Report for an overview of reports. Upgrade Advisor provides advice about whether to fix the problem before or after you perform the upgrade. Upgrade Advisor may also provide a warning message.
Collapse this imageExpand this image
This graphic shows the report that is provided by Upgrade Advisor.
Review the logs for detailed information, and then correct any problems that must be corrected before you perform the upgrade.

Perform the upgrade

Depending on the upgrade option that you select, you may have to perform one or more of the following tasks:
  • Rename the Windows cluster.
  • Install a named instance of SQL Server 2005 Analysis Services cluster side-by-side with the existing SQL Server 2000 Analysis Services cluster.
  • Uncluster SQL Server 2000 Analysis Services, and then install a default instance of SQL Server 2005 Analysis Services on the same shared drive.
  • Migrate SQL Server 2000 Analysis Services to SQL Server 2005 Analysis Services.
  • Remove the SQL Server 2000 Analysis Services cluster and the individual Analysis Services local server on both nodes.

Rename the Windows cluster

You can rename the Windows cluster in the Cluster network name dialog box. Click the Parameter tab, and then click Rename. You do not have to restart the computer.
Collapse this imageExpand this image
This graphic shows the Parameters tab of the Cluster network name dialog box.

Install a named instance of SQL Server 2005 Analysis Services cluster side-by-side with the existing SQL Server 2000 Analysis Services cluster

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
910230 How to install SQL Server 2005 Analysis Services on a failover cluster

Uncluster SQL Server 2000 Analysis Services, and then install a default instance of SQL Server 2005 Analysis Services on the same shared drive

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
308023 How to cluster SQL Server 2000 Analysis Services in Windows 2000 and Windows Server 2003

Migrate SQL Server 2000 Analysis Services to SQL Server 2005 Analysis Services

The Migration Wizard copies SQL Server 2000 Analysis Services database objects, and then re-creates the objects on the SQL Server 2005 Analysis Services server. 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.

By default, the Migration Wizard is located in the Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE folder.
Collapse this imageExpand this image
This graphic shows the Welcome Screen of the Analysis Services Migration Wizard.
Provide the clustered SQL Server 2000 Analysis Services name (cluster name) as the source and the clustered SQL Server 2005 Analysis Services named instance name as the destination.
Collapse this imageExpand this image
This graphic shows the dialog box where you will specify  the  source server and the destination server that will be used by the Migration Wizard.
The available user database that you want to migrate will be automatically detected. You can either migrate specific databases or migrate all databases.
Collapse this imageExpand this image
This graphic shows a list of user databases that the Migration Wizard has detected.
The first phase of the migration is "Validating databases." We can review the related information from the logs, make modifications, and restart this phase.
Collapse this imageExpand this image
This graphic displays the validation of the databases that were selected for migration.
The next phase is to migrate the database objects.
Collapse this imageExpand this image
This graphic shows the migration of the metadata for the databases. This screen contains a progress bar and shows when the migration is finished.
You can see an overview of what information has been migrated when you click Next after the migration phase.
Collapse this imageExpand this image
This graphic shows the last step of the Migration  Wizard. It lists the databases that have been migrated.

Remove the SQL Server 2000 Analysis Services cluster and the individual Analysis Services local server on both nodes

For more information about how to remove the SQL Server 2000 Analysis Services cluster and the individual Analysis Services local server on both nodes, see SQL Server Books Online.

Verify the upgrade

After the Migration Wizard is finished, we can verify the databases from SQL Server Management Studio, connect to the SQL Server 2005 Analysis Services cluster, and then process the databases from the original data sources before we run queries against them:
Collapse this imageExpand this image
This graphic shows the migrated databases by using the SQL Server Management Studio that is connected to the SQL Server 2005 Analysis Services cluster.
Note The individual database modification will be covered in a future article.
I’d like to thank Lan Lewis-Bevan for her hard work on this article. As always, feel free to submit ideas on topics you want addressed in future columns or in the Knowledge Base using the Ask For It form.

Properties

Article ID: 910233 - Last Review: January 15, 2007 - Revision: 3.1
APPLIES TO
  • Microsoft SQL Server 2005 Analysis Services
Keywords: 
kbhowto KB910233

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com