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

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. For more information, click the following article number to view the article in the Microsoft Knowledge Base: 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: The installation of this tool is straightforward: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.Click Launch Upgrade Advisor Analysis Wizard, and then click Analysis Services. Enter the virtual SQL Server 2000 Analysis Services name (cluster name). Click Run, and then note the report files location.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. 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.

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:

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:

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.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.The available user database that you want to migrate will be automatically detected. You can either migrate specific databases or migrate all databases. The first phase of the migration is "Validating databases." We can review the related information from the logs, make modifications, and restart this phase. The next phase is to migrate the database objects. You can see an overview of what information has been migrated when you click Next after the migration phase.

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: 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 (http://support.microsoft.com/common/survey.aspx?scid=sw;en;1301&p0=&p1=&p2=&p3=&p4=) form.