How to cluster SQL Server 2000 Analysis Services in Windows 2000 and in Windows Server 2003

This article provides step-by-step instructions on how to set up SQL Server Analysis Services 2000 as a clustered service. Although Analysis Services is not a cluster aware application, you can install and configure Analysis Services as a generic service on a cluster.

When you follow the steps listed in this article, you can operate Analysis Services in a clustered environment, which provides a high availability solution for Analysis Services. The following assumptions are made for this Microsoft Knowledge Base article:

• The cluster is running on a Microsoft Windows 2000 Advanced Server-based computer, a Windows 2000 Datacenter Server-based computer, or a Windows Server 2003-based computer.
• The cluster group in which you configure Analysis Services always runs on the same node as the default cluster group, which contains the Cluster IP Address and Cluster Name.

Note This Microsoft Knowledge Base article refers to the node that currently has control of the cluster server virtual name, IP Address, and shared disk where you want to store your Analysis Services data files as Node1.

NoteThere are instructions for Analysis Services 64-bit installations in some sections under the "Analysis Services 64-bit installations" heading. The setup is different because Analysis Services 64-bit installations must have the repository and the query log saved on the SQL Server database. You can have two configurations for the computer that is running SQL Server that has the repository and the query log for Analysis Services 64-bit:

• You can configure the computer that is running SQL Server that stores the repository and the query log off the cluster. This configuration is the easiest to configure for Analysis Services because you only have to make connection string changes to point to the computer that is running SQL Server.
• You can configure the computer that is running SQL Server that stores the repository and the query log on the cluster. This configuration implies that this computer will also be clustered in an Active/Passive configuration. This configuration requires more steps for Analysis Services to be clustered.

Important SQL Server 2000 Analysis Services can only be set up for an Active/Passive configuration. Active/Active configurations of Analysis Services are not supported.

We assume the following about Analysis Services 64-bit installations:

SQL Server will be configured by using the second configuration method. SQL Server will be installed and set up as a virtual instance on the same cluster as Analysis Services before Analysis Services is installed. This virtual instance will be the only instance of SQL Server on the nodes. This instance of SQL Server is intended to store the repository and the query log for Analysis Services 64-bit.

Install Analysis Services on Node1

1. Run the Setup program located in the MSOLAP\Install folder on the SQL Server 2000 CD, or click SQL Server 2000 Components...Install Analysis Services on the Autorun menu that appears when you insert the SQL Server CD.
2. In the Welcome dialog box, click Next.
3. In the Software License Agreement dialog box, read the Licensing Agreement, and then click Yes to signify your acceptance of the agreement.
4. In the Select Components dialog box, make sure that you select all the components, and then accept the default location for the Destination Folder. Click Next.
5. In the Data Folder Location dialog box, click to select a folder on the shared disk resource of the cluster in which to store your Analysis Services data files. If the folder you want to use does not exist, the setup program prompts you to allow the setup program to create it for you. Click Yes, and then click Next.
6. In the Select Program Folder dialog box, click Next to accept the default location.
7. When the setup completes, click Finish to exit the setup program.

Analysis Services 64-bit installations

Note Make sure that a virtual instance of SQL Server is installed and clustered before you install Analysis Services. The installation of Analysis Services 64-bit is different than the installation of Analysis Services 32-bit, and you will see a tree that details SQL Server and Analysis Services.

To install Analysis Services 64-bit, follow these steps:

1. Click Start a New Installation, click Local Instance, and then click Next.
2. Click Next two times.
3. Click to clear the SQL Server check box so that only the Analysis Server product check box is selected.
4. Under Analysis Server, click Data Files .
5. Click Browse, locate the folder on the Shared disk resource of the cluster where you want to store your Analysis Services data files, and then click Next.
6. In the Instance Name dialog box, type the name of a temporary SQL Server named instance, and then click Next.
7. Accept the default settings for the remaining pages.

Set the Analysis Server service to manual startup on Node1

1. Open the Services management console. To open the Services management console, on the Taskbar click Start, point to Programs, point to Administrative Tools, and then click Services.
2. Locate the MSSQLServerOLAPService service in the list, and then double-click the MSSQLServerOLAPService service name to open the Properties dialog box.
3. On the General tab, change the Startup Type to Manual.
4. If the Service Status is Started, click Stop.
5. Click OK to close the Properties dialog box.
6. Close the Services management console.

Create domain OLAP Administrators group

Analysis Services requires that the OLAP Administrators group have access permissions to the shared directory where the Analysis Services repository resides. Because the shared directory must be available regardless of which node in the cluster is active, you will create this shared directory as a cluster share at a later step in the configuration process. The ACL list on cluster shares requires domain level accounts and you must create the OLAP Administrators group as a domain level group and manage OLAP Administrator membership through the domain level OLAP Administrator group.

1. Log onto a system with an account that has Domain Administrator privileges.
2. Use either Active Directory Users and Computers or User Manager for Domains and create a new domain level group named OLAP Administrators.
3. Add all the users who should be Administrators for Analysis Services to the OLAP Administrators group.
4. Close the Active Directory Users and Computers or User Manager for Domains.

Add domain OLAP administrators to Node1 OLAP Administrators group

1. On Node1, right-click My Computer, and then click Manage.
2. Expand the Local Users and Groups node located under System Tools.
3. Click the Groups folder.
4. Right-click the OLAP Administrators group located in the right-hand window, and then click Properties.
5. In the OLAP Administrators Properties dialog box, click Add.
6. In the Select Users or Groups dialog box, make sure you select the domain in which you created the OLAP Administrators group.
7. Locate the OLAP Administrators group in the list, and then double-click the name to add the OLAP Administrators group.
8. Click OK to close the Select Users or Groups dialog box.
9. Click OK to close the OLAP Administrators Properties dialog box.
10. Close the Computer Management console.

Move the Analysis Services Query Log database

Microsoft Analysis Services stores a sampling of the queries sent to the server in a Query Log database in Microsoft Access named Msmdqlog.mdb. The Usage Based Optimization Wizard uses the Msmdqlog.mdb database to tune cube performance. Microsoft Analysis Services uses a shared folder to allow access to the Msmdqlog.mdb database from remote systems that run Analysis Manager. This shared folder should be separate from the data folder; however, it should be on the same shared disk resource in the cluster.

1. Open Windows Explorer.
2. Select the same shared disk you chose for the data folder in the preceding "Install Analysis Services on Node1" section.
3. Create a new folder named AnalysisLog.
4. Navigate to the C:\Program Files\Microsoft Analysis Services\Bin folder.
5. Copy the Msmdqlog.mdb file from this directory to the AnalysisLog folder you created on the shared disk of the cluster.

Analysis Services 64-bit installations

By default, the query log is located in the instance of SQL Server that you selected when you set up Analysis Services 64-bit.

Make sure that the OLAPQueryLog database is copied from the local instance of SQL Server to the virtual instance of SQL Server and that the OLAPQueryLog database resides on the shared disk. You can copy the database by using any of the following methods:

• The backup and restore procedure of the SQL Server database
• The detach and attach procedure of the SQL Server database
• A Data Transformation Services (DTS) script to move the structure to the virtual instance of SQL Server from a 32-bit instance of SQL Server Enterprise Manager

Note You should still create the AnalysisLog folder on the shared disk because you must have this folder for other files.

Remove sharing from the MsOLAPRepository$ share on Node1

1. Navigate to the C:\Program Files\Microsoft Analysis Services\Bin folder.
2. Right-click the Bin folder, and then click Sharing.
3. Click to select the Do not share this folder option button.
4. Click OK to close the Bin Properties dialog box.
5. Close Windows Explorer.

Force a fail over

1. Start the Cluster Administrator console.
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 your OLAP data files and AnalysisLog directory, and then click Move Group.
5. Close the Cluster Administrator.

Install Analysis Services on Node2

1. Run the Setup program located in the MSOLAP\Install folder of the SQL Server 2000 CD, or click SQL Server 2000 Components...Install Analysis Services on the Autorun menu that appears when you insert the SQL Server CD.
2. In the Welcome dialog box, click Next.
3. In the Software License Agreement dialog box, read the Licensing Agreement, and then click Yes to signify your acceptance of the agreement.
4. In the Select Components dialog box, make sure that you select all the components, and then accept the default location for the Destination Folder. Click Next.
5. In the Data Folder Location dialog box, select the same folder on the shared disk resource of the cluster that you selected when you installed Analysis Services on Node1 of the cluster. Click Next.
6. In the Select Program Folder dialog box, click Next to accept the default location.
7. When the setup completes, click Finish to exit the setup program.

Analysis Services 64-bit installations

Note Make sure that a virtual instance of SQL Server is installed and clustered before you install Analysis Services. The installation of Analysis Services 64-bit is different than the installation of Analysis Services 32-bit, and you will see a tree that details SQL Server and Analysis Services.

To install Analysis Services 64-bit, follow these steps:

1. Click Start a New Installation, click Local Instance, and then click Next.
2. Click Next two times.
3. Click to clear the SQL Server check box so that only the Analysis Server product check box is selected.
4. Under Analysis Server, click Data Files .
5. Click Browse, locate the folder on the Shared disk resource of the cluster where you want to store your Analysis Services data files, and then click Next.
6. In the Instance Name dialog box, type the name of a temporary SQL Server named instance, and then click Next.
7. Accept the default settings for the remaining pages.

Set the Analysis Server service to manual startup on Node2

1. Open the Services management console. To open the Services management console, on the Taskbar click Start, point to Programs, point to Administrative Tools, and then click Services.
2. Locate the MSSQLServerOLAPService service in the list, and then double-click the MSSQLServerOLAPService service to open the Properties dialog box.
3. On the General tab, change the Startup Type to Manual.
4. If the Service Status shows Started, click Stop.
5. Click OK to close the Properties dialog box.
6. Close the Services management console.

Add domain OLAP administrators to Node2 OLAP Administrators group

1. On Node2, right-click My Computer, and then click Manage.
2. Expand the Local Users and Groups node located under System Tools.
3. Click the Groups folder.
4. Right-click the OLAP Administrators group in the right-hand window, and then click Properties.
5. Click Add in the OLAP Administrators Properties dialog box.
6. In the Select Users or Groups dialog box, make sure that you select the domain in which you created the OLAP Administrators group.
7. Locate the OLAP Administrators group in the list, and then double click the OLAP Administrators name to add this group.
8. Click OK to close the Select Users or Groups dialog box.
9. Click OK to close the OLAP Administrators Properties dialog box.
10. Close the Computer Management console.

Remove sharing from the MsOLAPRepository$ share on Node2

1. Open Windows Explorer.
2. Navigate to the C:\Program Files\Microsoft Analysis Services\Bin folder.
3. Right-click the Bin folder, and then click Sharing.
4. Click to select the Do not share this folder option button.
5. Click OK to close the Bin Properties dialog box.

Move the Analysis Services repository

1. In Windows Explorer, navigate to the Program Files\Microsoft Analysis Services\Bin folder.
2. Copy the Msmdrep.mdb file from the local drive on the cluster to the AnalysisLog folder on the shared disk of the cluster.
3. Close Windows Explorer.

Analysis Services 64-bit installations

You do not have to complete this section because the MDB file does not exist, and the repository is on the computer that is running SQL Server. You must move the OLAPRepository database to the virtual instance of SQL Server by using one of the following methods:

• The backup and restore procedure
• The detach and attach procedure
• DTS copy

Create a cluster share in the AnalysisLog folder

1. Open the Cluster Administrator console. To open the Cluster Administrator console, on the Taskbar 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, and then click New...Resource.
4. In the New Resource dialog box, type the following for the resource name:
   
   "MsOLAPRepository$" (without the quotation marks)
5. For the description, type:
   
   "Analysis Services Share" (without the quotation marks)
6. Change the resource type to File Share.
7. Change the group to be the same group that contains the shared disk in which you want to store the Analysis Services data files.
8. Click Next.
9. In the Possible Owners dialog box, make sure both nodes are listed in the possible owners list.
10. Click Next.
11. In the Available Resources list of the Dependencies dialog box, select the physical disk that contains the OLAP data files.
12. Click Add to move the physical disk to the Resource Dependencies list.
13. Click Next.
14. In the File Share Parameters dialog box, for the share name type:
    
    "MsOLAPRepository$" (without the quotation marks)
15. Type the path to the AnalysisLog folder you created in the "Create Folder for Query Log" step.
16. Click Permissions to configure permissions for the share.
17. In the Permissions dialog box, click Add.
18. In the Select Users or Groups dialog box, make sure you have selected the domain in which you created the OLAP Administrators group.
19. Locate the OLAP Administrators group in the list, and then double-click the name to add the OLAP Administrators group.
20. Locate the Domain Admins group in the list, and then double-click the name to add the Domain Admins group.
21. Click OK to close the Select Users or Groups dialog box.
22. In the Permissions dialog box, click the Domain Admins group, and then select the Allow check box for full control.
23. In the Permissions dialog box, click on the OLAP Administrators group, and then select the Allow check box for full control.
24. In the Permissions dialog box, click the Everyone group, and then click Remove to remove the Everyone group from the permissions on the share.
25. Click OK to close the Permissions dialog box.
26. Click Finish to complete the New Resource wizard. The following message should appear:
    Cluster resource 'MsOLAPRepository$' created successfully.
27. Click OK to close the Message dialog box.
28. Locate the MsOLAPRepository$ in the resource list, and then right-click MsOLAPRepository$.
29. Click Bring Online to make the cluster share available.
30. Close the Cluster Administrator console.

Modify the registry keys for the Analysis server on Node2

Microsoft Analysis Services reads numerous registry keys to obtain information about the configuration of the Analysis Server. You must modify some of the registry keys to reflect the fact that the server is running on a cluster.

1. Run the Registry Editor utility (Regedt32.exe).
2. Navigate to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Server Connection Info registry key.
3. The "RemoteRepositoryConnectionString" value contains the name of the node for the data source. Change the "RemoteRepositoryConnectionString" value to the name of the virtual server. For example:
   
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\MyClusterNode2\MsOLAPRepository$\Msmdrep.mdb
   
   Becomes:
   
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ClusterServerVirtualName\MsOLAPRepository$\Msmdrep.mdb
   
   Analysis Services 64-bit installations
   
   From a computer that is running the 32-bit version of Analysis Manager, right-click the server name, and then clickModify Repository Connection String. You can modify the repository connection string to point to the virtual instance of SQL Server for both the Repository connection string and the Remote repository connection string. If the Analysis server computer is not running when you try to make these changes, you receive the following error message:
   �Automation Error�
   The following is an example of a connection string:

   Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial
   				Catalog=OLAPRepository;Data Source=vsql64cluster;Use Procedure for
   				Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AP135949;Use
   				Encryption for Data=False;Tag with column collation when possible=False 

4. The "Repository Connection String" value contains the path in which the binaries for the Analysis Services product were originally installed. Change the "Repository Connection String" value to point to the new location selected in the "Move Analysis Services Repository" section of this article. For example:
   
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Analysis Services\Bin\Msmdrep.mdb
   
   Becomes:
   
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Q:\AnalysisLog\Msmdrep.mdb
   Assuming Q:\ is the drive where the AnalysisLog directory was created.
   
   Analysis Services 64-bit installations
   
   You made this change in step 3.
5. The "Locks Directory" value contains the path in which the binaries for the Analysis Services product were originally installed. Change the "Locks Directory" value to point to the new location selected in the "Move Analysis Query Log" section of this article. For example:
   
   C:\Program Files\Microsoft Analysis Services\Bin
   
   Becomes:
   
   Q:\AnalysisLog
   Assuming that Q:\ is the drive in which the AnalysisLog directory is created.
6. Navigate to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\ CurrentVersion key.
7. The data source in the QueryLogConnectionString contains the path in which the binaries for the Analysis Services product were originally installed. You must change the data source to point to the new AnalysisLog directory. For example:
   
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Analysis Services\Bin\Msmdqlog.mdb
   
   Becomes:
   
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Q:\AnalysisLog\Msmdqlog.mdb
   Assuming Q:\ is the drive in which the AnalysisLog directory was created.
   
   Analysis Services 64-bit installations
   
   Modify this connection string to point to the virtual instance of SQL Server. The following is an example of a connection string:

   Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial
   				Catalog=OLAPQueryLog;Data Source=vSQL64cluster;Use Procedure for Prepare=1;Auto
   				Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column
   				collation when possible=False

8. The data source in the RemoteQueryLogConnectionString value contains the name of the node. You must change the data source to the virtual server name of the cluster. For example:
   
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\MyClusterNode2\MsOLAPRepository$\Msmdqlog.mdb
   
   Becomes:
   
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ClusterServerVirtualName\MsOLAPRepository$\Msmdqlog.mdb
   
   Analysis Services 64-bit installationsModify this connection string to point to the virtual instance of SQL Server. The following is an example of a connection string:

   Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial
   				Catalog=OLAPQueryLog;Data Source=vSQL64cluster;Use Procedure for Prepare=1;Auto
   				Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column
   				collation when possible=False

9. Close the Registry Editor.

Note If you are running SQL Server 2000 Analysis Services Service Pack 3 or a later version to modify the RemoteRepositoryConnectionString, follow these steps:

1. Click the server name in Analysis Services.
2. Click Modify Repository Connection String.

The Edit Repository Connection String dialog box appears. Now, you can modify the connection string.

Configure Analysis Services service as a generic service

1. Open the Cluster Administrator console. To open the Cluster Administrator console, on the Taskbar 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, and then click New...Resource.
4. In the New Resource dialog box, for the resource name type:
   
   "Analysis Services" (without the quotation marks)
5. For the description, type:
   
   "Analysis Services" (without the quotation marks)
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 the cluster IP address and Name.
8. Click Next.
9. In the Possible Owners dialog box, make sure that both nodes are listed in the Possible Owners list, and then click Next.
10. In the Available Resources list in the Dependencies dialog box, click to select the MsOLAPRepository$ share.
11. Click Add to move the MsOLAPRepository file share to the Resource dependencies list.
12. Click Next.
13. In the Generic Service Parameters dialog box, type the following as the Service Name:
    
    "MSSQLServerOLAPService" (without quotation marks)
14. In the Start Parameters text box, type:
    
    "net start MSSQLServerOLAPService" (without quotation marks)
15. Click Next.
16. In the Registry Replication dialog box, click Add, and then type the following in the Root registry key entry text box:
    
    "Software\Microsoft\OLAP Server" (without quotation marks)
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 dialog box.
20. Locate, and then right-click Analysis Services in the resource list.
21. Click Bring Online to make the Analysis Server available.
22. Close the Cluster Administrator console.

Troubleshooting

Because Analysis Services is not cluster aware, the following pitfalls exist if Analysis Services is clustered and you use the steps listed in this Microsoft Knowledge Base article.

• Registry replication synchronizes the memory settings for the Analysis Server, which may be a problem if the two nodes in the cluster have different amounts of RAM.
• Although it is possible to administer and query the Analysis Server by using the name of the currently active node on the cluster, you should not do this. You must perform all administration and querying by using the cluster server name.
• Analysis Manager registers the Analysis Server by using the machine name of the node. You must remove this server registration and then register the cluster server name.
• Analysis Manager stores all server registrations in the registry. Registry replication synchronizes the registered servers on the two nodes of the cluster. Therefore, you must perform any new server registrations in Analysis Manager on the currently active node of the cluster.
• Because Analysis Services is not cluster aware, Microsoft Product Support Services may ask you to uncluster Analysis Services when working with you to troubleshoot Analysis Services issues.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

See the following white paper on the following Microsoft Web site: