Microsoft KB Archive/916665

From BetaArchive Wiki

Article ID: 916665

Article Last Modified on 3/30/2006


APPLIES TO

  • Microsoft SQL Server 2000 Analysis Services


SQL Server Support Voice column

How to cluster SQL Server 2000 Analysis Services on a cluster that is running a 64-bit version of Windows

To customize this column to your needs, we want to invite you to submit your ideas about topics that interest you and 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.

Introduction

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 about every other month. For this month's column, we will discuss how to set up Microsoft SQL Server 2000 Analysis Services as a clustered service on a cluster that is running a 64-bit version of Microsoft Windows.

This column 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.

Overview

This column provides step-by-step instructions on how to set up Analysis Services as a clustered service on a cluster that is running a 64-bit version of Windows. Although Analysis Services is not a cluster-aware application, you can install and configure Analysis Services as a generic service on a cluster. The setup process is different from the 32-bit setup process because Analysis Services 64-bit installations must have the repository and the query log saved on a SQL Server database. Additionally, the Analysis Services client tools are not currently available on the node. The Analysis Services 64-bit installation on a cluster that is running a 64-bit version of Windows will involve the following major steps:

  1. Install Analysis Services client tools on a 32-bit computer on the same network.


Note Make sure that you have installed SQL Server 2000 Service Pack 3 (SP3) or a later version on that computer.

  1. Install a clustered instance of SQL Server 2000 on a virtual server of the cluster to save the repository and the query log of Analysis Services.
  2. Install a local instance of Analysis Services on each of the cluster nodes.
  3. Combine those local instances of Analysis Services into a virtual server as a generic service.

This column discusses only step 3 and step 4 mentioned above. We assume that you have already clustered a default instance of SQL Server 2000 on the cluster. If you need help installing a clustered instance of SQL Server 2000 on a Windows cluster, see the "Creating a Failover Cluster" topic in SQL Server 2000 Books Online.

Install a local instance of Analysis Services on each of the cluster nodes

Install Analysis Services on Node1

To install a local instance of Analysis Services on the first node, referenced as Node1, follow these steps:

  1. Log on to the computer of Node1 as a domain administrator, and then start SQL Server Setup.
  2. On the Setup Mode page, in the Start a New Installation list, click Local Instance, and then click Next.
  3. On the Welcome page, click Next.
  4. On the Registration Information page, click Next.
  5. On the Software License Agreement page, click Yes.
  6. On the Feature Selection page, click the icon for SQL Server, and then click Entire feature will be unavailable so that only Analysis Services is selected.
  7. Under Analysis Services, click SQL Repository, and then click Browse.
  8. Locate the folder in a shared disk of the cluster where you want to store the Analysis Services data files, and then click Next.
  9. On the Instance Name page, type the instance name of the local instance of SQL Server 2000 that will be installed with this instance of Analysis Services, and then click Next.
  10. On the Service Account page, click to clear the Auto-start service check box, specify the domain account that is used to start the Analysis Services service, and then click Next.
  11. On the Authentication Mode page, select an authentication mode. If you select Mixed Mode, you must specify a password for the sa login. Click Next to continue.
  12. On the remaining pages, accept the default settings.

Prepare the local instance of Analysis Services on Node1 for clustering

To prepare the local instance of Analysis Services on Node1 for clustering, follow these steps:

  1. Make sure that the newly installed SQL Server service (MSSQL$InstanceName) and the Analysis Services service (MSSQLServerOLAPService) are not started. You can check the status of those services by using the Services Microsoft Management Console (MMC).

    Note InstanceName is a placeholder for the name of the instance that you specified.
  2. Copy the following files from the folder that you specified in step 8 above to the data folder of the clustered instance of SQL Server 2000:
    • Foodmart 2000.ldf
    • Foodmart 2000.mdf
    • OLAPQueryLog.ldf
    • OLAPQueryLog.mdf
    • OLAPRepository.ldf
    • OLAPRepository.mdf
  3. Attach the databases to the clustered instance of SQL Server 2000. To do this, run the following commands at a command prompt:

    osql -S VirtualServerName -E -Q "EXEC sp_attach_db @dbname = N'Foodmart 2000', @filename1 = N'PATH\Foodmart 2000.mdf', @filename2 = N'PATH\Foodmart 2000.ldf' "

    osql -S VirtualServerName -E -Q "EXEC sp_attach_db @dbname = N'OLAPQueryLog', @filename1 = N'PATH\OLAPQueryLog.mdf', @filename2 = N'PATH\OLAPQueryLog.ldf' "

    osql -S VirtualServerName -E -Q "EXEC sp_attach_db @dbname = N'OLAPRepository', @filename1 = N'PATH\OLAPRepository.mdf', @filename2 = N'PATH\OLAPRepository.ldf' "

    Note PATH is a placeholder for the path of the folder that you copy those files to. VirtualServerName is a placeholder for the name of the virtual server where you install the clustered instance of SQL Server 2000.
  4. Start the SQL Server service (MSSQL$InstanceName) and the Analysis Services service (MSSQLServerOLAPService) by using the Services MMC.
  5. Start Registry Editor, and then locate the following registry subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\CurrentVersion

  6. Modify the value of the QueryLogConnectionString registry entry and the value of the RemoteQueryLogConnectionString registry entry to point to the new data source of the clustered instance of SQL Server 2000.
  7. Log on to the computer that has the Analysis Services client tools installed, and then change the repository connection string by using Analysis Manager. To do this, follow these steps:
    1. Start Analysis Manager.
    2. Register the Analysis server by using the computer name of Node1.
    3. Right-click the newly added computer name in the left pane, and then click Edit Repository Connection String.
    4. In the Edit Repository Connection dialog box, modify the connection string in both the Local and Remote text areas to point to the clustered instance of SQL Server 2000 in the cluster.
    5. Unregister the server of Node1 from Analysis Manager.

Install Analysis Services on Node2

To install a local instance of Analysis Services on the second node, referenced as Node2, follow these steps:

  1. Fail over all cluster resources from Node1 to Node2. To do this, follow these steps:
    1. Start Cluster Administrator.
    2. Open a connection to the cluster.
    3. Right-click the cluster group that contains the IP address and cluster name, and then click Move Group.
    4. Right-click the cluster group that contains the physical disk in which you stored the Analysis Services data files, and then click Move Group.
    5. Exit Cluster Administrator.
  2. Log on to the computer of Node2 as a domain administrator, and then repeat the steps in the "Install Analysis Services on Node1" section.

    Note You can choose the same folder as in Node1 to store the Analysis Services data files. This will result in two subfolders created in the same drive.
  3. For Node2, repeat steps 4 through 7 in the "Prepare the local instance of Analysis Services on Node1 for clustering" section.

Combine the local instances of Analysis Services into a virtual server as a generic service

To do this, follow these steps:

  1. Click Start, point to Programs, point to Administrative Tools, and then click Cluster Administrator.
  2. Open a connection to the cluster.
  3. Right-click the Resources folder, point to New, and then click Resource.
  4. In the New Resource wizard, for the resource name, type Analysis Services.
  5. For the description, type SQL Server 2000 Analysis Services 64-bit.
  6. Change the resource type to Generic Service.
  7. Change the group to be the same group that contains the shared disk in which the data files are located and that contains the cluster IP address and cluster name.
  8. Click Next.
  9. On the Possible Owners page, make sure that both nodes are listed in the Possible Owners list, and then click Next.
  10. On the Dependencies page, in the Available Resources list, click to select the shared disk resource where you store the Analysis Services data files.
  11. Click Add to move the shared disk resource to the Resource dependencies list.
  12. Click Next.
  13. On the Generic Service Parameters page, type MSSQLServerOLAPService as the service name.
  14. In the Start Parameters box, type net start MSSQLServerOLAPService.
  15. Click Next.
  16. On the Registry Replication page, click Add, and then type Software\Microsoft\OLAP Server in the Root registry key entry box.
  17. Click OK to close the Registry Key dialog box.
  18. Click Finish to complete the New Resource wizard. The following message should appear:

    Cluster resource 'Analysis Services' created successfully.

  19. Click OK to close the message.
  20. Locate and then right-click Analysis Services in the resource list.
  21. Click Bring Online to make Analysis Services available.
  22. Exit Cluster Administrator.

Connect to the clustered Analysis Services by using the virtual server name

To do this, log on to the computer that has the Analysis Services client tools installed, and then follow these steps:

  1. Start Analysis Manager.
  2. Register the Analysis server by using the name of the virtual server.
  3. Expand the newly registered server. You will see the Foodmart 2000 sample database.
  4. Edit the data source of the Foodmart 2000 database to point to the clustered instance of SQL Server 2000.

Notes

  • About the local named instances of SQL Server 2000

After this process, you will have two local named instances of SQL Server 2000 left on your cluster node, one on each node. Do not attempt to remove those instances because Analysis Services is still dependent on the SQL Server services locally.

  • About installing a SQL Server service pack to the clustered Analysis Services

Because Analysis Services is not cluster aware, you cannot directly apply a service pack to the clustered service. You must uncluster it first, apply the service pack, and then cluster it again.

To do this, follow these steps:

    1. Remove Analysis Services from the cluster resource.
    2. Manually restore the repository back from the clustered instance of SQL Server 2000 to each local named instance of SQL Server 2000.
    3. Install the service pack on both the local instances and the clustered instance of SQL Server 2000.
    4. Repeat the clustering process as described above to regenerate the clustered service of Analysis Services with the service pack installed.


For more information about how to cluster Analysis Services, 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


I’d like to thank Lan for her hard work on this column. 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.

Keywords: kbhowto kbexpertiseadvanced KB916665



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/916665&oldid=220437’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki