Clustering and SQL Server 2005 SP1 Issue

[acarruth]

Hi,

We have one XI R2 SP2 server up and running, and it is leveraging a database on a separate SQL Server 2005 SP1 machine for the CMS DB. We now have a second machine, which is identical to the first in terms of hardware, OS, and XI. I created an ODBC DSN on server 2, which matches what is created on server 1 (same name, uses same credentials, same driver). When I test the connection in the ODBC setup screen, it says it is successful. However, when I point server 2's CMS to this DSN, and then start it, it fails saying (in the Event Viewer):

Event Type:   Error 
Event Source:   BusinessObjects_CMS 
Description: 
The root server reported an error Initialization Failure. (Reason: Database access error. Reason [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "CMS_IdNumbers5" because it does not exist or you do not have permissions..).

Has anyone experienced this before? It seems as though only one connection at a time can access the SQL Server database?

Windows 2003 Server SP1
SQL Server 2005 SP1 (9.00.2047)
BOE XI R2 SP2 RTM
SQL Server ODBC Driver 2000.86.1830.00

Thanks!
Allan

 

[MJRBIM]

When you installed the CMS on Server-2 did you configure it to be part of the Server-1 CLUSTER during the set-up prompts...?

 

[acarruth]

No, XI R2 SP2 was already installed when I got here, but with the services down. It mirrors the first server, though. I imagine the end result would be the same as it would use the ODBC DSN of the same name.. do you think it would act differently?

I wonder if FP2.4 would help, hmm..

 

[MJRBIM]

Yes, it would act differently.

You can't just change the ODBC - you need to configure the CMS on Server-2 to be part of the Server-1 CLUSTER the set-up prompts...

You might need to uninstall the CMS on Server-2, and reinstall it as a part of the Server-1 CLUSTER.

 

[acarruth]

Got it.

Turns out the DBA removed CREATE/ALTER TABLE priveleges from the account used to connect to the database. When a new CMS joins the cluster, it needs to add another column to CMS_IdNumbers5. Temporarily giving the account the dbo role allowed the clustering to happen.

By the way, clustering the CMS via the installation program is no different than adding in a previously installed CMS later on. They both require you to select the CMS database that is already in use. Once the new CMS reads the existing database, it performs steps to join the cluster.

Thanks for your help,
Allan

 

[AndrewBaines]

The CMS needs to be clustered, not just pointing at the same DB. Database corruption here we come!
You can change the 2nd server to be part of the cluster, but it's probably easier and quicker just to reinstall.

Andrew Baines

http://www.pursuittechnology.co.uk

 

[acarruth]

Andrew,

It is the process of having the second CMS connect to the system database of the first CMS that initiates the clustering. This is well documented in the deployment guides. Refer to page 117 of the "Business Objects Deployment and Configuration Guide" (xir2_bip_deploy_en.pdf).

[b]To add an installed CMS to a cluster on Windows[/b]

[ol]
[li]Use the CCM to stop the independent Central Management Server.[/li]
[li]With the CMS selected, click Specify CMS Data Source on the toolbar.[/li]
[li]Click Select a Data Source; then click OK.[/li]
[li]In the Select Database Driver dialog box, specify whether you want to connect to the production CMS database through ODBC, or through one of the native drivers.[/li]
[li]Click OK.[/li]
[li]The remaining steps depend upon the connection type you selected:[/li]
[ol i]
[li]If you selected ODBC, the Windows “Select Data Source” dialog box appears. Select the ODBC data source that corresponds to your production CMS database; then click OK. If prompted, provide your database credentials and click OK. The CCM connects to the database server and adds the new CMS to the cluster.[/li]
[li]If you selected a native driver, you are prompted for your database Server Name, your Login ID, and your Password. Once you provide this information, the CCM connects to the database server and adds the new CMS to the cluster. The SvcMgr dialog box notifies you when the CMS database setup is complete.[/li][/ol]
[li]Click OK.[/li]
[li]Start the Central Management Server.[/li]
[/ol]

If the second CMS starts up successfully, then it has successfully clustered with the servers that already leverage the system database to which it connected. This process will not corrupt the database, as you say. However, if the file repository servers of the second server are not configured to point to the same file store as the first server (typically on a SAN or NAS), they will not be able to properly serve up the requested report templates and instances.

Thanks,
Allan